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