RE: CBO parameters

From: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com>
Date: Thu, 1 Jun 2000 13:01:29 -0700 (PDT)
Message-Id: <10515.107463@fatcity.com>

Gary et. al.,

Having tested the CBO behavior modification parameters for a while now and having implemented them at a few production sites, I will be glad to share my experiences. I have personally implemented all of these parameters with the values that are recommended and the optimizer's capability to create more meaningful and stable plans was noticeable.

Probal Shome from the System Performance Group at Oracle Corpn. and I had some in-depth discussions about these parameters at Oracle Open World 1999. Based on our discussions and some real-life production implementations, I wrote about these parameters in my paper on "Oracle Database Performance Management" at IOUG-A 2000. The following is a "cut & paste" from my paper.

Cheers,

Gaja.

CBO SPECIAL PARAMETERS: The following parameters should not be modified from the default values until all documented performance-tuning techniques have been exhausted. The working efficacy of these parameters should be tested thoroughly before implementing them in a production environment. These parameters are most significant for packaged applications (SAP, PeopleSoft) where most of the SQL code is usually inaccessible.

These parameters are also relevant for environments that require hash joins for their "batch processing", but want to hold back the optimizer from being over-influenced by the hash join method. Most of these parameters are relevant in Oracle 7.3.5 (platform-specific) or above, but definitely are relevant in Oracle 8/8i.

OPTIMIZER_MAX_PERMUTATIONS
This parameter defaults to a value of 80000. When set to a value below 80000, say between 79000-79999, it forces the optimizer to try up to 4 different tables as the driving table for queries that involve joins. This results in the optimizer picking the least expensive of the 4 plans that it generates. Usually, the default behavior is to build a plan with the smallest table as the driving table. The default behavior may not always generate the most suitable plan, especially for packaged applications. The result of setting this parameter is a nominal increase in "parse time", but a significant potential in reduction of "execution time" of SQL statements.

OPTIMIZER_INDEX_COST_ADJ
This parameter defaults to a value of 100. The range of values for this parameter is 1-10000. When this is set to a low value (1-10), the optimizer is more encouraged to perform index scans over full table scans.

OPTIMIZER_SEARCH_LIMIT
This parameter defaults to a value of 5. When this is set to 1, the optimizer is totally discouraged from considering "cartesian products". With its default value, the optimizer can and will perform cartesian products for certain queries with 5 or less tables in the FROM clause. This behavior of performing cartesian products is suitable for small tables, but for obvious reasons is a NO-NO for large tables. Depending on the nature of the application this parameter needs to be tweaked.

OPTIMIZER_INDEX_CACHING
This parameter defaults to a value of 0. The range of values for this parameter is 0-100. When this is set to 99, the optimizer is encouraged the use of nested loops over other join methods. Starting from 7.3, with 733_PLANS_ENABLED = TRUE and HASH_JOIN_ENABLED = TRUE, the default value for this parameter will influence the optimizer to consider "hash joins" over all other joins. Hash joins are suitable for applications when small table(s) are joined with very large table(s), and a significant portion of the large table(s) is processed. Manually configuring this parameter to a value of 99 does not turn off hash joins, but does hold back the optimizer from going with hash joins as the default join method.

Steve Adams <steve.adams_at_ixora.com.au> wrote:
> Hi Gary,
>
> I suggest that you leave the cost adjustment parameter alone
> (it should
> really be hidden along with the other fudge factor
> parameters). However,
> setting optimizer_index_caching is helpful in some cases where
> the
> optimizer is biased against index range scans with table
> access. Have a
> look at the cached_blocks.sql script on my web site for help
> with working
> out the actual degree of caching.
>
> Regards,
> Steve Adams
> http://www.ixora.com.au/
> http://www.oreilly.com/catalog/orinternals/
> http://www.christianity.net.au/
>
>
> -----Original Message-----
> From: Kirsh, Gary [SMTP:gary.kirsh_at_gs.com]
> Sent: Thursday, June 01, 2000 3:58 AM
> To: Multiple recipients of list ORACLE-L
> Subject: CBO parameters
>
> Does anybody have any words of wisdom to share regarding
> tuning the CBO
> with
> the init.ora parameters
> OPTIMIZER_INDEX_COST_ADJ
> OPTIMIZER_INDEX_CACHING
> We're looking to come up with some good values to use for
> these across all
> our instances. I know that our mileage will vary, but has
> anybody seen any
> benefit by changing these from their default values?
>
> TIA,
> Gary
>
> Gary Kirsh
> Next Extent, Inc
> --
> Author: Kirsh, Gary
> INET: gary.kirsh_at_gs.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> San Diego, California -- Public Internet access /
> Mailing Lists
>

> 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).
> --
> Author: Steve Adams
> INET: steve.adams_at_ixora.com.au
>
> Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> San Diego, California -- Public Internet access /
> Mailing Lists
>

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

Gaja Krishna Vaidyanatha   | 3460 West Bayshore Road,
Manager - Integration      | Palo Alto, CA 94303
& Consulting Services      | gaja_at_brio.com
Global Alliances           | (650)-565-4442
Brio Technology            | www.brio.com

"Opinions and views expressed are my own and not of Brio Technology" Received on Thu Jun 01 2000 - 15:01:29 CDT