Re: Optimizer puzzle: unnecessary full index scan

Are you running statistics using DBMS_STAT? Without current statistics the optimizer can only guess. And I've yet to see anyone run statistics on a test machine.

And what exactly do you mean by "an inadequate index"? If the index is inadequate ... fix it.

Daniel Morgan

bonminh lam wrote:

> Hello Oracle tuning experts,
>
> I have an optimizer mystery that puzzles me. For the same statement,
> Oracle utilizes different execution plans on two instances. They are a
> production and a test instance, so the test is actually a mirror of
> the prior, with the same index definitions and data amount. Both
> instances run 8.1.7.2. For some reasons that I can not explain, the
> production instance (a big Unix server, the test instance is on an NT
> box) chose a very bad access method to an inadequate index on table
> CUSTOMERS. This table has on both instances a primary key constraint
> on CUST_ID and 2 indexes containing column CUST_ID:
>
> CREATE INDEX CUST_DSTMP_IX2 ON
> CUSTOMERS(CUST_DATE_STAMP, CUST_ID, CUST_STATUS)
> STORAGE(INITIAL 10485760 NEXT 10485760 PCTINCREASE 0 )
> ;
>
> CREATE INDEX PROWA_STD.CUST_IX2 ON
> CUSTOMERS(CUST_ID, CUST_ID_EXT_GENESYS, CUST_ID_JDE)
> STORAGE(INITIAL 10485760 NEXT 10485760 PCTINCREASE 0 )
>
> Index CUST_IX2 is used to support the primary key constraint.
>
> The statement is this:
>
> insert into KSC_CALLER
> select *
> from caller_at_remote_schema b
> where not exists
> (select 1
> from local_schema.customers c
> where c.cust_id = b.caller);
>
> On the production instance the execution plan shows (cut and pasted
> from TOAD) that an index full scan is used:
>
> Operation Object Name Rows Bytes Cost TQ In/Out PStart PStop
>
> INSERT STATEMENT Hint=CHOOSE 16 K 1576
> FILTER
> REMOTE 16 K 3 M 1576 .,OM SERIAL
> INDEX FULL SCAN CUST_DSTMP_IX2 1 12 2934
>
> On the test instance, the execution is more efficient:
>
> INSERT STATEMENT Hint=CHOOSE 16 K 1576
> FILTER
> REMOTE 16 K 3 M 1576 .,OM SERIAL
> INDEX RANGE SCAN CUST_IX2 1 12 3
>
> The runtime difference is 2 minutes versus almost the whole weekend
> (test beats production!) Below are the stats on these two indexes
> (applicable to both instances):
>
> INDEX_NAME UNIQUENESS BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
>
> CUST_DSTMP_IX2 NONUNIQUE 2 2932 467287 334248 467287,5
> CUST_IX2 NONUNIQUE 2 2675 518950 321535 518950
>
> Any explanations for the different behaviour?
Received on Mon Apr 15 2002 - 10:45:47 CDT