Re: DBMS_STATS.DELETE_INDEX_STATS does _not_ delete index statistics ??

Hi,

Sorry for the imprecision,
Index stats are not cleared with dbms_stats. Table stats are ...

Dias

Wolfgang Breitling <John.Doe_at_aol.com> wrote in message news:<Xns9429F035C1E4Fbreitliwcentrexcccom_at_198.80.55.250>...
> ydias_at_hotmail.com (dias) wrote in
> news:55a68b47.0311040646.193880a8_at_posting.google.com:
>
> > Hi,
> >
> > Thanks.
> >
> > Small test shows that dbms_stats does not clear table or index stats.
> > dba_XXX show no statistics, but v$sql_plan still show that the objects
> > are analyzed and the cbo still use it.
> >
>
> That's not what my tests show. Following is an excerpt from a 10053
> trace:
>
> BEGIN dbms_stats.gather_table_stats('scott','t1'); END;
> QUERY
> BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
> QUERY
> BEGIN dbms_stats.gather_index_stats('scott','t1p'); END;
> QUERY
> BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
> QUERY
> explain plan set statement_id='001' for
> select t1.d1, t1.d2, t2.d1, t2.d2
> from t1, t2
> where t2.fk1=t1.pk1
> and t2.d2 = 499
>
> [stuff removed for brevity]
>
> ***********************
> Table stats Table: T1 Alias: T1
> TOTAL :: CDN: 5000 NBLKS: 1772 AVG_ROW_LEN: 1015
> Column: PK1 Col#: 1 Table: T1 Alias: T1
> NDV: 51 NULLS: 0 DENS: 1.9608e-02 LO: 1 HI: 51
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> -- Index stats
> INDEX NAME: T1P COL#: 1 2
> TOTAL :: LVLS: 1 #LB: 30 #DK: 5000 LB/K: 1 DB/K: 1 CLUF: 1828
>
> [stuff removed for brevity]
>
> QUERY
> BEGIN dbms_stats.delete_table_stats('scott','t1'); END;
> QUERY
> BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
> QUERY
> explain plan set statement_id='002' for
> select t1.d1, t1.d2, t2.d1, t2.d2
> from t1, t2
> where t2.fk1=t1.pk1
> and t2.d2 = 499
>
> [stuff removed for brevity]
>
> ***********************
> Table stats Table: T1 Alias: T1
> TOTAL :: (NOT ANALYZED) CDN: 72156 NBLKS: 1772 AVG_ROW_LEN: 100
> Column: PK1 Col#: 1 Table: T1 Alias: T1
> NO STATISTICS (using defaults)
> NDV: 2255 NULLS: 0 DENS: 4.4348e-04
> NO HISTOGRAM: #BKT: 0 #VAL: 0
> -- Index stats
> INDEX NAME: T1P COL#: 1 2
> TOTAL :: LVLS: 1 #LB: 30 #DK: 5000 LB/K: 1 DB/K: 1 CLUF: 1828
>
> As you can see it clearly says "NOT ANALYZED" and the statistics are
> different since the optimizer uses defaults in place of the missing
> statistics.
Received on Wed Nov 05 2003 - 15:49:12 CST