DBMSSTATS

DBMS_STATS is an Oracle Database package that manages optimizer statistics. It provides facilities to collect and maintain statistics that describe data distribution, table sizes, and other properties used by the cost-based optimizer to estimate query costs and choose execution plans. The statistics are stored in the data dictionary and can be refreshed as data changes.

The package can gather statistics for the entire database, a specific schema, or individual tables and indexes. It collects core metrics such as row counts, blocks, and average row length, and it can compute column statistics including the number of distinct values, density, and histograms. It also supports incremental statistics for partitioned tables and can update statistics for indexes.

Statistics gathering can be performed manually by invoking procedures such as GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS, or automatically by Oracle’s automatic statistics gathering feature. Users can control collection behavior through preferences such as estimate_percent, method_opt, and cascade, and can publish, reset, or delete statistics using corresponding procedures.

The gathered statistics are exposed by data dictionary views such as DBA_TABLES, DBA_TAB_COLUMNS, DBA_INDEXES, and DBA_IND_COLUMNS, allowing monitoring and auditing of the collected data. These statistics influence the optimizer’s plan choices, and changes to statistics may affect performance or trigger invalidation of dependent objects, depending on the nature of the changes.

Best practices include scheduling statistics gathering during maintenance windows for large data changes, using incremental statistics for partitioned tables to reduce cost, and validating performance impact by comparing query plans before and after statistics updates.