Purging statistics from the SYSAUX tablespace

set linesize 120
set pagesize 100

SELECT * FROM V$SYSAUX_OCCUPANTS;

COLUMN "Item" FORMAT A25
COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Schema" FORMAT A25
COLUMN "Move Procedure" FORMAT A40

SELECT occupant_name "Item",
space_usage_kbytes/1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1
/

select snap_interval, retention
from dba_hist_wr_control
/

select OCCUPANT_NAME,SCHEMA_NAME,SPACE_USAGE_KBYTES from V$sysaux_occupants;
/
SELECT
snap_id, begin_interval_time, end_interval_time
FROM
SYS.WRM$_SNAPSHOT
WHERE
snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)
UNION
SELECT
snap_id, begin_interval_time, end_interval_time
FROM
SYS.WRM$_SNAPSHOT
WHERE
snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)
/

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
15720 11-08-2008 19:00:25.442 11-08-2008 20:00:40.792
24219 21-09-2009 10:00:19.262 21-09-2009 11:00:41.262

BEGIN
dbms_workload_repository.drop_snapshot_range(low_snap_id => 7556, high_snap_id=>15000);
END;
/


select dbms_stats.get_stats_history_retention from dual;

Set retention of old stats to 10 days

exec dbms_stats.alter_stats_history_retention(10);

Purge stats older than 10 days (best to do this in stages if there is a lot of data (sysdate-30,sydate-25 etc)

exec DBMS_STATS.PURGE_STATS(SYSDATE-10);

Show available stats that have not been purged

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
26-08-2008 19:38:08.580380000 -05:00

Show how big the tables are and rebuild after stats have been purged

col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6

set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='TABLE'
group by segment_name,segment_type order by 1 asc

MB SEGMENT_NAME SEGMEN
---------- ---------------------------------------- ------
0 WRI$_OPTSTAT_OPR TABLE
0 WRI$_OPTSTAT_AUX_HISTORY TABLE
88 WRI$_OPTSTAT_TAB_HISTORY TABLE
126 WRI$_OPTSTAT_IND_HISTORY TABLE
158 WRI$_OPTSTAT_HISTGRM_HISTORY TABLE
4,482 WRI$_OPTSTAT_HISTHEAD_HISTORY TABLEShow how big the indexes are ready for a rebuild after stats have been purged


col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc
/

MB SEGMENT_NAME SEGMEN
---------- ---------------------------------------- ------
0 WRH$_OPTIMIZER_ENV_PK INDEX
0 I_WRI$_OPTSTAT_OPR_STIME INDEX
0 I_WRI$_OPTSTAT_AUX_ST INDEX
88 I_WRI$_OPTSTAT_TAB_ST INDEX
105 I_WRI$_OPTSTAT_IND_ST INDEX
105 I_WRI$_OPTSTAT_H_ST INDEX
195 I_WRI$_OPTSTAT_TAB_OBJ#_ST INDEX
213 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
214 I_WRI$_OPTSTAT_IND_OBJ#_ST INDEX
2,055 I_WRI$_OPTSTAT_HH_ST INDEX
3,883 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX


Note that you cannot enable row movement and shrink the tables as the indexes are function based

alter table WRI$_OPTSTAT_IND_HISTORY enable row movement;
alter table WRI$_OPTSTAT_IND_HISTORY shrink space;


select 'alter table '||segment_name||' move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE'

Run the rebuild table commands – note that this does cause any gather_stats jobs to fail

alter table WRI$_OPTSTAT_TAB_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_IND_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_AUX_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_OPR move tablespace sysaux;
alter table WRH$_OPTIMIZER_ENV move tablespace sysaux;Script to generate rebuild statements

select 'alter index '||segment_name||' rebuild online parallel (degree 14);' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'

select di.index_name,di.index_type,di.status from dba_indexes di
where di.tablespace_name = 'SYSAUX'
and di.index_name like '%OPT%'
order by 1 asc


SQL>
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
I_WRI$_OPTSTAT_AUX_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_HH_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_H_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_IND_OBJ#_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_IND_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_OPR_STIME FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_TAB_OBJ#_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_TAB_ST FUNCTION-BASED NORMAL VALID
WRH$_OPTIMIZER_ENV_PK NORMAL VALID

Finally lets see what space has been saved with a retention date of 1 day and a gather schema stats for the SYSASDM schema

exec dbms_stats.alter_stats_history_retention(1);

select dbms_stats.get_stats_history_retention from dual;

MB SEGMENT_NAME SEGMEN
---------- ---------------------------------------- ------
0 WRI$_OPTSTAT_OPR TABLE
0 WRI$_OPTSTAT_AUX_HISTORY TABLE
3 WRI$_OPTSTAT_TAB_HISTORY TABLE
4 WRI$_OPTSTAT_IND_HISTORY TABLE
8 WRI$_OPTSTAT_HISTGRM_HISTORY TABLE
104 WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE

MB SEGMENT_NAME SEGMEN
---------- ---------------------------------------- ------
0 WRH$_OPTIMIZER_ENV_PK INDEX
0 I_WRI$_OPTSTAT_OPR_STIME INDEX
0 I_WRI$_OPTSTAT_AUX_ST INDEX
2 I_WRI$_OPTSTAT_IND_ST INDEX
2 I_WRI$_OPTSTAT_TAB_ST INDEX
3 I_WRI$_OPTSTAT_TAB_OBJ#_ST INDEX
4 I_WRI$_OPTSTAT_IND_OBJ#_ST INDEX
5 I_WRI$_OPTSTAT_H_ST INDEX
9 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
41 I_WRI$_OPTSTAT_HH_ST INDEX
96 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX

Snapshots. Config en DBA_HIST_WR_CONTROL. Ej de cambio a intervalo de 30días cada
30min (expresado en minutos y si intervalo=0 no se calculan más snapshots):


SELECT snap_id, startup_time, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1,2

– DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(43200,30);
– DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (22, 32); Borra
rango

• Baselines. Pareja de snapshots (q ya no se borrarán).

Speed up ‘removal’ of old AWR reports

removing the entries takes ages and fails on undo errors … Metalink note Doc ID: 852028.1 states that I can safely remove the AWR metadata tables and recreate them.

SQL> connect / as sysdba
SQL> @?/rdbms/admin/catnoawr.sql
SQL> @?/rdbms/admin/catawrtb.sql


– DBMS_WORKLOAD_REPOSITORY.create_baseline (210, 220, 'batch baseline'); Snapshots
inicial y final, y nombre para el baseline

– DBMS_WORKLOAD_REPOSITORY.drop_baseline ( 'batch baseline', 'FALSE); Si
TRUE, borra los snaphosts asociados

• Informes AWR en $ORACLE_HOME/rdbms/admin (con salida en HTML o TEXTO):
– awrrpt.sql, pedirá formato salida (text o html), los snapshots inicial y final, y el nombre
del fichero del informe. Existe awrrpti.sql q permite seleccionar una instancia.
– awrsqrpt.sql, muestra estadísticas de una sentencia SQL para un rango de snapshots.
– awrddrpt.sql, compara atributos detallados de rendimiento y valores de configuración
entre dos períodos de tiempo.