Table of contents
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:
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
- Implementing ILM with Oracle Database
- Information Lifecycle Management (ILM), Heat Map, Automatic Data Optimization (ADO) (Doc ID 1612385.1)
- Exploring Oracle 12c’s Automatic Database Optimization (ADO) Features
- Information Life Cycle Management with Oracle Database 12c
- Oracle 12c New Feature – Heat Map and Automatic Data Optimization
- Automatic Data Optimization with Oracle Database 12c (PDF)
- Advanced Compression with Oracle Database 12c (PDF)
