Re: CPU Per Exec - SQL Ordered By CPU Time

  • From: Oracle Dba Wannabe <oracledbawannabe@xxxxxxxxx>
  • To: Brandon.Allen@xxxxxxxxxxx, "harel.safra@xxxxxxxxx" <harel.safra@xxxxxxxxx>, "Oracle-L@xxxxxxxxxxxxx" <Oracle-L@xxxxxxxxxxxxx>
  • Date: Wed, 24 Nov 2010 10:44:38 -0800 (PST)

Perhaps you're right, looking at slide 19/53, the 4th bullet points states:

• => wait event times are artificially inflated

If I look at non peak and peak wait events for my awr I see:
Non Peak:


Event
Waits
Time(s)
Avg Wait(ms)
% Total Call Time
Wait Class
CPU time   4,262   20.8   
db file sequential read 320,813 3,726 12 18.2 User I/O 
db file scattered read 422,587 3,239 8 15.8 User I/O 
gc buffer busy 373,929 2,265 6 11.0 Cluster 
db file parallel read 38,122 1,992 52 9.7 User I/O 
Peak:

Event
Waits
Time(s)
Avg Wait(ms)
% Total Call Time
Wait Class
gc buffer busy 986,001 95,686 97 37.0 Cluster 
enq: TX - index contention 200,074 49,370 247 19.1 Concurrency 
buffer busy waits 285,277 19,594 69 7.6 Concurrency 
gc cr multi block request 1,251,468 11,719 9 4.5 Cluster 
enq: SQ - contention 47,491 10,044 212 3.9 Configuration 
GC Buffer Busy has jumped right to the top, CPU time is no where to be seen, 
but 
I know during this 30 minute snapshot with 8 cores I have 30x8x60=14400

--> 

BUSY_TIME 1,046,108 

104610/14440 x100=  72.4%
The same slide says tune for CPU , before waits when CPU constrained - guess 
that mean I shouldn't focus on determining the segment/segment type that might 
be causing contention?



________________________________
From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
To: "oracledbawannabe@xxxxxxxxx" <oracledbawannabe@xxxxxxxxx>; 
"harel.safra@xxxxxxxxx" <harel.safra@xxxxxxxxx>; "Oracle-L@xxxxxxxxxxxxx" 
<Oracle-L@xxxxxxxxxxxxx>
Sent: Wed, November 24, 2010 11:12:08 PM
Subject: RE: CPU Per Exec - SQL Ordered By CPU Time

  
I’m not sure on this – I thought that time waiting in the CPU run queue was 
included in the v$sql.cpu_time and “CPU used by this session” stats, but 
according  to slide 20 here, it looks like maybe the time in the CPU run queue 
is actually accounted for in the IO wait event instead:
 
http://www.oracle.com/technetwork/database/features/manageability/db-perf-tuning-ow08-131582.pdf

 
________________________________
 Privileged/Confidential Information may be contained in this message or 
attachments hereto. Please advise immediately if you or your employer do not 
consent to Internet email for messages of this kind. Opinions, conclusions  and 
other information in this message that do not relate to the official business 
of 
this company shall be understood as neither given nor endorsed by it.


      

Other related posts: