ILM storage tiering

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!

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

*