| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Tuning 9i Database - after migrating from 8i. -
3. What do you do in a case where it works for most but not for all of
your SQL??
Figure out what's wrong with the SQL, the statistics,
or the optimizer.
You can too execute a alter session, to set specific values for exception querys
>
> Changing optimizer_index_cost_adj and optimizer_index_caching
> when you don't really know why the optimizer is doing what it is
> doing anyway is a bit like adding chilli sauce to fish when you
> don't know how to cook. Sometimes people are impressed.
>
> o_i_c_a is an indicator to the optimizer of the relative
> cost of a single block read time compared to a multi-block
> read time; and a multiblock read (unless you are using
> Oracle 9 system statistics) is deemed to be size of
> db_file_multiblock_read_count with a reality adjustment
> thrown in.
>
> For a properly designed, well-tuned, well-written, stable
> system, there is a value for o_i_c_a that correctly balances
> the dfmbrc, and helps the optimizer towards appropriate paths.
> For the real world, systems tend to be badly-written, and
> unstable.
>
> A simple example of instability is the switch from OLTP
> to batch that often happens overnight - the value of o_i_c_a
> is often a time (or perhaps task)-dependent.
>
> Batch jobs often do things the brute-force way - hacking through
> tablescans to get the job done. Sometimes these jobs go faster
> than expected because the file-system, or device cache, protects
> Oracle from the (apparent) physical reads it is doing. When you
> indicate to Oracle single block reads are cheaper than multi-block
> reads (in your case by a factor of 4) you may find Oracle takes
> a path which IN THEORY is more sensible, by in practice is
> no longer protected by a backing cache, and therefore runs
> more slowly.
>
> To answer your questions:
> 1. Do you set these parameters ??
>
> Yes.
>
> 2. If you do what values work for you ??
> To the values that seem most appropriate to the
> specific client site for most of the time, and then
> use various mechanisms to vary the values for
> critical periods. (Literal text in batch SQL is
> convenient, logon-triggers that check the clock
> or a reference table are okay).
>
> 3. What do you do in a case where it works for most but not for all of
> your SQL??
> Figure out what's wrong with the SQL, the statistics,
> or the optimizer.
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> March 2004 Hotsos Symposium - The Burden of Proof
> Dynamic Sampling - an investigation
> March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
> April 2004 Iceland
> June 2004 UK - Optimising Oracle Seminar
>
>
> ----- Original Message -----
> From: "Arnon, Yuval" <Yuval.Arnon_at_webloyalty.com>
> To: <oracle-l_at_freelists.org>
> Sent: Thursday, March 04, 2004 5:32 PM
> Subject: Tuning 9i Database - after migrating from 8i. -
>
>
>
> 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
> -----------------------------------------------------------------
>
-- 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 - 16:00:46 CST
 |
 |