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.
In 12.1 the syntax diagram for ILM clause was quite misleading, suggesting that we can define the policy based on conditions.
Obviously it was a documentation bug, it simply doesn’t work:
SCOTT> ALTER TABLE scott.employee 2 ILM ADD POLICY TIER TO low_cost_store 3 SEGMENT AFTER 10 DAYS OF NO MODIFICATION; SEGMENT AFTER 10 DAYS OF NO MODIFICATION * ERROR at line 3: ORA-01735: invalid ALTER TABLE option SCOTT> ALTER TABLE scott.employee ILM ADD POLICY TIER TO low_cost_store; Table altered.
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 and TBS_PERCENT_FREE are there to further control the behavior. Oracle’s White Paper states the following:
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.
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.
The 12.2 documentation offers the corrected version of the syntax diagram for storage tiering (now separated from compression tiering):
According to this one it should be possible to define conditions if we move the segments to a read-only tablespace.
Does it work as described? Let us construct an example based on Oracle’s one.
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.
SYS> create tablespace low_cost_store 2 datafile '&_data_path/lowcoststore01.dbf' size 1M ; Tablespace created. SYS> SYS> alter tablespace low_cost_store read only; Tablespace altered. SYS> SYS> grant alter tablespace, select any dictionary to scott; Grant succeeded. SYS> --------------------------------------------------------- SYS> -- Table EMPLOYEE is stored in default tablespace USERS SYS> --------------------------------------------------------- SYS> SELECT owner, segment_name, tablespace_name 2 FROM dba_segments 3 WHERE segment_name = 'EMPLOYEE'; OWNER SEGMENT_NAME TABLESPACE_NAME ---------- -------------------- ------------------------------ SCOTT EMPLOYEE USERS
Next 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.
SCOTT> -- Storage tiering with condition only to move to read-only tablespace SCOTT> ALTER TABLE scott.employee 2 ILM ADD POLICY TIER TO low_cost_store READ ONLY 3 SEGMENT AFTER 10 DAYS OF NO MODIFICATION; Table altered. SCOTT> SCOTT> ---------------------------------------------------------------------------- SCOTT> -- Show ILM policy information SCOTT> ---------------------------------------------------------------------------- SCOTT> SELECT policy_name, action_type, scope, tier_tablespace, condition_type, condition_days 2 FROM user_ilmdatamovementpolicies; POLICY_NAME ACTION_TYPE SCOPE TIER_TABLESPACE CONDITION_TYPE CONDITION_DAYS ----------- ----------- ------- ---------------- ---------------------- -------------- P162 STORAGE SEGMENT LOW_COST_STORE LAST MODIFICATION TIME 10 SCOTT> SCOTT> SELECT policy_name, object_name, object_type, enabled 2 FROM user_ilmobjects; POLICY_NAME OBJECT_NAME OBJECT_TYPE ENABLED ----------- -------------------- ------------------ ------- P162 EMPLOYEE TABLE YES
Next 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:
SYS> -- 1. Switch ILM unit to seconds SYS> EXECUTE dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.POLICY_TIME, DBMS_ILM_ADMIN.ILM_POLICY_IN_SECONDS); PL/SQL procedure successfully completed. SYS> -- 2. set start date SYS> EXECUTE DBMS_ILM_ADMIN.SET_HEAT_MAP_START(start_date => sysdate - 50); PL/SQL procedure successfully completed. SYS> EXECUTE dbms_ilm.flush_all_segments; PL/SQL procedure successfully completed. SYS> EXECUTE dbms_lock.sleep(15); PL/SQL procedure successfully completed.
Execute the ILM job manually and see what happened. The table was moved to the new tablespace low_cost_store. ILM task was executed as expected.
SCOTT> ---------------------------------------------------------------------------------------------------- SCOTT> -- Run ILM data movement task 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 EMPLOYEE is now moved to tablespace LOW_COST_STORE SCOTT> -------------------------------------------------------------------------------------------------- SCOTT> select tablespace_name, segment_name 2 from user_segments 3 where segment_name='EMPLOYEE'; TABLESPACE_NAME SEGMENT_NAME ------------------------------ -------------------- LOW_COST_STORE EMPLOYEE 1 row selected. SCOTT> ---------------------------------------------------------------------------------------------------- SCOTT> -- Show ILM task results SCOTT> -------------------------------------------------------------------------------------------------- SCOTT> SELECT task_id, to_char(start_time, 'dd/mm/yyyy hh24:mi:ss') as start_time 2 FROM user_ilmtasks; TASK_ID START_TIME ---------- ------------------- 162 12/10/2017 14:50:24 1 row selected. 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 ---------- --------------- ----------------------------------- ------------------------------ 162 ILMJOB4148 COMPLETED SUCCESSFULLY 12/10/2017 14:50:26 1 row selected.
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!
Hi there
i am trying to use function based tiering policy , i created a function that count number of tables in a fast tablespace fast_tbs and return true if the number of tables is greater than 3.
then i tried to create policy based on that function
alter table my_tab ilm add policy tier to slow_tablespace on my_check_function.
i always get
have you tried to use this kind of policies ?
Hi Koji,
it works for me. Don’t forget to turn HEAT_MAP=ON. Although your function is not using Heat Map, this setting is required and this also means, you need the Advanced Compression Option license.