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