Recently I've upgraded our analyze scripts to dbms_stats against a particular schema in Oracle 9i and employed the parallel processing option. The script loops through a subset of the tables, as not all need to be analyzed and also this saves time.
Following doing this we had a massive outage caused by an extremely inefficient explain plan being generated on a set of eight tables (all of which were large). The query went from running in 61 ms to 21 minutes; these basically backed up onto user processes until nothing worked.
Can you explain how dbms_stats would cause the CBO to choose a different route for a select?
Dig Deeper on Oracle database performance problems and tuning
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.