Information Lifecycle Management (ILM)

Preamble

Over the past last years we have all seen the size of our databases increasing drastically. Sometimes for regulatory reasons (SOX, HIPAA and so on compliance) or (quite common where I work) because internal customers want huge amount of historical data online and accessible just in case of (in other words archiving not an option). Even if storage cost has decreased it remains a challenge to store at low cost and maintaining performance on an increasing amount of data. To try to solve this complex equation Oracle has implemented within its database what is called Information Lifecycle Management (ILM).

The idea behind ILM is to use storage tiering and compress old figures that you are not supposed to be updated any more. It will save (fast) disk space and increase disk read response time (less compressed blocks to read on disk) with the overhead to use more CPU to decompress. Means that your hot data (current month figures for example) remains on your fastest (and most expensive) storage, warm data (beginning of the year till last month for example) on an average cost storage and finally cold data (all previous years) on a low cost storage. Versus archiving this strategy has the great added value to keep all figures online in case of a compliance audit or for the need of an historical report. This can be summarized with this picture:

ilm01
ilm01

Of course this could be manually achieved manually using Oracle partitioning and compression options but this would be really time consuming. Please note that to benefit from interesting compression options you may need to buy Advanced Compression enterprise option or extremely be on Exadata or on an Oracle storage (ZFS appliance or Pilar Axiom storage).

ILM is an Enterprise edition option and is included in Advanced Compression enterprise option. No need to say that without Partitioning Enterprise edition option ILM has much less interest has it would be difficult to implement storage tiering within same object.

Oracle ILM is made of two sub-features:

  • HEAT_MAP: tracks modifications for individual rows and segments (aggregated to the block level) and modifications and queries at the partition or table level
  • Automatic Data Optimization (ADO): declarative syntax for specifying Information Lifecycle Management (ILM) policies at the row, segment, and table level

ADO policies are:

  • Space based: move segment between storage tiers or when tablespace is full
  • Time based: compress data within objects. Compression at three level: ROW STORAGE, SEGMENT and GROUP

To be honest the space based ADO policy has, for me, a minor interest as I difficulty see how it can be used in a real life scenario…

Testing has been done on Oracle Enterprise edition 12cR1 (12.1.0.2.0) running on Red Hat Enterprise Linux Server release 6.3 (Santiago).

ILM preparation

First I start by creation of three ADO tablespaces:

SQL> CREATE TABLESPACE hot_data
     datafile '/oracleE2POC/data01/s2poc/hot_data01.dbf' SIZE 10m
     SEGMENT SPACE management auto
     extent management local autoallocate;
 
TABLESPACE created.
 
SQL> CREATE TABLESPACE warm_data
     datafile '/oracleE2POC/data01/s2poc/warm_data01.dbf' SIZE 10m
     SEGMENT SPACE management auto
     extent management local autoallocate;
 
TABLESPACE created.
 
SQL> CREATE TABLESPACE cold_data
     datafile '/oracleE2POC/data01/s2poc/cold_data01.dbf' SIZE 10m
     SEGMENT SPACE management auto
     extent management local autoallocate;
 
TABLESPACE created.

Then I activate heat map:

SQL> ALTER SYSTEM SET heat_map=ON;
 
SYSTEM altered.

I change few ILM parameters: tablespace completion percentage that trigger ADO policy, execution interval of ADO policy (in minutes) and finally the execution mode of ADO (online or offline):

SQL> EXEC dbms_ilm_admin.customize_ilm(parameter=>dbms_ilm_admin.TBS_PERCENT_USED, VALUE=>80);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC dbms_ilm_admin.customize_ilm(parameter=>dbms_ilm_admin.EXECUTION_INTERVAL, VALUE=>1);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC dbms_ilm_admin.customize_ilm(parameter=>dbms_ilm_admin.EXECUTION_MODE, VALUE=>DBMS_ILM_ADMIN.ILM_EXECUTION_ONLINE);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> col name FOR a20
SQL> SELECT * FROM DBA_ILMPARAMETERS;
 
NAME                      VALUE
-------------------- ----------
ENABLED                       1
RETENTION TIME               30
JOB LIMIT                     2
EXECUTION MODE                2
EXECUTION INTERVAL            1
TBS PERCENT USED             80
TBS PERCENT FREE             25
POLICY TIME                   0

