| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: cardinality in query plans?
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_at_cox.net at ryan.gaffuri_at_cox.net 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_at_sagelogix.com >> Date: 2004/03/15 Mon AM 11:07:22 EST >> To: oracle-l_at_freelists.org >> 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)... >>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Mar 15 2004 - 22:12:40 CST
 |
 |