OK, let'c clarify what will you achieve by setting
optimizer_index_caching=90 and optimizer_index_cost_adj=25.
That means that index block has approximately 90% chance
to be cached and that reading index is 4 times cheaper then reading a
table. What is a practical consequence? Practical consequence is
that the optimizer is far more likely to chose an index plan with
that setting, then without it. You can even say that optimizer will
use an index, if it exists, which is the operating principle of RBO.
In other words, with a setting like that, your CBO reverts into
some form of RBO, but without "access method ranks".
Which queries will suffer? The answer is very simple: the queries
that were meant to utilize full table scans, like large reports
or DSS type queries, are likely to discover indexes and suffer the
consequences. Small and quick OLTP queries, which were written with
RBO in mind, in the first place, will continue to behave well.
Is that good or bad? That depends on your site. Mixed mode OLTP/DSS/Batch
sites are a losing proposition any way you look at them. Having data guard
replicate your reporting tables to an operational data store and then
running reports from ODS is much better solution.
On 03/04/2004 12:32:40 PM, "Arnon, Yuval" wrote:
>
> Over the last few weeks I have been reading thru many emails in this
> list that came from people who seem to have ran into performance
> problems soon after they moved from 8i to 9i.
> We are facing similar issues and if you have gone thru this and was
> successful in overcoming it I would appreciate any advise you may have.
>
> I am trying to determine if setting the two spfile parameters
> "optimizer_index_caching" and "optimizer_index_cost_adj" to values of
> other than the default does actually enhance or degrade the SQL queries.
>
>
> I have noticed instances where setting these to optimizer_index_caching
> = 90 and optimizer_index_cost_adj =25 will force Oracle to choose a
> "good" plan and some other instances where Oracle had chosen a "bad"
> plan.
>
> After migrating to 9i I have these two parameters set 90 and 25
> accordingly only to find out some of the batch processes ended two hours
> longer. Since then I have set them to their default values , most of the
> batch jobs are performing the way they were before the move to 9i, but a
> few of them who ran fine after migration jumped to taking much longer.
> Setting the two params to 90 and 25 only for those jobs and they ran
> fine.
>
> So my questions are
> 1. Do you set these parameters ??
> 2. If you do what values work for you ??
> 3. What do you do in a case where it works for most but not for all of
> your SQL??
>
> TIA.
>
> Yuval.
>
>
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Thu Mar 04 2004 - 13:24:57 CST
