Custom ILM ADO policy conditions using PL/SQL

In my previous post about ILM storage tiering I wrote about ADO conditions that we can provide for storage tiering policies if moving the segments to a read only tablespace. Another option to make storage tiering “conditional” is to provide a custom condition as pl/sql function returning a boolean. How does it work? Can we use Heat Map information?

Unfortunately, the documentation provides not so much information about it:

ON
Use this clause to specify a PL/SQL function that returns a boolean value. For function_name, specify the name of the function. The policy will take effect when the function returns TRUE.

A good example of using customized policies can be found in the blog by Uwe Hesse. It uses rather simple function which provide a kind of global ON/OFF logic. Maybe that’s why I was missing the point a long time. I asked myself, does it make sense to define a customized policy condition not aware of objects it is being evaluated for? If not, how we can pass the information about those objects? Finally I saw the overlooked point! The function accepts OBJECT_ID as parameter.

Since that it was like Baader-Meinhof phenomenon (Frequency Illusion): I have also found another good example on MOS Doc ID 1967038.1 and even a documentation page mentioning the existence of the parameter for the function.

Let’s take a look on it. We start with the same setup as in previous post about ILM storage tiering. The same table SCOTT.EMPLOYEE and the same tablespace LOW_COST_STORE. We aim to move the table to the new tablespace after some user defined condition is evaluated to true. Let’s emulate the ADO condition “AFTER 10 DAY OF NO MODIFICATION”.

Next create a function to evaluate the desired condition. Using the passed object_id we can query the view USER_HEAT_MAP_SEGMENT to check the last modification time. Note that we have to check the ILM-paramater POLICY_TIME, to be able to cope with its change for testing purposes.

After creating the function we can create a storage tiering policy using it and show the information from data dictionary. Note that the database has now no clue about our conditions (CONDITION_DAYS=0)

CREATE OR REPLACE FUNCTION f_custom (p_object_id number) RETURN BOOLEAN IS
  2  v_time NUMBER;
  3  v_days_no_modification NUMBER := 10;
  4  v_ilm_parameter number;
  5  BEGIN
  6    SELECT  sysdate-SEGMENT_WRITE_TIME
  7    INTO    v_time
  8    FROM    user_heat_map_segment
  9    WHERE   object_name = (SELECT object_name FROM user_objects WHERE object_id=p_object_id);
 10  
 11    SELECT value
 12    INTO v_ilm_parameter
 13    FROM dba_ilmparameters WHERE name = 'POLICY TIME';
 14  
 15    IF v_ilm_parameter = 1 /*DBMS_ILM_ADMIN.POLICY_IN_SECONDS*/ THEN
 16  	 v_time:=v_time*60*60*24;
 17    END IF;
 18  
 19    RETURN v_time > v_days_no_modification;
 20  
 21  END;
 22  /

Function created.

SCOTT> -- Storage tiering with user defined condition function
SCOTT> ALTER TABLE scott.employee1  ILM ADD POLICY TIER TO low_cost_store ON f_custom;

Table altered.

SCOTT> ----------------------------------------------------------------------------------------------------
SCOTT> -- Show ILM policy information
SCOTT> ----------------------------------------------------------------------------------------------------
SCOTT> SELECT policy_name, action_type, scope, tier_tablespace
  2    ,      condition_type, condition_days, custom_function
  3    FROM   user_ilmdatamovementpolicies;

POLICY_NAME ACTION_TYPE SCOPE	TIER_TABLESPACE  CONDITION_TYPE         CONDITION_DAYS  CUSTOM_FUNCTION
----------- ----------- ------- ---------------- ---------------------- --------------  ------------------
P261	    STORAGE     SEGMENT LOW_COST_STORE	 USER DEFINED           0               F_CUSTOM


SCOTT> 
SCOTT> SELECT policy_name, object_name, object_type, enabled
  2    FROM user_ilmobjects;

POLICY_NAME OBJECT_NAME          OBJECT_TYPE	    ENABLED
----------- -------------------- ------------------ -------
P261	    EMPLOYEE1            TABLE              YES

Now we set up the Heat Map for testing, update the table and flush Heat Map information. Querying USER_HEAT_MAP_SEGMENT shows the WRITE_TIME for the table EMPLOYEE1. Execute ILM manually and check, where the table EMPLOYEE1 now is. Nothing happens? The query is still showing the tablespace USERS. But that’s correct, because we didn’t wait 10 seconds. The view USER_ILMEVALUATIONDETAILS is telling that: PRECONDITION NOT SATISFIED.

