Re: cardinality in query plans?
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 15 Mar 2004 21:16:24 -0700
How about reducing the number of variables in the problem by leaving
OPTIMIZER_INDEX_COST_ADJ alone? Changing OPTIMIZER_INDEX_CACHING doesn't
automatically mean changing the other one as well -- they are really quite
different from one another...
on 3/15/04 12:22 PM, ryan.gaffuri@xxxxxxx at ryan.gaffuri@xxxxxxx wrote:
> We updated to 9.2.0.3 from 8.1.7.3 and kept the defaults. I didn't do it. I
> have not been budgeted
> time to test the queries against better settings so I have to stick with the
> old settings.
>
> Our parameters are
>
> optimizer_index_caching=0
> optimizer_index_cost_adj=50
>
> I did an
> alter session set optimizer_index_cashing=90
> alter session optimizer_index_cost_adj=10
>
> Re-ran the query. It chose an index, but the wrong index. Instead of TAB2
> using an index of the form:
> name,col1,col2,col3
>
> where the col1,col2,col3 are the join columns and name is the column with the
> like it chooses an
> index of the form
> col1,col2,col3,name
>
> This query has 565,000 LIOs as opposed to a worst case of 13,000 and best case
> of 4,700. I try with
> bind variables and Oracle flips the join order which gets me down to 17,000
> LIOs, but still chooses
> the wrong index.
>
> I'm at a loss.
>
> Below is the new plan without bindvariables.
>
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=456 Card=1 Bytes=152
> )
>
> 1 0 VIEW (Cost=456 Card=1 Bytes=152)
> 2 1 COUNT (STOPKEY)
> 3 2 VIEW (Cost=456 Card=1 Bytes=139)
> 4 3 WINDOW (SORT) (Cost=456 Card=1 Bytes=106)
> 5 4 WINDOW (SORT) (Cost=456 Card=1 Bytes=106)
> 6 5 TABLE ACCESS (BY INDEX ROWID) OF 'TAB2' (Cost=2 Card=1
> Bytes=90)
>
> 7 6 NESTED LOOPS (Cost=452 Card=1 Bytes=106)
> 8 7 INDEX (RANGE SCAN) OF 'TAB1_pk
> ' (UNIQUE) (Cost=11 Card=2250 Bytes=36000)
>
> 9 7 INDEX (RANGE SCAN) OF 'TAB2_IND2
> D' (NON-UNIQUE) (Cost=1 Card=1)
>>
>> From: tim@xxxxxxxxxxxxx
>> Date: 2004/03/15 Mon AM 11:07:22 EST
>> To: oracle-l@xxxxxxxxxxxxx
>> Subject: Re: Re: Re: cardinality in query plans?
>>
>> Just curious: what is the value of OPTIMIZER_INDEX_CACHING during these
>> tests?
>>
>> Remember that "cost" is essentially the Oracle optimizer's prediction of the
>> number of PIOs, but it can only use formulas that count LIOs to arrive at the
>> estimate of PIOs With FULL table scans, the translation of LIOs to PIOs is
>> relatively straightforward and accurate (i.e. divide by "real" multiblock
>> read count, etc).
>>
>> With indexed scans, however, it is almost impossible to predict due to the
>> vagaries of a Buffer Cache's configuration and usage. Thus, Oracle has built
>> in a configurable "discount" factor which is the parameter
>> OPTIMIZER_INDEX_CACHING. When O_I_C is set to its default of "0", then
>> essentially this discount is disabled and all of the LIOs calculated by
>> Oracle for indexed access are costed as PIOs, every single blessed one.
>>
>> As my kids would say, "That is *SO* wrong!"...
>>
>> ...(which actually means "that is SO cool" in boomer-speak, but I take the
>> meaning of "wrong" literally here)...
>>
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
