{"id":448,"date":"2017-10-15T21:29:34","date_gmt":"2017-10-15T19:29:34","guid":{"rendered":"http:\/\/blog.sqlora.com\/en\/?p=448"},"modified":"2018-04-06T13:56:22","modified_gmt":"2018-04-06T11:56:22","slug":"ilm-storage-tiering","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/ilm-storage-tiering\/","title":{"rendered":"ILM storage tiering"},"content":{"rendered":"

Being not really new term introduced in 12c, Information Lifecycle Management has been enriched with two new features: Heat Map and ADO (Automatic Data Optimization). The letter allows to define policies on data segments (tables, partitions), which consist of conditions and actions. The conditions are generally being checked based on the information gathered with the Heat Map and defined actions are then taken: compress the data (compression tiering) or move the data (storage tiering). I said “generally”, because, on the one hand, one can define custom conditions (PL\/SQL functions) not necessarily using Heat Map and, on the other hand, because of the rather confusing situation using conditions with storage tiering. <\/p>\n

In 12.1 the syntax diagram for ILM clause was quite misleading, suggesting that we can define the policy based on conditions. <\/p>\n

\"\"<\/a><\/p>\n

Obviously it was a documentation bug, it simply doesn’t work:<\/p>\n

\r\nSCOTT> ALTER TABLE scott.employee\r\n  2  ILM ADD POLICY TIER TO low_cost_store\r\n  3  SEGMENT AFTER 10 DAYS OF NO MODIFICATION;\r\nSEGMENT AFTER 10 DAYS OF NO MODIFICATION\r\n        *\r\nERROR at line 3:\r\nORA-01735: invalid ALTER TABLE option \r\n\r\nSCOTT> ALTER TABLE scott.employee ILM ADD POLICY TIER TO low_cost_store;\r\n\r\nTable altered.\r\n<\/pre>\n
Beside of custom conditions using PL\/SQL functions, only the simplest form without condition seems to work. The decision when to start moving segments is based upon tablespace space pressure. ADO parameters TBS_PERCENT_USED<\/strong>  and TBS_PERCENT_FREE<\/strong> are there to further control the behavior. Oracle’s White Paper<\/a> states the following:<\/p>\n
The justification for making storage tiering dependent on “space pressure” is exactly as you might imagine, the belief that organizations will want to keep as much data as possible on their high performance (and most expensive) storage tier, and not move data to a lower performance storage tier until it is absolutely required. The exception to the storage pressure requirement are storage tiering policies with the ‘READ ONLY’ option, these are triggered by a heat-map based condition clause.<\/em><\/p>\n
Personally I cannot completely agree  with this argumentation. Why not also provide other options the same way as for compression? But the second statement made me curious.<\/p>\n
 
\nThe 12.2 documentation  offers the corrected version of the syntax diagram for storage tiering (now separated from compression tiering):<\/p>\n
\"\"<\/a><\/p>\n
 
\nAccording to this one it should be possible to define conditions if we move the segments to a read-only tablespace.
\nDoes it work as described? Let us construct an example based on Oracle’s one<\/a>.<\/p>\n
First we create a tablespace to move the table to and set it read-only. Quite important: this tablespace will be set read-write to move data and then again read-only. That is why we need to grant ALTER TALESPACE to SCOTT. <\/p>\n
\r\nSYS> create tablespace low_cost_store\r\n  2  datafile '&_data_path\/lowcoststore01.dbf' size 1M ;\r\n\r\nTablespace created.\r\n\r\nSYS> \r\nSYS> alter tablespace low_cost_store read only;\r\n\r\nTablespace altered.\r\n\r\nSYS> \r\nSYS> grant alter tablespace, select any dictionary to scott;\r\n\r\nGrant succeeded.\r\n\r\nSYS> ---------------------------------------------------------\r\nSYS> -- Table EMPLOYEE is stored in default tablespace USERS\r\nSYS> ---------------------------------------------------------\r\nSYS> SELECT owner, segment_name, tablespace_name\r\n  2    FROM dba_segments\r\n  3   WHERE segment_name = 'EMPLOYEE';\r\n\r\nOWNER\t   SEGMENT_NAME \tTABLESPACE_NAME\r\n---------- -------------------- ------------------------------\r\nSCOTT\t   EMPLOYEE             USERS\r\n\r\n<\/pre>\n
 
\nNext we create the policy to move the data after ten days of no modification and verify that in data dictionary. Note that we have ACTION_TYPE=”STORAGE” and nevertheless CONDITION_TYPE is set.
\n <\/p>\n
\r\n\r\nSCOTT> -- Storage tiering with condition only to move to read-only tablespace\r\nSCOTT> ALTER TABLE scott.employee\r\n  2  ILM ADD POLICY TIER TO low_cost_store READ ONLY\r\n  3  SEGMENT AFTER 10 DAYS OF NO MODIFICATION;\r\n\r\nTable altered.\r\n\r\nSCOTT> \r\nSCOTT> ----------------------------------------------------------------------------\r\nSCOTT> -- Show ILM policy information\r\nSCOTT> ----------------------------------------------------------------------------\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\nP162\t    STORAGE     SEGMENT LOW_COST_STORE   LAST MODIFICATION TIME \t    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 \t OBJECT_TYPE\t    ENABLED\r\n----------- -------------------- ------------------ -------\r\nP162\t    EMPLOYEE\t\t TABLE\t\t    YES\r\n \r\n<\/pre>\n
 
\nNext we’ll do this trick with setting the ILM evaluation time to seconds, as shown in Oracle’s example, flush Heat Map information and wait more than ten seconds:
\n <\/p>\n
\r\n\r\nSYS> -- 1. Switch ILM unit to seconds\r\nSYS> 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\nSYS> -- 2. set start date\r\nSYS> EXECUTE DBMS_ILM_ADMIN.SET_HEAT_MAP_START(start_date => sysdate - 50);\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSYS> EXECUTE dbms_ilm.flush_all_segments;\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSYS> EXECUTE  dbms_lock.sleep(15);\r\n\r\nPL\/SQL procedure successfully completed. \r\n\r\n<\/pre>\n
 
\nExecute the ILM job manually and see what happened. The table was moved to the new tablespace low_cost_store<\/strong><\/em>. ILM task was executed as expected.
\n <\/p>\n
\r\nSCOTT> ----------------------------------------------------------------------------------------------------\r\nSCOTT> -- Run ILM data movement task\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 EMPLOYEE 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='EMPLOYEE';\r\n\r\nTABLESPACE_NAME \t       SEGMENT_NAME\r\n------------------------------ --------------------\r\nLOW_COST_STORE\t\t       EMPLOYEE\r\n\r\n1 row selected.\r\n\r\nSCOTT> ----------------------------------------------------------------------------------------------------\r\nSCOTT> -- Show ILM task results\r\nSCOTT> --------------------------------------------------------------------------------------------------\r\nSCOTT> SELECT task_id, to_char(start_time, 'dd\/mm\/yyyy hh24:mi:ss') as start_time\r\n  2  FROM user_ilmtasks;\r\n\r\n   TASK_ID START_TIME\r\n---------- -------------------\r\n       162 12\/10\/2017 14:50:24\r\n\r\n1 row selected.\r\n\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       162 ILMJOB4148\t   COMPLETED SUCCESSFULLY\t       12\/10\/2017 14:50:26\r\n\r\n1 row selected. \r\n\r\n<\/pre>\n
 <\/p>\n
I’ve tested it in 12.1.0.1 – it works! So obviously this was no enhancement or bugfix in 12.1.0.2 or 12.2.0.1 but simply a documentation bug. And now the documentation is fixed for 12.1 as well!<\/p>\n
Related Posts<\/h3>