table stats

From: Daud <daud11_at_hotmail.com>
Date: 20 Apr 2004 23:25:07 -0700
Message-ID: <f0bf3cc3.0404202225.4145b7f5@posting.google.com>

Hi All

This morning we reloaded two of our tables and then I ran the dbms_stats.gather_table_stats to collect statistics. The two tables have pretty much the same the number of records as before (more than 8 mil each).
I found that when I did this it caused the exec plan of some of our queries to change (to one that was not the best) and performance degraded by quite a lot.
Then, out of curiousity I collected statictics from the 2 tables using:
analyze table <mytab> estimate statistics. After I did that, the exec plan changed to what it was before and everything went back to normal. Why is this? What is the difference what method 1 (dbms_stats) and method 2 (analyze). I thought method 1 is the way to go.
The exact command: dbms_stats.gather_table_stats ( ownname => null, tabname => 'MYTAB', method_opt => null, cascade => true, estimate_percent => 20).
Oracle version 8.1.7.4 on HP-UX 11.0.

regards
Daud Received on Wed Apr 21 2004 - 01:25:07 CDT