RE: dbms_stats
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: "mhdmehraj@xxxxxxxxx" <mhdmehraj@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 3 Jun 2009 15:12:00 -0700 (PDT)
Hello,
I would look at the results in your DBA_TAB_COLUMNS and DBA_HISTOGRAMS,
comparing how the EMP table information data appears to the database when you
analyze vs. when you utilize dbms_stats.
I admit that I have some tables that I use the analyze statement on in one of
my databases, and others that I even drop the index stats on to gain the best
performance, (ususally due to poor design or code that is causing the CBO to
make poor choices....), like collecting statistics on the indexed columns vs.
every column. Collecting histograms on columns that contain askew values..
Retaining statistics and then deleting old histograms on tables before
collecting a new method_opt when making drastic changes to how you collect the
stats on a certain table, etc.
Collecting statistics is always a fine art, never an exact science- but there
are best practices that will give you the best chance at the best performance
with the CBO.
I would also try method_opt=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY' , (my
personal favorite if I'm going to collect histograms...) There are times where
histograms are the answer, others they are not and should be deleted. I don't
think there is a one-size-fits-all answer when it comes to creating the best
statistics for a database environment- there are always (at least) one or two
surprises...
Good luck,
Kellyn
--- On Wed, 6/3/09, Johnson, William L (TEIS) <WLJohnson@xxxxxxxxxxxxxxxxxxx>
wrote:
From: Johnson, William L (TEIS) <WLJohnson@xxxxxxxxxxxxxxxxxxx>
Subject: RE: dbms_stats
To: "mhdmehraj@xxxxxxxxx" <mhdmehraj@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx"
<oracle-l@xxxxxxxxxxxxx>
Date: Wednesday, June 3, 2009, 5:48 AM
According to my handy-dandy Oracle SQL Tuning Pocket Reference book, Oracle is
assuming that your data is evenly spread based on key values. If this is not
the case, you should include the clause "FOR ALL INDEXES COLUMNS" to help the
optimizer better understand the specific cardinality of the data. Give
dbms_stats a try with this additional syntax and see what you get.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Mohammed Mehraj hussain
Sent: Wednesday, June 03, 2009 5:38 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: dbms_stats
Hi, oracle 10.2.0.4
i have deleted the statistics on a table EMP
then i have gathered the stats with dbms_stats:
EXEC
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HR',tabname=>'EMP',estimate_percent=>NULL);
-- it takes 498 sec
Then i have queried the emp table
in that last_name column is not indexed and salary column is indexed .
Here is my test:
select count(*) from emp where last_name='KUMAR';
this takes 40.717 sec to produce the output
select count(*) from emp where salary=2000;
this takes 0.187 sec to produce the output.
then with Analyze command:
EXEC DBMS_STATS.delete_schema_STATS('HR');
Analyze table emp compute statistics; -- it takes 430 sec
Here is my test:
select count(*) from emp where last_name='KUMAR';
this takes 12 sec to produce the output
select count(*) from emp where salary=2000;
this takes 0.0001 sec to produce the output.
so from my test , i show analyze is better than DBMS_STATS..... Any
other ideas plz
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
