How to Gather Statistics on Database Level for Various Situations

  • by

DBMS_STATS.GATHER_DATABASE_STATS

We usually gather statistics of the database globally after data migration.

Default

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

Probably, you might want to indicate the sampling percentage.

SQL> exec dbms_stats.gather_database_stats(estimate_percent => 20);

PL/SQL procedure successfully completed.

Parallelism

Seems slow? you can raise the degree of parallelism 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.92

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_CASCADE

AUTO_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

Leave a Reply

Your email address will not be published. Required fields are marked *