4.1 Setting Up Compression Tiering for Automatic Data Optimization

«« Previous
Next »»

1. Overview


Purpose

This tutorial covers how to set up Automatic Data Optimization (ADO) policies so that less frequently modified tables are automatically compressed at segment or block level.

Time to Complete

Approximately 30 minutes

Introduction

You can use automatic data optimization (ADO) to trigger data compression at segment or block level after data access or modification. 
In Oracle Database 12.1, row-level tracking is allowed for modifications only. Statistics are collected at the row level but aggregated to the block level.
The functionality includes the ability to create ADO policies specifying different compression levels and conditions triggering the compression. ADO requires that heat map tracking be enabled for the database instance. Also, ADO can only be implemented in a non-container database.

This tutorial will show how to:
  • Enable heat map tracking statistics
  • Understand the difference between segment and row levels row store compression ADO policies
  • Create row store compression ADO policies at segment and row levels
  • View ADO policy definitions
  • Trigger the compression policy and verify the automatic optimization of data compression
  • Delete ADO policies
  • Disable heat map tracking statistics
Scenario

In this tutorial, you will create and enable an ADO row store compression policy on the SCOTT.EMPLOYEE table at the two different levels:
  • The segment level: The entire segment properties get changed. In this case, you observe that the segment level policy is executed only once. The segment level compression policies are most useful for partitioned tables where partitions have become unused or infrequently used.
  • The row level: Blocks where all the rows in the blocks meet the policy are compressed. The row level policy continues to be executed after the first execution.

Prerequisites

Before starting this tutorial: 
  • Oracle Database 12c should be installed.
  • You need a non-container database to start.

2. Enable Heat Map Tracking


Enable heat map tracking. Then create and save a SQL script that will be used later to check the compression results on SCOTT.EMPLOYEE table upon ADO compression policy. Also set the policy time so that ADO policies are treated as though they were specified in seconds rather than in days to avoid waiting for days before evaluation can take place.

1. Enable heat map tracking once all statistics have cleared up.

. oraenv
[enter orcl at the prompt]
sqlplus / as sysdba

Setting Up Compression Tiering for Automatic Data Optimization

EXEC dbms_ilm_admin.clear_heat_map_all
ALTER SYSTEM SET heat_map=on SCOPE=both;

Setting Up Compression Tiering for Automatic Data Optimization

2. Create the comp.sql script. The SELECT statement will fail because the SCOTT.EMPLOYEE table does not exist yet, but you can nevertheless save the statement in the script to use it later.

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, 'EMPLOYEE', ROWID) AS COMPRESSION_TYPE
              FROM scott.employee)
      GROUP  BY compression_type
      );

Setting Up Compression Tiering for Automatic Data Optimization

3. Set the policy time to seconds instead of days.

EXEC dbms_ilm_admin.customize_ilm(dbms_ilm_admin.POLICY_TIME,dbms_ilm_admin.ILM_POLICY_IN_SECONDS)

Setting Up Compression Tiering for Automatic Data Optimization

3. Segment Level Compression ADO  Policies


  • Results after the first evaluation of the ADO policy
1. Create the SCOTT.EMPLOYEE table. 

CREATE TABLE scott.employee
       (EMPNO      NUMBER(4) NOT NULL,
        ENAME      VARCHAR2(10),
        JOB        VARCHAR2(9),
        MGR        NUMBER(4),
        HIREDATE   DATE,
        SAL        NUMBER(7,2),
        COMM       NUMBER(7,2),
        DEPTNO     NUMBER(2))
/

Setting Up Compression Tiering for Automatic Data Optimization

2. Insert around 3500 rows into SCOTT.employee table.

