Look at:
- When the data was last analyzed on both instances
- OPTIMIZER_INDEX_COST_ADJ
- OPTIMIZER_INDEX_CACHING
- DB_FILE_MULTI_BLOCK_READ_COUNT
"Daniel Morgan" <damorgan_at_exesolutions.com> wrote in message
news:3CBAF5A6.E732D328_at_exesolutions.com...
> 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 - 11:23:34 CDT
