Re: Problem with indexes

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Tue, 4 Nov 2003 15:23:44 -0000
Message-ID: <3fa7c482$0$252$ed9e5944@reading.news.pipex.net>

Comments embedded.
"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:bo88j7$1apagv$1_at_ID-152732.news.uni-berlin.de...
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> schrieb im
> Newsbeitrag news:3fa795e7$0$255$ed9e5944_at_reading.news.pipex.net...
> > "Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message
> > news:bo81h8$1b1m09$1_at_ID-152732.news.uni-berlin.de...
> <snip...>
> > > DBMS_STATS has been confirmed to be buggy.
> >
> > I have had a look at this thread and I'm not at all clear that you can
> apply
> > it to the situation in this case.

>
> Niall, thanx for the comment, but I would not know why DBMS_STATS would
not
> be as
> applicable as ANALYZE. The doco does not point out any difference about
> them.
> So wyh should some type of statistics be better or worse for each of them
> ... sorry, I am just curious.

The below is output from SQLPlus. My comments are marked *******

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Nov 4 14:47:51 2003

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

SQL> create table t1 as select * from all_objects;

Table created.

SQL> insert into t1 select * from t1;

29013 rows created.

SQL> / 58026 rows created.

SQL> / 116052 rows created.

SQL> / 232104 rows created.

SQL> commit;

Commit complete.

SQL> create index idx1 on t1(object_id);

Index created.

SQL> create index idx2 on t1(object_id,owner);

Index created.

SQL> create index idx3 on t1(object_type,object_id) compress 1;

Index created.

create a table and create multiple indexes on it.

SQL> alter session set tracefile_identifier=INDEX;

Session altered.

SQL> alter session set max_dump_file_size=UNLIMITED;

Session altered.

SQL> exec dbms_stats.gather_SCHEMA_stats('NIALL',CASCADE=> TRUE);

PL/SQL procedure successfully completed.

get some stats

SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

This will dump a trace file with the CBO's calculation of access paths.

SQL> select count(*) from t1 where object_id between 61 AND 71;

COUNT(*)

176

SQL> alter session set events '10053 trace name context off';

Session altered.

end the dump

SQL> ANALYZE INDEX IDX1 DELETE STATISTICS; Index analyzed.

delete stats on 1 index only

SQL> alter session set tracefile_identifier=INDEX_NOSTATS;

Session altered.

dump the trace to a different file

SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

SQL> select count(*) from t1 where object_id between 80 AND 90;

COUNT(*)

176

SQL> alter session set events '10053 trace name context off';

Session altered.

SQL>

this gives me 2 trace files.

From the first I get the following information

BASE STATISTICAL INFORMATION

Table stats Table: T1 Alias: T1

TOTAL :: CDN: 464208 NBLKS: 6323 AVG_ROW_LEN: 93

Index stats

INDEX NAME: IDX1 COL#: 4 TOTAL :: LVLS: 2 #LB: 1013 #DK: 29013 LB/K: 1 DB/K: 16 CLUF: 464208 INDEX NAME: IDX2 COL#: 4 1 TOTAL :: LVLS: 2 #LB: 1381 #DK: 29013 LB/K: 1 DB/K: 16 CLUF: 464208 INDEX NAME: IDX3 COL#: 6 4 TOTAL :: LVLS: 2 #LB: 1017 #DK: 29013 LB/K: 1 DB/K: 16 CLUF: 464208 So the optimizer believes that we have 3 indexes, all with a height of 3 (lvl 2+1) and about 1000 leaf blocks each (the LB figure).

From the second trace file we get

BASE STATISTICAL INFORMATION

Table stats Table: T1 Alias: T1

TOTAL :: CDN: 464208 NBLKS: 6323 AVG_ROW_LEN: 93

Index stats

INDEX NAME: IDX1 COL#: 4 TOTAL :: LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800 INDEX NAME: IDX2 COL#: 4 1 TOTAL :: LVLS: 2 #LB: 1381 #DK: 29013 LB/K: 1 DB/K: 16 CLUF: 464208 INDEX NAME: IDX3 COL#: 6 4 TOTAL :: LVLS: 2 #LB: 1017 #DK: 29013 LB/K: 1 DB/K: 16 CLUF: 464208 Index idx1, which we deleted stats on now has some 'default' values for its key stats. These change the attractiveness of the index to the CBO compared with the other indexes. Thus *any* query which accesses this table may have its execution plan changed. in fact that doesn't happen in my case as I have made the most attractive index more attractive. As a general rule the more accurate and complete the information you feed the CBO the better it will behave.

> True, it makes only sense in that way that it *did* work for my very
special
> case - surprizingly,
> and so it *may* work in other cases too, who knows ?
> If something is stupid but works, it is not stupid (Murphy) - it remains
> only unclear why unless You meet somebody
> who knows - here for instance ...
> A rebuild would have been even more "stupid", so I went for the "least
> stupid" solution.

That is of course correct, what I guess I would probably do as a follow up is some experimenting on the test box to try to find out why something is behaving unexpectedly. The more rules that you have for your system (we delete stats on these tables, compute them on these, estimate them on these etc etc) the more unsupportable your environment becomes). of course I'm a curious b*gger as well.

> greetings
> Jan

>
> Socrates knew that he knew nothing - but how then could he know that ...

He didn't *know* that, that was his base assumption in contrast to the assumption of the sophists that they knew everything. Received on Tue Nov 04 2003 - 09:23:44 CST