From time to time we see a complaint on OTN about the stats history tables being the largest objects in the SYSAUX tablespace and growing very quickly, with requests about how to work around the (perceived) threat. The quick answer is – if you need to save space then stop holding on to the history for so long, and then clean up the mess left by the history that you have captured; on top of that you could stop gathering so many histograms because you probably don’t need them, they often introduce instability to your execution plans, and they are often the largest single component of the history (unless you are using incremental stats on partitioned objects***)
For many databases it’s the histogram history – using the default Oracle automatic stats collection job – that takes the most space, here’s a sample query that the sys user can run to get some idea of how significant this history can be:
SQL> select table_name , blocks from user_tables where table_name like 'WRI$_OPTSTAT%HISTORY' order by blocks; TABLE_NAME BLOCKS -------------------------------- ---------- WRI$_OPTSTAT_AUX_HISTORY 80 WRI$_OPTSTAT_TAB_HISTORY 244 WRI$_OPTSTAT_IND_HISTORY 622 WRI$_OPTSTAT_HISTHEAD_HISTORY 1378 WRI$_OPTSTAT_HISTGRM_HISTORY 2764 5 rows selected.
As you can see the “histhead” and “histgrm” tables (histogram header and histogram detail) are the largest stats history tables in this (admittedly very small) database.
Oracle gives us a couple of calls in the dbms_stats package to check and change the history setting, demonstrated as follows:
SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION --------------------------- 31 1 row selected. 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 1 row selected.
Changing the retention period doesn’t reclaim any space, of course – it simply tells Oracle how much of the existing history to eliminate in the next “clean-up” cycle. This clean-up is controllled by a “savtime” column in each table:
SQL> select table_name from user_tab_columns where column_name = 'SAVTIME' and table_name like 'WRI$_OPTSTAT%HISTORY'; TABLE_NAME -------------------------------- WRI$_OPTSTAT_AUX_HISTORY WRI$_OPTSTAT_HISTGRM_HISTORY WRI$_OPTSTAT_HISTHEAD_HISTORY WRI$_OPTSTAT_IND_HISTORY WRI$_OPTSTAT_TAB_HISTORY 5 rows selected.
If all you wanted to do was stop the tables from growing further you’ve probably done all you need to do. From this point onwards the automatic Oracle job will start deleting the oldest saved stats and re-using space in the existing table. But you may want to be a little more aggressive about tidying things up, and Oracle gives you a procedure to do this – and it might be sensible to use this procedure anyway at a time of your own choosing:
SQL> execute dbms_stats.purge_stats(sysdate - 7);
Basically this issues a series of delete statements (including a delete on the “stats operation log (wri$_optstat_opr)” table that I haven’t previously mentioned) – here’s an extract from an 11g trace file of a call to this procedure (output from a simple grep command):
delete /*+ dynamic_sampling(4) */ from sys.wri$_optstat_tab_history where savtime < :1 and rownum <= NVL(:2, rownum) delete /*+ dynamic_sampling(4) */ from sys.wri$_optstat_ind_history h where savtime < :1 and rownum <= NVL(:2, rownum) delete /*+ dynamic_sampling(4) */ from sys.wri$_optstat_aux_history where savtime < :1 and rownum <= NVL(:2, rownum) delete /*+ dynamic_sampling(4) */ from sys.wri$_optstat_opr where start_time < :1 and rownum <= NVL(:2, rownum) delete /*+ dynamic_sampling(4) */ from sys.wri$_optstat_histhead_history where savtime < :1 and rownum <= NVL(:2, rownum) delete /*+ dynamic_sampling(4) */ from sys.wri$_optstat_histgrm_history where savtime < :1 and rownum <= NVL(:2, rownum)
Two points to consider here: although the appearance of the rownum clause suggests that there’s a damage limitation strategy built into the code I only saw one commit after the entire delete cycle, and I never saw a limiting bind value being supplied. If you’ve got a large database with very large history tables you might want to delete one day (or even just a few hours) at a time. The potential for a very long, slow, delete is also why you might want to do a manual purge at a time of your choosing rather than letting Oracle do the whole thing on auto-pilot during some overnight operation.
Secondly, even though you may have deleted a lot of data from these table you still haven’t reclaimed the space – so if you’re trying to find space in the sysaux tablespace you’re going to have to rebuild the tables and their indexes. Unfortunately a quick check of v$sysaux_occupants tells us that there is no official “move” producedure:
SQL> execute print_table('select occupant_desc, move_procedure, move_procedure_desc from v$sysaux_occupants where occupant_name = ''SM/OPTSTAT''') OCCUPANT_DESC : Server Manageability - Optimizer Statistics History MOVE_PROCEDURE : MOVE_PROCEDURE_DESC : *** MOVE PROCEDURE NOT APPLICABLE ***
So we have to run a series of explicit calls to alter table move and alter index rebuild. (Preferably not when anyone is trying to gather stats on an object). Coding that up is left as an exercise to the reader, but it may be best to move the tables in the order of smallest table first, rebuilding indexes as you go.
*** Incremental stats on partitioned objects: I tend to assume that sites which use partitioning are creating very large databases and have probably paid a lot more attention to the details of how to use statistics effectively and successfully; that’s why this note is aimed at sites which don’t use partitioning and therefore think that the space taken up by the stats history significant.