Database Reference
In-Depth Information
UPDATE /*+ index(t) */ T SET D = SYSDATE WHERE ID = :B1 AND N10 = ID
Call Count Misses CPU Elapsed PIO LIO Consistent Current Rows
------- ------ ------ ----- ------- --- ------ ---------- ------- ------
Parse 1 1 0.001 0.000 0 0 0 0 0
Execute
10,000
1 2.410 6.186 0 73,084 43,797 29,287 10,000
Fetch 0 0 0.000 0.000 0 0 0 0 0
------- ------ ------ ----- ------- --- ------ ---------- ------- ------
Total 10,001 2
2.411 6.187
0 73,084 43,797 29,287 10,000
Since the CPU time is only 39% of the response time, it's necessary to look at the waits that occurred during
processing to find out how the time was spent. The following excerpt shows precisely that information. You can see
that the greatest consumer is, with 3.322 seconds,
buffer busy waits
. Also notice that, in this specific case, there's
minimal contention for the cache buffers chains latch.
Total Number of Duration per
Component Duration % Events Event
--------------------------- -------- ------- --------- ------------
buffer busy waits
3.322
53.843 10,953 0.000
CPU 2.410 39.056 n/a n/a
latch: In memory undo latch 0.278 4.509 6,389 0.000
latch: cache buffers chains 0.158 2.559 6,238 0.000
recursive statements 0.001 0.016 n/a n/a
enq: HW - contention 0.000 0.008 1 0.000
Disk file operations I/O 0.000 0.007 1 0.000
latch free 0.000 0.001 1 0.000
--------------------------- -------- -------
Total 6.170 100.000
The additional information provided by TVD$XTAT and missing in the TKPROF output is a list containing the
blocks on which the waits occurred. The following excerpt shows what such a list looks like in the case being analyzed
here. You can see that more than 99% of the
buffer busy waits
occurred on block 836,775 in file 4. Also notice that
the block experiencing contention is a data block.
File Block Total Number of Duration per
Number Number Duration % Events % Event Class
------ ------- -------- ------- --------- ------- ------------ ---------------
4 836,775
3.290
99.045
9,779 89.281 336
data block
(1)
3 272 0.006 0.173 196 1.789 29 undo header (35)
3 192 0.003 0.094 108 0.986 29 undo header (25)
3 128 0.003 0.094 117 1.068 27 undo header (17)
3 256 0.003 0.090 122 1.114 24 undo header (33)
3 144 0.003 0.088 88 0.803 33 undo header (19)
3 208 0.003 0.083 99 0.904 28 undo header (27)
3 240 0.003 0.083 112 1.023 24 undo header (31)
3 176 0.003 0.082 105 0.959 26 undo header (23)
3 224 0.003 0.081 107 0.977 25 undo header (29)
...
------ ------- -------- ------- --------- ------- ------------
Total 3.322 100.000 10,953 100.000 303
