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

2 thoughts on “ILM storage tiering

  1. koji kaboto

    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

    SELECT SELECTED_FOR_EXECUTION FROM DBA_ILMEVALUATIONDETAILS WHERE TASK_ID=124;
    
    SELECTED_FOR_EXECUTION
    ------------------------------------------
    PRECONDITION NOT SATISFIED
    
    

    have you tried to use this kind of policies ?

    Reply
    1. admin Post author

      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.

      
      SQL> alter system set heat_map=on scope=both;
      
      System SET altered.
      
      SQL> 
      SQL> 
      SQL> create tablespace fast_tbs
        2  datafile 'fast_tbs.dbf' size 1M;
      
      Tablespace FAST_TBS created.
      
      SQL> 
      SQL> create tablespace slow_tbs
        2  datafile 'slow_tbs.dbf' size 1M;
      
      Tablespace SLOW_TBS created.
      
      SQL> -- Create a table in the tablespace FAST_TBS
      SQL> 
      SQL> create table employee1 (
        2          EMPNO     NUMBER(4) NOT NULL,
        3          ENAME     VARCHAR2(10),
        4          JOB       VARCHAR2(9),
        5          MGR       NUMBER(4),
        6          HIREDATE  DATE,
        7          SAL       NUMBER(7,2),
        8          COMM      NUMBER(7,2),
        9          DEPTNO    NUMBER(2)
       10  ) tablespace fast_tbs;
      
      Table EMPLOYEE1 created.
      
      SQL> 
      SQL> insert into employee1 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
        2  select empno, ename, job, mgr, hiredate, sal, comm, deptno from scott.emp;
      
      14 rows inserted.
      
      SQL> 
      SQL> COMMIT;
      
      Commit complete.
      
      SQL> 
      SQL> ----------------------------------------
      SQL> -- Table EMPLOYEE1 is stored in FAST_TBS
      SQL> ----------------------------------------
      SQL> SELECT owner, segment_name, tablespace_name
        2    FROM dba_segments
        3   WHERE segment_name = 'EMPLOYEE1';
      
      OWNER      SEGMENT_NAME         TABLESPACE_NAME               
      ---------- -------------------- ------------------------------
      O12TEST    EMPLOYEE1            FAST_TBS                      
      
      SQL> 
      SQL> ------------------------------------------------------
      SQL> -- Creating a Storage Tiering Policy on the Table 
      SQL> ------------------------------------------------------
      SQL> 
      SQL> CREATE OR REPLACE FUNCTION f_custom (p_object_id number) RETURN BOOLEAN IS
        2     v_cnt NUMBER;
        3  BEGIN
        4       SELECT  count(*)
        5       INTO    v_cnt
        6       FROM    user_tables
        7       WHERE   tablespace_name = 'FAST_TBS';
        8  
        9      IF v_cnt > 3 THEN    
       10        RETURN true;
       11      ELSE 
       12        RETURN false;
       13      END IF;
       14  END;
       15  /
      
      Function F_CUSTOM compiled
      
      SQL> 
      SQL> 
      SQL> -- Adding the policy
      SQL> ALTER TABLE employee1
        2  ILM ADD POLICY TIER TO slow_tbs ON f_custom;
      
      Table EMPLOYEE1 altered.
      
      SQL> 
      SQL> 
      SQL> 
      SQL> ---------------------------------------
      SQL> -- Show ILM policy information
      SQL> ---------------------------------------
      SQL> SELECT policy_name, action_type, scope, tier_tablespace
        2        ,      condition_type, condition_days, custom_function
        3  FROM user_ilmdatamovementpolicies;
      
      POLICY_NAME ACTION_TYPE SCOPE   TIER_TABLESPACE  CONDITION_TYPE         CONDITION_DAYS CUSTOM_FUNCTION                                                                                                                 
      ----------- ----------- ------- ---------------- ---------------------- -------------- -------------------
      P101        STORAGE     SEGMENT SLOW_TBS         USER DEFINED                        0 F_CUSTOM                                                                                                                        
      
      SQL> 
      SQL> SELECT policy_name, object_name, object_type, enabled
        2    FROM user_ilmobjects;
      
      POLICY_NAME OBJECT_NAME          OBJECT_TYPE        ENABLED
      ----------- -------------------- ------------------ -------
      P101        EMPLOYEE1            TABLE              YES    
       
      
      SQL> -----------------------------------
      SQL> -- Run ILM data movement task
      SQL> -----------------------------------
      SQL> 
      SQL> 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_online,
        6              task_id   => v_executionid);
        7  end;
        8  /
      
      PL/SQL procedure successfully completed.
      
      SQL> 
      SQL> 
      SQL> ------------------
      SQL> -- Nothing happens
      SQL> ------------------
      SQL> select tablespace_name, segment_name
        2  from user_segments
        3  where segment_name='EMPLOYEE1';
      
      TABLESPACE_NAME                SEGMENT_NAME        
      ------------------------------ --------------------
      FAST_TBS                       EMPLOYEE1           
      
      SQL> 
      
      SQL> SELECT TASK_ID, SELECTED_FOR_EXECUTION FROM USER_ILMEVALUATIONDETAILS;
      
         TASK_ID SELECTED_FOR_EXECUTION                    
      ---------- ------------------------------------------
              42 PRECONDITION NOT SATISFIED    
      
      SQL> -----------------------------------
      SQL> -- Create another three tables
      SQL> -----------------------------------
      SQL> CREATE TABLE employee2 TABLESPACE fast_tbs AS SELECT * FROM employee1;
      
      Table EMPLOYEE2 created.
      
      SQL> CREATE TABLE employee3 TABLESPACE fast_tbs AS SELECT * FROM employee1;
      
      Table EMPLOYEE3 created.
      
      SQL> CREATE TABLE employee4 TABLESPACE fast_tbs AS SELECT * FROM employee1;
      
      Table EMPLOYEE4 created.
      
      SQL> 
      SQL> -----------------------------------
      SQL> -- Re-Run ILM data movement task
      SQL> -----------------------------------
      SQL> 
      SQL> 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_online,
        6              task_id   => v_executionid);
        7  end;
        8  /
      
      PL/SQL procedure successfully completed.
      
      SQL> 
      SQL> 
      SQL> 
      SQL> ----------------------------------------------------------------------------------------------------
      SQL> -- Show ILM task results
      SQL> --------------------------------------------------------------------------------------------------
      SQL> SELECT TASK_ID, SELECTED_FOR_EXECUTION FROM USER_ILMEVALUATIONDETAILS;
      
         TASK_ID SELECTED_FOR_EXECUTION                    
      ---------- ------------------------------------------
              42 PRECONDITION NOT SATISFIED                
              43 SELECTED FOR EXECUTION                    
      
      SQL> 
      SQL> SELECT task_id, to_char(start_time, 'dd/mm/yyyy hh24:mi:ss') as start_time
        2  FROM user_ilmtasks;
      
         TASK_ID START_TIME         
      ---------- -------------------
              42 28/06/2018 00:35:15
              43 28/06/2018 00:36:19
      
      8 rows selected. 
      
      SQL> 
      SQL> 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                    
      ---------- --------------- ----------------------------------- ------------------------------
              43 ILMJOB9722      JOB CREATED                                                       
      
      SQL> 
      SQL> -- After some time the table is moved
      SQL> exec dbms_lock.sleep(10);
      
      PL/SQL procedure successfully completed.
      
      SQL> select tablespace_name, segment_name
        2  from user_segments
        3  where segment_name='EMPLOYEE1';
      
      TABLESPACE_NAME                SEGMENT_NAME        
      ------------------------------ --------------------
      SLOW_TBS                       EMPLOYEE1           
      
      
      Reply

Leave a Reply

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