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