SAP BC ORACLE 12C Cleanup ILM_EXECUTION$, ILM_RESULTS$

SAP EHP7 on oracle 12 C  IO高，经过bc老师的检测，建议用2773463 - Bad performance due to high number of entries in ILM_RESULT$ get not purged - SAP ONE Support Launchpad对应的SQL为

cleanup_ilmtasks.sql

-- Cleanup ILM_EXECUTION$, ILM_RESULTS$
-- Oracle Development Support@SAP
-- Bernhard Baumann
-- 27.03.2019
-- 
-- How To:
--  execute this script with a first inital run to remove the overdated records
--  it will remove COMPLETED tasks and STOPPED tasks, when retention time is over.
--  Its runtime may be slow due to each delete will be commited, but its safe regarding
--  UNDO usage. In same cases script need to be restarted after errors and overall initial run 
--  can need some days until all records are purged.
--  
--  sqlplus /nolog @cleanup_ilm_tasks
--  
--  after an error it can be restarted.
--  see the logfile for operation results
--  
-- Recommendation:
--  define a CRON job for execute the script once per day
--  

conn / as sysdba
spool cleanup_ilmtasks.log append

select systimestamp "BEGIN CLEANUP" from DUAL;

set serveroutput on
declare
cnt number :=0;
ntk number :=0;
rtm number;
begin

    select value into rtm from dba_ilmparameters where name = 'RETENTION TIME';

    dbms_output.put_line('Retention time for ILM task: '|| to_char(rtm) ||' days');           

    Select count(*) into ntk from SYS.ILM_EXECUTION$
              WHERE COMPLETION_TIME IS NULL 
              AND START_TIME < (SYSDATE - rtm)
              AND EXECUTION_STATE = 3;

    dbms_output.put_line('Number of obsolete ILM task seen: '|| to_char(ntk));           

 FOR r in ( Select execution_id from SYS.ILM_EXECUTION$
              WHERE COMPLETION_TIME IS NULL 
              AND START_TIME < (SYSDATE - rtm)
              AND EXECUTION_STATE = 3)   
    LOOP
    		DELETE SYS.ILM_EXECUTION$
    		WHERE EXECUTION_ID = r.execution_id;
    		
    		-- also deletes referenced records on ILM_RESULTS$ due to
    		--	foreign key (execution_id)
    		--	references  ilm_execution$(execution_id)
    		-- 	on delete cascade

		-- due to high number of records affected in ILM_RESULTS$
		-- commit is done per execution_id
		COMMIT;
		cnt := cnt+1;
    END LOOP;	
    
    
    COMMIT;
    dbms_output.put_line('Number of obsolete ILM tasks cleaned up: ' || to_char(cnt));

    exception
       when others then
	  ROLLBACK;   
	  dbms_output.put_line('Cleanup job aborted after removing '|| to_char(cnt)||' tasks');
end;
/
select systimestamp "END CLEANUP" from DUAL;
exit
-- end cleanup_ilmtask.sql

放到服务器上去执行的经过  

上传服务器 linux 的制定目录  chmod 给权限 

在sqlplus / as sysdba  下

@ 目录/cleanup_ilmtasks.sql