17 Aralık 2015 Perşembe

[EN] Cleaning SYSAUX Optimizer Statistics History

When new optimizer statistics collected for a database object, old statisitcs are sent to SYSAUX tablespace. This information is stored in "Server Manageability - Optimizer Statistics History" component of SYSAUX tablespace. 

By this feature we can use DBMS_STATS.RESTORE... procedures.

The default retention for Optimizer Statistics History is 31 days. Old information are automaticly deleted by Oracle Database. Without any manual intervention.

But sometimes this 31 day retention may be too much. In one of my database this component was 75 GB.

--Check SYSAUX components and their sizes.
select * from v$sysaux_occupants order by space_usage_kbytes desc;


--Check biggest segments in SYSAUX
select tablespace_name,owner,segment_name,partition_name,segment_type,trunc((bytes/1024/1024/1024),2) "GB" 
from dba_segments where tablespace_name = 'SYSAUX' order by bytes desc; 

--Check current retention
select dbms_stats.get_stats_history_retention from dual;

--Check how far in past optimizer stats go
select dbms_stats.get_stats_history_availability from dual;

--Change retention
exec dbms_stats.alter_stats_history_retention(10);

--Disable retention. (Turn off SYSAUX Optimizer Statistics History feature)
exec dbms_stats.alter_stats_history_retention(0);

--Completely remove old statistics
EXEC DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);

--Partially remove old statistics
exec DBMS_STATS.PURGE_STATS(SYSDATE-10);

Above command can take too long and also consume too much UNDO space. So i use the following series of commands;

exec DBMS_STATS.PURGE_STATS(SYSDATE-31);
exec DBMS_STATS.PURGE_STATS(SYSDATE-30);
exec DBMS_STATS.PURGE_STATS(SYSDATE-29);
exec DBMS_STATS.PURGE_STATS(SYSDATE-28);
exec DBMS_STATS.PURGE_STATS(SYSDATE-27);
exec DBMS_STATS.PURGE_STATS(SYSDATE-26);
exec DBMS_STATS.PURGE_STATS(SYSDATE-25);
exec DBMS_STATS.PURGE_STATS(SYSDATE-24);
exec DBMS_STATS.PURGE_STATS(SYSDATE-23);
exec DBMS_STATS.PURGE_STATS(SYSDATE-22);
exec DBMS_STATS.PURGE_STATS(SYSDATE-21);
exec DBMS_STATS.PURGE_STATS(SYSDATE-20);
exec DBMS_STATS.PURGE_STATS(SYSDATE-19);
exec DBMS_STATS.PURGE_STATS(SYSDATE-18);
exec DBMS_STATS.PURGE_STATS(SYSDATE-17);
exec DBMS_STATS.PURGE_STATS(SYSDATE-16);
exec DBMS_STATS.PURGE_STATS(SYSDATE-15);
exec DBMS_STATS.PURGE_STATS(SYSDATE-14);
exec DBMS_STATS.PURGE_STATS(SYSDATE-13);
exec DBMS_STATS.PURGE_STATS(SYSDATE-12);
exec DBMS_STATS.PURGE_STATS(SYSDATE-11);
exec DBMS_STATS.PURGE_STATS(SYSDATE-10);
exec DBMS_STATS.PURGE_STATS(SYSDATE-9);
exec DBMS_STATS.PURGE_STATS(SYSDATE-8);
exec DBMS_STATS.PURGE_STATS(SYSDATE-7);
exec DBMS_STATS.PURGE_STATS(SYSDATE-6);
exec DBMS_STATS.PURGE_STATS(SYSDATE-5);
exec DBMS_STATS.PURGE_STATS(SYSDATE-4);
exec DBMS_STATS.PURGE_STATS(SYSDATE-3);
exec DBMS_STATS.PURGE_STATS(SYSDATE-2);
exec DBMS_STATS.PURGE_STATS(SYSDATE-1);

After we clear statistics history, we can optionally rebuild %OPT% tables and indexes in SYSAUX.

--First check status of all %OPT% indexes. They must be VALID before rebuild.
select  di.index_name,di.index_type,di.status  from  dba_indexes di , dba_tables dt
where  di.tablespace_name = 'SYSAUX' and dt.table_name = di.table_name
and di.table_name like '%OPT%' order by 1 asc;

--Rebuild tables.
select 'alter table '||segment_name||'  move tablespace SYSAUX parallel 8;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE';

--Rebuild indexes.
select 'alter index '||segment_name||'  rebuild online parallel 8;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX';

--Check status of all %OPT% indexes. They must be VALID after rebuild.
select  di.index_name,di.index_type,di.status  from  dba_indexes di , dba_tables dt
where  di.tablespace_name = 'SYSAUX' and dt.table_name = di.table_name
and di.table_name like '%OPT%' order by 1 asc;

1 yorum:

  1. hi Bunyamin ;
    for undo generation problem ; instaed of deleting small partions ; you could use just PURGE_ALL
    A flag that can be passed to the PURGE_STATS Procedure and unconditionally deletes all the history statistics. The deletion uses TRUNCATE statements on the various dictionary statistics tables holding the history of statistics.

    YanıtlaSil