Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle's use of Indexes

RE: Oracle's use of Indexes

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 03 Jun 2003 12:54:41 -0800
Message-ID: <F001.005A9410.20030603125441@fatcity.com>


The cardinalities are the same, but the costs are different. It looks as if production has somehow optimizer_index_cost_adj set to 50 or lower or has db_file_multiblock_read_count set to 8. It's the same instance so that is not possible unless they are changed at a session level. 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: breitliw_at_centrexcc.com

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: ListGuru_at_fatcity.com (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).
Received on Tue Jun 03 2003 - 15:54:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US