dbms_stats changes the explain plan--BADLY!

[sapatos]

Recently I've upgraded our analyze scripts to dbms_stats against a particular schema on 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 8 tables (all of which large). The query went from running in 61ms to 21 minutes, these basically backed up onto user processes until nothing worked.

Can you explain how dbms_stats would cause the COB to choose a different route for a select.

 

[djbjr]

We have had a similar problem to a job we were running. We werent using DBMS_STATS but an analyze.

We didnt have the bandwidth to research the issue so we ended up dropping the stats before running the job and re analyzing after the job completes.

This worked and we do it nightly.

I am curious to what parameters you are using in your DBMS STATS command.

 

[sapatos]

The code is dynamically generated but results in something similar to the below:

ANALYZE TABLE XYZ DELETE STATISTICS

BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SCOTT'
,tabname => 'TABNAME'
,estimate_percent => 25;
,method_opt => 'FOR ALL INDEXED COLUMNS'
,degree => 2
,granularity => 'ALL'

ANALYZE INDEX XYZ DELETE STATISTICS

DBMS_STATS.GATHER_INDEX_STATS (ownname => 'SCOTT'
,indname => 'INDNAME'
,estimate_percent => 25
,degree => 2
,granularity => 'ALL'

The only way we've managed to solve this is to re-analyze the tables using the following commands, avoiding dbms_stats:

ANALYZE TABLE ABC DELETE STATISTICS;

analyze table ABC estimate statistics sample 25 percent for table for all columns for all indexes

We've had to back out all analysis with dbms_stats pending a lengthy investigation with Oracle.

 

[JtheRipper]

Hi,

Just my 2 cents...

We also had lots of problems with Oracle 9i's dbms_stats. There are more than a few bugs in that release that have apparently been fixed in 10G.

Just one thing I know of, it is pointless to do a degree=>2 for the dbms_stats.gather_index_stats because it does it in single stream anyway... just one of the many Oracle bugs in 9i.

As for the table stats I am not sure. We use dbms_stats to gather our table stats and that seems to work ok. Have you tried only doing a 10 percent estimate and to not gather histogram stats as a test?

J.

 

[sapatos]

thats worth knowing, thanks.

We re-did that stats using 10% to speed up the return to the older "analyze" command when reversing out the change.

Will try those recommendations when I get a chance to test this again. Its gonna be hard to sell this to management for a while I reckon