Remark:
Oracle will move objects to target tablespace if source tablespace is more than 80% full (TBS PERCENT USED) and will do it till source tablespace is less than 25% free (TBS PERCENT FREE).

The start of heat map collection could be erratic and I have been obliged to issue something like below to really start it:

SQL> EXEC dbms_ilm_admin.set_heat_map_start(start_date => SYSDATE - 5);
 
PL/SQL PROCEDURE successfully completed.

I have also re-issue this:

SQL> ALTER SYSTEM SET heat_map=ON scope=memory;
 
SYSTEM altered.

ADO space based policy testing

Then I create sales test table with below script:

DROP TABLE sales;
 
CREATE TABLE sales
(ID NUMBER,
MONTH__CODE VARCHAR2(7),
CITY__CODE NUMBER,
QTY NUMBER,
VAL NUMBER
)
TABLESPACE HOT_DATA;

The below PL/SQL to fill the sales table. This PL/SQL is rerunable to let you fill HOT_DATA tablespace:

DECLARE
  i NUMBER;
  nbrows NUMBER;
BEGIN
  i:=1;
  SELECT NVL(MAX(id),0)+1 INTO i FROM sales;
  nbrows:=i+10000-1;
  LOOP
    EXIT WHEN i>nbrows;
    INSERT INTO sales VALUES(i,'M'||ROUND(dbms_random.VALUE(2010,2015))||LPAD(ROUND(dbms_random.VALUE(1,12)),2,'0'),
    ROUND(dbms_random.VALUE(1,30)),ROUND(dbms_random.VALUE(1,10000)),dbms_random.VALUE(1,10000));
    i:=i+1;
  END LOOP;
  COMMIT;
  dbms_stats.gather_table_stats('yjaquier', 'sales');
END;
/

Once you have started to insert rows in sales table you can see Heat Map tracking with:

SQL> SELECT * FROM v$heat_map_segment WHERE object_name='SALES';
 
OBJECT_NAME                    SUBOBJECT_NAME                       OBJ#   DATAOBJ#        TS# TRACK_TIME           SEG SEG FUL LOO     CON_ID
------------------------------ ------------------------------ ---------- ---------- ---------- -------------------- --- --- --- --- ----------
SALES                                                             118940     118940         45 23-JAN-2015 14:54:42 NO  NO  NO  NO           0

There are also many Heat Map views like DBA_HEATMAP_TOP_TABLESPACES, DBA_HEATMAP_TOP_OBJECTS, DBA_HEAT_MAP_SEGMENT and DBA_HEAT_MAP_SEG_HISTOGRAM but they did not contains interesting information in my environment. The DBA_HEATMAP_TOP_OBJECTS view even contained information related to a previous test with a bigger sales table…

As a reference the few queries you would issue:

ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';
SELECT * FROM DBA_HEATMAP_TOP_TABLESPACES WHERE tablespace_name IN ('HOT_DATA','WARM_DATA','COLD_DATA');
SELECT * FROM DBA_HEATMAP_TOP_OBJECTS WHERE owner='YJAQUIER' AND object_name='SALES';
SELECT * FROM DBA_HEAT_MAP_SEGMENT WHERE owner='YJAQUIER' AND object_name='SALES';
SELECT * FROM DBA_HEAT_MAP_SEG_HISTOGRAM WHERE owner='YJAQUIER' AND object_name='SALES';

If you write and do a Full Table Scan on sales table then you will see SEGMENT_WRITE and FULL_SCAN column of V$HEAT_MAP_SEGMENT view updtaed:

SQL> SELECT * FROM v$heat_map_segment WHERE object_name='SALES';
 
OBJECT_NAME                    SUBOBJECT_NAME                       OBJ#   DATAOBJ#        TS# TRACK_TIME           SEG SEG FUL LOO     CON_ID
------------------------------ ------------------------------ ---------- ---------- ---------- -------------------- --- --- --- --- ----------
SALES                                                             118940     118940         45 23-JAN-2015 16:05:41 YES NO  YES NO           0

Heat Map also comes with the DBMS_HEAT_MAP package but again the result are not the ones I expected, as a reference:

SELECT * FROM TABLE(dbms_heat_map.block_heat_map('YJAQUIER','SALES'));
SELECT * FROM TABLE(dbms_heat_map.extent_heat_map('YJAQUIER','SALES'));
SELECT * FROM TABLE(dbms_heat_map.object_heat_map('YJAQUIER','SALES'));
SELECT * FROM TABLE(dbms_heat_map.tablespace_heat_map('HOT_DATA'));

