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