10G R2 Grid Control Hang Analyis Feature

Creating the Contention Problem
To show you how the Hang Analysis feature works in 10G R2 Grid Control, I created a table called FOOT. EMP and inserted a row that contains the value 'abc' in the column titled COL1 (I've never been known for my creativity in my naming conventions). I created two SQL*PLUS sessions that used the following statement to update the same row in the table:

UPDATE foot.emp SET col1='123' where col1='abc'

We know that Oracle locks at the row level. The first session that executed the update statement will hold the row level lock until I execute a COMMIT or a ROLLBACK. The second session I created is unable to obtain the row level lock until the first session commits or rolls back its unit of work. We have successfully created our first deadlock.

Problem Analysis
Let's begin our problem determination by navigating to 10G R2's Hang Analysis screen. We accomplish this by selecting the 'Hang Analysis' link displayed on the bottom of 10G R2 Grid Control's Performance Administration Home page. 10G EM R2 responds by displaying the Hang Analysis Home page.

The Hang Analysis home page displays a graphical representation of the blocking and blocked session. The blocking session is shown at the top of the diagram, while the blocked session is shown at the bottom. This simple graphic representation may not seem very worthwhile to you now, but the benefits become readily apparent when you the contention problem becomes more complex. The more complex the locking problem, the greater benefit the Hang Analysis diagram provides. I'll show you some complex blocking diagrams later in this blog.

We can see on the diagram that the blocking session has an ID of 128. When we click on the block, 10G R2 Grid Control responds by displaying the Session Summary Page. The Session Summary Page provides basic information pertaining to the session. The panel also provides a 'View Session Details' link on the center of the panel.

Clicking on the link displays the Session Details page. This page contains a wealth of information on the session. Let's review some of the most important information provided on this page:

Server

• Status of the server process (in our case inactive).
• Serial number of the server process.
• USERID of the server process. I know I'm using SYS, but it is my own little toy database.
• OS Process ID.
• Logon time and duration.
• Connection Type (our connection is dedicated).

Contention

Since session 128 is the blocking process, there is no contention information to be displayed. We'll take a look at the blocked session contention information later in this blog.

Client Information

• OS user name - Name of the account that is running the Oracle client software (in our case SQLPLUS).
• Host - Name of the machine that is running the client software.

Wait Event

• Current Wait Event - What resource the session is currently waiting for. In our case, we are waiting from a response from the client. It is expecting me to either execute another SQL Statement or perform a COMMIT or ROLLBACK. Until I perform the COMMIT or ROLLBACK, session 128 will continue to block session 118.
• Wait Duration - How long the session has been waiting.

Application

• Current SQL - This column is blank because the SQL statement I executed has completed processing.
• Current SQL Command - This column is also blank because of the reason stated above.
• Previous SQL - If I click on the link, Oracle will display the SQL Details page for this statement. The page displays the SQL statement, performance statistics, access path information and also provides a link to run the SQL Tuning advisor.
• Program - The application program that executed the SQL statement (in our case SQLPLUS)

The Session Details page also provides a series of tabs across the top. Since we already found most of the information we need to determine what work session 128 is performing, let's click on the Blocking Tree tab at the top of the page. 10G R2 Grid Control responds by displaying the Blocking Tree panel. This panel provides information on the problem session 128 is creating. The panel displays session 128 at the top of the hierarchy stating that it is the blocking session and displays session 118 below it telling us that it is being blocked by session 128. The output panel provides links to view session 118's SQL statement and also displays wait information for both sessions.

Let's take a look at the wait information for session 118. It shows that it is experiencing row lock contention. Lastly, the Blocking Tree panel contains a 'Kill Session' navigation button that will allow us to kill one, or both, sessions.

We'll conclude our investigation of session 128 by reviewing wait history information. We accomplish this by clicking on the 'Wait Event History' tab at the top of the Session Details page. 10G R2 Grid Control responds by displaying the Wait Event History page. The Wait Event History page displays a running history of all the events that session 128 waited for during its execution life cycle.

Continuing our Analysis
Let's continue our problem determination by reviewing session 118, which we now know is being blocked by session 128. I start the analysis by navigating back to the Hang Analysis Home page and clicking on the green block titled '118' at the bottom of the panel. 10G R2 responds as it did previously by displaying session 118's Session Summary page. Nothing out of the ordinary here, so let's continue. I'll use the 'View Session Details' navigation link to ask 10G R2 Grid Control to display session 118's Session Detail page.

Session 118's Session Detail page contains much of the same information as session 128's did except for a few VERY important details. We see that session 118's Contention column shows that it is being blocked by session 128 and it displays the file that contains the row that both sessions are attempting to update.

The Wait column for session 118 also shows the resource that it is waiting for. In this case, it shows that it is experiencing row lock contention on a row contained in the FOOT. EMP table.

If I click on 'Current SQL' link on the top right hand side of the page, 10G R2 Grid Control will respond by displaying the SQL Details page for this statement. The page displays the SQL statement, performance statistics, access path information and also provides a link to run the SQL Tuning advisor.

We'll conclude our investigation of session 118 by reviewing wait history information. We accomplish this by clicking on the 'Wait Event History' tab at the top of the Session Detail page. 10G R2 Grid Control responds by displaying the Wait Event History page. The Wait Event History page displays a running history of all the events that session 118 waited for during its execution life cycle. Notice that the information displayed for session 118 is quite different than for session 128. The information shows that session 118 is experiencing row lock contention.

Solving the Problem
We can easily solve this problem by executing a COMMIT or ROLLBACK command in session 128 or killing the process which will automatically roll back the work it has performed. Problem Solved!

More Complex Problems
We have already reviewed a simple graphical lock diagram that showed one session blocking another. Let's try something more complex and see how 10G R2 Grid Control responds.

I created a third session and executed the same SQL that the previous two sessions did. Here is the new graphical lock diagram for the three sessions. Notice that we now have two blocked sessions.

Let's finish this exercise by increasing the complexity once again. I created another table called FOOT. DEPT that contains one row. I created a fourth session and executed the following two statements:

UPDATE foot.dept SET col1='123' where col1='abc'
Notice that this is updating my new table.

UPDATE foot.emp SET col1='123' where col1='abc'
Updating the same row that session 128 is holding the row lock on.

I created one more session and executed this statement:

UPDATE foot.dept SET col1='123' where col1='abc'
Notice that this is also updating my new table.

Let's take a look at 10G R2 Grid Control's blocking diagram. Our first session (session 128) is blocking our fourth session (session 149). But session 149 is also holding a lock on our new FOOT.DEPT table, which our fifth session (session 104) is waiting for.

We have created a situation where the fourth session is being blocked and is also blocking other sessions.

Summary
I think you'll agree that 10G R2 Grid Control's Hang Analysis feature will provide great benefits when debugging contention problems.

Thanks for reading!

Chris