Setting Up Compression Tiering for Automatic Data Optimization
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.
- 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
- 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.
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:
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:
Prerequisites
Before starting this tutorial:
- Oracle Database 12c should be installed.
- You need a non-container database to start.
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.
Enable heat map tracking once all statistics have cleared up.
. oraenv
[enter orcl at the prompt]
sqlplus / as sysdba
EXEC
dbms_ilm_admin.clear_heat_map_all
ALTER SYSTEM SET heat_map=on SCOPE=both;
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
);
EXEC
dbms_ilm_admin.customize_ilm(dbms_ilm_admin.POLICY_TIME,dbms_ilm_admin.ILM_POLICY_IN_SECONDS)
Segment Level Compression ADO
Policies
Results
after the first evaluation of the
ADO policy
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))
/
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;
/
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMPLOYEE')
SCOTT.EMPLOYEE
table.
SELECT count(*) FROM scott.employee;
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';
Execute the comp.sql
script to verify that
no blocks are compressed yet
in the SCOTT.EMPLOYEE table.
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;
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;
COLUMN
object_name FORMAT A10
SELECT
policy_name, object_name,
enabled FROM
user_ilmobjects;
SELECT * FROM
user_ilmpolicies;
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)
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;
/
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;
SELECT
task_id, job_name,
job_state, completion_time
completion
FROM user_ilmresults
ORDER BY 1 ;
SELECT
task_id, policy_name,
object_name,
selected_for_execution,
job_name
FROM
user_ilmevaluationdetails
ORDER BY 1;
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.
Check if ADO triggered compression on the SCOTT.EMPLOYEE segment.
SELECT
compression, compress_for
FROM
user_tables
WHERE table_name =
'EMPLOYEE';
All rows (3584) have been compressed with the Advanced Row Compression feature. No rows are left uncompressed.
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;
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
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;
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)
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;
/
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;
SELECT
task_id, job_name,
job_state, completion_time
completion
FROM user_ilmresults
ORDER BY 1 ;
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;
This view contains details the reason why the policy has not been selected for execution.
Row Level Compression ADO
Policies
Results
after the first evaluation of the
ADO policy
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))
/
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;
/
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMPLOYEE')
SELECT count(*) FROM scott.employee;
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';
Execute the comp.sql
script to verify that
no blocks are compressed in
the SCOTT.EMPLOYEE table.
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;
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;
COLUMN
object_name FORMAT A10
SELECT
policy_name, object_name,
enabled FROM
user_ilmobjects;
SELECT * FROM
user_ilmpolicies;
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)
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;
/
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;
SELECT
task_id, job_name,
job_state, completion_time
completion
FROM user_ilmresults
ORDER BY 1 ;
SELECT
task_id, policy_name,
object_name,
selected_for_execution,
job_name
FROM
user_ilmevaluationdetails
ORDER BY 1;
Check if ADO triggered compression on SCOTT.EMPLOYEE rows in blocks.
SELECT
compression, compress_for
FROM
user_tables
WHERE table_name =
'EMPLOYEE';
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.
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;
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
Insert more rows into SCOTT.EMPLOYEE table.
INSERT INTO scott.employee SELECT * FROM scott.employee;
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)
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;
/
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;
SELECT
task_id, job_name,
job_state, completion_time
completion
FROM user_ilmresults
ORDER BY 1 ;
SELECT
task_id, policy_name,
object_name,
selected_for_execution,
job_name
FROM
user_ilmevaluationdetails
ORDER BY 1;
Check if ADO triggered compression on SCOTT.EMPLOYEE new rows in blocks.
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.
Cleanup the Environment
Delete all ADO Policies on the SCOTT.EMPLOYEE table.
ALTER TABLE
scott.employee ILM DELETE_ALL;
Verify that there is no ILM policies on the SCOTT.EMPLOYEE table.
SELECT * FROM user_ilmpolicies;
SELECT
*
FROM
user_ilmdatamovementpolicies;
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;
Summary
- 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
- To
learn more about Heat Map, compression
and Automatic Data Optimization, refer
to additional OBEs in the Oracle Learning
Library.
- Lead Curriculum Developer: Dominique Jeunot, Jean-Francois Verrier
- Other
Contributors: ADO PM Team: Andy
Rivenes
In this tutorial, you have learned how to:
Overview