I add the below ADO space based policy to ask Oracle to automatically move the sales table to cold_data tablespace when hot_data tablespace will be 80% full:

SQL> ALTER TABLE sales ilm ADD policy tier TO cold_data;
 
TABLE altered.

You can verify it has been done with:

SQL> SELECT policy_name FROM DBA_ILMOBJECTS WHERE object_owner='YJAQUIER' AND object_name='SALES';
 
POLICY_NAM
----------
P22
 
SQL> col POLICY_NAME FOR a10
SQL> col TIER_TABLESPACE FOR a10
SQL> SELECT POLICY_NAME, ACTION_TYPE, SCOPE, TIER_TABLESPACE FROM DBA_ILMDATAMOVEMENTPOLICIES WHERE policy_name='P22';
 
POLICY_NAM ACTION_TYPE SCOPE   TIER_TABLE
---------- ----------- ------- ----------
P22        STORAGE     SEGMENT COLD_DATA
 
SQL> SELECT * FROM DBA_ILMPOLICIES WHERE policy_name='P22';
 
POLICY_NAM POLICY_TYPE   TABLESPACE                     ENABLE DELETED
---------- ------------- ------------------------------ ------ -------
P22        DATA MOVEMENT                                YES    NO

Then ADO policies are estimated during the maintenance windows, but you can execute them manually with something like:

SET serveroutput ON SIZE 999999
DECLARE
  taskid NUMBER;
BEGIN
  dbms_ilm.execute_ilm(owner=>'YJAQUIER', object_name=>'SALES', task_id=> taskid);
  dbms_output.put_line('Task ID: ' || taskid);
END;
/

If you execute it when the condition is not satisfied (tablespace completeness below 80%):

SQL> SELECT tablespace_name
FROM dba_tables
WHERE owner='YJAQUIER'
AND table_name='SALES';
 
TABLESPACE_NAME
------------------------------
HOT_DATA
 
SQL> SELECT b.tablespace_name, tbs_size SizeMb, NVL(a.free_space,0) FreeMb
     FROM
     (SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024 ,2) AS free_space
     FROM dba_free_space WHERE tablespace_name IN ('HOT_DATA','WARM_DATA','COLD_DATA') GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes)/1024/1024 AS tbs_size
     FROM dba_data_files WHERE tablespace_name IN ('HOT_DATA','WARM_DATA','COLD_DATA') GROUP BY tablespace_name ) b
     WHERE a.tablespace_name(+)=b.tablespace_name;
 
TABLESPACE_NAME                    SIZEMB     FREEMB
------------------------------ ---------- ----------
HOT_DATA                               10       8.44
COLD_DATA                              10          9
WARM_DATA                              10          9
 
SQL> col job_name FOR a15
SQL> SELECT SELECTED_FOR_EXECUTION, job_name FROM DBA_ILMEVALUATIONDETAILS WHERE task_id='22';
 
SELECTED_FOR_EXECUTION                     JOB_NAME
------------------------------------------ ---------------
PRECONDITION NOT SATISFIED
 
SQL> SELECT STATE, START_TIME, COMPLETION_TIME FROM DBA_ILMTASKS WHERE task_id='22';
 
STATE     START_TIME                                                                  COMPLETION_TIME
--------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
COMPLETED 23-JAN-15 04.31.40.677857 PM                                                23-JAN-15 04.31.40.677857 PM

Once the HOT_DATA tablespace is more than 80% full the ADO policy now trigger a job to be executed:

SQL> SELECT SELECTED_FOR_EXECUTION, job_name FROM DBA_ILMEVALUATIONDETAILS WHERE task_id='24';
 
SELECTED_FOR_EXECUTION                     JOB_NAME
------------------------------------------ ---------------
SELECTED FOR EXECUTION                     ILMJOB1132
 
SQL> SELECT JOB_NAME,JOB_STATE FROM DBA_ILMRESULTS WHERE task_id='24';
 
JOB_NAME        JOB_STATE
--------------- -----------------------------------
ILMJOB1132      COMPLETED SUCCESSFULLY
 
SQL> SELECT STATE, START_TIME, COMPLETION_TIME FROM DBA_ILMTASKS WHERE task_id='24';
 
STATE     START_TIME                                                                  COMPLETION_TIME
--------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
COMPLETED 23-JAN-15 04.42.00.286367 PM                                                23-JAN-15 04.42.23.794554 PM

