Oracle Statistics Gathering with DBMS_STATS Explained

calendar Dec 12, 2024 / · 8 min read · Database Management Performance Optimization dbms_stats dbms_stats.gather_database_stats dba_tab_statistics dba_tab_col_statistics dba_ind_statistics dba_tab_modifications  ·
Share on: linkedin copy

Understanding Oracle Database Statistics Gathering: A Comprehensive Guide

Purpose

DBA_TAB_STATISTICS is the view that shows what the optimizer knows about each table: the number of rows, the average row length and the number of data blocks. DBMS_STATS is the package that fills those numbers in. The optimizer reads them to estimate how many rows a step will return. Then it picks the plan with the lowest cost. If the numbers are wrong or old, the estimates are wrong, and the plan can be slow.

The sections below explain what optimizer statistics are, which views show them, how the automatic job works and how to tell when statistics are stale. It also shows the database-wide gather command and what its options mean. The schema-level and table-level commands have their own post: Gather Schema and Table Statistics with DBMS_STATS.

The practical question is simple. Is the automatic job enough for this database, or does a manual gather need to run now? Most of the time the job is enough. The checks below show you when it is not.

Code

 1-- 1. Gather stats on the entire database (default options)
 2execute dbms_stats.gather_database_stats;
 3
 4-- 2. Gather stats on the entire database with options set
 5execute dbms_stats.gather_database_stats( -
 6  estimate_percent => 1, -
 7  method_opt       => 'FOR ALL COLUMNS SIZE 1', -
 8  cascade          => TRUE);
 9
10-- 3. Check table statistics
11SELECT owner, table_name, num_rows, avg_row_len, blocks,
12       last_analyzed, stale_stats
13FROM   dba_tab_statistics
14WHERE  owner = 'SH'
15AND    table_name = 'CUSTOMERS';
16
17-- 4. Check column statistics
18SELECT column_name, num_distinct, num_nulls, histogram
19FROM   dba_tab_col_statistics
20WHERE  owner = 'SH'
21AND    table_name = 'CUSTOMERS';
22
23-- 5. Check index statistics
24SELECT index_name, blevel, leaf_blocks, distinct_keys, last_analyzed, stale_stats
25FROM   dba_ind_statistics
26WHERE  owner = 'SH'
27AND    table_name = 'CUSTOMERS';
28
29-- 6. See how much DML each table has had since the last gather
30SELECT table_owner, table_name, inserts, updates, deletes, truncated
31FROM   dba_tab_modifications
32WHERE  table_owner = 'SH';

Replace SH and CUSTOMERS with an owner and table from your own database. The trailing - at the end of a line in statement 2 is the SQL*Plus continuation character.

Code Breakdown

Statement 1: default database-wide gather

gather_database_stats with no arguments gathers statistics for every object in the database. The package reference describes it in one sentence: "This procedure gathers statistics for all objects in the database." Every option takes its default value.

Statement 2: explicit options

  • estimate_percent => 1 sets the share of rows to sample. The valid range is 0.000001 to 100. A value of 1 reads about one row in every hundred. The default is the constant DBMS_STATS.AUTO_SAMPLE_SIZE, which lets the database choose the sample size.
  • method_opt => 'FOR ALL COLUMNS SIZE 1' controls column statistics and histograms. The documented default is FOR ALL COLUMNS SIZE AUTO. This example replaces it with a fixed size value of 1, which is the number of histogram buckets.
  • cascade => TRUE gathers index statistics together with the table statistics. This is the same as running GATHER_INDEX_STATS on each index. The default is DBMS_STATS.AUTO_CASCADE, which lets the database decide whether index statistics are needed.

Statements 3 to 5: what the optimizer sees

  • DBA_TAB_STATISTICS holds table statistics. The NUM_ROWS value is used when the database works out cardinality. The STALE_STATS column tells you if the statistics are stale.
  • DBA_TAB_COL_STATISTICS holds column statistics, such as the number of distinct values, the number of nulls and histogram data.
  • DBA_IND_STATISTICS holds index statistics. BLEVEL shows the number of blocks needed to go from the root block to a leaf block. The optimizer uses index statistics to work out the cost of index scans.

Each of these views has a USER_ and an ALL_ version. Use USER_ for your own schema when you do not have access to the DBA_ views.

Statement 6: change tracking

DBA_TAB_MODIFICATIONS shows the approximate number of insert, update and delete operations on each table, and whether the table was truncated, since the last statistics collection. The STALE_STATS flag is based on this view.