SCOTT> EXECUTE dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.POLICY_TIME, DBMS_ILM_ADMIN.ILM_POLICY_IN_SECONDS);

PL/SQL procedure successfully completed.

SCOTT> -- 2. set start date
SCOTT> EXECUTE DBMS_ILM_ADMIN.SET_HEAT_MAP_START(sysdate -50);

PL/SQL procedure successfully completed.

SCOTT> 
SCOTT> UPDATE scott.employee1 e SET e.empno = e.empno+1;

3584 rows updated.

SCOTT> commit;

Commit complete.

SCOTT> 
SCOTT> EXECUTE dbms_ilm.flush_all_segments;

PL/SQL procedure successfully completed.

SCOTT> SELECT	object_name, to_char(segment_write_time,'DD-MON-YY hh24:mi:ss') write_time
  2  FROM	user_heat_map_segment
  3  WHERE	object_name ='EMPLOYEE1';

OBJECT_NAME	     WRITE_TIME
-------------------- ---------------------------
EMPLOYEE1	     21-OCT-17 11:03:50

SCOTT> 
SCOTT> declare
  2  v_executionid number;
  3  begin
  4    dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA,
  5  		 execution_mode => dbms_ilm.ilm_execution_offline,
  6  		 task_id   => v_executionid);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SCOTT> ----------------------------------------------------------------------------------------------------
SCOTT> -- Table EMPLOYEE1 was NOT moved to tablespace LOW_COST_STORE
SCOTT> --------------------------------------------------------------------------------------------------
SCOTT> select tablespace_name, segment_name
  2  from   user_segments
  3  where  segment_name='EMPLOYEE1';

TABLESPACE_NAME 	       SEGMENT_NAME
------------------------------ --------------------
USERS			       EMPLOYEE1

SCOTT> 
SCOTT> select task_id, object_name, selected_for_execution from user_ilmevaluationdetails;

   TASK_ID OBJECT_NAME		SELECTED_FOR_EXECUTION
---------- -------------------- ------------------------------------------
       219 EMPLOYEE1		PRECONDITION NOT SATISFIED

So we’ll be waiting 10 seconds and then repeat. Now the table EMPLOYEE1 has been moved to the new tablespace.

SCOTT> EXECUTE	dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

SCOTT> 
SCOTT> declare
  2  v_executionid number;
  3  begin
  4    dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA,
  5  		 execution_mode => dbms_ilm.ilm_execution_offline,
  6  		 task_id   => v_executionid);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SCOTT> ----------------------------------------------------------------------------------------------------
SCOTT> -- Table EMPLOYEE1 is now moved to tablespace LOW_COST_STORE
SCOTT> --------------------------------------------------------------------------------------------------
SCOTT> select tablespace_name, segment_name
  2  from   user_segments
  3  where  segment_name='EMPLOYEE1';

TABLESPACE_NAME 	       SEGMENT_NAME
------------------------------ --------------------
LOW_COST_STORE		       EMPLOYEE1

SCOTT> 
SCOTT> SELECT task_id, to_char(start_time, 'dd/mm/yyyy hh24:mi:ss') as start_time, u.STATE
  2  FROM user_ilmtasks u;

   TASK_ID START_TIME	       STATE
---------- ------------------- ---------
       219 21/10/2017 11:03:52 COMPLETED
       220 21/10/2017 11:04:13 COMPLETED

SCOTT> 
SCOTT> select task_id, job_name, job_state, to_char(completion_time, 'dd/mm/yyyy hh24:mi:ss') completion
  2  from user_ilmresults;

   TASK_ID JOB_NAME	   JOB_STATE			       COMPLETION
---------- --------------- ----------------------------------- ------------------------------
       220 ILMJOB4212	   COMPLETED SUCCESSFULLY	       21/10/2017 11:04:14

SCOTT> 
SCOTT> select task_id, object_name, selected_for_execution from user_ilmevaluationdetails;

   TASK_ID OBJECT_NAME		SELECTED_FOR_EXECUTION
---------- -------------------- ------------------------------------------
       219 EMPLOYEE1		PRECONDITION NOT SATISFIED
       220 EMPLOYEE1		SELECTED FOR EXECUTION

It works. If you would like to base your storage tiering policies on heat map information like for compression tiering instead of waiting until the tablespace’s free space limit will be reached, you could do this using user defined function. Unfortunately, you’ll lose the transparency and a kind of self-documenting, because the conditions are not saved in the data dictionary.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *