Re: Optimizer related init parameters

> optimizer_index_cost_adj=10
> optimizer_index_caching=50

Use of these two depends on a number of things, all of which are relative to your application and users.

When you say 'hybrid', it seems to indicate that you have reporting schema in the same database. How these will effect the querying of the reporting tables depends on the kind of reporting system you have setup.

If you have a star schema, I don't know how these options might effect it. Someone else on the list undoubtedly knows.

If more of a traditional relational layout is used, these parameters could serve to slow down the reporting queries, as nested loop operations mightbe favored by the CBO where a hash join might be more efficient.

> optimizer_max_permutations=8000

If you have some very complex queries, this parameter can greatly decrease the parse time.

You could probably go even lower - the default on 9i is 2000 IIRC. I've set to to 1000 on on 8i database that has some annoyingly complex views for use with INSTEAD OF triggers.

> optimizer_dynamic_sampling=4

I don't have any experience with this one yet.

To answer your own questions properly, you need to consider a couple things:

Which usage has a higher performance priority - the reporting side or OLTP side?

Have acceptable performance levels been established for both?

Quite a few more items to consider, I;m sure, much of it site dependent. This is all I can think of before the first cup of coffee.

Jared

On Fri, 2003-10-10 at 05:54, VIVEK_SHARMA 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: Jared Still
  INET: jkstill_at_cybcon.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).