Oracle DBMS_STATS: Gather Schema and Table Statistics

calendar Dec 11, 2024 / · 8 min read · Database Management Performance Optimization dbms_stats.gather_schema_stats dbms_stats  ·
Share on: linkedin copy

Optimizing Performance with Oracle Database: Gathering Schema and Table Statistics

Purpose

Automatic optimizer statistics collection runs by itself inside the maintenance windows. Manual DBMS_STATS calls run when you decide, on the objects you choose, with the options you set. The SQL Tuning Guide names a different principal difference: automatic collection prioritizes the objects that need statistics. A DBA keeps a few manual commands ready for the cases the automatic job does not cover. The commands below gather statistics for one schema, for the SYS schema, and for one table.

Oracle stores optimizer statistics in the data dictionary. They describe the size and shape of tables, columns and indexes. The optimizer reads them to estimate how many rows a step will return, and then it picks the plan with the lowest cost. If the numbers are missing or old, the estimates are wrong, and the plan can be slow.

Most of the time the automatic job is enough. You run the manual commands in a few clear cases. One case is a bulk load that must be queried at once. Another is a volatile table that is deleted or truncated and then rebuilt during the day. The commands here stay at the schema and table level. Database-wide gathering is a separate call.

Code

 1-- 1. Gather statistics for every object in one schema (default options)
 2execute dbms_stats.gather_schema_stats('SCOTT');
 3
 4-- 2. Same schema, with the sample, the column option and the index option set
 5execute dbms_stats.gather_schema_stats( -
 6  ownname          => 'SCOTT', -
 7  estimate_percent => 1, -
 8  method_opt       => 'FOR ALL COLUMNS SIZE 1', -
 9  cascade          => TRUE);
10
11-- 3. Let the database choose the sample size
12execute dbms_stats.gather_schema_stats( -
13  ownname          => 'SCOTT', -
14  cascade          => TRUE, -
15  estimate_percent => dbms_stats.auto_sample_size);
16
17-- 4. The SYS schema (the owner of the data dictionary)
18execute dbms_stats.gather_schema_stats( -
19  ownname => 'SYS', -
20  cascade => TRUE);
21
22-- 5. One table
23exec dbms_stats.gather_table_stats('<owner>', '<table_name>');

Code Breakdown

The trailing - at the end of a line is the SQL*Plus continuation character. It lets one execute command run over several lines.

Statement 1: default schema gathering

gather_schema_stats('SCOTT') gathers statistics for all objects in the schema. The first argument is ownname. The Oracle reference says NULL for this argument means the current schema. Every other option uses its default.

Statement 2: explicit options

  • ownname => 'SCOTT' names the schema to analyze.
  • 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.
  • method_opt => 'FOR ALL COLUMNS SIZE 1' controls column statistics and histograms. The documented default is FOR ALL COLUMNS SIZE AUTO. This example replaces that default with a fixed size clause of 1.
  • cascade => TRUE gathers index statistics together with the table statistics. The reference says this is the same as running GATHER_INDEX_STATS on each index in the schema.

Statement 3: automatic sample size

auto_sample_size is a constant, and you pass it as the value of estimate_percent. It is not a separate parameter. The reference says that with this constant the database decides the right sample size, and that this is the default. So you can also leave estimate_percent out and get the same result, as long as no ESTIMATE_PERCENT preference has been set with SET_DATABASE_PREFS, SET_GLOBAL_PREFS, SET_SCHEMA_PREFS or SET_TABLE_PREFS.

Statement 4: the SYS schema

The call is the same as for any other schema, but the privilege rule is different. For objects owned by SYS, you must own the object. Otherwise you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege. The Oracle SQL Tuning Guide also lists GATHER_DICTIONARY_STATS, which collects statistics for all system schemas, including SYS and SYSTEM.

Statement 5: one table

gather_table_stats takes the owner and the table name. It collects table, column and index statistics. Replace <owner> and <table_name> with real names before you run it.