INSERT INTO scott.employee (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno 
  FROM scott.emp;

DECLARE
  blowup PLS_INTEGER := 8; 
  sql_test clob; 
BEGIN
  for i in 1..blowup 
  loop sql_test := 'insert /*+ append */ into employee select * from scott.employee'; 
  execute immediate sql_test; 
  commit; 
end loop; 
END; 
/

Setting Up Compression Tiering for Automatic Data Optimization

3. Collect the object statistics required by ADO evaluations.
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMPLOYEE')

Setting Up Compression Tiering for Automatic Data Optimization

4. Count the number of rows into SCOTT.EMPLOYEE table.
SELECT count(*) FROM scott.employee;

Setting Up Compression Tiering for Automatic Data Optimization

5. Check if the COMPRESSION attribute of the SCOTT.EMPLOYEE table is disabled before ADO enables it.
SELECT compression, compress_for 
FROM user_tables 
WHERE table_name = 'EMPLOYEE';

Setting Up Compression Tiering for Automatic Data Optimization

6. Execute the comp.sql script to verify that no blocks are compressed yet in the SCOTT.EMPLOYEE table.

Setting Up Compression Tiering for Automatic Data Optimization

7. Add a segment level row compression policy on SCOTT.EMPLOYEE table that will compress the segment when no modification on the segment will have occured in the last 4 days (considered as 4 seconds due to the policy time).

ALTER TABLE scott.employee
  ILM ADD POLICY
  ROW STORE COMPRESS ADVANCED
  SEGMENT
  AFTER 4 DAYS OF NO MODIFICATION;

Setting Up Compression Tiering for Automatic Data Optimization

8. Verify that the policy is added.
SET NUMWIDTH 10
COLUMN policy_name FORMAT A8
COLUMN COMPRESSION_LEVEL FORMAT A17
SELECT policy_name, action_type, scope, compression_level, condition_type, condition_days
FROM   user_ilmdatamovementpolicies
ORDER BY policy_name;

Setting Up Compression Tiering for Automatic Data Optimization

COLUMN object_name FORMAT A10
SELECT policy_name, object_name, enabled FROM user_ilmobjects;
SELECT * FROM user_ilmpolicies;

Setting Up Compression Tiering for Automatic Data Optimization

9. For the purpose of this tutorial, we cannot wait for 4 days to pass by. In a previous step, we made the ADO evaluation interval short enough to be practical for the tutorial by changing the POLICY TIME to 1 via the DBMS_ILM_ADMIN.CUSTOMIZE_ILM procedure. It changed the evaluation of days to seconds.
Flush the heat map statistics from memory to disk and make a pause of 4 seconds so that we are sure that the segment was not modified in the last 4 days (4 seconds in reality).

CONN / as sysdba

EXEC dbms_ilm.flush_all_segments
EXEC dbms_lock.sleep(4)

Setting Up Compression Tiering for Automatic Data Optimization

10 . For the purpose of this tutorial, we cannot wait for the maintenance window to open and trigger the ADO policies jobs. Instead, you are going to use the following PL/SQL block and trigger it as the table owner.

CONN scott

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;
/

Setting Up Compression Tiering for Automatic Data Optimization

11. View the result of the job that completed the compression operation.

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

Setting Up Compression Tiering for Automatic Data Optimization

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

Setting Up Compression Tiering for Automatic Data Optimization

SELECT task_id, policy_name, object_name, selected_for_execution, job_name
FROM user_ilmevaluationdetails 
ORDER BY 1;

Setting Up Compression Tiering for Automatic Data Optimization

This view contains details about the task execution after the ADO evaluation. The SELECTED_FOR_EXECUTION column informs whether the policy has been selected for execution on the object on which the ADO policy exists. A SELECTED FOR EXECUTION value triggers an ADO job whereas a PRECONDITION NOT SATISFIED value does not. This column can take one of the following values: POLICY DISABLED, POLICY OVERRULED, INHERITED POLICY OVERRULED, JOB ALREADY EXISTS, NO OPERATION SINCE LAST ILM ACTION, TARGET COMPRESSION NOT HIGHER THAN CURRENT, STATISTICS NOT AVAILABLE.

12. Check if ADO triggered compression on the SCOTT.EMPLOYEE segment.

SELECT compression, compress_for 
FROM   user_tables 
WHERE  table_name = 'EMPLOYEE';

Setting Up Compression Tiering for Automatic Data Optimization

All rows (3584) have been compressed with the Advanced Row Compression feature. No rows are left uncompressed.

13. Display the status of the ADO policy on the SCOTT.EMPLOYEE table after the first ADO evaluation.

SELECT policy_name, object_name, enabled FROM user_ilmobjects;

Setting Up Compression Tiering for Automatic Data Optimization

Because the compression properties of the entire segment got changed to ENABLED ADVANCED, the ADO compression policy is no more useful and therefore disabled.
  • Results after the second evaluation of the ADO policy
1. Insert more rows into SCOTT.EMPLOYEE table.

INSERT INTO scott.employee (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno 
  FROM scott.emp;

Setting Up Compression Tiering for Automatic Data Optimization

2. Flush the heat map statistics from memory to disk and make a pause of 4 seconds so that we are sure that the segment was not modified in the last 4 days (4 seconds in reality).

CONN / as sysdba

EXEC dbms_ilm.flush_all_segments
EXEC dbms_lock.sleep(4)

Setting Up Compression Tiering for Automatic Data Optimization

3. For the purpose of this tutorial, we cannot wait for the maintenance window to open and trigger the ADO policies jobs. Instead, you are going to use the following PL/SQL block and trigger it as the table owner.

CONN scott

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;
/

Setting Up Compression Tiering for Automatic Data Optimization

4. View the results of the  job that completed the ADO policy evaluation.

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

Setting Up Compression Tiering for Automatic Data Optimization

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

Setting Up Compression Tiering for Automatic Data Optimization

Notice that the job did not execute  because the policy is disabled.
SELECT task_id, policy_name, object_name, selected_for_execution, job_name
FROM user_ilmevaluationdetails 
ORDER BY 1;

Setting Up Compression Tiering for Automatic Data Optimization

This view contains details the reason why the policy has not been selected for execution.

4. Row Level Compression ADO Policies


  • Results after the first evaluation of the ADO policy
1. Re-create the SCOTT.EMPLOYEE table. 
DROP TABLE scott.employee;
CREATE TABLE scott.employee
       (EMPNO      NUMBER(4) NOT NULL,
        ENAME      VARCHAR2(10),
        JOB        VARCHAR2(9),
        MGR        NUMBER(4),
        HIREDATE   DATE,
        SAL        NUMBER(7,2),
        COMM       NUMBER(7,2),
        DEPTNO     NUMBER(2))
/

Setting Up Compression Tiering for Automatic Data Optimization

2. Insert about 3500 rows into SCOTT.employee table.

INSERT INTO scott.employee (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno 
  FROM scott.emp;

DECLARE
  blowup PLS_INTEGER := 8; 
  sql_test clob; 
BEGIN
  for i in 1..blowup 
  loop sql_test := 'insert /*+ append */ into employee select * from scott.employee'; 
  execute immediate sql_test; 
  commit; 
end loop; 
END; 
/

Setting Up Compression Tiering for Automatic Data Optimization

3. Collect the object statistics required by ADO evaluations.
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMPLOYEE')

Setting Up Compression Tiering for Automatic Data Optimization

4. Count the number of rows into SCOTT.EMPLOYEE table.
SELECT count(*) FROM scott.employee;

Setting Up Compression Tiering for Automatic Data Optimization

5. Check if the COMPRESSION attribute of the SCOTT.EMPLOYEE table is disabled before ADO enables it.
SELECT compression, compress_for 
FROM user_tables 
WHERE table_name = 'EMPLOYEE';

Setting Up Compression Tiering for Automatic Data Optimization

6. Execute the comp.sql script to verify that no blocks are compressed in the SCOTT.EMPLOYEE table.

Setting Up Compression Tiering for Automatic Data Optimization

7. Add a row level row compression policy on SCOTT.EMPLOYEE table that will compress the rows in blocks when no modification on the rows in blocks will have occured in the last 4 days (considered as 4 seconds due to the policy time).

ALTER TABLE scott.employee
  ILM ADD POLICY
  ROW STORE COMPRESS ADVANCED
  ROW
  AFTER 4 DAYS OF NO MODIFICATION;

Setting Up Compression Tiering for Automatic Data Optimization

8. Verify that the policy is added.
SET NUMWIDTH 10
COLUMN policy_name FORMAT A8
COLUMN COMPRESSION_LEVEL FORMAT A17
SELECT policy_name, action_type, scope, compression_level, condition_type, condition_days
FROM   user_ilmdatamovementpolicies
ORDER BY policy_name;

Setting Up Compression Tiering for Automatic Data Optimization

COLUMN object_name FORMAT A10
SELECT policy_name, object_name, enabled FROM user_ilmobjects;
SELECT * FROM user_ilmpolicies;

Setting Up Compression Tiering for Automatic Data Optimization

9. Flush the heat map statistics from memory to disk and make a pause of 4 seconds so that we are sure that the segment was not modified in the last 4 days (4 seconds in reality).

CONN / as sysdba

EXEC dbms_ilm.flush_all_segments
EXEC dbms_lock.sleep(4)

Setting Up Compression Tiering for Automatic Data Optimization

10. For the purpose of this tutorial, we cannot wait for MMON background process to trigger the ADO policies jobs. Instead, you are going to use the following PL/SQL block and trigger it as the table owner.

CONN scott

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;
/

Setting Up Compression Tiering for Automatic Data Optimization

11. View the results of the job that completed the compression operation.

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

Setting Up Compression Tiering for Automatic Data Optimization

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

Setting Up Compression Tiering for Automatic Data Optimization

SELECT task_id, policy_name, object_name, selected_for_execution, job_name
FROM user_ilmevaluationdetails 
ORDER BY 1;

Setting Up Compression Tiering for Automatic Data Optimization

12. Check if ADO triggered compression on SCOTT.EMPLOYEE rows in blocks.

SELECT compression, compress_for 
FROM   user_tables 
WHERE  table_name = 'EMPLOYEE';

Setting Up Compression Tiering for Automatic Data Optimization

The segment compression attributes have not been modified, but  some rows (3060) have been compressed and others (524) not. Only blocks where all the rows meet the policy criteria are compressed.

13. Display the status of the ADO policy on the SCOTT.EMPLOYEE table after the first ADO evaluation.

SELECT policy_name, object_name, enabled FROM user_ilmobjects;

Setting Up Compression Tiering for Automatic Data Optimization

Because the ADO compression policy is set at the row level, the ADO compression policy is still useful to verify whether further updated or new inserted rows in blocks need to be compressed.

  • Results after the second evaluation of the ADO policy
1. Insert more rows into SCOTT.EMPLOYEE table.

INSERT INTO scott.employee SELECT * FROM scott.employee;
                                       
Setting Up Compression Tiering for Automatic Data Optimization

2. Flush the heat map statistics from memory to disk and make a pause of 4 seconds so that we are sure that the segment was not modified in the last 4 days (4 seconds in reality).

CONN / as sysdba

EXEC dbms_ilm.flush_all_segments
EXEC dbms_lock.sleep(4)

Setting Up Compression Tiering for Automatic Data Optimization

3. Rather than waiting for the evaluation to be scheduled by MMON (default interval of 15 minute) , you are going to use the following PL/SQL block and trigger it as the table owner.

CONN scott

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;
/

Setting Up Compression Tiering for Automatic Data Optimization

4. View the results of the  job that completed the ADO policy evaluation.

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

Setting Up Compression Tiering for Automatic Data Optimization

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

Setting Up Compression Tiering for Automatic Data Optimization

SELECT task_id, policy_name, object_name, selected_for_execution, job_name
FROM user_ilmevaluationdetails 
ORDER BY 1;

Setting Up Compression Tiering for Automatic Data Optimization

5. Check if ADO triggered compression on SCOTT.EMPLOYEE new rows in blocks.

Setting Up Compression Tiering for Automatic Data Optimization

Some of the new rows (106809 - 3060 = 103749) have been compressed and others are left uncompressed.
Notice that Basic compression level is reported rather than Advanced compression level. This is bug  number 17947871.

5. Cleanup the Environment



1. Delete all ADO Policies on the SCOTT.EMPLOYEE table.

ALTER TABLE scott.employee ILM DELETE_ALL;

Setting Up Compression Tiering for Automatic Data Optimization

2. Verify that there is no ILM policies on the SCOTT.EMPLOYEE table.

SELECT * FROM user_ilmpolicies;

SELECT * FROM user_ilmdatamovementpolicies;

Setting Up Compression Tiering for Automatic Data Optimization

3. Disable heat map tracking and reset policy time in days.

CONNECT / as sysdba

ALTER SYSTEM SET heat_map=off SCOPE=both;

SHOW parameter heat_map

EXEC dbms_ilm_admin.customize_ilm(dbms_ilm_admin.POLICY_TIME,dbms_ilm_admin.ILM_POLICY_IN_DAYS)

SELECT * FROM dba_ilmparameters;

Setting Up Compression Tiering for Automatic Data Optimization

«« Previous
Next »»