Database Reference
In-Depth Information
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE | T |
| 2 | TABLE ACCESS BY INDEX ROWID| T |
| 3 | INDEX UNIQUE SCAN | T_PK |
---------------------------------------------
To further investigate the buffer busy waits , you can execute Snapper once again, as in the following example,
but this time execute it with a set of parameters to show detailed information about the buffer busy waits . Note that
for buffer busy waits , the parameters associated to the event have the following meaning: p1 is the file number (4),
p2 is the block number (836,775), and p3 is the class of the block (1 = data block) that experienced the waits.
SQL> @snapper.sql ash=event+p1+p2+p3 1 1 user=chris
-----------------------------------------------------------------------------
Active% | EVENT | P1 | P2 | P3
-----------------------------------------------------------------------------
560% | buffer busy waits | 4 | 836775 | 1
190% | ON CPU | | |
20% | latch: cache buffers chains | 1992028296 | 155 | 0
20% | latch: In memory undo latch | 1966009168 | 251 | 0
10% | latch: In memory undo latch | 1966009648 | 251 | 0
In summary, the analysis with Snapper pinpoints exactly the same problem as was identified with SQL Trace.
Solutions
By identifying the SQL statement, the block class, and the segment that experienced the waits, it should be possible to
identify the root cause of the problem. Let's discuss some typical cases for common block classes.
Contention for Data Blocks
All the blocks that make up a table or index segments that are not used for storing metadata (for example, segment
headers) are called data blocks . Contention for them has two main causes. The first is the very high frequency of
data block accesses on a given segment. The second is the very high frequency of executions. At first glance, both are
the same thing. Why they're, in fact, different requires some explanation. In the first case, the problem is inefficient
execution plans causing frequent data block accesses of the same blocks. Usually, it's because of inefficient related-
combine operations (for example, nested loops joins). In this case, even two or three SQL statements executed
concurrently might be enough to cause contention. Instead, in the second case, the problem is the very high
frequency of the execution of several SQL statements accessing the same block at the same time. In other words, it's
the number of SQL statements executed concurrently against (few) blocks that's the problem. It might be that both
happen at the same time. If this is the case, take care of solving the first problem before facing the second one. In fact,
the second problem might disappear when the first is gone.
To solve the first problem, SQL optimization is necessary. An efficient execution plan must be executed in place
of the inefficient one. Of course, in some situations, that is easier said than done. Nevertheless, this is really what you have
to achieve.
 
Search WWH ::




Custom Search