dbms_stats.delete_table_stats
We see two different situations using DBMS_STATS.
Case 1) oracle 10.2.0.4
we gathered stats using
1) EXEC dbms_stats.gather_table_stats(ownname=>'OWNER',tabname=>'TABLE_NAME',partname=>NULL,estimate_percent=>25,cascade=>TRUE,DEGREE=>8);
After we completed gather stats for all tables, there was no improvement in query response. Then we did delete_stats
1) EXEC dbms_stats.delete_table_stats(ownname=>'OWNER_NAME',tabname=>'TABLE_NAME');
and again did gather stats using
2) EXEC dbms_stats.gather_table_stats(ownname=>'OWNER',tabname=>'TABLE_NAME',partname=>NULL,estimate_percent=>25,cascade=>TRUE,DEGREE=>8);
Now the query ran very fast.
Case 2)
On another database, Which is also on oracle 10.2.0.4
Delete stats was executed first and then Gather stats was executed with same parameters.
EXEC dbms_stats.delete_table_stats(ownname=>'OWNER_NAME',tabname=>'TABLE_NAME');
EXEC dbms_stats.gather_table_stats(ownname=>'OWNER',tabname=>'TABLE_NAME',partname=>NULL,estimate_percent=>25,cascade=>TRUE,DEGREE=>8);
Query was running very slowly.
Then we restored the stats which was done without delete stats and just gather_table_stats. Query was running very fast.
Would you please let us know when to use delete_table_stats?
When not to use delete_table_stats before gather_table_stats?