{"id":528,"date":"2017-11-13T23:12:28","date_gmt":"2017-11-13T21:12:28","guid":{"rendered":"http:\/\/blog.sqlora.com\/en\/?p=528"},"modified":"2018-04-06T13:53:45","modified_gmt":"2018-04-06T11:53:45","slug":"ilm-is-it-possible-to-mix-ado-policies-for-compression-and-storage","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/ilm-is-it-possible-to-mix-ado-policies-for-compression-and-storage\/","title":{"rendered":"ILM – is it possible to mix ADO policies for compression and storage?"},"content":{"rendered":"

Never thought I would write much about Information Lifecycle Management, as I am actually a developer and not a DBA. I think, it is indeed a topic mostly relevant for DBA’s. But it is generally a good thing, if developers and DBA’s have a deep understanding of each others job, isn’t it? We are giving an overview of the ILM features in our training “12c New Features for Developers”<\/a> and I’m one of the course instructors for it. That’s the reason, why I’m writing meanwhile the third post about it. Just to clarify some questions, which are not so obviously documented. <\/p>\n

After looking at ADO conditions for storage tiering policies <\/a> and using user defined PL\/SQL conditions<\/a> in previous posts, I was curious whether it is possible to mix storage and compression policies for the same segment? Wouldn’t it for example make sense to move the segment to a low cost tablespace and compress the data within the same action as well? I’ve sometimes heard the opposite statement. But it is very simple to test it, not just trust it. Let’s try it. <\/p>\n

So we start again with the same setup as in previous posts ( 1<\/a> and 2<\/a>) on ILM and then define both policies on the table EMPLOYEE1<\/strong>: we would like to compress the data (basic) after ten days of no access while moving it to LOW_COST_STORE<\/strong> tablespace at the same time. To proof the compression let’s also show the initial segment size.<\/p>\n

\r\nSYS> SELECT owner, segment_name, tablespace_name, bytes, blocks\r\n  2    FROM dba_segments\r\n  3   WHERE segment_name = 'EMPLOYEE1';\r\n\r\nOWNER   SEGMENT_NAME  TABLESPACE_NAME   BYTES    BLOCKS\r\n------- ------------- ----------------- -------- --------\r\nSCOTT   EMPLOYEE1     USERS             327680   40\r\n\r\nSCOTT> ALTER TABLE scott.employee1\r\n  2  ILM ADD POLICY ROW STORE COMPRESS BASIC\r\n  3  SEGMENT AFTER 10 DAYS OF NO ACCESS;\r\n\r\nTable altered.\r\n\r\nSCOTT> \r\nSCOTT> -- Storage tiering with condition only to move to read-only tablespace\r\nSCOTT> ALTER TABLE scott.employee1\r\n  2  ILM ADD POLICY TIER TO low_cost_store READ ONLY\r\n  3  SEGMENT AFTER 10 DAYS OF NO ACCESS;\r\n\r\nTable altered.\r\n\r\nSCOTT> SELECT policy_name, action_type, scope, tier_tablespace, condition_type, condition_days\r\n  2    FROM user_ilmdatamovementpolicies;\r\n\r\nPOLICY_NAME ACTION_TYPE SCOPE\tTIER_TABLESPACE   CONDITION_TYPE    CONDITION_DAYS\r\n----------- ----------- ------- ----------------- ----------------- --------------\r\nP307\t    COMPRESSION SEGMENT                   LAST ACCESS TIME  10\r\nP308\t    STORAGE     SEGMENT LOW_COST_STORE    LAST ACCESS TIME  10\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\nP307\t    EMPLOYEE1             TABLE\t            YES\r\nP308\t    EMPLOYEE1             TABLEE            YES\r\n<\/pre>\n
At least, there is no error message. Policies are there and enabled. Seems to work. But does it really work?  Set the evaluation time in seconds, set the start time,  flush Heat Map information and wait more than ten seconds:<\/p>\n
\r\nSCOTT> ----------------------------------------------------------------------------------------------------\r\nSCOTT> -- Triggering the Table to Move to Low Cost Storage\r\nSCOTT> ----------------------------------------------------------------------------------------------------\r\nSCOTT> -- 1. Switch ILM unit to seconds\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> EXECUTE dbms_ilm.flush_all_segments;\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSCOTT> EXECUTE  dbms_lock.sleep(15);\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\n
Now we start the ILM execution manually and see what happens:<\/p>\n
\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 and compressed\r\nSCOTT> ------------------------------------------------------------------------------\r\nSCOTT> select tablespace_name, segment_name, bytes, blocks\r\n  2  from   user_segments\r\n  3  where  segment_name='EMPLOYEE1';\r\n\r\nTABLESPACE_NAME   SEGMENT_NAME\t BYTES\t  BLOCKS\r\n----------------- -------------- -------- ----------\r\nLOW_COST_STORE    EMPLOYEE1      131072\t  16\r\n\r\nSCOTT> SELECT compression, compress_for\r\n  2  FROM user_tables WHERE table_name = 'EMPLOYEE1';\r\n\r\nCOMPRESS COMPRESS_FOR\r\n-------- ------------------------------\r\nENABLED  BASIC\r\n<\/pre>\n
And voil\u00e1, the table is moved and compressed. Note how the size dropped by almost two-thirds. The execution information about the policies confirms that:<\/p>\n
\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          STATE\r\n---------- ------------------- ---------\r\n       276 31\/10\/2017 17:14:38 COMPLETED\r\n\r\nSCOTT> \r\nSCOTT> select task_id,\tjob_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     JOB_STATE                COMPLETION\r\n---------- ------------ ------------------------ ------------------------------\r\n       276 ILMJOB4438   COMPLETED SUCCESSFULLY   31\/10\/2017 17:14:39\r\n\r\nSCOTT> \r\nSCOTT> select task_id, policy_name, object_name, selected_for_execution from user_ilmevaluationdetails;\r\n\r\n   TASK_ID POLICY_NAME OBJECT_NAME     SELECTED_FOR_EXECUTION\r\n---------- ----------- --------------- ------------------------------------------\r\n       276 P307        EMPLOYEE1       SELECTED FOR EXECUTION\r\n       276 P308        EMPLOYEE1       SELECTED FOR EXECUTION\r\n\r\n<\/pre>\n
Obviously, it works. Of course the rules for multiple policies apply. A good overview about the rules and restrictions can be found  here.<\/a> <\/p>\n
Related Posts<\/h3>