changing query execution plans?

[mrchrispy]

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?

[mrchrispy]

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

[mrchrispy]

i take this as a no then :(