362 lines
12 KiB
MySQL
362 lines
12 KiB
MySQL
|
|
|
|
----------------------------------------------------------------
|
|
-- Take Snapshot
|
|
----------------------------------------------------------------
|
|
exec dbms_workload_repository.create_snapshot('TYPICAL');
|
|
exec dbms_workload_repository.create_snapshot('ALL');
|
|
|
|
|
|
----------------------------------------------------------------
|
|
-- Modify AWR Configuration
|
|
----------------------------------------------------------------
|
|
BEGIN
|
|
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 60*24*95 -- In Minutes (1 year = 525600)
|
|
, interval => 15 -- In Minutes
|
|
--, topnsql => NULL
|
|
--, dbid => NULL
|
|
);
|
|
END;
|
|
/
|
|
|
|
|
|
|
|
----------------------------------------------------------------
|
|
-- AWR - Gather Stats
|
|
----------------------------------------------------------------
|
|
BEGIN
|
|
-- AWR Table partitions with STALE/MISSING stats
|
|
FOR i in (
|
|
select STALE_STATS, last_analyzed,
|
|
'BEGIN dbms_stats.gather_table_stats(''' || owner || ''',''' || table_name || ''',partname=>''' || partition_name|| ''',GRANULARITY=>''PARTITION'',DEGREE=>16,CASCADE =>TRUE); END;' sqltext
|
|
from DBA_TAB_STATISTICS
|
|
where owner = 'SYS'
|
|
and table_name like 'WRH$%'
|
|
and (STALE_STATS = 'YES' OR last_analyzed is null)
|
|
and partition_name is not null
|
|
ORDER BY owner, table_name, partition_name
|
|
)
|
|
LOOP
|
|
execute immediate i.sqltext ;
|
|
END LOOP;
|
|
-- AWR Table with STALE/MISSING stats
|
|
FOR i in (
|
|
select STALE_STATS, last_analyzed,
|
|
'exec dbms_stats.gather_table_stats(''' || owner || ''',''' || table_name || ''',DEGREE=>16,CASCADE =>TRUE);'
|
|
from DBA_TAB_STATISTICS
|
|
where owner = 'SYS'
|
|
and table_name like 'WRH$%'
|
|
and (STALE_STATS = 'YES' OR last_analyzed is null)
|
|
and partition_name is null
|
|
order by num_rows asc
|
|
)
|
|
LOOP
|
|
execute immediate i.sqltext ;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
|
|
|
|
----------------------------------------------------------------
|
|
-- Split AWR table partitions
|
|
----------------------------------------------------------------
|
|
alter session set "_swrf_test_action" = 72;
|
|
|
|
|
|
----------------------------------------------------------------
|
|
-- Purge AWR Repository
|
|
----------------------------------------------------------------
|
|
Doc ID 1478615.1: How to Purge WRH$_SQL_PLAN Table in AWR Repository, Occupying Large Space in SYSAUX Tablespace.
|
|
|
|
----------------------------------------------------------------
|
|
-- Purge AWR Snapshots ( 1 day at time)
|
|
----------------------------------------------------------------
|
|
SELECT 'exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>' || min(snap_id)
|
|
||' , high_snap_id=>'
|
|
|| max(snap_id)
|
|
|| ' , dbid=>' || min(dbid)
|
|
||');'
|
|
from dba_hist_snapshot s
|
|
WHERE 1=1
|
|
AND TO_CHAR(s.begin_interval_time,'HH24:MI') NOT LIKE '00:0%' -- Exclude midnight snapshots
|
|
and s.begin_interval_time < sysdate - 45
|
|
group by trunc(begin_interval_time)
|
|
order by trunc(begin_interval_time) asc
|
|
;
|
|
|
|
|
|
|
|
|
|
----------------------------------------------------------------
|
|
-- Purge AWR Snapshots ( 1 snap at a time)
|
|
----------------------------------------------------------------
|
|
SELECT begin_interval_time
|
|
, 'exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>' || snap_id
|
|
||' , high_snap_id=>'
|
|
|| snap_id
|
|
|| ' , dbid=>' || dbid
|
|
||');'
|
|
from dba_hist_snapshot s
|
|
WHERE 1=1
|
|
AND TO_CHAR(s.begin_interval_time,'HH24:MI') NOT LIKE '00:0%' -- Exclude midnight snapshots
|
|
order by begin_interval_time asc
|
|
;
|
|
|
|
|
|
----------------------------------------------------------------
|
|
-- Purge AWR Snapshots ( 1 snap at a time)
|
|
----------------------------------------------------------------
|
|
DECLARE
|
|
lv_days_to_keep NUMBER := 90;
|
|
lv_sqltext VARCHAR2(4000);
|
|
BEGIN
|
|
execute immediate 'alter session enable parallel DDL';
|
|
execute immediate 'alter session enable parallel DML';
|
|
|
|
FOR snap in ( SELECT distinct s.snap_id
|
|
, s.dbid
|
|
, TRUNC(s.begin_interval_time,'MI') begin_interval_time
|
|
from dba_hist_snapshot s
|
|
WHERE 1=1
|
|
-- Exclude midnight snapshots
|
|
AND TO_CHAR(s.begin_interval_time,'HH24:MI') NOT LIKE '00:0%'
|
|
-- Exclude Baseline snaphosts
|
|
AND NOT EXISTS (select 1 from sys.WRM$_BASELINE b where s.dbid = b.dbid and s.snap_id BETWEEN b.start_snap_id AND b.end_snap_id )
|
|
and s.begin_interval_time <= systimestamp - lv_days_to_keep
|
|
order by TRUNC(s.begin_interval_time,'MI') asc
|
|
)
|
|
LOOP
|
|
FOR t IN ( select t.owner, t.table_name
|
|
from dba_tables t
|
|
where 1=1
|
|
and t.owner = 'SYS'
|
|
and t.table_name like 'WRH%'
|
|
and exists (select 1 from dba_tab_columns c where c.owner = t.owner and c.table_name = t.table_name and c.column_name = 'SNAP_ID')
|
|
order by t.owner, t.table_name
|
|
)
|
|
LOOP
|
|
lv_sqltext := 'delete /*+ parallel(8) */ from ' || t.owner || '.' || t.table_name || ' a where a.dbid = :DBID and a.snap_id = :SNAP_ID ';
|
|
IF t.table_name = 'WRH$_SQLTEXT' THEN
|
|
lv_sqltext := lv_sqltext || ' and not exists (select 1 from sys.wri$_sqltext_refcount r where r.dbid = a.dbid and r.sql_id = a.sql_id and r.ref_count > 0 ) ';
|
|
END IF;
|
|
execute immediate lv_sqltext USING snap.dbid , snap.snap_id;
|
|
--commit;
|
|
END LOOP;
|
|
--execute immediate 'delete /*+ parallel(8) */ from SYS.WRM$_DATABASE_INSTANCE a where a.dbid = :DBID and a.snap_id = :SNAP_ID ' USING snap.dbid , snap.snap_id;
|
|
execute immediate 'delete /*+ parallel(8) */ from SYS.WRM$_SNAP_ERROR a where a.dbid = :DBID and a.snap_id = :SNAP_ID ' USING snap.dbid , snap.snap_id;
|
|
execute immediate 'delete /*+ parallel(8) */ from SYS.WRM$_SNAPSHOT_DETAILS a where a.dbid = :DBID and a.snap_id = :SNAP_ID ' USING snap.dbid , snap.snap_id;
|
|
execute immediate 'delete /*+ parallel(8) */ from SYS.WRM$_SNAPSHOT a where a.dbid = :DBID and a.snap_id = :SNAP_ID ' USING snap.dbid , snap.snap_id;
|
|
commit;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
|
|
----------------------------------------------------------------
|
|
-- AWR - Purge Orphaned ASH rows
|
|
--
|
|
-- Doc ID 1478615.1: How to Purge WRH$_SQL_PLAN Table in AWR Repository, Occupying Large Space in SYSAUX Tablespace.
|
|
----------------------------------------------------------------
|
|
SELECT COUNT(1) Orphaned_ASH_Rows
|
|
FROM sys.wrh$_active_session_history a
|
|
WHERE NOT EXISTS
|
|
(SELECT 1
|
|
FROM sys.wrm$_snapshot
|
|
WHERE snap_id = a.snap_id
|
|
AND dbid = a.dbid
|
|
AND instance_number = a.instance_number
|
|
)
|
|
;
|
|
|
|
|
|
DECLARE
|
|
lv_batch_count NUMBER := 10000;
|
|
BEGIN
|
|
LOOP
|
|
delete
|
|
FROM sys.wrh$_active_session_history a
|
|
WHERE NOT EXISTS
|
|
(SELECT 1
|
|
FROM sys.wrm$_snapshot
|
|
WHERE snap_id = a.snap_id
|
|
AND dbid = a.dbid
|
|
AND instance_number = a.instance_number
|
|
)
|
|
AND ROWNUM <= lv_batch_count;
|
|
EXIT WHEN SQL%ROWCOUNT = 0 ;
|
|
commit;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
|
|
----------------------------------------------------------------
|
|
-- AWR - Purge Event Histogram
|
|
--
|
|
-- Doc ID 1912201.1: Excessive AWR Growth From Partitioned Objects Such as SYS.WRH$_EVENT_HISTOGRAM Causing Sysaux to Grow
|
|
----------------------------------------------------------------
|
|
|
|
|
|
|
|
----------------------------------------------------------------
|
|
-- AWR - Purge Older Partitions
|
|
----------------------------------------------------------------
|
|
|
|
exec dbms_workload_repository.create_snapshot;
|
|
|
|
-- Split partitions
|
|
alter session set "_swrf_test_action" = 72;
|
|
|
|
-- Create snapshot
|
|
exec dbms_workload_repository.create_snapshot;
|
|
|
|
-- Check Tablespace size
|
|
@tbs SYSAUX
|
|
|
|
-- Find older partitions
|
|
set lines 1000
|
|
set numf 999,999,999,999
|
|
SELECT bytes,segment_name, partition_name FROM dba_segments where owner = 'SYS' and segment_name like 'WR%' order by bytes asc;
|
|
|
|
|
|
select min(snap_id), min(BEGIN_INTERVAL_TIME) , MAX(snap_id) , max(END_INTERVAL_TIME) from dba_hist_snapshot;
|
|
|
|
-- Drop partitions from another DBID
|
|
BEGIN
|
|
FOR i in (
|
|
SELECT 'alter table sys.' || table_name || ' drop partition ' || partition_name sqltext
|
|
FROM dba_tab_partitions where table_owner ='SYS' and table_name like 'WRH$%'
|
|
and partition_name NOT like '%' || (select dbid from v$database) || '%'
|
|
and partition_name not LIKE '%MXDB_MXSN%'
|
|
)
|
|
LOOP
|
|
execute immediate i.sqltext;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
|
|
|
|
-- Drop partitions
|
|
BEGIN
|
|
FOR I IN (
|
|
select table_owner, table_name, partition_name, p.HIGH_VALUE
|
|
, substr(partition_name,INSTR(p.partition_name,'_',-1)+1) snap_id
|
|
, 'alter table ' || table_owner || '.' || table_name || ' drop partition ' || p.partition_name sqltext
|
|
from v$database d
|
|
JOIN dba_tab_partitions p ON 1=1
|
|
where table_owner = 'SYS'
|
|
and table_name like 'WRH$_%'
|
|
and partition_name like '%' || d.dbid || '%'
|
|
and substr(partition_name,INSTR(p.partition_name,'_',-1)+1)
|
|
< (select max(snap_id) from dba_hist_snapshot s where s.dbid = d.dbid and s.END_INTERVAL_TIME < sysdate - 1)
|
|
order by table_owner, table_name, partition_position
|
|
)
|
|
LOOP
|
|
execute immediate i.sqltext;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
|
|
-- Check Tablespace size
|
|
@tbs SYSAUX
|
|
|
|
-- Create snapshot
|
|
exec dbms_workload_repository.create_snapshot;
|
|
|
|
-- Split partitions
|
|
alter session set "_swrf_test_action" = 72;
|
|
|
|
|
|
|
|
-- Drop partitions
|
|
BEGIN
|
|
FOR i in (
|
|
SELECT 'alter table sys.' || table_name || ' drop partition ' || partition_name sqltext
|
|
FROM dba_tab_partitions where table_owner ='SYS' and table_name like 'WRH$%' and partition_name like '%218469294%'
|
|
)
|
|
LOOP
|
|
execute immediate i.sqltext;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
|
|
|
|
----------------------------------------------------------------
|
|
-- AWR Extract
|
|
----------------------------------------------------------------
|
|
sqlplus / as sysdba
|
|
spool awr_extract.log
|
|
DECLARE
|
|
l_days NUMBER := 90;
|
|
l_dbid SYS.DBA_HIST_SNAPSHOT.DBID%TYPE;
|
|
l_bid SYS.DBA_HIST_SNAPSHOT.SNAP_ID%TYPE;
|
|
l_eid SYS.DBA_HIST_SNAPSHOT.SNAP_ID%TYPE;
|
|
begin
|
|
SELECT s.dbid, min(s.snap_id) bid, max(s.snap_id) eid
|
|
INTO l_dbid, l_bid, l_eid
|
|
FROM dba_hist_snapshot s
|
|
WHERE s.dbid = (select dbid from v$database)
|
|
and begin_interval_time > sysdate - l_days
|
|
group by s.dbid ;
|
|
/* call PL/SQL routine to extract the data */
|
|
sys.dbms_swrf_internal.awr_extract(dmpfile => 'AWR_exp'
|
|
, dmpdir => 'BETA_REFRESH'
|
|
, bid => l_bid
|
|
, eid=> l_eid
|
|
, dbid=> l_dbid
|
|
);
|
|
sys.dbms_swrf_internal.clear_awr_dbid;
|
|
end;
|
|
/
|
|
spool off
|
|
|
|
|
|
|
|
----------------------------------------------------------------
|
|
-- Drop AWR
|
|
----------------------------------------------------------------
|
|
sqlplus / as sysdba
|
|
spool awr_drop.log
|
|
alter system set "_awr_restrict_mode" = TRUE scope=spfile sid='*';
|
|
shutdown immediate
|
|
startup
|
|
@?/rdbms/admin/catnoawr.sql
|
|
alter system flush shared_pool;
|
|
spool off
|
|
|
|
----------------------------------------------------------------
|
|
-- Create AWR
|
|
----------------------------------------------------------------
|
|
sqlplus / as sysdba
|
|
spool awr_create.log
|
|
@?/rdbms/admin/catawr.sql
|
|
@ ?/rdbms/admin/utlrp.sql
|
|
@ ?/rdbms/admin/execsvrm.sql
|
|
spool off
|
|
|
|
----------------------------------------------------------------
|
|
-- Import AWR Data
|
|
----------------------------------------------------------------
|
|
TRUNCATE TABLE sys.wrm$_wr_control;
|
|
TRUNCATE TABLE sys.wrm$_wr_usage;
|
|
TRUNCATE TABLE sys.wrm$_snapshot_details;
|
|
impdp "'/ as sysdba'" parallel=16 directory=BETA_REFRESH dumfile=AWR_exp.dmp logfile=AWR.imp.log
|
|
|
|
----------------------------------------------------------------
|
|
-- Enable AWR Data
|
|
----------------------------------------------------------------
|
|
sqlplus / as sysdba
|
|
spool awr_drop.log
|
|
alter system reset "_awr_restrict_mode" = TRUE scope=spfile sid='*';
|
|
shutdown immediate
|
|
startup
|
|
exec dbms_workload_repository.create_snapshot;
|
|
exec dbms_workload_repository.create_snapshot;
|
|
select max(snap_id), max(end_interval_time) max_time, min(snap_id), min(end_interval_time) min_time
|
|
from dba_hist_snapshot;
|
|
|