Lab128 Screenshots (2).

Locks and Locked Objects.

The Locks and Locked Objects window provides details about locking activity. This data originates from the v$lock view. When an object ID is supplied, Lab128 internally fetches the name and type of locked objects. It also highlights the blocking and waiting sessions, helping you make a decision on how to resolve the locking conflict.

Lab128 Locks and Locked Objects window

In this picture, session 156 is holding two locks: TM and TX type. Another session, SID=139, is holding two TM locks and is trying to get an exclusive TX lock held by session 156. Therefore it is blocked by session 156 and this is indicated in the column "Blocked by". Yet another session, SID=152, is trying to get an exclusive TX lock held by session 139, therefore it is blocked by 139. Decoding highlight color, session 156 is a Blocker, while session 139 is a Blocker and Waiter at the same time. Session 152 is a Waiter. TM locks can be linked to the database object; in this case there are two tables involved ADM_A and ADM_B. From this point, check the intended activity of contending sessions by drilling-down to the Session Details window using links in the SID column.

SQL Explorer window

SQL Explorer is another very powerful tool especially for Oracle 10gR2 and up. It helps to find the most expensive queries at any time interval and helps explore individual SQL statement to the finest detail. SQL Explorer works by taking snapshots of v$sql view (or v$sqlstats on 10gR2) and calculating deltas between snapshots. If snapshots are taken frequently, a grand picture of SQL statements performed at any moment in time can be reconstructed with high precision. Every important SQL statistic such as number of gets, number of reads, elapsed time, CPU time, etc. are recorded and can be used to reveal the SQL statements responsible for spending the server's resources.

Lab128 SQL Explorer window

In this picture, we are exploring the spike in CPU usage. To get details about this spike, the period of time is selected. The tabular view shows SQL statistics ordered by CPU time in descending order. The SQL statement in the first line (qu4m6ydqvk28j) is a top CPU consumer. The graphical chart of this individual statement correlates with the observed spikes, confirming that it was the cause. From the tabular view, we can conclude that there was a series of 8652 executions, there were no physical reads, and all blocks have been accessed from cache (block buffers). The number of parses (8652) suggests that the cursor was opened and closed for each execution. These happened to be soft parses because this statement was using bind variables. The ASH section provides additional info: which sessions were executing this statement and all other informations provided by ASH collection.

SQL Statement History Details

The detailed history of individual SQL statements is available for the analysis. This picture show how the query was executed in the past. Data is available in the graphical form and as numbers.

Lab128 SQL Statement History window

To see more screenshots: