RE: dbms_stats
Date: Wed, 3 Jun 2009 15:12:00 -0700 (PDT)
Message-ID: <860038.31481.qm_at_web32008.mail.mud.yahoo.com>
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_at_tycoelectronics.com> wrote:
From: Johnson, William L (TEIS) <WLJohnson_at_tycoelectronics.com>
Subject: RE: dbms_stats
To: "mhdmehraj_at_gmail.com" <mhdmehraj_at_gmail.com>, "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mohammed Mehraj hussain
Sent: Wednesday, June 03, 2009 5:38 AM
To: oracle-l_at_freelists.org
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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 03 2009 - 17:12:00 CDT