Database Reference
In-Depth Information
Based on this information, you can find out the name of the segment on which the waits occurred with a query
like the following (be careful, as executing this query might be resource-intensive):
SQL> SELECT owner, segment_name, segment_type
2 FROM dba_extents
3 WHERE file_id = 4
4 AND 836775 BETWEEN block_id AND block_id+blocks-1;
OWNER SEGMENT_NAME SEGMENT_TYPE
----- ------------ ------------
CHRIS T TABLE
In summary, the whole analysis provided the following information:
•
The SQL statement that experienced the waits is an
UPDATE
statement.
The waits occurred, most of the time, on a single data block.
•
•
The segment on which the waits occurred is the table on which the
UPDATE
statement was
executed.
Using Snapper
With Snapper, you can aggregate the data according to several criteria. But, provided you already identified the
session(s) you have to troubleshoot, you probably start by executing a command like the one shown in the following
example. In this case, I specified to target all the sessions executing the
buffer_busy_waits.sql
script. As the output
shows, not only is one SQL statement (
9bjs886z43g7k
) consuming most of the database time (during the sampling
period, it has seven active sessions in total), but in doing so, it experiences a lot of
buffer busy waits
.
SQL> @snapper.sql ash=sql_id+wait_class+event 1 1 user=chris
-------------------------------------------------------------------
Active% | SQL_ID | WAIT_CLASS | EVENT
-------------------------------------------------------------------
560%
|
9bjs886z43g7k
| Concurrency |
buffer busy waits
100%
|
9bjs886z43g7k
| ON CPU | ON CPU
40% | 091f2847g34rm | ON CPU | ON CPU
20% | 48gc5511n38a1 | ON CPU | ON CPU
20%
|
9bjs886z43g7k
| Concurrency | latch: cache buffers chains
20%
|
9bjs886z43g7k
| Concurrency | latch: In memory undo latch
10% | 93053g60rwz0x | ON CPU | ON CPU
10% | | ON CPU | ON CPU
10% | cktrdz5u39r04 | ON CPU | ON CPU
10% | 93053g60rwz0x | Concurrency | latch: In memory undo latch
Then, to get the text and the execution plan of the problematic SQL statement, you can use the
dbms_xplan
package. As expected, it's the same
UPDATE
statement as the one identified in the previous section.
SQL> SELECT * FROM table(dbms_xplan.display_cursor('9bjs886z43g7k', 0, 'basic'));
UPDATE /*+ index(t) */ T SET D = SYSDATE WHERE ID = :B1 AND N10 = ID
