Tuning 9i Database - after migrating from 8i. -

From: Arnon, Yuval <Yuval.Arnon_at_webloyalty.com>
Date: Thu, 4 Mar 2004 12:32:40 -0500
Message-ID: <7D18327AF31AE04B88B3801C8D4AB14D050ED44D@exchange01.webloyalty.com>

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

Received on Thu Mar 04 2004 - 12:12:15 CST