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

From: joel garry <joel-garry_at_home.com>
Date: Wed, 26 Aug 2009 14:11:15 -0700 (PDT)
Message-ID: <87bf5607-72d1-43c0-8111-a205ed5e66d2_at_v15g2000prn.googlegroups.com>

On Aug 26, 12: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 see the same thing 10.2.0.4. As to whether it is a bug... my initial feeling is no, since the doc examples for restoring statistics do the delete procedure, and generally using manual updates to statistics is designed to update the existing statistics - if you are doing it manually, then you must delete if changing how they are gathered. It is certainly counter-intuitive in this example, and (I'm guessing) could explain optimizer weirdness as people do this. Poking about on the interwebs, I see misadvice being given at places like expert-sexchange (typo intended, I ain't registering), people blabbing without considering this situation which must be common if the common advice is that the default optimizer collection is inappropriate.

http://structureddata.org/2008/10/14/dbms_stats-method_opt-and-for-all-indexed-columns/ is a classic, and in the context of this post seems to imply the delete ought to be obvious or well-known. Not obvious to me, anyways.

I think it would be a bug if it showed this when changing the default gathering behavior, I haven't looked closely enough to see if that is doing the same thing as this example. Looking at morgan's library, I see a 11g procedures for comparing different historical statistics - that may be fertile ground for bughunts.

jg

--
_at_home.com is bogus.
"The end of the RAC-on-Mac story ... It absolutely screamed. Beat heck
out of Sun Solaris and Linux.  But this lesson came with a lot of
bricks on the foot." - damorgan

Received on Wed Aug 26 2009 - 16:11:15 CDT