That's why Oracle recommends "stored outlines" concept.

1. First run your application in RULE based mode.
2.Collect stored outlines
3.Switch the database in CHOOSE mode after collecting statistics.
4. The idea is that query plans are preserved with stored outlines and
with statistics response time improves






"Daniel Woodruff/EmCare "
<oracledba-ezmlmshield-x29908082.[Email address protected]
06/29/2005 12:14 AM


To

"LazyDBA Discussion" <[Email address protected]
cc





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