Q
Manage Learn to apply best practices and optimize your operations.

Upgrading to dbms_stats for analyze scripts

Recently I've upgraded our analyze scripts to dbms_stats against a particular schema and employed the parallel processing option. The script loops through a subset of the tables. Following doing this we had a massive outage caused by an extremely inefficient explain plan being generated on a set of eight large tables. Can you explain how dbms_stats would cause the CBO to choose a different route for a select?

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?

The Oracle optimizer is no doubt a complex bit of software, and its ways can be mysterious. I have just started working at a new client (Oracle 10g) that was experiencing persistent performance problems in queries against a particular schema. The DBAs tried all manner of different approaches to running dbms_stats with no luck; finally, they tried running "analyze table estimate statistics" with the default sample size, and the result was a significant performance improvement. My advice to you is to find something that works (in your case, using analyze) and stick to it.

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close