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 ----- Original Message ----- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Thursday, March 04, 2004 4:49 PM Subject: 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 > > > ----- 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 > ----------------------------------------------------------------- > ---------------------------------------------------------------- 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 -----------------------------------------------------------------