4 Things To Know About Statistics History

By default, whenever new statistics are gathered in the database, the existing statistics are backed up in the data dictionary.
The statistics backups are stored in the SYSAUX tablespace, in the WRI$_OPTSTAT%HISTORY tables. The statistics can take up a lot of space in SYSAUX, especially if you are gathering many histograms, and have incremental statistics turned on, on partitioned tables, or if the retention for the statistics backup is too high.

If you are curious to see the tables related to statistics backup, and their size, run the following query:

select segment_name , sum(bytes)/1024/1024 size_MB 
from dba_segments 
where segment_name like 'WRI$_OPTSTAT%HISTORY' 
and segment_type like 'TABLE%'
group by segment_name
order by size_MB;

Why do we need statistics backups?

If the optimizer generates poor performing execution plans for the SQL statements, due to new stats, then you have the option to restore previous stats from these backups. This is a great thing! This can save you many times!

Now that we know these statistics backup exist, they take up space, which will generate alerts if we space is getting tight…so it is natural that you are going to ask me the following questions:

1. What is the default statistics history retention set to?
2. How to change the retention settings?
3. How can I cleanup or delete these statistics?
4. What statistics are available for me?

1. What is the default statistics history retention set to?

It is always good to know what is the retention set to in the databases you are managing. By default the retention is set to 31 days.
In order to find out the retention, run the following SQL statement:

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
			 31

2. How to change the retention settings?

Is the default 31 days a good value? Will SYSAUX grow too much?
That of course, it depends on a few factors: are you gathering histograms, are you gathering incremental stats, are you gathering stats daily, weekly, monthly?

If you want to change or need to change the retention, this is how you do it:

SQL> execute dbms_stats.alter_stats_history_retention(7);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
			  7

An interesting thing to note in 12c, is that you can have different retention for the CDB and PDBs. The above statement will only alter the database you are in.

3. How can I cleanup or delete statistics history?

Oracle takes care of the cleanup for you. More precisely MMON wakes up and cleans up the historical stats, that are older than the retention.

You can also manually delete the data as well from these tables, with the following statement:

-- >> in this case you want to keep 7 days worth of stats, everything else is getting deleted.

SQL> execute dbms_stats.purge_stats(sysdate - 7);

If your tables are large, it is recommended to delete the data in smaller chunks, to avoid running out of undo tablespace.

To check for space usage in SYSAUX tablespace for the WRI$ tables, run the query:

SQL> select occupant_desc, space_usage_kbytes 
     from v$sysaux_occupants 
     where occupant_desc like '%Statistics%';

OCCUPANT_DESC							 SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
Server Manageability - Optimizer Statistics History			      26176

4. What statistics are available for me?

You check the retention setting with dbms_stats.get_stats_history_retention, but what are the oldest stats that you can compare or restore?

SQL> select dbms_stats.get_stats_history_availability from dual;  

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
15-NOV-17 02.22.31.661730000 PM -08:00

Putting it all together in a script

Prompt Stats History Retention:

select dbms_stats.get_stats_history_retention from dual;

Prompt Oldest Stats Available To Restore:

select dbms_stats.get_stats_history_availability from dual; 

Prompt Space Usage in SYSAUX

select occupant_desc, space_usage_kbytes 
from v$sysaux_occupants 
where occupant_desc like '%Statistics%';

Prompt WRI$ Tables Size

select segment_name , sum(bytes)/1024/1024 size_MB 
from dba_segments 
where segment_name like 'WRI$_OPTSTAT%HISTORY' 
and segment_type like 'TABLE%'
group by segment_name
order by size_MB;


Stats History Retention:

GET_STATS_HISTORY_RETENTION
---------------------------
			  7

Oldest Stats Available To Restore:

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
15-NOV-17 08.22.35.268746000 PM -08:00

Space Usage in SYSAUX

OCCUPANT_DESC							 SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
Server Manageability - Optimizer Statistics History			      26176

WRI$ Tables Size

SEGMENT_NAME					      SIZE_MB
-------------------------------------------------- ----------
WRI$_OPTSTAT_AUX_HISTORY				.0625
WRI$_OPTSTAT_IND_HISTORY				.1875
WRI$_OPTSTAT_TAB_HISTORY				.1875
WRI$_OPTSTAT_HISTHEAD_HISTORY				.3125
WRI$_OPTSTAT_HISTGRM_HISTORY				    1

Are you curious to know how to compare the current statistics with the statistics from the backup tables? Don’t miss out next week’s post here on dbaparadise.com!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana