You may also want to spool all init parameters from a well running 8.1.7
database (including hidden parameters) and compare to a similar spool file
for the 9.2 database. Oracle changed default values for several hidden
parameters which have been known to introduce performance problems.

-----Original Message-----
From: Daniel Woodruff/EmCare
[mailto:oracledba-ezmlmshield-x29908082.[Email address protected]
Sent: Tuesday, June 28, 2005 2:44 PM
To: LazyDBA Discussion
Subject: Moving from rule based to cost based optimizer? Huge load on
system.






Oracle 9.2 on HP/UX 11.i
Database size 35 Gig.
Users about 120.

We are going through the process of trying to move from RULE based
optimizer to CHOOSE optimizer. I have set the instance in CHOOSE mode,
but when I did that and there were statistics we had a huge load problem.
This problem does not show up in our development system. I have tried
adjusting the two optimizer parameters as shown. I did a analyze using the
dbms_stats of all tables and indexes and we still have load issues when
statistics are available and also if these are set.

optimizer_index_caching=90
optimizer_index_cost_adj=50

The load on our systems goes from 50% to 100% and users slow down and stop
working.
Any ideas?/



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html

Oracle LazyDBA home page