Try optimizer_index_cost_adj=10
-----Original Message-----
From: Daniel Woodruff/EmCare
[mailto:oracledba-ezmlmshield-x80736650.[Email address protected]
Sent: Thursday, June 30, 2005 10:25 AM
To: LazyDBA Discussion
Subject: RE: Moving from rule based to cost based optimizer? Huge load
on system.
Yes I had set those to:::
optimizer_index_caching=90
optimizer_index_cost_adj=50
Any other suggestions?
"Anthony Ballo "
<oracledba-ezmlms
hield-x46593405.x To
[Email Address Removed] "LazyDBA Discussion"
A.com> <[Email address protected]
cc
06/29/2005 12:15
PM Subject
RE: Moving from rule based to cost
based optimizer? Huge load on
system.
Don't you also need to set:
optimizer_index_cost_adj
optimizer_index_caching
In the init.ora?
I've been told to set these to:
optimizer_index_cost_adj = 15
optimizer_index_caching = 90
???
-----Original Message-----
From: Daniel Woodruff/EmCare
[mailto:oracledba-ezmlmshield-x45318192.[Email address protected]
Sent: Tuesday, June 28, 2005 1:12 PM
To: LazyDBA Discussion
Subject: RE: Moving from rule based to cost based optimizer? Huge load
on system.
I did it from the dbastudio tool with uses the dbms_stats tool, I had
very large tables (20,000,000 rows) and smaller ones so I wanted to pick
and
choose which to do full statistics and percentage based. I had
previously
used the "compute statistics..." command with the same results.
The system tables were not analyzed.
"Bowers Edward L
\(N-BAE Systems\)
"
To
<oracledba-ezmlms "LazyDBA Discussion"
hield-x4967820.x1 <[Email address protected]
[Email Address Removed] .com>
Subject
06/28/2005 03:45 RE: Moving from rule based to
cost
PM based optimizer? Huge load on
system.
What statement was used to gather the statistics. Don't gather
statistics on the dictionary (GATHER_SYS='FALSE').
Execute DBMS_STATS.gather_database_stats( GRANULARITY => 'ALL', CASCADE
=> TRUE, GATHER_SYS => FALSE;
Ed Bowers
-----Original Message-----
From: Pepling Todd C.
[mailto:oracledba-ezmlmshield-x30916258.[Email address protected]
Sent: Tuesday, June 28, 2005 1:34 PM
To: LazyDBA Discussion
Subject: RE: Moving from rule based to cost based optimizer? Huge load
on system.
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
--------
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
--------
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
--------
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