Database Reference
In-Depth Information
I also processed the SQL trace file with ESQLTRCPROF. It gave hundreds of warnings like
the following:
Warning: WAIT event 'direct path read' for cursor 4 at line 5492 without prior
PARSING IN CURSOR #4 - ignored for per statement response time accounting
Warning: WAIT event 'direct path write' for cursor 4 at line 5497 without prior
PARSING IN CURSOR #4 - ignored for per statement response time accounting
The warning indicates that there is a problem with response time accounting for cursor 4.
The resource profile from ESQLTRCPROF is shown here:
ORACLE version 10.2 trace file. Timings are in microseconds (1/1000000 sec)
Resource Profile
================
Response time: 5.627s; max(tim)-min(tim): 74.957s
Total wait time: 50.974s
----------------------------
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
-------- ------ ------------ ---------- -----------------------------------
26.153s 464.76% 7135 0.003665s db file sequential read
13.234s 235.17% 8010 0.001652s direct path read
3.232s 57.43% 5 0.646382s log file switch completion
2.604s 46.28% 8507 0.000306s SQL*Net message from client
2.312s 41.08% 71 0.032562s think time
1.768s 31.41% 8 0.220956s log file switch (checkpoint incomplete)
0.690s 12.27% 45619 0.000015s direct path write
0.516s 9.18% 86 0.006004s log file sync
0.434s 7.72% 32182 0.000013s SQL*Net more data from client
0.313s 5.55% 1033 0.000303s total CPU
0.030s 0.53% 8578 0.000003s SQL*Net message to client
0.001s 0.02% 10 0.000109s latch: shared pool
0.000s 0.00% 1 0.000207s latch: library cache
-45.659s -811.39% unknown
--------- ------- -----------------------------------------------------------
5.627s 100.00% Total response time
Total number of roundtrips (SQL*Net message from/to client): 8578
CPU usage breakdown
------------------------
parse CPU: 0.05s (313 PARSE calls)
exec CPU: 0.25s (379 EXEC calls)
fetch CPU: 0.02s (341 FETCH calls)