Which move, as expected, the sales table in COLD_DATA tablespace:

SQL> SELECT b.tablespace_name, tbs_size SizeMb, NVL(a.free_space,0) FreeMb
     FROM
     (SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024 ,2) AS free_space
     FROM dba_free_space WHERE tablespace_name IN ('HOT_DATA','WARM_DATA','COLD_DATA') GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes)/1024/1024 AS tbs_size
     FROM dba_data_files WHERE tablespace_name IN ('HOT_DATA','WARM_DATA','COLD_DATA') GROUP BY tablespace_name ) b
     WHERE a.tablespace_name(+)=b.tablespace_name;
 
TABLESPACE_NAME                    SIZEMB     FREEMB
------------------------------ ---------- ----------
HOT_DATA                               10          9
COLD_DATA                         17.0625       5.06
WARM_DATA                              10          9
 
SQL> SELECT tablespace_name
FROM dba_tables
WHERE owner='YJAQUIER'
AND table_name='SALES';
 
TABLESPACE_NAME
------------------------------
COLD_DATA

ADO time based policy testing

DROP TABLE sales;
 
CREATE TABLE sales
(ID NUMBER,
MONTH__CODE VARCHAR2(7),
CITY__CODE NUMBER,
QTY NUMBER,
VAL NUMBER
)
TABLESPACE MOVE_DATA
PARTITION BY RANGE (MONTH__CODE)
(
PARTITION COLD VALUES LESS THAN ('M201313') TABLESPACE COLD_DATA,
PARTITION WARM VALUES LESS THAN ('M201413') TABLESPACE WARM_DATA,
PARTITION HOT VALUES LESS THAN (MAXVALUE) TABLESPACE HOT_DATA
);

Six levels of compression:

  • Basic table compression
  • Advanced table compression
  • Hybrid Columnar Compression [ low | high ] Query *
  • Hybrid Columnar Compression [ low | high ] Archive *

* The four last ones are available only on Exadata, ZFS appliance or Pilar Axiom storage.

I expected to compress the hot partition at row level after a period of no access but this combination is not possible, basic compression at row level is also not possible:

ORA-38338: incorrect ILM policy scope
OR
ORA-38336: invalid policy

So at the end will compress hot partition at row level after a period of 300 days of no modification, warm partition at segment level after a period of 600 days of no modification (till the past year figures are stable) and finally cold partition at segment level (with advanced compression) after a period of 900 days of no access:

SQL> ALTER TABLE sales
     MODIFY PARTITION cold
     ILM ADD POLICY
     ROW STORE COMPRESS ADVANCED
     SEGMENT
     AFTER 900 DAYS OF NO ACCESS;
 
TABLE altered.
 
SQL> ALTER TABLE sales
     MODIFY PARTITION warm
     ILM ADD POLICY
     ROW STORE COMPRESS BASIC
     SEGMENT
     AFTER 600 DAYS OF NO MODIFICATION;
 
TABLE altered.
 
SQL> ALTER TABLE sales
     MODIFY PARTITION hot
     ILM ADD POLICY
     ROW STORE COMPRESS ADVANCED
     ROW
     AFTER 300 DAYS OF NO MODIFICATION;
 
TABLE altered.

This can be controlled with:

SQL> col POLICY_NAME FOR a5
SQL> col SUBOBJECT_NAME FOR a15
SQL> SELECT a.policy_name,a.subobject_name,a.object_type,b.action_type,b.scope,b.compression_level,b.condition_type,b.condition_days
     FROM dba_ilmobjects a, dba_ilmdatamovementpolicies b
     WHERE a.policy_name=b.policy_name
     AND a.object_owner='YJAQUIER'
     AND a.object_name='SALES';
 
POLIC SUBOBJECT_NAME  OBJECT_TYPE        ACTION_TYPE SCOPE   COMPRESSION_LEVEL              CONDITION_TYPE         CONDITION_DAYS
----- --------------- ------------------ ----------- ------- ------------------------------ ---------------------- --------------
P70   COLD            TABLE PARTITION    COMPRESSION SEGMENT ADVANCED                       LAST ACCESS TIME                  900
P71   WARM            TABLE PARTITION    COMPRESSION SEGMENT                                LAST MODIFICATION TIME            600
P72   HOT             TABLE PARTITION    COMPRESSION ROW     ADVANCED                       LAST MODIFICATION TIME            300

