AWR, Automatic Workload Respository

The Oracle AWR, Automatic Workload Respository, is tored in the SYSAUX tablespace. This post conatians information useful for purging/cleaning the AWR when it becomes large due to failure of cleanup jobs to remove partitions in the large AWR table, e.g. WRH$_ACTIVE_SESSION_HISTORY. Mainly Oracle 12c release 1.

-- check ASH history compared to retention period
select sysdate - a.sample_time ash,
sysdate - s.begin_interval_time snap,
c.RETENTION
from sys.wrm$_wr_control c,
(
select db.dbid,
min(w.sample_time) sample_time
from sys.v_$database db,
sys.Wrh$_active_session_history w
where w.dbid = db.dbid group by db.dbid
) a,
(
select db.dbid,
min(r.begin_interval_time) begin_interval_time
from sys.v_$database db,
sys.wrm$_snapshot r
where r.dbid = db.dbid
group by db.dbid
) s
where a.dbid = s.dbid
and c.dbid = a.dbid;

-- How much space is being used?
SELECT  occupant_name "Item",
space_usage_kbytes/1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
WHERE occupant_name in  ('SM/AWR','SM/OPTSTAT')
ORDER BY 1

-- Get orphaned ASH rows
SELECT COUNT(1) Orphaned_ASH_Rows FROM wrh$_active_session_history a
  WHERE NOT EXISTS
  (SELECT 1
  FROM wrm$_snapshot
  WHERE snap_id       = a.snap_id
  AND dbid            = a.dbid
  AND instance_number = a.instance_number
  );

-- check ASH history compared to retention period
select sysdate - a.sample_time ash,
sysdate - s.begin_interval_time snap,
c.RETENTION
from sys.wrm$_wr_control c,
(
select db.dbid,
min(w.sample_time) sample_time
from sys.v_$database db,
sys.Wrh$_active_session_history w
where w.dbid = db.dbid group by db.dbid
) a,
(
select db.dbid,
min(r.begin_interval_time) begin_interval_time
from sys.v_$database db,
sys.wrm$_snapshot r
where r.dbid = db.dbid
group by db.dbid
) s
where a.dbid = s.dbid
and c.dbid = a.dbid;

-- Get current settings
select DBID, SNAP_INTERVAL,  RETENTION, TOPNSQL from DBA_HIST_WR_CONTROL;

select
       extract( day from snap_interval) *24*60+
       extract( hour from snap_interval) *60+
       extract( minute from snap_interval ) "Snapshot Interval (minutes)",
       extract( day from retention) *24*60+
       extract( hour from retention) *60+
       extract( minute from retention ) "Retention Interval (minutes)"
from dba_hist_wr_control

-- Check snapshoits are being created properly
SELECT (CASE WHEN NUMTODSINTERVAL((SYSDATE - MAX(CAST(END_INTERVAL_TIME AS DATE))), 'DAY') 
< SNAP_INTERVAL THEN 'OK AWR Snapshots are being collected' ELSE 'NOK Problems with Snapshot collection' END) "RESULT"
FROM DBA_HIST_SNAPSHOT, DBA_HIST_WR_CONTROL
GROUP BY SNAP_INTERVAL

-- Disable snapshots
BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 11520,    -- Mins (43200 = 30 Days). Current value retained if NULL.(11520 is 8 days)
    interval  => 0);              -- Mins. Current value retained if NULL. If zero --> snapshots are stopped
END;
/

-- Enable snapshots
BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL. (11520 is 8 days)
    interval  => 30);          -- Minutes. Current value retained if NULL. If zero --> snapshots are stopped
END;
/

-- Get Objects at high end of tablespace
select * from (
select
owner,segment_name, partition_name, segment_type, max((block_id + blocks-1)*8192)/1024/1024 "HWM_MB"
from dba_extents  where file_id=<file id for SYSAUX (2)>
group by owner,segment_name, partition_name, segment_type)
where HWM_MB > <Put size here e.g. 10000 for 10G>
order by 5 DESC

Move Segments to a temp SYSAUX to reorganize SYSAUX objects

• Move CTXSYS
• Move LogMiner
• Move DBAudit Tables
• Move LOB segments (BLOB and CLOB columns)
• Move Normal tables and rebuild indexes
• Move partitioned tables and rebuild partitioned indexes
• Move tables with LONG columns
• Move nested tables
• Move IOTs
• 12c – HEATMAP
• AQ datapump tables

exec ctxsys.DRI_MOVE_CTXSYS(TBS_NAME=>'SYSAUX_TEMP', PRINT_ONLY=>false)

select count(*), segment_type from dba_segments where tablespace_name = 'SYSAUX'
and owner = 'CTXSYS'
group by segment_type

exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE('SYSAUX_TEMP')

exec DBMS_AUDIT_MGMT.move_dbaudit_tables('SYSAUX_TEMP')


BEGIN
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
audit_trail_location_value => 'SYSAUX_TEMP');
END;
/

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
audit_trail_location_value => 'SYSAUX_TEMP');
END;
/

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'SYSAUX_TEMP');
END;
/

None of these move the CLI_ objects