Key Points

  • The automatic job is the default. The package reference says: "By default, the database collects statistics automatically, so this package is intended only for specialized cases."
  • Only dictionary statistics count. The reference says only optimizer statistics stored in the data dictionary have an effect on the cost-based optimizer.
  • Stale means more than 10 percent changed. The default value of STALE_PERCENT is 10. If DML changes more than 10 percent of the rows, the table is stale.
  • The calls commit. Most procedures in the package commit the current transaction, perform the operation, and then commit again.
  • Locks can block a gather. Most of the procedures have a force parameter that overrides a lock on statistics.
  • Privileges. To run GATHER_DATABASE_STATS you need the SYSDBA role, or both the ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.
  • Old statistics are kept. Whenever statistics in the dictionary change, the database saves the old versions so you can restore them later.

Insights and Best Practices

How the automatic job works

Automatic optimizer statistics collection runs as part of the AutoTask framework. By default it runs in all predefined maintenance windows, and one window is scheduled for each day of the week. The main difference from manual collection is the priority order. Before the window closes, the job assesses all objects and puts first those that have no statistics or very old statistics. It also honors all statistics preferences set in the database.

Automatic collection does not gather system statistics. If you need those, you gather them by hand.

How to check for stale statistics

Query the STALE_STATS column in DBA_TAB_STATISTICS and DBA_IND_STATISTICS. The column has three states: the statistics are stale, they are not stale, or they were not collected. Check for the third state too. A table with no statistics at all is a larger risk than a table with slightly old ones.

In Oracle Database 12.2 and later you no longer need to call DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO first to make this view data current. The statistics in these views come from both disk and memory.

Do not use LAST_ANALYZED alone as your test. A recent date does not prove the statistics are valid for your data, and an old date can be fine for a table that does not change.

Gather only what needs it

The options parameter accepts GATHER STALE and GATHER AUTO. With GATHER AUTO, the call collects statistics only for objects with no statistics or stale statistics. The default, GATHER, gathers statistics on all objects. On a large database, the GATHER AUTO form is often the shorter run:

1execute dbms_stats.gather_database_stats(options => 'GATHER AUTO');

Choose a quiet time

Gathering statistics can be very resource intensive for the server. Schedule large manual runs outside your peak hours, and test a sampling change on one schema first.

Aim for representative statistics

You do not always need statistics that are up to date to the minute. You need statistics that are representative of your data. For a table that changes slowly, a weekly or monthly gather can be enough. For a table that changes fast, gather often enough that the statistics do not go stale, but not so often that the gather itself slows the system.

Partitioned tables and incremental statistics

For a large partitioned table, incremental statistics scan only the changed partitions. When this is enabled, DBMS_STATS gathers statistics and creates synopses for changed partitions only. To enable it, use DBMS_STATS.SET_TABLE_PREFS to set the INCREMENTAL value to true. The default is false.

When to Use This Check

  • After a bulk load, when queries must run before the maintenance window starts.
  • When a plan changed for the worse and the table statistics look old or missing.
  • When tables are deleted or truncated and rebuilt during the day.
  • When you want to confirm that the automatic job reached a new schema.
  • Before you change estimate_percent or method_opt, to record the current values.
  • When you need system statistics, which the automatic job does not gather.

Troubleshooting Common Issues

The statistics did not change after the run. The table may have its statistics locked. Most procedures in the package have a force parameter that overrides a lock. Use it only if you are sure the lock was not set on purpose.

A privilege error on the database-wide call. Check that your account has the SYSDBA role, or both ANALYZE ANY DICTIONARY and ANALYZE ANY. For one schema, use the schema-level call described in the sibling post linked above.

Statistics are missing on a volatile table. If you set the statistics of a volatile object to null, the database gathers the statistics it needs during optimization, using dynamic statistics. The OPTIMIZER_DYNAMIC_SAMPLING initialization parameter controls this feature. Dynamic statistics do not replace a planned gather for a table that every query uses.

A gather made a plan worse. The database saves old versions of statistics whenever they change, so earlier statistics can be restored. For the next run, keep a record of the key plans from before the gather so you can compare them afterwards.

A query on an X$ table has no statistics. Unlike other tables, the database does not automatically use dynamic statistics for statements that reference X$ tables when statistics are missing. Expect plans for such statements to rest on missing statistics.

References

Posts in this series