Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Optimizer related init parameters

RE: Optimizer related init parameters

From: Gaja Krishna Vaidyanatha <oraperfman_at_yahoo.com>
Date: Mon, 13 Oct 2003 13:59:25 -0800
Message-ID: <F001.005D2F5C.20031013135925@fatcity.com>


Vivek,

I wrote:



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

You asked:



Qs. COULD YOU GIVE SOME DETAIL ON THIS PLEASE (ABOVE PARA)? My response to your question:

Logical I/O (db block gets + consistent gets) should be the primary determining factor on deciding whether a given SQL statement should use an index (or multiple indexes) or perform a full table scan.

If an FTS on a table consumes 1000 blocks of logical I/O and the corresponding index scan consumes 1500 blocks of logical I/O, then, even though from response time perspective both execution plans may compare well, it is pretty evident that the 500 additional logical I/Os require 500 additional requests for the cache buffers chains latch. This is resource consumption (and potential for contention) that you should avoid. Thus, in this case, the FTS should be preferred over the index scan.

So my point to you was : Do not deliberately PREVENT full-table scans, as logical I/O should drive the preferred execution plan for your queries.

Hope that helps,

Gaja
--- VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com> wrote:
> 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).


Gaja Krishna Vaidyanatha
Principal Technical Product Manager,
Application Performance Management, Veritas Corporation E-mail : gaja_at_veritas.com Phone: (650)-527-3180 Website: http://www.veritas.com

Do you Yahoo!?
The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gaja Krishna Vaidyanatha
  INET: oraperfman_at_yahoo.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).
Received on Mon Oct 13 2003 - 16:59:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US