Database Reference
In-Depth Information
32 1921 5232 latch: cache buffers chains 129 1
32 1921 5232 latch: library cache 86 1
27 769 4400 latch: library cache 35 1
SQL> ORADEBUG SETOSPID 5232
Oracle pid: 18, Windows thread id: 5232, image: ORACLE.EXE (SHAD)
SQL> ORADEBUG EVENT 10046 trace name context forever, level 8
Statement processed.
SQL> ORADEBUG TRACEFILE_NAME
c:\programs\oracle\product\admin\ten\udump\ten_ora_5232.trc
Since session (
SID
) 32 with operating system process identifier (
SPID
) 5332 had the highest
amount of waits for
latch: cache buffers chains
, I decided to trace this session at level 8 using
ORADEBUG
(see Chapter 37). Then I generated a resource profile from the trace file using the free
extended SQL trace profiler ESQLTRCPROF, included with this topic.
C:> esqltrcprof.pl ten_ora_5232.trc
ORACLE version 10.2 trace file. Timings are in microseconds (1/1000000 sec)
Resource Profile
================
Response time: 43.387s; max(tim)-min(tim): 58.216s
Total wait time: 26.738s
----------------------------
Note: 'SQL*Net message from client' waits for more than 0.005s
are considered think time
Wait events and CPU usage:
Duration Pct Count Average Wait Event/CPU Usage/Think Time
-------- ------ ------------ ---------- -----------------------------------
22.439s 51.72% 188693 0.000119s SQL*Net message from client
16.250s 37.45% 188741 0.000086s total CPU
1.124s 2.59% 2 0.561751s log file switch (checkpoint incomplete)
0.774s 1.78% 3 0.257905s think time
0.642s 1.48% 362 0.001774s enq: TX - index contention
0.625s 1.44% 4543 0.000138s buffer busy waits
0.569s 1.31% 188696 0.000003s SQL*Net message to client
0.399s 0.92% unknown
0.329s 0.76% 4 0.082256s log file switch completion
0.170s 0.39% 5 0.034085s log buffer space
0.057s 0.13% 4 0.014250s db file sequential read
0.005s 0.01% 1 0.004899s log file sync
0.002s 0.00% 20 0.000093s latch: library cache
0.001s 0.00% 13 0.000056s latch: cache buffers chains
0.001s 0.00% 10 0.000054s latch free
0.000s 0.00% 28 0.000015s buffer deadlock
0.000s 0.00% 10 0.000022s latch: cache buffers lru chain
0.000s 0.00% 1 0.000101s latch: library cache pin
--------- ------- -----------------------------------------------------------
