What is DBMS_STATS
The DBMS_STATS package was introduced in Oracle 8i and is Oracle’s preferred method of gathering object statistics.
DBMS_STATS is a package in Oracle Database that provides procedures to manage statistics for database objects. Statistics are essential for the optimizer to make efficient execution plans for SQL statements. The package offers procedures to gather, export, import, and delete statistics for objects such as tables, indexes, partitions, and columns.
The primary benefits of using it are
- parallel execution
- long-term storage of statistics
- Ability to import/export statistics
- Able to manually modify statistics
- Ability to modify, view, export, import, and delete statistics.
The DBMS_STATS package can gather statistics on tables and indexes, as well as individual columns and partitions of tables.
When we generate statistics for a table, column, or index, if the data dictionary already contains statistics for the object, then Oracle updates the existing statistics. The older statistics are saved and can be restored later if necessary.
It is recommended to gather statistics regularly, especially after significant changes to the data or schema, to ensure optimal query performance. The DBMS_STATS package offers a variety of options for managing statistics efficiently in an Oracle database.
What are the procedures to collect statistics?
How to collect table statistics
It is done using gather_table_stats
It takes the default value from dbms_stats.get_parms whichever option is not given while executing gather table stats
Example
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP',ESTIMATE_PERCENT=>15);
How to collect schema statistics
It is done using gather_table_stats
It takes the default value from dbms_stats.get_parms whichever option is not given while executing gather table stats
Examples
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'SCOTT',OPTIONS=>'GATHER AUTO');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'HR',CASCADE=>TRUE);
How to collect Database statistics
Examples
EXEC DBMS_STATS.GATHER_DATABASE_STATS;
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>20);
The same way is there for Gather_index_stats also
I hope this article on How to gather Statistics with DBMS_STATS Procedures helps you. It is the oracle’s preferred way of generating statistics.
Related Articles
How to import statistics in Oracle
How to export statistics in Oracle
Optimizer statistics preferences in Oracle
How to check gather stats on a table