How to Use DBMS_STATS.GATHER_DATABASE_STATS ?
We usually gather statistics of the database globally after data migration. The following examples are very practical in post-migration phase.
Default Preferences
You can do it without any indications, it will run as its own will (default preferences).
SQL> exec dbms_stats.gather_database_stats();
PL/SQL procedure successfully completed.Sampling Percentage
Probably, you might want to indicate the sampling percentage.
SQL> exec dbms_stats.gather_database_stats(estimate_percent => 20);
PL/SQL procedure successfully completed.It's to sample 20% rows on each table.
Degree of Parallelism (DOP)
Seems slow? you can raise the degree of parallelism (DOP) to speed it up.
SQL> set timing on;
SQL> exec dbms_stats.gather_database_stats(estimate_percent => 20, degree => 4);
PL/SQL procedure successfully completed.
Elapsed: 02:41:26.92Cascade to Index?
Will it also gather index statistics? Maybe, maybe not. We can check it out by this.
SQL> select dbms_stats.get_prefs('CASCADE') pref_cascade from dual;
PREF_CASCADE
-------------------------
DBMS_STATS.AUTO_CASCADEAUTO_CASCADE means that the database will decide whether to collect statistics for indexes or not. That is to say, some statistics of indexes may be gathered, some may not. You can explicitly indicate to gather indexes statistics as well by this.
SQL> exec dbms_stats.gather_database_stats(estimate_percent => 20, degree => 4, cascade => TRUE);
PL/SQL procedure successfully completed.For more information about using this package properly, you may refer to the documentation. GATHER_DATABASE_STATS Procedures