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.