Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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, 
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
share|improve this question

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...

share|improve this answer
Thanks Gaius..We dont enforce any parallelism,our queries run faster but executed thousands of times in a day.Our env uses oracle VPD functionality to enforce rules,so,every time table listed in the policy queried,predicate is determined on the runtime and applied, determining predicate is one of our top sqls, gui users complaining slowness.. – Sree Feb 14 '11 at 22:20
Am I reading correctly that there is 48G of RAM in this box, but you are only using 2G SGA? Is the box doing something else as well? If so that AWR report won't be telling the whole truth. If not, up your SGA to 24G and see what happens. I have some systems with 30G SGAs on 48G boxes. That will cut your sequential reads hopefully... – Gaius Feb 14 '11 at 22:44

There's wait event enq: TM - contention. This wait event occurs when you have unindexed foreign key constrained column(s). An update on the master table will lock entire child table. I have a query which generate create index statement for the missing indices. You may change the naming conventions to your favor.

SELECT 'CREATE INDEX '||a.owner||'.IX_'||a.table_name||'__'||
    replace(a.column_name, 'FK_', '') ||' ON '||a.owner||'.'||a.table_name||
    ' ('||a.column_name||') LOGGING TABLESPACE '||
    replace(b.tablespace_name, '_DAT', '_IDX')||' NOPARALLEL ONLINE;
   SELECT c.owner, c.table_name, cc.column_name, cc.position column_position
   FROM   all_constraints c, all_cons_columns cc
   WHERE  c.constraint_name = cc.constraint_name and c.owner=cc.owner
   AND    c.constraint_type = 'R'
   SELECT i.owner, i.table_name, ic.column_name, ic.column_position
   FROM   all_indexes i, all_ind_columns ic
   WHERE  i.index_name = ic.index_name and i.owner=ic.index_owner
) a
inner join all_tables b on b.owner=a.owner and b.table_name=a.table_name
where a.owner='<owner>'
ORDER BY a.owner, a.table_name, a.column_position;
share|improve this answer
We have identiified this and fix is going into prod soon,Thank you. – Sree Feb 15 '11 at 14:59

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 & 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).

share|improve this answer
Thanks Justin,following is order by cpu time.09-Feb-2011 CPU Time (s) Executions CPU per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text 9,453.92 10,511 0.90 35.93 9,466.79 99.86 0.15 0dp0c8wux0jnt select case when max(LMA.LEAS... 8,698.59 1,553 5.60 33.06 8,714.06 99.82 0.17 3g2kw67v3v0qs SELECT NAME, 4,805.17 960,387 0.01 18.26 4,816.80 99.76 0.00 55a17fhdqg315 DBMS_SCHEDULER begin :con := SELECT_POLICY_FU... 2,872.26 1,047 2.74 10.92 2,693.38 106.64 5.95 dfd3gcuamscs8 select sc.Doc_ID docId , sc.DO... 2,711.61 980,593 0.00 10.31 2,715.21 – Sree Feb 14 '11 at 22:14
@Sreedhar - Can you add that to your question (and use the formatting options to keep things lined up)? – Justin Cave Feb 14 '11 at 22:20
Justin,I coudnt really format it.please paste this to notepad CPU Time (s)Executions CPUperExec(s) %Total ElapsedTime(s) %CPU %IO 4,679.00 8,106 0.58 21.84 4,703.85 99.47 0.57 4,040.80 1,437 2.81 18.86 4,067.93 99.33 0.69 3,797.96 761,428 0 17.73 3,805.93 99.79 0 3,129.01 1,191 2.63 14.61 2,978.27 105.06 5.81 2,141.68 778,900 0 10 2,149.43 99.64 0 – Sree Feb 15 '11 at 15:11
@Sreedhar - If you post unformatted text, we can't just copy it to notepad, it's already lost all of the formatting. If you edit your question to post unformatted text, we can at least edit it to insert a small amount of formatting like we did with your initial question. Editing your question to add formatted text would be best though. – Justin Cave Feb 15 '11 at 15:16
I just did it..Thanks. – Sree Feb 15 '11 at 18:41

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.