Re: optimizer_index_cost_adj and optimizer_index

From: Tim Gorman <tim_at_sagelogix.com>
Date: Fri, 05 Mar 2004 16:04:11 -0700
Message-ID: <BC6E557B.1105A%tim@sagelogix.com>

Ryan,

My paper recommended setting O_I_C_A to a value calculated from observations from V$SYSTEM_EVENT for the wait-events "db file sequential read" and "db file scattered read". It does mention values like "10" and "50" in the very beginning of the paper, just to set expectations, but then recommends using observed values as it forward references the entire remainder of the paper.

The quote you mention came from the "Cut to the Chase" section in the very beginning, which was intended as an "executive summary" for those (PHB, CIOs, CTOs, VPs of IT, etc) who would not care to read all the way through. It was immediately followed by a section (comprising the remaining 95% of the text) entitled "The Rest of the Story"...

Hope this helps...

-Tim

P.S. Ironically, I do recommend a fixed value for O_I_C of 90, because I have never felt that it was worth the brain cycles to attempt to find a good observed value of the BCHR. Just set the thing to 90, accept the "discount" it provides on the cost calculation, and move on...

on 3/5/04 7:30 AM, ryan.gaffuri_at_cox.net at ryan.gaffuri_at_cox.net wrote:

> Oracle support(without knowing anything about my system) is telling me to use
> the following settings:
>
> OPTIMIZER_INDEX_CACHING = 50
> OPTIMIZER_INDEX_COST_ADJ = 5
>
> Tom Kyte's book effective Oracle by Design recommends starting
> optimizer_index_caching at my cache/hit ratio and adjusting as needed.
>
> Tim Gorman's paper 'Search for Intelligent Life in the Cost-Based Optimizer'
> states that OPTIMIZER_INDEX_COST_ADJ should be set between 10 and 50 for most
> OLTPs.
>
> This is a hybrid system, but I only have the OLTP parts to stress test(the
> other parts are still in early phase development). Does anyone have any
> opinions on this?
>
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------

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

Received on Sat Mar 06 2004 - 00:43:44 CST

Re: optimizer_index_cost_adj and optimizer_index_caching