I'll assume that all tables joined in the query have statistics and that
the indexes associated with those tables have statistics.
Check:
Select table_name,num_rows,blocks,avg_row_len,user_stats
From dba_tables
Where table_name='<table_name>';
USER_STATS should say YES
Select index_name, user_stats,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR,
NUM_ROWS
From dba_indexes where table_name='<table_name>';
Check system statistics in the dictionary:
Select * from sys.aux_stats$;
Check the validity of the gathered statistics:
Column statid format a7
Column c1 format a13
Column c2 format a16
Column c3 format a16
Select STATID, C1, C2, C3
From <stats_table>;
C1 - COMPLETED is good
C1 - BADSTATS were completed but not pertinent, because there was no
workload that could justify any statistics during the gathering
interval.
Regards,
Ed Bowers
-----Original Message-----
From: Daniel Woodruff/EmCare
[mailto:oracledba-ezmlmshield-x45318192.[Email address protected]
Sent: Tuesday, June 28, 2005 2: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