dbms_stats

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