DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: changing query execution plans?

Hybrid View

  1. #1
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202

    Question changing query execution plans?

    We had a situation last night when oracle CPU usage hit 100% and pretty much stayed there, we ended up shutting the database down and using a alternative database (we are lucky enough to have 2 live databases).

    Initial investigations seem to indicate that the query executions plans were to blame and the plans the optimiser was choosing were very inefficient (very high consistent gets). I say this because queries that are normally sub second where taking up to 10-15 seconds and the QEP's were completely wrong....the overall effect of several hundred sessions all hitting the database with bad QEPs was a flat lining box. I have since analysed the database (estimate statistics 50% sample size) and the same queries now run fine.

    The statistics on the tables in question were about 4 weeks old (this is as old as we've had them as they are normally refreshed every week) and I'm 99.99% certain that nothing else ran against the system at the time (no large updates/deletes/new indexes/etc..)

    My question is....
    Has anyone else seen similar behaviour where QEP's suddenly changed?
    Is there a threshold or something that Oracle uses to mark the statistics and stale after so many days?

  2. #2
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    oh yeah...there is a tracefile from around the same time but its a "FATAL ERROR IN TWO-TASK SERVER" type one and I think it is a symptom of the box flat lining and not initial the cause of the problem. there is nothing else in the Alert log of the listener log.

    Chris


    *** 2003-10-05 20:14:02.081
    *** SESSION ID:(388.1121) 2003-10-05 20:14:01.675
    FATAL ERROR IN TWO-TASK SERVER: error = 12571
    *** 2003-10-05 20:14:02.081
    ksedmp: internal or fatal error
    Current SQL statement for this session:
    SELECT count(*) FROM event e, aeven a WHERE e.eid = a.eid and e.curent = 'T' and e.curent = a.curent and (rac_mem_numbr = '3005008682' or rac_mem_numbr = '3005008682/') and ad_ts >= '20030905201227GD' order by ad_ts desc

  3. #3
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    i take this as a no then :(

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width