AWR Report - Tuning Database We have an OLTP system, following are our AWR reports taken during peak hour window for three days once each day. We see a lot of direct path reads; we want to add more PGA in-order to reduce wait time on this wait event. Following is the current setting in our 11g db.

Sqls order by cpu

CPU Time (s) Executions  CPU per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text 
7,279.38 9,694 0.75 31.50 7,297.79 99.75 0.27 0dp0c8wux0jnt    select case when max(LMA.LEAS... 
6,562.18 1,395 4.70 28.40 6,582.28 99.69 0.31 3g2kw67v3v0qs    SELECT NAME, vpd_predicates.ge... 
4,229.12 836,735 0.01 18.30 4,234.99 99.86 0.00 55a17fhdqg315 DBMS_SCHEDULER  begin :con := SELECT_POLICY_FU... 
2,683.17 968 2.77 11.61 2,605.03 103.00 7.61 dfd3gcuamscs8    select sc.Doc_ID docId , sc.DO... 
2,434.46 853,025 0.00 10.54 2,442.95 99.65 0.00 4y5m5y6y6wvzs DBMS_SCHEDULER  SELECT COUNT (*) FROM CONTACT_... 


Top 5 Timed Foreground Events 08-Feb-2011
Event   Waits   Time(s) Avg wait (ms)   % DB time   Wait Class
DB CPU      23,108      94.74    
db file sequential read 1,332,461   797 1   3.27    User I/O
direct path read    430,600 523 1   2.14    User I/O
db file scattered read  245,444 224 1   0.92    User I/O
SQL*Net more data to client 2,057,853   155 0   0.64    Network
It appears that something like 95% of your wait time is on CPU.  Even if you completely eliminated the direct path reads, you'd eliminate less than 2% of your total wait time.  It seems unlikely that a 2% reduction in your wait time would even register as an improvement.

Taking a look at what is consuming all that CPU-- you have a handful of very CPU intensive queries. 0dp0c8wux0jnt and 3g2kw67v3v0qs are consuming 60% of the CPU your system is using. Have you looked at optimizing those queries so that they use less CPU? 3g2kw67v3v0qs in particular takes nearly 5 seconds per execution.

What else do you have running on this machine?  A total SGA &amp; PGA that is 6% of the memory available on the machine implies that you probably have a number of other things running (perhaps many database instances).