Re: Tuning 9i Database - after migrating from 8i. -
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Mar 2004 20:49:16 -0000
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@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
