To check that, or any other init.ora differences that may be of importance select the name-value pairs from v$parameter in both schemas and do a diff. Of course the easiest is to diff 10053 event traces and see where they deviate, which will hopefully provide a clue as to why.
At 09:44 AM 6/3/2003 -0800, you wrote:
> From: DENNIS WILLIAMS > I believe that all the statistics that CBO uses to make a > decision are in > USER_TABLES and USER_INDEXES. You might compare the values > for both tables > to see if there is a difference that might cause the CBO to > make a different > decision. I'll look into that, thanks.
>Are you using different usernames? Any chance one > session is doing > an ALTER SESSION? Yes, different usernames, but neither are doing an ALTER SESSION.
Wolfgang, yes, it's doing a FTS instead of using the index. I'll look at doing a trace on it tomorrow.
Daniel: Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7060 Card=338 Bytes=52728) 1 0 SORT (ORDER BY) (Cost=7060 Card=338 Bytes=52728) 2 1 TABLE ACCESS (FULL) OF 'TBL_CUST_MAST' (Cost=7050 Card=338 Bytes=52728)
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12711 Card=338 Bytes=52728) 1 0 SORT (ORDER BY) (Cost=12711 Card=338 Bytes=52728) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_CUST_MAST' (Cost=12701 Card=338 Bytes=52728) 3 2 INDEX (RANGE SCAN) OF 'IDX_CUST_MAST_EMAIL1' (NON-UNIQUE) (Cost=887 Card=338)
Top plan takes about 20 seconds, the lower one less than 1 second.
* DBA_TABLES.NUM_ROWS = 3,161,764 * DBA_TABLES.BLOCKS = 73,294 * DBA_INDEXES.CLUSTERING_FACTOR = 118,131 * DBA_INDEXES.AVG_LEAF_BLOCKS_PER_KEY = 1 * DBA_INDEXES.AVG_DATA_BLOCKS_PER_KEY = 6 * DBA_INDEXES.DISTINCT_KEYS = 18,767 * DBA_INDEXEX.BLEVEL = 2 * DBA_INDEXES.LEAF_BLOCKS = 8850 index is on CLI_CD, CUST_EMAIL1, CUST_STATUS, densities are CLI_CD = 0.1 CUST_EMAIL1 = 0.00006 CUST_STATUS = 0.5 db_file_multiblock_read_count = 16
Craig Healey
Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
