Re: DBMS_STATS.DELETE_INDEX_STATS does _not

From: Wolfgang Breitling <John.Doe_at_aol.com>
Date: Wed, 05 Nov 2003 06:37:00 GMT
Message-ID: <Xns9429F035C1E4Fbreitliwcentrexcccom@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.

-- 
What lies behind us and what lies before us are small matters when 
compared to what lies within us.

Wolfgang Breitling
Oracle 7, 8, 8i, 9i OCP

Received on Wed Nov 05 2003 - 00:37:00 CST

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