Oracle Automatic Data Compression using Information Lifecycle Management

We  had requirement from customer to  share strategy for automatic data compression   that directed us to ILM

 

With Oracle 12c, the feature Automatic Data Optimization (ADO) can help us with auto Compression  of data under predefined conditions.  ADO is part of Information Lifecycle Management (ILM).

 

ADO requires Advanced Compression Option.

Enable Heat Map   : 

To use ADO for compression, the Heat Map must be enabled. Indeed once enabled, Heat Map will collect statistics required for ADO actions. All accesses are tracked by the in-memory activity tracking module.

SQL> alter system set heat_map=ON scope=both;

Before adding the ADO policy, we can verify that heat map statistics are already collected.

SELECT OBJECT_NAME, to_char(TRACK_TIME,'DD-MON-YYYY HH24:MI:SS'), SEGMENT_WRITE "Seg_write",

SEGMENT_READ "Seg_read", FULL_SCAN, LOOKUP_SCAN

FROM v$heat_map_segment WHERE object_name='ARTICLE';

Adding a segment level row compression policy

Adding a segment level row compression policy on  table that will compress the segment when no modification on the segment will have occurred 

in the last 30 days  

SQL> ALTER TABLE app.article ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;

SQL> SELECT policy_name, action_type, scope, compression_level, condition_type, condition_days FROM user_ilmdatamovementpolicies ORDER BY policy_name;

 

SQL> SELECT policy_name, object_name, enabled FROM user_ilmobjects;

 

Changing  policy time : 

By default the policy time is specified in days. If we query the DBA_ILMPARAMETERS, the value for POLICY TIME determines 

if ADO policies are specified in seconds or days. Values are 1 for seconds or 0 for days (default).

SQL> col name for a20

SQL> select * from DBA_ILMPARAMETERS;

Changing policy time from days to seconds . 

SQL> EXEC dbms_ilm_admin.customize_ilm(dbms_ilm_admin.POLICY_TIME,dbms_ilm_admin.ILM_POLICY_IN_SECONDS);

Flush the heat map statistics from memory to disk and let’s wait 30 days 

SQL> EXEC dbms_ilm.flush_all_segments;

Manually execute the policy without waiting the maintenance window.

DECLARE

v_executionid number;

BEGIN

dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA,

execution_mode => dbms_ilm.ilm_execution_offline,

task_id => v_executionid);

END;

/

 

Information  about the job can be obtained in the following views

SQL> SELECT task_id, start_time as start_time FROM user_ilmtasks order by 1;

SQL> SELECT task_id, job_name, job_state, completion_time completion FROM user_ilmresults ORDER BY 1 ;

To check compressesion 

SELECT compression, compress_for FROM dba_tables WHERE table_name = 'ARTICLE1';

SELECT CASE compression_type

WHEN 1 THEN 'No Compression'

WHEN 2 THEN 'Advanced compression level'

WHEN 4 THEN 'Hybrid Columnar Compression for Query High'

WHEN 8 THEN 'Hybrid Columnar Compression for Query Low'

WHEN 16 THEN 'Hybrid Columnar Compression for Archive High'

WHEN 32 THEN 'Hybrid Columnar Compression for Archive Low'

WHEN 64 THEN 'Compressed row'

WHEN 128 THEN 'High compression level for LOB operations'

WHEN 256 THEN 'Medium compression level for LOB operations'

WHEN 512 THEN 'Low compression level for LOB operations'

WHEN 1000 THEN 'Minimum required number of LOBs in the object for which LOB compression ratio is to be estimated'

WHEN 4096 THEN 'Basic compression level'

WHEN 5000 THEN 'Maximum number of LOBs used to compute the LOB compression ratio'

WHEN 1000000 THEN 'Minimum required number of rows in the object for which HCC ratio is to be estimated'

WHEN -1 THEN 'To indicate the use of all the rows in the object to estimate HCC ratio'

WHEN 1 THEN 'Identifies the object whose compression ratio is estimated as of type table'

ELSE 'Unknown Compression Type'

END AS compression_type, n as num_rows

FROM (SELECT compression_type, Count(*) n

FROM (SELECT dbms_compression.Get_compression_type(USER, 'ARTICLE', ROWID) AS COMPRESSION_TYPE

FROM app.article)

GROUP BY compression_type

);

References :

https://www.oracle.com/a/ocom/docs/database/implementing-ilm-with-oracle-database.pdf