Re: optimizer parameters in Oracle 9.2

Collecting system statistics in 9i is supposed to automagically do what optimizer_index_cost_adj does, at least if you set it according to the formula in Tim's paper ("The Search for Intelligent Life in the Cost-Based Optimizer"). The CBO then uses different cost factors for single-block IO (predominantly index accesses) and multiblock IO (table and index scans).

At 06:04 AM 7/30/2003 -0800, you wrote:
>Hi all:
>
>I have been reading on the optimizer_ parameters for
>Oracle 9i - optimizer_max_permutation,
>optimizer_index_caching and optimizer_index_cost_adj.
>I have also been playing whith them and I can see that
>they affect the explain plans for some queries quite
>dramatically. Is there any guidelines for what they
>should be set to for optimum performance (some kind of
>range and may be relationship between the values) or
>is this mostly a trial and error kind of thing? I have
>seen a few discussion on the Oracle Metalink, but
>mostly they are very specific - try this value or try
>that value. Does anyone have a link to a document with
>something more systematic than that?

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