Performance

How to Gather Statistics on Database Level for Various Situations

DBMS_STATS.GATHER_DATABASE_STATS

You can do it without any indications, it will run as its own will.

SQL> exec dbms_stats.gather_database_stats();

PL/SQL procedure successfully completed.

Probably, you might want to indicate the sampling percentage.

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

PL/SQL procedure successfully completed.

Seems slow? you can gather the statistics parallelly 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

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 *