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
Oracle LazyDBA home page