# dbms_stats

Date: Wed, 3 Jun 2009 15:07:43 +0530

Message-ID: <b081c0a50906030237k71898297g919a60615e684ff8_at_mail.gmail.com>

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
Received on Wed Jun 03 2009 - 04:37:43 CDT