Key Points

  • Schema gathering and table gathering differ in scope and in options. GATHER_SCHEMA_STATS covers all objects in a schema. GATHER_TABLE_STATS covers one table. The options parameter of the schema procedure also accepts GATHER STALE, GATHER EMPTY and the LIST values, and it has objlist and obj_filter_list. The table procedure has partname and stattype instead.
  • cascade defaults to DBMS_STATS.AUTO_CASCADE. With that value, Oracle decides whether index statistics are needed. Setting TRUE forces them.
  • The calls end with a commit. The package reference says most procedures commit the current transaction, do the work, and commit again.
  • Privileges matter. To run the schema procedure you must own the object, or hold the ANALYZE ANY privilege.
  • Statistics can be locked. Most procedures have a force parameter that overrides a lock on statistics.
  • Cursors may be invalidated. The no_invalidate parameter decides what happens to cursors that depend on the object. The default lets Oracle choose when to invalidate them.

Insights and Best Practices

Prefer the automatic sample size

The Oracle documentation names AUTO_SAMPLE_SIZE as the default for estimate_percent. A fixed value such as 1 is a choice you make on purpose, for example to shorten a run on a very large schema. If you set it, compare plans before and after, because you have changed what the optimizer sees.

Know what the automatic job already does

By default, Oracle runs DBMS_STATS automatically to collect statistics for objects whose statistics are missing or stale. This runs inside the predefined maintenance windows. The SQL Tuning Guide says a monitored table is stale when it has been modified by more than 10 percent. Because of this, a manual run is not needed after every change. Check first whether the statistics are really old.

Gather only what is missing or stale

The options parameter of GATHER_SCHEMA_STATS accepts GATHER AUTO. With it, the call collects statistics only for objects that have none or whose statistics are stale. The default value, GATHER, gathers statistics on all objects in the schema.

1execute dbms_stats.gather_schema_stats( -
2  ownname => 'SCOTT', -
3  options => 'GATHER AUTO');

Use parallelism on large tables

The default for degree is NULL, which uses the degree set by the DEGREE clause on the table. The Oracle SQL Tuning Guide recommends DBMS_STATS.AUTO_DEGREE, which lets the database choose a degree from the object size and the parallel settings.

1execute dbms_stats.gather_table_stats( -
2  ownname => '<owner>', -
3  tabname => '<table_name>', -
4  degree  => dbms_stats.auto_degree);

Choose a quiet time

Gathering reads your data, so it uses CPU and I/O. As a practical rule, run large manual jobs in a quiet period when few users are on the system.

Check results after the run

After a gather, look at the plans of your key queries. The goal is not fresh numbers for their own sake. What matters is that the statistics are representative of your data, not that they are new.

When to Run This

  • After a bulk load, when queries must run before the maintenance window starts
  • After large data loads or database maintenance, even when the automatic job is scheduled
  • When a plan changed for the worse and the statistics on a table look old
  • When a new schema or table was created and no one has gathered statistics yet
  • When you tested a sampling change and want the same setting on one schema only
  • When you need to refresh a single table without touching the rest of the schema

Troubleshooting Common Issues

ORA-20000. The reference lists this error for a schema or table that does not exist, or for insufficient privileges. Check the spelling of ownname and tabname. Then check that your account owns the object or has the ANALYZE ANY privilege.

ORA-20001. The reference lists this error for a bad input value. Check each argument. For example, estimate_percent must be between 0.000001 and 100.

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

A plan did not change after the run. By default, the database decides when to invalidate dependent cursors. The no_invalidate parameter controls this: FALSE invalidates them at once.

Missing statistics on a new object. The SQL Tuning Guide says statistics can be missing when an application creates new objects and does not call DBMS_STATS afterwards. If statistics are missing at parse time, the database may gather dynamic statistics. Those are lower in quality than statistics gathered with DBMS_STATS, so a manual gather is the better fix.

References

Posts in this series