As I understand, stored outlines depend on *exact* syntax matching. If so,
using them kills the ability of the business and application developers to
add or enhance existing SQL.

-----Original Message-----
From: siva
[mailto:oracledba-ezmlmshield-x29717695.[Email address protected]
Sent: Wednesday, June 29, 2005 3:45 AM
To: LazyDBA Discussion
Subject: Re: Moving from rule based to cost based optimizer? Huge load
on system.


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





--------
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
**************************************************************************************
The information contained in this email is privileged and confidential information intended only for the use of the individual or entity named as recipient. If the reader is not the intended recipient, please be notified that any dissemination, distribution, or copy of this communication is strictly prohibited. If you have received this communication in error, please do not disclose this communication to any other person. Please notify us immediately by telephone and return the original to us at the address indicated above. Thank you

Oracle LazyDBA home page