{"id":479,"date":"2017-10-24T21:02:01","date_gmt":"2017-10-24T19:02:01","guid":{"rendered":"http:\/\/blog.sqlora.com\/en\/?p=479"},"modified":"2018-04-06T13:55:34","modified_gmt":"2018-04-06T11:55:34","slug":"custom-ilm-ado-policy-conditions-using-plsql","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/custom-ilm-ado-policy-conditions-using-plsql\/","title":{"rendered":"Custom ILM ADO policy conditions using PL\/SQL"},"content":{"rendered":"

In my previous post about ILM storage tiering<\/a> 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? <\/p>\n

Unfortunately, the documentation provides not so much information about it:
\n
\nON<\/strong>
\nUse this clause to specify a PL\/SQL function that returns a boolean value. For function_name<\/strong>, specify the name of the function. The policy will take effect when the function returns TRUE<\/strong>.
\n<\/code>
\nA good example of using customized policies can be found in the blog by Uwe Hesse<\/a>. 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. <\/p>\n

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

Let’s take a look on it. We start with the same setup as in previous post about ILM storage tiering.<\/a> The same table SCOTT.EMPLOYEE<\/strong> and the same tablespace LOW_COST_STORE<\/strong>. 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”<\/strong>.<\/p>\n

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,<\/em><\/strong> to be able to cope with its change for testing purposes. <\/p>\n

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)<\/p>\n

\r\nCREATE OR REPLACE FUNCTION f_custom (p_object_id number) RETURN BOOLEAN IS\r\n  2  v_time NUMBER;\r\n  3  v_days_no_modification NUMBER := 10;\r\n  4  v_ilm_parameter number;\r\n  5  BEGIN\r\n  6    SELECT  sysdate-SEGMENT_WRITE_TIME\r\n  7    INTO    v_time\r\n  8    FROM    user_heat_map_segment\r\n  9    WHERE   object_name = (SELECT object_name FROM user_objects WHERE object_id=p_object_id);\r\n 10  \r\n 11    SELECT value\r\n 12    INTO v_ilm_parameter\r\n 13    FROM dba_ilmparameters WHERE name = 'POLICY TIME';\r\n 14  \r\n 15    IF v_ilm_parameter = 1 \/*DBMS_ILM_ADMIN.POLICY_IN_SECONDS*\/ THEN\r\n 16  \t v_time:=v_time*60*60*24;\r\n 17    END IF;\r\n 18  \r\n 19    RETURN v_time > v_days_no_modification;\r\n 20  \r\n 21  END;\r\n 22  \/\r\n\r\nFunction created.\r\n\r\nSCOTT> -- Storage tiering with user defined condition function\r\nSCOTT> ALTER TABLE scott.employee1  ILM ADD POLICY TIER TO low_cost_store ON f_custom;\r\n\r\nTable altered.\r\n\r\nSCOTT> ----------------------------------------------------------------------------------------------------\r\nSCOTT> -- Show ILM policy information\r\nSCOTT> ----------------------------------------------------------------------------------------------------\r\nSCOTT> SELECT policy_name, action_type, scope, tier_tablespace\r\n  2    ,      condition_type, condition_days, custom_function\r\n  3    FROM   user_ilmdatamovementpolicies;\r\n\r\nPOLICY_NAME ACTION_TYPE SCOPE\tTIER_TABLESPACE  CONDITION_TYPE         CONDITION_DAYS  CUSTOM_FUNCTION\r\n----------- ----------- ------- ---------------- ---------------------- --------------  ------------------\r\nP261\t    STORAGE     SEGMENT LOW_COST_STORE\t USER DEFINED           0               F_CUSTOM\r\n\r\n\r\nSCOTT> \r\nSCOTT> SELECT policy_name, object_name, object_type, enabled\r\n  2    FROM user_ilmobjects;\r\n\r\nPOLICY_NAME OBJECT_NAME          OBJECT_TYPE\t    ENABLED\r\n----------- -------------------- ------------------ -------\r\nP261\t    EMPLOYEE1            TABLE              YES\r\n<\/pre>\n
Now we set up the Heat Map for testing, update the table and flush Heat Map information. Querying USER_HEAT_MAP_SEGMENT<\/strong> 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<\/strong> is telling that: PRECONDITION NOT SATISFIED.<\/p>\n
\r\nSCOTT> EXECUTE dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.POLICY_TIME, DBMS_ILM_ADMIN.ILM_POLICY_IN_SECONDS);\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSCOTT> -- 2. set start date\r\nSCOTT> EXECUTE DBMS_ILM_ADMIN.SET_HEAT_MAP_START(sysdate -50);\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSCOTT> \r\nSCOTT> UPDATE scott.employee1 e SET e.empno = e.empno+1;\r\n\r\n3584 rows updated.\r\n\r\nSCOTT> commit;\r\n\r\nCommit complete.\r\n\r\nSCOTT> \r\nSCOTT> EXECUTE dbms_ilm.flush_all_segments;\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSCOTT> SELECT\tobject_name, to_char(segment_write_time,'DD-MON-YY hh24:mi:ss') write_time\r\n  2  FROM\tuser_heat_map_segment\r\n  3  WHERE\tobject_name ='EMPLOYEE1';\r\n\r\nOBJECT_NAME\t     WRITE_TIME\r\n-------------------- ---------------------------\r\nEMPLOYEE1\t     21-OCT-17 11:03:50\r\n\r\nSCOTT> \r\nSCOTT> declare\r\n  2  v_executionid number;\r\n  3  begin\r\n  4    dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA,\r\n  5  \t\t execution_mode => dbms_ilm.ilm_execution_offline,\r\n  6  \t\t task_id   => v_executionid);\r\n  7  end;\r\n  8  \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSCOTT> ----------------------------------------------------------------------------------------------------\r\nSCOTT> -- Table EMPLOYEE1 was NOT moved to tablespace LOW_COST_STORE\r\nSCOTT> --------------------------------------------------------------------------------------------------\r\nSCOTT> select tablespace_name, segment_name\r\n  2  from   user_segments\r\n  3  where  segment_name='EMPLOYEE1';\r\n\r\nTABLESPACE_NAME \t       SEGMENT_NAME\r\n------------------------------ --------------------\r\nUSERS\t\t\t       EMPLOYEE1\r\n\r\nSCOTT> \r\nSCOTT> select task_id, object_name, selected_for_execution from user_ilmevaluationdetails;\r\n\r\n   TASK_ID OBJECT_NAME\t\tSELECTED_FOR_EXECUTION\r\n---------- -------------------- ------------------------------------------\r\n       219 EMPLOYEE1\t\tPRECONDITION NOT SATISFIED\r\n\r\n<\/pre>\n
So we’ll be waiting 10 seconds and then repeat. Now the table EMPLOYEE1 has been moved to the new tablespace.<\/p>\n
\r\nSCOTT> EXECUTE\tdbms_lock.sleep(10);\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSCOTT> \r\nSCOTT> declare\r\n  2  v_executionid number;\r\n  3  begin\r\n  4    dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA,\r\n  5  \t\t execution_mode => dbms_ilm.ilm_execution_offline,\r\n  6  \t\t task_id   => v_executionid);\r\n  7  end;\r\n  8  \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSCOTT> ----------------------------------------------------------------------------------------------------\r\nSCOTT> -- Table EMPLOYEE1 is now moved to tablespace LOW_COST_STORE\r\nSCOTT> --------------------------------------------------------------------------------------------------\r\nSCOTT> select tablespace_name, segment_name\r\n  2  from   user_segments\r\n  3  where  segment_name='EMPLOYEE1';\r\n\r\nTABLESPACE_NAME \t       SEGMENT_NAME\r\n------------------------------ --------------------\r\nLOW_COST_STORE\t\t       EMPLOYEE1\r\n\r\nSCOTT> \r\nSCOTT> SELECT task_id, to_char(start_time, 'dd\/mm\/yyyy hh24:mi:ss') as start_time, u.STATE\r\n  2  FROM user_ilmtasks u;\r\n\r\n   TASK_ID START_TIME\t       STATE\r\n---------- ------------------- ---------\r\n       219 21\/10\/2017 11:03:52 COMPLETED\r\n       220 21\/10\/2017 11:04:13 COMPLETED\r\n\r\nSCOTT> \r\nSCOTT> select task_id, job_name, job_state, to_char(completion_time, 'dd\/mm\/yyyy hh24:mi:ss') completion\r\n  2  from user_ilmresults;\r\n\r\n   TASK_ID JOB_NAME\t   JOB_STATE\t\t\t       COMPLETION\r\n---------- --------------- ----------------------------------- ------------------------------\r\n       220 ILMJOB4212\t   COMPLETED SUCCESSFULLY\t       21\/10\/2017 11:04:14\r\n\r\nSCOTT> \r\nSCOTT> select task_id, object_name, selected_for_execution from user_ilmevaluationdetails;\r\n\r\n   TASK_ID OBJECT_NAME\t\tSELECTED_FOR_EXECUTION\r\n---------- -------------------- ------------------------------------------\r\n       219 EMPLOYEE1\t\tPRECONDITION NOT SATISFIED\r\n       220 EMPLOYEE1\t\tSELECTED FOR EXECUTION\r\n\r\n<\/pre>\n
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. <\/p>\n
Related Posts<\/h3>