PROCEDURE MOVE_DBAUDIT_TABLES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 AUDIT_TRAIL_TBS                VARCHAR2                IN     DEFAULT


12c

--THIS ONE removed CLI_SWP* (very quickly!) – when the AUDIT objects are moved
-- they are recreated in the new tablespace
SQL> exec dbms_audit_mgmt.clean_audit_trail(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified,use_last_arch_timestamp=>FALSE);

BLOB and CLOB Segments
General
alter table <owner>.<table_name> move lob(<column name>) store as ( tablespace sysaux_temp );



Find Generate Move LOB Commands

SELECT 'alter table '||owner||'.'||TABLE_NAME||
' move lob('||COLUMN_NAME||') store as (tablespace SYSAUX_TEMP);' "move lob script",
DATA_TYPE
FROM dba_tab_columns
WHERE DATA_TYPE IN ('CLOB','BLOB')
AND table_name IN (SELECT table_name
FROM dba_tables
WHERE tablespace_name='SYSAUX'
and       partitioned = 'NO'
and       NESTED = 'NO') 
and       owner = 'SYS'

This will generate commands that will work for normal (non-partitioned and non-nested) tables. 

Partitioned table commands are different

Move to SYSAUX_TEMP
alter table  WRH$_SQL_PLAN move lob(other_xml) store as ( tablespace sysaux_temp )

12c
alter table  WRH$_SQLTEXT move lob(SQL_TEXT) store as ( tablespace sysaux_temp )
Move back to SYSAUX
alter table  WRH$_SQL_PLAN move lob(other_xml) store as ( tablespace sysaux )

12c
alter table  WRH$_SQLTEXT move lob(SQL_TEXT) store as ( tablespace sysaux )

alter table  WRI$_ADV_RATIONALE move lob(ATTR5) store as ( tablespace sysaux_temp )

alter table  WRI$_DBU_FEATURE_USAGE move lob(FEATURE_INFO) store as ( tablespace sysaux_temp )


And for partitioned tables, example:

Find the LOB PARTITION name
Find the table name from dba_lobs
Find the column name by DESC’ing the table
select 'alter table <table name> move partition '
||partition_name||' lob(<column name>) '||' store as (tablespace SYSAUX_TEMP);'
from dba_tab_partitions where table_name = '<table name>'


select 'alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move partition '
||partition_name||' lob(EXPRESSION) '||' store as (tablespace SYSAUX_TEMP);'
from dba_tab_partitions where table_name = 'WRI$_OPTSTAT_HISTHEAD_HISTORY'


Normal Tables and Indexes

Generate Table Move (to SYSAUX_TEMP) Scripts

select 'alter table '||owner||'.'||segment_name||' move tablespace sysaux_temp;'
from dba_segments where  segment_type = 'TABLE' and tablespace_name = 'SYSAUX'and owner = 'SYS'


Generate Index Rebuild (to SYSAUX_TEMP) Scripts

select 'alter index '||owner||'.'||index_name||' rebuild tablespace SYSAUX_TEMP;'
from dba_indexes where status = 'UNUSABLE'
and tablespace_name = 'SYSAUX'

Partitioned tables and Indexes

Move table partitions to SYSAUX_TEMP

select 'alter table '||owner||'.'||segment_name||' move partition '||partition_name||' tablespace sysaux_temp;'
from dba_segments where segment_type = 'TABLE PARTITION' and tablespace_name = 'SYSAUX'
order by segment_name


Rebuild Index Partitions in SYSAUX_TEMP
They become USABLE again and are rebuilt in SYSAUX_TEMP

select 'alter index '||index_name|| ' rebuild partition '||
partition_name||' tablespace SYSAUX_TEMP;' from dba_ind_partitions
where status = 'UNUSABLE'
and tablespace_name = 'SYSAUX'

Move to SYSAUX_TEMP

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


Index-Organized Tables
IOT

Find the table concerned and shrink it
Enable row movement on the table first (if not already enabled)

Display row_movement


select owner,table_name, row_movement from dba_tables where table_name in 
(select table_name from dba_indexes where index_name like '%IOT%' and tablespace_name = 'SYSAUX')


GSMADMIN_INTERNAL – may not be required to move this – see 

WMSYS 

CTXSYS – these should have been moved with the move proc  but may not have been

IOT Indexes

alter table AQ$_ALERT_QT_H shrink space cascade;

12c - HEATMAP

See MOS
Doc ID 2024036.1
HEATMAP Segment Size Is Large In SYSAUX Even When Heatmap=Off (Doc ID 2024036.1)
This is not the same as my problem – but the solution to my HEATMAP problem is in this document


SQL> show parameter HEAT_MAP;

NAME                                 TYPE                                         VALUE
------------------------------------ -------------------------------------------- ------------------------------
heat_map                             string                                       OFF

ALTER SYSTEM SET "_drop_stat_segment"=1 scope=memory;

Heatmap is dropped

AQ DATAPUMP Tables

Examples
alter table "AQ$_KUPC$DATAPUMP_QUETAB_2_P" move tablespace sysaux_temp;

