a change in data volumes on one or more tables/indexes in the old plan, which a recompute on the statistics will catchup and generate a new plan. This could have been something sudden or a gradual growth that finally reached some critical threshold.
it could also be bind peeking. if your queries were running fine and then you statements get aged out of the sga and the first to come back in does a bind peek, finds an anomolous value and generates a new plan, that may be good for that one special case but is bad for everyone else.
Then all of the other sessions will continue to use that bad plan. recomputing the statistics and invalidating all the cursors will force a reparse and a re-peek.
If someone flushed the shared pool, reparse and re-peek would also occur. As would bouncing the database or other large system wide changes. Alterations to the optimizer parameters would also cause that or DDL altering one or more of the objects in the query, forcing a reparse and repeek
Main Topics
Browse All Topics
by: sujith80Posted on 2008-08-27 at 07:43:26ID: 22325293
Do have a scheduled activity to compute statistics in the database.
Various methods are available.
You can either use a dbms_job.
Or a custom procedure called from a scheduler
Or part of a periodic maintenance activity