Hi List,

I'm having a problem with high CPU usage that has escalated during the
last week. The SQL causing this is already tuned to the best possible with
the current design.

When I look at a tkprof summary of the trace file, it shows high 'latch
free' waits compared to the total CPU usage. Since latch gets consume CPU,
are they included under the 'CPU used by this session' statistic?

For instance, the tkprof summary below shows 1.86 seconds of CPU, and a
'latch free' wait time of 1.10 seconds. Does this mean that 59% (1.10 /
1.87) of the CPU resources used by this query comes from latch gets? Just
so I can be sure if this problem is caused by latch contention.

My first guess would be that when a process goes to sleep from consequtive
latch misses, the wait time will increase but not the 'CPU used'
statistic? If so, is there any way to know how much CPU resources my latch
misses are consuming?

I'm running 9.2.0.6.

call   count     cpu   elapsed     disk    query   current    
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse     1    0.00     0.00       0       0       0    
  0
Execute    1    0.01     0.00       0       0       0    
  0
Fetch     49    1.86    19.40       0    38779       0    
483
------- ------ -------- ---------- ---------- ---------- ----------
----------
total     51    1.87    19.41       0    38779       0    
483

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 40

Elapsed times include waiting on following events:
Event waited on                   Times  Max. Wait Total Waited
----------------------------------------  Waited ---------- ------------
SQL*Net message to client               50     0.00       0.00
SQL*Net message from client             50     0.10       0.32
latch free                         16     0.48       1.10
********************************************************************************


Thanks,

/Kristian

--
http://www.freelists.org/webpage/oracle-l