alter table "AQ$_KUPC$DATAPUMP_QUETAB_2_S" move tablespace sysaux_temp;

Tables with LONG columns

Display Tables with LONG Columns


SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE
     FROM dba_tab_columns
     WHERE DATA_TYPE IN ('LONG','LONG RAW')
     AND table_name IN (SELECT table_name
                        FROM dba_tables
                        WHERE tablespace_name='SYSAUX')


Some tables with long columns do not contain data- these can be truncated directly. 

For tables with LONG columns that contain data AND there is no data in the LONG column:
•	create a temporary table with the data without the LONG column(s)
•	truncate the table
•	insert the rows back into the table

WRI$_ADV_SQLT_PLAN
Doc ID 2205210.1


create table SQLT_PLANS_TEMP as select
TASK_ID, OBJECT_ID, ATTRIBUTE, PLAN_HASH_VALUE, PLAN_ID,STATEMENT_ID, TIMESTAMP,
REMARKS, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS,
OBJECT_INSTANCE,OBJECT_TYPE,OPTIMIZER,SEARCH_COLUMNS,ID,PARENT_ID,DEPTH,POSITION,
COST,CARDINALITY,BYTES,OTHER_TAG,PARTITION_START,PARTITION_STOP,PARTITION_ID,
DISTRIBUTION,CPU_COST,IO_COST,TEMP_SPACE,ACCESS_PREDICATES,FILTER_PREDICATES,
PROJECTION,TIME,QBLOCK_NAME,OTHER_XML         from WRI$_ADV_SQLT_PLANS

select count(*) from WRI$_ADV_SQLT_PLANS where other is not null

select count(*) from SQLT_PLANS_TEMP

select * from SQLT_PLANS_TEMP

truncate table WRI$_ADV_SQLT_PLANS

insert into WRI$_ADV_SQLT_PLANS
select
TASK_ID,OBJECT_ID,ATTRIBUTE, PLAN_HASH_VALUE, PLAN_ID,STATEMENT_ID, TIMESTAMP,
REMARKS, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS,
OBJECT_INSTANCE, OBJECT_TYPE,OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID, DEPTH,POSITION,
COST, CARDINALITY, BYTES,  OTHER_TAG,PARTITION_START,PARTITION_STOP,PARTITION_ID,
NULL,DISTRIBUTION,CPU_COST, IO_COST,TEMP_SPACE,ACCESS_PREDICATES,FILTER_PREDICATES,
PROJECTION,TIME,QBLOCK_NAME, OTHER_XML  from SQLT_PLANS_TEMP





create table PLAN_temp as select
SIGNATURE, CATEGORY, OBJ_TYPE, PLAN_ID, STATEMENT_ID, XPL_PLAN_ID, TIMESTAMP,
REMARKS, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME,
OBJECT_ALIAS, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID,
PARENT_ID, DEPTH, POSITION, COST,CARDINALITY,BYTES,OTHER_TAG,PARTITION_START,
PARTITION_STOP,PARTITION_ID,DISTRIBUTION,CPU_COST,IO_COST,TEMP_SPACE,
ACCESS_PREDICATES,FILTER_PREDICATES,PROJECTION,TIME,QBLOCK_NAME,OTHER_XML
from SQLOBJ$PLAN

truncate table SQLOBJ$PLAN

insert into SQLOBJ$PLAN select
SIGNATURE, CATEGORY, OBJ_TYPE, PLAN_ID, STATEMENT_ID, XPL_PLAN_ID, TIMESTAMP, REMARKS, OPERATION,
OPTIONS, OBJECT_NODE,OBJECT_OWNER,OBJECT_NAME,OBJECT_ALIAS,OBJECT_INSTANCE,OBJECT_TYPE,
OPTIMIZER,SEARCH_COLUMNS,ID,PARENT_ID,DEPTH,POSITION,COST,CARDINALITY,BYTES,OTHER_TAG,
PARTITION_START,PARTITION_STOP,PARTITION_ID,NULL,DISTRIBUTION,CPU_COST,IO_COST,TEMP_SPACE,
ACCESS_PREDICATES,FILTER_PREDICATES,PROJECTION,TIME,QBLOCK_NAME,OTHER_XML  from PLAN_TEMP;

commit

select count(*) from SQLOBJ$PLAN

Check ADDM Jobs – Shows AWR is Working

If there are unusable indexes or other problems, ADDM tasks may not have a status of ‘COMPLETED’

This query shows ADDM tasks

select owner || '/' || advisor_name as owner_adv
        , task_id || '/' || task_name as task_id_name
        , created
        , (execution_end - execution_start)*86400 as exe_duratn
        , (execution_end - created) * 86400 as exe_creatn
        , how_created
        , status
        , 'Description: ' || description as task_desc
        , 'Error Msg  : ' || error_message as error_msg
from   (select t.*, rank() over (order by execution_end desc) rnk
        from   dba_advisor_tasks t) dat
where  dat.rnk <= 50
  and  1 = 1
order by created desc;



Check Object Validity and Recompile

Select count(*) from dba_objects where status <> 'VALID'

@?/rdbms/admin/utlrp.sql