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
Best Answer
Direct path read is a classic symptom of parallel full table scans - what's your top SQL by I/O? I'd look at the query plan for that to make sure an index is being used. What's your parallelism set to?
Upping the PGA will only help if it's one session doing all of those for a given table, in the worst case each session will need a PGA large enough to cache the entire table...