266 lines
10 KiB
SQL
266 lines
10 KiB
SQL
DECLARE
|
|
lv_sql VARCHAR2(4000);
|
|
v_env_type VARCHAR2(30) := '&&1';
|
|
v_db_version VARCHAR2(30) := '&2';
|
|
v_instance VARCHAR2(30) := '&3';
|
|
BEGIN
|
|
|
|
DBMS_OUTPUT.PUT_LINE('###########################################');
|
|
DBMS_OUTPUT.PUT_LINE('Job Started at ' || to_char(sysdate,'DD-MON-YYYY hh24:MI:SS') );
|
|
|
|
|
|
FOR i in (SELECT DISTINCT dbid
|
|
, c.database
|
|
, instance_number
|
|
, c.keep_days
|
|
FROM stats$database_instance i
|
|
, perfstat_control.statspack_merge_control c
|
|
WHERE c.database = i.db_name
|
|
AND c.database = NVL(v_instance,c.database)
|
|
AND c.version = v_db_version
|
|
AND c.purge_active = 'Y')
|
|
LOOP
|
|
|
|
DBMS_OUTPUT.PUT_LINE('------------------------------------------------');
|
|
DBMS_OUTPUT.PUT_LINE(i.database || to_char(sysdate,'DD-MON-YYYY hh24:MI:SS'));
|
|
|
|
/*--------------------------------------------------------
|
|
Delete all snapshots except for midnight ones older thans keep_days
|
|
retain all midnight snapshots
|
|
----------------------------------------------------------*/
|
|
DELETE
|
|
FROM stats$snapshot
|
|
WHERE dbid = i.dbid
|
|
AND instance_number = i.instance_number
|
|
AND snap_time < sysdate - i.keep_days
|
|
AND to_char(snap_time,'HH24MI') NOT between '0000' and '0010'
|
|
;
|
|
|
|
commit;
|
|
|
|
|
|
/*---------------------------------------------------------------------
|
|
Delete all SQL captured which are below set threshold
|
|
This manual cleanup is required in historical data in case thresholds
|
|
have been increased to reduce the SQLs captured.
|
|
-----------------------------------------------------------------------*/
|
|
if v_db_version = '9.2'
|
|
then
|
|
|
|
lv_sql := 'DELETE FROM stats$sql_summary
|
|
WHERE(snap_id, dbid, instance_number, hash_value, text_subset) IN
|
|
(SELECT ss.snap_id,
|
|
ss.dbid,
|
|
ss.instance_number,
|
|
ss.hash_value,
|
|
ss.text_subset
|
|
FROM stats$sql_summary ss,
|
|
stats$statspack_parameter sp
|
|
WHERE ss.dbid = sp.dbid
|
|
AND ss.instance_number = sp.instance_number
|
|
AND ss.dbid = ' || i.dbid || '
|
|
AND ss.instance_number = ' || i.instance_number || '
|
|
AND ss.executions < sp.executions_th *1
|
|
AND ss.parse_calls < sp.parse_calls_th *1
|
|
AND ss.disk_reads < sp.disk_reads_th *1
|
|
AND ss.buffer_gets < sp.buffer_gets_th *1
|
|
AND ss.sharable_mem < sp.sharable_mem_th *1
|
|
AND ss.version_count < sp.version_count_th *1)
|
|
';
|
|
|
|
EXECUTE IMMEDIATE lv_sql;
|
|
COMMIT;
|
|
|
|
lv_sql := 'DELETE /*+ index(spu )*/
|
|
FROM stats$sql_plan_usage spu
|
|
WHERE NOT EXISTS
|
|
(SELECT 1
|
|
FROM stats$sql_summary ss
|
|
WHERE ss.dbid = spu.dbid
|
|
AND ss.instance_number = spu.instance_number
|
|
AND ss.dbid = ' || i.dbid || '
|
|
AND ss.instance_number = ' || i.instance_number || '
|
|
AND ss.snap_id = spu.snap_id
|
|
AND ss.hash_value = spu.hash_value
|
|
AND ss.text_subset = spu.text_subset)
|
|
';
|
|
|
|
EXECUTE IMMEDIATE lv_sql;
|
|
COMMIT;
|
|
|
|
end if; -- 9i
|
|
|
|
|
|
if v_db_version = '10.2'
|
|
then
|
|
|
|
lv_sql := 'DELETE FROM stats$sql_summary
|
|
WHERE(snap_id, dbid, instance_number, old_hash_value, text_subset) IN
|
|
(SELECT ss.snap_id,
|
|
ss.dbid,
|
|
ss.instance_number,
|
|
ss.old_hash_value,
|
|
ss.text_subset
|
|
FROM stats$sql_summary ss,
|
|
stats$statspack_parameter sp
|
|
WHERE ss.dbid = sp.dbid
|
|
AND ss.instance_number = sp.instance_number
|
|
AND ss.dbid = ' || i.dbid || '
|
|
AND ss.instance_number = ' || i.instance_number || '
|
|
AND ss.executions < sp.executions_th *1
|
|
AND ss.parse_calls < sp.parse_calls_th *1
|
|
AND ss.disk_reads < sp.disk_reads_th *1
|
|
AND ss.buffer_gets < sp.buffer_gets_th *1
|
|
AND ss.sharable_mem < sp.sharable_mem_th *1
|
|
AND ss.version_count < sp.version_count_th *1)
|
|
';
|
|
|
|
EXECUTE IMMEDIATE lv_sql;
|
|
COMMIT;
|
|
|
|
lv_sql := 'DELETE /*+ index(spu )*/
|
|
FROM stats$sql_plan_usage spu
|
|
WHERE NOT EXISTS
|
|
(SELECT 1
|
|
FROM stats$sql_summary ss
|
|
WHERE ss.dbid = spu.dbid
|
|
AND ss.instance_number = spu.instance_number
|
|
AND ss.dbid = ' || i.dbid || '
|
|
AND ss.instance_number = ' || i.instance_number || '
|
|
AND ss.snap_id = spu.snap_id
|
|
AND ss.old_hash_value = spu.old_hash_value
|
|
AND ss.text_subset = spu.text_subset)
|
|
';
|
|
|
|
EXECUTE IMMEDIATE lv_sql;
|
|
COMMIT;
|
|
|
|
end if; -- 10g
|
|
|
|
END LOOP;
|
|
|
|
|
|
-- if iextended_purge then
|
|
--
|
|
-- Purge segments which do not have RI constraints to stats$snapshot
|
|
|
|
if v_db_version = '9.2'
|
|
then
|
|
|
|
--
|
|
-- SQL text
|
|
--
|
|
lv_sql := 'delete from stats$sqltext st1
|
|
where (st1.hash_value, st1.text_subset) in
|
|
(select /*+ index_ffs(st) */
|
|
st.hash_value, st.text_subset
|
|
from stats$sqltext st
|
|
where (hash_value, text_subset) not in
|
|
(select /*+ hash_aj index_ffs(ss) */
|
|
distinct hash_value, text_subset
|
|
from stats$sql_summary ss
|
|
)
|
|
and st.piece = 0
|
|
)';
|
|
EXECUTE IMMEDIATE lv_sql;
|
|
commit;
|
|
|
|
--
|
|
-- SQL execution plans
|
|
--
|
|
lv_sql := 'delete from stats$sql_plan sp1
|
|
where sp1.plan_hash_value in
|
|
(select /*+ index_ffs(sp) */
|
|
sp.plan_hash_value
|
|
from stats$sql_plan sp
|
|
where plan_hash_value not in
|
|
(select /*+ hash_aj */
|
|
distinct plan_hash_value
|
|
from stats$sql_plan_usage spu
|
|
)
|
|
and sp.id = 0
|
|
)';
|
|
EXECUTE IMMEDIATE lv_sql;
|
|
commit;
|
|
|
|
|
|
--
|
|
-- Segment Identifiers
|
|
--
|
|
lv_sql := 'delete /*+ index_ffs(sso) */
|
|
from stats$seg_stat_obj sso
|
|
where (dbid, dataobj#, obj#, ts#) not in
|
|
(select /*+ hash_aj full(ss) */
|
|
dbid, dataobj#, obj#, ts#
|
|
from stats$seg_stat ss
|
|
)';
|
|
EXECUTE IMMEDIATE lv_sql;
|
|
commit;
|
|
|
|
end if; -- is it 9i
|
|
|
|
|
|
if v_db_version = '10.2'
|
|
then
|
|
|
|
--
|
|
-- SQL Text
|
|
--
|
|
lv_sql:= 'delete from stats$sqltext st1
|
|
where (st1.old_hash_value, st1.text_subset) in
|
|
(select /*+ index_ffs(st) */
|
|
st.old_hash_value, st.text_subset
|
|
from stats$sqltext st
|
|
where (old_hash_value, text_subset) not in
|
|
(select /*+ hash_aj index_ffs(ss) */
|
|
distinct old_hash_value, text_subset
|
|
from stats$sql_summary ss
|
|
)
|
|
and st.piece = 0
|
|
)';
|
|
EXECUTE IMMEDIATE lv_sql;
|
|
commit;
|
|
|
|
--
|
|
-- SQL execution plans
|
|
--
|
|
lv_sql := 'delete from stats$sql_plan sp1
|
|
where sp1.plan_hash_value in
|
|
(select /*+ index_ffs(sp) */
|
|
sp.plan_hash_value
|
|
from stats$sql_plan sp
|
|
where plan_hash_value not in
|
|
(select /*+ hash_aj */
|
|
distinct plan_hash_value
|
|
from stats$sql_plan_usage spu
|
|
)
|
|
and sp.id = 0
|
|
)';
|
|
EXECUTE IMMEDIATE lv_sql;
|
|
commit;
|
|
|
|
|
|
--
|
|
-- Segment Identifiers
|
|
--
|
|
lv_sql := 'delete /*+ index_ffs(sso) */
|
|
from stats$seg_stat_obj sso
|
|
where (dbid, dataobj#, obj#, ts#) not in
|
|
(select /*+ hash_aj full(ss) */
|
|
dbid, dataobj#, obj#, ts#
|
|
from stats$seg_stat ss
|
|
)';
|
|
EXECUTE IMMEDIATE lv_sql;
|
|
commit;
|
|
|
|
end if; -- Is it 10g
|
|
|
|
-- end if; -- extended purge
|
|
|
|
DBMS_OUTPUT.PUT_LINE('###########################################');
|
|
DBMS_OUTPUT.PUT_LINE('Job Finished at ' || to_char(sysdate,'DD-MON-YYYY hh24:MI:SS') );
|
|
|
|
END;
|
|
/
|
|
|