Re: Does this look like a bug? Test case enclosed ...

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Wed, 26 Aug 2009 23:44:35 -0700 (PDT)
Message-ID: <07493960-285f-4776-98cb-8ef0b6796c98_at_d4g2000yqa.googlegroups.com>

On Aug 26, 11:50 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> Running 11.1.0.7 64 bit on OEL 5.2 system.
>
> Old statistics are left in DBA_TAB_COL_STATISTICS when doing
> dbms_stats gather_table_stats with different METHOD_OPT than the
> previous run.  Can bypass by doing a delete_table_stats before the
> ( different ) gather_table_stats.
>
> Similar results observed with going the gather_schema_stats instead of
> individual tables.
>
> Here's my test case ... please change in the SOMEOWNER to something
> that works for you.
>
> ***************************************************************
>
> drop table SOMEOWNER.testmeout;
>
> create table SOMEOWNER.testmeout(col1 number, col2 varchar2(20), col3
> date ) logging noparallel;
>
> create unique index SOMEOWNER.testmeout_index on SOMEOWNER.testmeout
> (col1) logging noparallel;
>
> insert into SOMEOWNER.testmeout(col1, col2, col3) values(1,'Row
> 1',sysdate);
> insert into SOMEOWNER.testmeout(col1, col2, col3) values(2,'Row
> 2',sysdate);
> insert into SOMEOWNER.testmeout(col1, col2, col3) values(3,'Row
> 3',sysdate);
> commit;
>
> -- following statement may not be available in all versions ...
> uncomment if it works in your version ...
>
> -- select dbms_stats.get_param('METHOD_OPT') from dual;
>
> exec dbms_stats.gather_table_stats
> (OWNNAME=>'SOMEOWNER',TABNAME=>'TESTMEOUT',CASCADE=>TRUE);
>
> -- default for METHOD_OPT ( should be 'FOR ALL COLUMNS SIZE AUTO' );
>
> select * from dba_tab_col_statistics where table_name='TESTMEOUT';
>
> -- Will return 3 rows ...
>
> exec dbms_stats.gather_table_stats
> (OWNNAME=>'SOMEOWNER',TABNAME=>'TESTMEOUT',METHOD_OPT=>'FOR ALL
> INDEXED COLUMNS',cascade=>TRUE);
>
> select * from dba_tab_col_statistics where table_name='TESTMEOUT';
>
> -- Still returning 3 rows ... 2 of them are left over from last table
> statistics ...
>
> execute dbms_stats.delete_table_stats
> (OWNNAME=>'SOMEOWNER',TABNAME=>'TESTMEOUT');
>
> exec dbms_stats.gather_table_stats
> (OWNNAME=>'SOMEOWNER',TABNAME=>'TESTMEOUT',METHOD_OPT=>'FOR ALL
> INDEXED COLUMNS',cascade=>TRUE);
>
> select * from dba_tab_col_statistics where table_name='TESTMEOUT';
>
> -- Now only 1 row is returned ...

I think this behavior is as designed: dbms_stats does not touch stats gathered for different scope. In your test case the first run gathered stats for all columns, the second run gathered (and replaced) them just for all indexed columns - you changed the scope and dbms_stats obeyed. Not sure if it should unconditionally delete previously gathered statistics - if you think they are no longer relevant it's up to you to get rid of them.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Aug 27 2009 - 01:44:35 CDT