Introduction
Oracle optimizer cost model relies on statistics collected about the objects involved in a query. The query optimizer uses these statistics to choose the execution plan with the lowest cost, so up-to-date optimizer statistics is key to optimal SQL execution.
Stale statistic about Few tables with data frequently changing can cause performance issues. Therefore customer should be pointed to recommendations in Application Admin Guide (“Database optimization – Automatic gathering of optimizer statistics for Oracle”)
Automated Maintenance Tasks
Oracle uses automatic optimizer statistics collection, enabled by default, as part of AutoTask in all predefined maintenance windows. In this case, the database automatically runs DBMS_STATS to collect optimizer statistics for all schema objects for which statistics are missing or stale.
Oracle strongly recommends not to disable automatic statistics gathering
AutoTasks enabled by default:
- auto optimizer stats job => gathers stale or missing statistics for database objects, generates statistics for optimizer
- auto space advisor job => detects fragmented tablespace and identifies segments that should be reorganized to free space
- automatic sql tuning task => identifies and attempts to tune high load SQL
SQL> SELECT operation_name, attributes, status FROM dba_autotask_operation;
OPERATION_NAME ATTRIBUTES STATUS
------------------------------ ------------------------------------------------------------ --------
auto optimizer stats job ON BY DEFAULT, VOLATILE, SAFE TO KILL ENABLED
auto space advisor job ON BY DEFAULT, VOLATILE, SAFE TO KILL ENABLED
automatic sql tuning task ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL ENABLED
Jobs generates findings and recommendations, available in OEM or AWR reports.
Optimizer statistics
Oracle statistics level parameter:
- basic => disable all statistics and advisories
- typical => default (few stats not enabled)
- all
SQL> show parameter statistics_level;
SQL> SELECT statistics_name, session_status, system_status, activation_level FROM v$statistics_level ORDER BY statistics_name;
STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT
----------------------------- -------- -------- -------
Automated Maintenance Tasks ENABLED ENABLED TYPICAL
...
Show last date of optimizer statistics for tables:
SQL> SELECT
2 table_name,
3 partition_name,
4 num_rows,
5 last_analyzed,
6 global_stats,
7 stale_stats
8 FROM
9 dba_tab_statistics
10 WHERE
11 owner LIKE '%10%'
12 ORDER BY
13 table_name;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLOBAL_STATS STALE_STATS
---------------------------- ---------------------------------- ---------- --------------- ------------- -----------
DEVICE_MEASUREMENT 71968 29-DEC-25 YES NO
DEVICE_MEASUREMENT SYS_P2715 6561 29-DEC-25 YES NO
DEVICE_MEASUREMENT SYS_P1945 6265 29-DEC-25 YES NO
DEVICE_MEASUREMENT SYS_P3127 6580 29-DEC-25 YES NO
DEVICE_MEASUREMENT SYS_P1496 12200 29-DEC-25 YES NO
DEVICE_MEASUREMENT DEVICE_MEASUREMENT_2202 2 29-DEC-25 YES NO
...
STALE_STATS: stats stale (YES) or not
GLOBAL_STATS: stats gathered (YES) or not.
For partitioned tbls: stats calculated without underlying partitions (YES) or not.
- If query needs to access only a single partition, the optimizer uses only the statistics of the accessed partition.
- If query might access more than one partition, optimizer will consider to use global (table level) statistics.
Show last date of optimizer statistics for indexes:
SELECT
index_name,
table_name,
partition_name,
num_rows,
last_analyzed,
global_stats,
stale_stats
FROM
dba_ind_statistics
WHERE
owner LIKE '%10%'
ORDER BY
index_name
FETCH FIRST 20 ROWS ONLY;
INDEX_NAME TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLOBAL_STATS STALE_STATS
--------------------------------------- -------------------------------- ---------------------- --------- ------------- ------------- -----------
FK_DCT_EXEC_CNT_PK_DCT_CLASS DCT_EXECUTION_COUNTER 0 29-DEC-25 YES NO
FK_DEV_JOB_TYP_PK_DCT_CLASS DEVICE_JOB_TYPE 0 29-DEC-25 YES NO
K_DCT_GROUP_DISPATCHED_COUNT DCT_GROUP_DISPATCHED 12 29-DEC-25 YES YES
K_DEVICE_ALARM_LAST_OCCURRED_AT ALARM 5617 29-DEC-25 YES YES
K_DEVICE_ALARM_LAST_OCCURRED_AT ALARM SYS_P4690 679 29-DEC-25 YES NO
K_DEVICE_ALARM_LAST_OCCURRED_AT ALARM SYS_P4689 654 29-DEC-25 YES NO
...
Statistic preferences
- STALE_PERCENT: percentage of rows in a table that must change before the database deems the statistics stale and in need of regathering
- ESTIMATE_PERCENT: percentage of rows used to calculate the statistics
- CONCURRENT: gathering statistics concurrently on multiple objects, or serially one object at a time
SQL> select dbms_stats.get_param('METHOD_OPT') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
SQL> select dbms_stats.get_prefs('STALE_PERCENT','CUSTOMER','DEVICE_MEASUREMENT') stale_percent from dual;
STALE_PERCENT
------------------------------------------------------------------------
10
SQL> select dbms_stats.get_prefs('ESTIMATE_PERCENT','CUSTOMER','DEVICE_MEASUREMENT') est_percent from dual;
EST_PERCENT
------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE
SQL> select dbms_stats.get_prefs('CONCURRENT','CUSTOMER','DEVICE_MEASUREMENT') conc from dual;
CONC
------------------------------------------------------------------------
OFF
Manually collect stats
Automatic optimizer statistics collection should be sufficient for most database objects being modified at a moderate speed. For some reasons manual collection of stats might be needed (after bulk load, outdated stats, truncated tables):
Example non-partitioned table:
SQL> select TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED, GLOBAL_STATS, STALE_STATS from DBA_TAB_STATISTICS where TABLE_NAME='VEE_REGISTER_PROGRESS';
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLO STALE_S
------------------------- ------------------------- ---------- ------------------ --- -------
VEE_REGISTER_PROGRESS 325 10-DEC-25 NO YES
SQL> EXEC DBMS_STATS.gather_table_stats('ZONOS_OSS_10121', 'VEE_REGISTER_PROGRESS');
SQL> select TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED, GLOBAL_STATS, STALE_STATS from ALL_TAB_STATISTICS ...
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLO STALE_S
------------------------- ------------------------- ---------- ------------------ --- -------
VEE_REGISTER_PROGRESS 398 29-DEC-25 YES NO
Example partitioned table:
SQL> EXEC DBMS_STATS.gather_table_stats('ZON', 'ALARM', 'SYS_P1203');
SQL> select TABLE_NAME, PARTITION_NAME, NUM_ROWS, LAST_ANALYZED, GLOBAL_STATS, STALE_STATS from DBA_TAB_STATISTICS where TABLE_NAME='ALARM';
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLO STALE_S
-------------------- -------------------- ---------- ------------------ --- -------
ALARM 1277 30-DEC-25 YES NO
...
ALARM SYS_P1203 496 30-DEC-25 YES NO
ALARM ALARM_2202 0 30-DEC-25 YES NO
Special settings for manual stats collection (set sample size)
To avoid performance issues, or issues related to undo_retention/rollback segment size, you might consider more control over how Oracle Database gathers statistics.
To minimize the resources necessary to gather statistics consider to run DBMS_STATS with arguments. See Oracle docs for more details.
Examples:
Gather stats on a (hopefully) representative sample size only:
SQL> exec dbms_stats.gather_table_stats('CUSTOMER','DEVICE_MEASUREMENT',estimate_percent=>25);
PL/SQL procedure successfully completed.
SQL> select sample_size,num_rows from dba_tables where table_name='MEASUREMENT';
SAMPLE_SIZE NUM_ROWS
----------- ----------
18128 72512
Gather stats on single partition only, sample size 10% (estimate_percent), global statistics aggregated from partition level statistics (granularity), degree of parallelism depends on CPU etc, collect statistics on indexes for this table/part. (cascade):
SQL> exec dbms_stats.gather_table_stats('CUSTOMER','MEASUREMENT', 'SYS_P1192' , estimate_percent=>10, granularity=>'APPROX_GLOBAL AND PARTITION', degree=>4, cascade=>true);
PL/SQL procedure successfully completed.
SQL> select sample_size,num_rows from dba_tables where table_name='MEASUREMENT';
SAMPLE_SIZE NUM_ROWS
----------- ----------
7338 73380
