RE: dbms_stats

From: Johnson, William L (TEIS) <"Johnson,>
Date: Wed, 3 Jun 2009 07:48:44 -0400
Message-ID: <2F161F8A09B99B4ABF8AE832D546E78903DD02A0C8_at_us194mx002.tycoelectronics.net>

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 Received on Wed Jun 03 2009 - 06:48:44 CDT