Cbo

[netbar]

If query's are suddenly running slowly, could it be because we analyze tables estimate statistics 60%. Is there problem once you exceed that estaimation. Hope this makes sense

[ggnanaraj]

You could run/automate a compute statistics every day using ... and see the outcome.

exec dbms_stats.gather_schema_stats(ownname => 'SCOTT', cascade => TRUE);

[slimdave]

Use tkprof to find out what wait events are slowing you down.

Preserve a number of key queries and their execution plans, so that in future you can look for changes.

run ...

ALTER TABLE my_table MONITORING;

... to allow DBMS_STATS to decide whether a table needs re-analyzing. COMPUTE every day is overkill i think.

Make a record of any parameter changes.

[nagarjuna]

Originally posted by slimdave
Use tkprof to find out what wait events are slowing you down.

???!!!

[slimdave]

Originally posted by nagarjuna
???!!!

Yes, combined with ...

alter session set events '10046 trace name context forever, level 12';

... of course, rather than the regular sql_trace=true