Oracle Statistics Gathering Timeout
19/01/2024 2 Comments
January 2024 – Oracle 12C, 19C, 21C, 23C+
Gathering object statistic in Oracle is important. The optimizer needs metadata about the object, such as the amount of rows and number of distinct values in a column, to help it decide the optimum way to access your data. This takes effort, and takes place automatically in scheduled windows, both overnight or at the weekend. These windows have a limited duration, and if there’s a lot of data to analyse to get the requisite metadata, it may run out of time.
Large tables, with billions of rows, tend to be partitioned. Oracle invented INCREMENTAL statistics gathering to minimise the amount of time which it needs to spend gathering statistics by ignoring partitions which have not changed.
If you have a large partitioned table, it is frequently a good idea to switch on INCREMENTAL statistics. However, the actual behaviours are not always obvious when doing this and the following message formed part of an online help request: “the manual gather(which is incremental stats) on those partition table runs longer as it tries to gather on all the partitions which are not even modified. How should we tackle this scenario“.
To switch on INCREMENTAL stats gathering:
EXEC dbms_stats.set_table_prefs('<schema>','<table>','INCREMENTAL','TRUE')After the first stats gather, the gather job should only gather stats for partitions which have changed which will minimise the gather time. Global statistics are calculated from the statistics associated with each partition (not subpartition), in conjunction with some stored SYNOPSES to calculate global distinct column values associated with each partition.
Check the stats gathering regularly
Stats gathering fails silently. You need to check it yourself, regularly! To do this query DBA_AUTOTASK_JOB_HISTORY where the JOB_STATUS is “STOPPED”. If you see the JOB_INFO message “Stop job called because associated window was closed”, you should investigate as you ran out of time on the stats gathering. Usually the autotask will catch up at the weekend, when the default window is much longer than the 4 hour weekday window, but you need to understand if old stats are acceptable to your database (some will be, some may not). Importantly, the weekend window may fail too, and then you’re not getting new stats at all.
2 strategies for speeding up the stats gather are to gather stats in parallel, or to gather stats concurrently. Both of these will increase the CPU required by the database to gather the stats, so this needs to be balanced with the workload during the stats gather windows to ensure you are not negatively impacting the application.
Check out the options:
exec DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','AUTOMATIC')
(gathers several tables at the same time using multiple jobs)
exec DBMS_STATS.SET_TABLE_PREFS('<schema>', '<table>', 'DEGREE', n)
(where n is the degree of parallel required, or DBMS_STATS.AUTO_DEGREE)
WARNING: If you are doing ANY PARALLEL processing you really should use Resource Manager to control it
Unexpected Gotchas
Here’s a few gotcha’s you should know if using INCREMENTAL stats gathering:
Problem #1
Partitions which are not labelled as “stale” might actually be stale. The modification of a single row in a partition will cause the stats to re-gather for that partition. You cannot rely on the ALL_TAB_STATISTICS.STALE_STATS status, which will still show as “NO” under these circumstances.
Solution #1:
tell the incremental stats gather to use the tables “stale percent” (default 10%, modifiable with table_pref ‘STALE_PERCENT’) before it sees a partition as stale, rather than *any* change:
exec dbms_stats.set_table_prefs
('<schema>','<table>','INCREMENTAL_STALENESS','USE_STALE_PERCENT')
Problem #2
if the stats on a partition are locked, and a single row in that partition is modified, INCREMENTAL is effectively (silently) disabled as Oracle can’t trust the stats for a global aggregation and you revert to normal “full table” stats gathering.
Solution #2
tell the stats gather to use stale stats on a locked partition (regardless of the amount of DML) as if it was not locked (and combine with the previous solution):
exec dbms_stats.set_table_prefs('schema','table','INCREMENTAL_STALENESS','USE_STALE_PERCENT,USE_LOCKED_STATS';
Problem #3
a new histogram appears due to a SQL having a previously unused predicate (column in the WHERE clause) and the METHOD_OPT is default (for all columns size auto), Oracle may create a new histogram automatically.
Solution #3
check which predicates are candidates for a histogram (in SYS.COL_USAGE$) and explicitly control your histograms. This does mean that you don’t get new histograms automatically, which might not be your intention, but you will only get the histograms that you ask for. exec dbms_stats.set_table_prefs('<schema>','<table>','METHOD_OPT',
'FOR ALL COLUMNS SIZE 1
FOR COLUMNS SIZE 256 colA,colQ
FOR COLUMNS SIZE 2000 colB,colZ');
Problem #4
there are job(s) gathering stats, as well as the auto task, and the job is passing in parameters which are causing the issue (rather than relying on table or global “prefs” for consistency)
Solution #4
Consider ignoring any command line parameters for stats gather jobs. In this way, every stats gather will only use the preferences and will be consistent across each gather
dbms_stats.set_global_prefs(‘PREFERENCE_OVERRIDES_PARAMETER‘,’TRUE’);
or
dbms_stats.set_table_prefs(‘<schema>’,'<table>’,’PREFERENCE_OVERRIDES_PARAMETER‘,’TRUE’);
To validate what is going on, you can always (carefully!) trace the stats gather, either at a table level or globally using the UNDOCUMENTED preference “trace”. Be aware that you may get a lot of trace file output and a potential performance hit on the stats gather job too.
exec dbms_stats.set_global_prefs('trace',65532);Finally, when you switch on INCREMENTAL statistics, Oracle needs to store additional SYNOPSES data to allow it to calculate the number of distinct values in a column more easily. This can take a lot of space in SYSAUX, although this has been significantly improved in Oracle 12.2 onwards with the synopses tables occupying around 3-5% of their previous large footprint. For more information about SYNOPSES, Oracle Support maintains a note with a query: 1953961.1 and for more information about INCREMENTAL stats in general, the Oracle Optimizer PM Nigel Bayliss has some excellent blog posts from 2020 here
Neil Chandler's DB Blog 