Database Reference
In-Depth Information
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 662 15-Oct-07 20:20:20 16 5.1 contention customer
End Snap: 663 15-Oct-07 20:24:30 17 6.1 contention customer
Elapsed: 4.17 (mins)
…
Top 10 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read 3,109 89 29 39.7
CPU time 59 26.3
control file sequential read 532 23 43 10.1
db file parallel write 191 17 87 7.4
db file scattered read 580 9 15 3.8
buffer busy waits 12,530 7 1 3.2
read by other session 420 4 8 1.6
os thread startup 5 3 597 1.3
log file sync 59 3 48 1.3
SQL*Net more data to client 88,397 3 0 1.2
…
Wait Events DB/Inst: TEN/ten Snaps: 662-663
…
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
db file sequential read 3,109 0 89 29 97.2
…
latch: cache buffers chains 53 0 0 5 1.7
…
In the five-minute interval covered by the Statspack report, a meager 165 ms were spent
waiting for the cache buffers chains latch. What about individual sessions that are affected by
this wait event? These are identified by querying
V$SESSION_EVENT
.
SQL> SELECT s.sid, s.serial#, p.spid, e.event, e.total_waits, e.time_waited
FROM v$session s, v$process p, v$session_event e
WHERE s.paddr=p.addr
AND s.sid=e.sid
AND s.type='USER'
AND e.event LIKE 'latch%'
ORDER BY time_waited;
SID SERIAL# SPID EVENT TOTAL_WAITS TIME_WAITED
--- ------- ---- --------------------------- ----------- -----------
27 769 4400 latch: cache buffers chains 49 0
27 769 4400 latch: library cache pin 3 0
32 1921 5232 latch: library cache pin 29 0