I load my sales table with below PL/SQL script, 50,000 rows per month code starting from January 2012 till January 2015:

DECLARE
  i NUMBER;
  nbrows NUMBER;
  MONTH NUMBER;
  YEAR NUMBER;
BEGIN
  i:=1;
  YEAR:=2012;
  MONTH:=1;
  SELECT NVL(MAX(id),0)+1 INTO i FROM sales;
  nbrows:=i+50000-1;
  LOOP
    EXIT WHEN (YEAR=2015 AND MONTH=2);
    INSERT INTO sales VALUES(i,'M'||YEAR||LPAD(MONTH,2,'0'),ROUND(dbms_random.VALUE(1,30)),ROUND(dbms_random.VALUE(1,10000)),dbms_random.VALUE(1,10000));
    IF (MOD(i,50000)=0)
    THEN 
      MONTH:=MONTH+1;
      IF MONTH=13 THEN
        MONTH:=1;
        YEAR:=YEAR+1;
      END IF;
    END IF;
    i:=i+1;
  END LOOP;
  COMMIT;
  dbms_stats.gather_table_stats('yjaquier', 'sales');
END;
/

Before executing the ADO policies let’s check sales table usage:

SQL> col partition_name FOR a10
SQL> SELECT partition_name,compress_for,compression,blocks,avg_row_len FROM dba_tab_partitions
     WHERE table_owner='YJAQUIER'
     AND table_name='SALES'
     ORDER BY partition_position;
 
PARTITION_ COMPRESS_FOR                   COMPRESS     BLOCKS AVG_ROW_LEN
---------- ------------------------------ -------- ---------- -----------
COLD                                      DISABLED       8074          42
WARM                                      DISABLED       4030          43
HOT                                       DISABLED       1006          43

As no one would wait so many days to see if ADO policies are working well Oracle is providing a trick to transform days in seconds (do not do it in production and do not forget to revert it back):

SQL> EXEC dbms_ilm_admin.customize_ilm(parameter=>dbms_ilm_admin.policy_time, VALUE=>dbms_ilm_admin.ilm_policy_in_seconds);
 
PL/SQL PROCEDURE successfully completed.

After a period a 15 minutes (900 seconds) I execute manually the ADO policies with PL/SQL code we have seen above, not to be obliged to wait the maintenance window:

SQL> col job_name FOR a15
SQL> SELECT SELECTED_FOR_EXECUTION, job_name FROM DBA_ILMEVALUATIONDETAILS WHERE task_id='181';
 
SELECTED_FOR_EXECUTION                     JOB_NAME
------------------------------------------ ---------------
SELECTED FOR EXECUTION                     ILMJOB1398
SELECTED FOR EXECUTION                     ILMJOB1400
SELECTED FOR EXECUTION                     ILMJOB1402
 
SQL> SELECT STATE, START_TIME, COMPLETION_TIME FROM DBA_ILMTASKS WHERE task_id='181';
 
STATE     START_TIME                                                                  COMPLETION_TIME
--------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
COMPLETED 27-JAN-15 10.30.30.410230 AM                                                27-JAN-15 10.30.50.251152 AM
 
SQL> SELECT JOB_NAME,JOB_STATE FROM DBA_ILMRESULTS WHERE task_id='181';
 
JOB_NAME        JOB_STATE
--------------- -----------------------------------
ILMJOB1400      COMPLETED SUCCESSFULLY
ILMJOB1402      COMPLETED SUCCESSFULLY
ILMJOB1398      COMPLETED SUCCESSFULLY

After the execution of the three ADO policies we can see that chosen compression have been implemented, the row level compression of the hot partition is not displayed and does not provide any significant result (maybe linked to figures nature):

SQL> EXEC dbms_stats.gather_table_stats('yjaquier', 'sales');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT partition_name,compress_for,compression,blocks,avg_row_len FROM dba_tab_partitions
     WHERE table_owner='YJAQUIER'
     AND table_name='SALES'
     ORDER BY partition_position;
 
PARTITION_ COMPRESS_FOR                   COMPRESS     BLOCKS AVG_ROW_LEN
---------- ------------------------------ -------- ---------- -----------
COLD       ADVANCED                       ENABLED        6609          42
WARM       BASIC                          ENABLED        3024          43
HOT                                       DISABLED       1006          43

References

About Post Author

Leave a Reply

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

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>