RE: Optimizer related init parameters

Gaja,List

QUESTION IN CAPITALS BELOW :- Thanks indeed

P.S. Welcome Back to the List . All have been missing you.

-----Original Message-----
Sent: Sunday, October 12, 2003 12:19 AM
To: Multiple recipients of list ORACLE-L

Vivek and list,

I don't think any reasonable person will be able to say with a high-level of certainty whether the values that you have suggested, are optimal for your environment. The answer is a huge - IT DEPENDS.

Having said that, here are some things you may want to take into consideration:

1. From a functionality perspective OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ were meant to do the same thing. It just happened to end up as 2 different parameters with 2 different code paths, which pretty much do the same(similar) thing. So usually, it is enought to set either one or the other, although setting both in my experience has generated no harm.

So, if you want to optimizer to show bias towards index scans, then setting OPTIMIZER_INDEX_CACHING to a high value (90 or higher) will achieve that. Right now your value of 50, tells the optimizer that only 50% of the time, will it find index blocks in the DB buffer cache. This will affect the optimizer's decision making.

Tim Gorman has a very simple formula to calculate the appropriate value on your system for
OPTIMZER_INDEX_COST_ADJ, stated in his paper "Searching for intelligence in the Oracle Optimizer" (or something to that effect) on his site http://www.evdbt.com. It basically calculates a ratio of the average time for db file sequential read/db file scattered read from v$system_event, for your system.

On a related topic, I think it is relevant to mention here that to carte-blanche curtail full-table-scans, may not work to the long-term benefit of your applications. However, I will assume here that you are aware of the core point - "amount of logical I/O" to be the most important (if not only) determinant when deciding whether FTS is better than index scans.

Qs. COULD YOU GIVE SOME DETAIL ON THIS PLEASE (ABOVE PARA)?

2) John Kanagaraj did some work and testing to determine that setting OPTIMIZER_MAX_PERMUTATIONS to a low value (2000 if I remember right), has a positive impact on the plans that is generated, especially in an Oracle Apps environment. You should check it out.

3) Julian Dyke and Steve Adams have performed some good tests and research on OPTIMIZER_DYNAMIC_SAMPLING. But, I think the jury is still out on what the optimal value for this might be. I guess 4 is good enough. But, realize that this parameter is relevant when you have "partial statistics" in your schema. Otherwise, I don't think there is any impact of this parameter.

Final notes:

1. All of these parameters can be set at the session level. I would urge you to perform extensive tests before making global init.ora changes.
2. At the end of the day, you should ask yourself, why you are embarking on this effort of changing these values. If you have enough "trace data" to warrant these changes, then by all means. Otherwise, you may be setting yourself up for surprises in the future.

Cheers,

Gaja
--- VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com> wrote:
> How Good/advisable are the following 4 parameters'
> Values in a Hybrid
> Application?
>
> Are there any know ill-effects of the same?
>
>
>
> Application - Banking (Hybrid)
>
> Solaris 9
>
> Oracle 9.2
>
>
>
>
>
> optimizer_max_permutations=8000
> optimizer_index_cost_adj=10
> optimizer_index_caching=50
> optimizer_dynamic_sampling=4
>
>
>
> Some INFO :-
>
> Database has 6000 Concurrent Users accessing
>
> We do ONLY INDEX Scans with exceptional FTS .
>
> FTS if present occur only on SMALL Tables (a few
> Hundred Rows)
>
> FTS if unchecked greatly harm our performance
>
> Stripe Unit Size 64K
>
> Oracle Block Size 8K
>
>
>
> Will Give any info required
>
>
>
> Thanks
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: VIVEK_SHARMA
  INET: VIVEK_SHARMA_at_infosys.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).