Re: Does this look like a bug? Test case enclosed ...
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Thu, 27 Aug 2009 06:36:54 -0700 (PDT)
Message-ID: <2f3f8de3-13d4-490c-9794-e2e313d01729_at_c14g2000yqm.googlegroups.com>
On Aug 26, 3: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 ...
Date: Thu, 27 Aug 2009 06:36:54 -0700 (PDT)
Message-ID: <2f3f8de3-13d4-490c-9794-e2e313d01729_at_c14g2000yqm.googlegroups.com>
On Aug 26, 3: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 ...
The following does not directly apply to the observation but with 11g you should use get_prefs instead of get_parms which has been obsoleted.
Also on 11g the setting related to if the stats should be automatically published or not may be of interest: PUBLISH ([default] TRUE | FALSE). 11g also supports setting the dbms_stats parameters at the table level.
Depending on what you are looking at you may need to check additional factors on 11g to verify you are not see the side effect of specific object settings.
HTH -- Mark D Powell -- Received on Thu Aug 27 2009 - 08:36:54 CDT