Optimizer puzzle: unnecessary full index scan

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 - 09:57:31 CDT