Re: Re: Re: cardinality in query plans?

From: <tim_at_sagelogix.com>
Date: Mon, 15 Mar 2004 09:07:22 -0700 (MST)
Message-ID: <551304.1079366842768.JavaMail.oracle@ocs.sagelogix.com>

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)...

attached mail follows:

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------



message/rfc822 attachment: Re:_Re:_cardinality_in_query_plans_

Received on Mon Mar 15 2004 - 10:17:06 CST