By Mouhamadou Diaw

Nowadays data are increasing more and more. And some challenges we can face can be how to reduce storage costs and how to improve performance. With Oracle 12c, the feature Automatic Data Optimization (ADO) can help us.
In this first blog we will see how we can use ADO to compress data under predefined conditions.
ADO is part of Information Lifecycle Management (ILM). Note that ADO requires Advanced Compression Option.
In this article we are using oracle a 12.1.0.2 non-CDB database.
First let’s create the user we will use for the demonstration

SQL> create user app identified by app default tablespace users temporary tablespace temp;
User created.
.
SQL> grant create session,create table,alter tablespace,select any dictionary,unlimited tablespace to app;
Grant succeeded.

Now with this user let’s create a table and let’s insert some data
SQL> show user
USER is "APP"
SQL> create table article(idart number,designation varchar2(20));
Table created.
.
SQL> select count(*) from article;
COUNT(*)
----------
1048576

What we will demonstrate is how data in table ARTICLE can be automatically compressed if they are not modified within the last 30 days for example. It’s just an example and there are many other conditions.
First let’s verify that the compression attribute of the table article is disabled
SQL> show user
USER is "APP"
SQL> SELECT compression, compress_for FROM user_tables WHERE table_name = 'ARTICLE';
COMPRESS COMPRESS_FOR
-------- ------------------------------
DISABLED

We can also confirm that there is no compression in the table using this oracle script.
[oracle@serverora1 ]$ cat comp_art.sql
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
);
[oracle@serverora1 ]$

Below we can see that there is no compressed data
SQL> col COMPRESSION_TYPE for a20
SQL> @comp_art
COMPRESSION_TYPE NUM_ROWS
-------------------- ----------
No Compression 1048576

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. So let’s enable the Heat Map at instance level

SQL> show parameter heat
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
heat_map string OFF
.
SQL> alter system set heat_map=ON scope=both;
System altered.
.
SQL> show parameter heat
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
heat_map string ON

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;
NAME VALUE
-------------------- ----------
ENABLED 1
RETENTION TIME 30
JOB LIMIT 2
EXECUTION MODE 2
EXECUTION INTERVAL 15
TBS PERCENT USED 85
TBS PERCENT FREE 25
POLICY TIME 0
8 rows selected.

But in our demonstration we will not wait 30 days, so we will set the policy time in seconds instead of days and then if we specify 30 days this will mean 30 seconds.

SQL> EXEC dbms_ilm_admin.customize_ilm(dbms_ilm_admin.POLICY_TIME,dbms_ilm_admin.ILM_POLICY_IN_SECONDS);
PL/SQL procedure successfully completed.
.
SQL> select * from DBA_ILMPARAMETERS;
NAME VALUE
-------------------- ----------
ENABLED 1
RETENTION TIME 30
JOB LIMIT 2
EXECUTION MODE 2
EXECUTION INTERVAL 15
TBS PERCENT USED 85
TBS PERCENT FREE 25
POLICY TIME 1
8 rows selected.
SQL>

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';
.
OBJECT_NAME TO_CHAR(TRACK_TIME,'DD-MON-YY Seg Seg FUL LOO
------------------------------ ----------------------------- --- --- --- ---
ARTICLE 08-JAN-2018 17:02:21 NO NO YES NO
SQL>

Now let’s add a segment level row compression policy on ARTICLE table that will compress the segment when no modification on the segment will have occurred in the last 30 days (considered as 30 seconds due to the policy time).

SQL> show user
USER is "APP"
SQL> ALTER TABLE app.article ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;
Table altered.
SQL>

We can use the user_ilmdatamovementpolicies and user_ilmobjects with user app to verify the policy.

SQL> show user
USER is "APP"
SQL> SELECT policy_name, action_type, scope, compression_level, condition_type, condition_days FROM user_ilmdatamovementpolicies ORDER BY policy_name;
POLICY_NAME ACTION_TYPE SCOPE COMPRESSION_LEVEL CONDITION_TYPE CONDITION_DAYS
--------------- ----------- ------- ----------------- ---------------------- --------------
P45 COMPRESSION SEGMENT ADVANCED LAST MODIFICATION TIME 30
.
SQL> SELECT policy_name, object_name, enabled FROM user_ilmobjects;
POLICY_NAME OBJECT_NAME ENA
--------------- -------------------- ---
P45 ARTICLE YES

Flush the heat map statistics from memory to disk and let’s wait 30 days ( in fact 30 seconds because don’t forget the policy_time was changed to seconds instead of days). Can be also sometimes useful to gather the statistics of the table

SQL> EXEC dbms_ilm.flush_all_segments;
PL/SQL procedure successfully completed.
SQL>

By default ADO policies are automatically triggered during maintenance window. But in this demonstration we are going to manually execute the policy without waiting the maintenance window.

SQL> show user
USER is "APP"
SQL> 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;
/
PL/SQL procedure successfully completed.
SQL>

Some info 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;
TASK_ID
----------
START_TIME
---------------------------------------------------------------------------
26
08-JAN-18 05.28.45.788076 PM
SQL>
.
SQL> SELECT task_id, job_name, job_state, completion_time completion
FROM user_ilmresults ORDER BY 1 ;
TASK_ID
----------
JOB_NAME
--------------------------------------------------------------------------------
JOB_STATE
-----------------------------------
COMPLETION
---------------------------------------------------------------------------
26
ILMJOB98
COMPLETED SUCCESSFULLY
08-JAN-18 05.28.49.368009 PM

We can see that job run successfully. And we can verify that compression in table ARTICLE is now enabled

SQL> SELECT compression, compress_for FROM user_tables WHERE table_name = 'ARTICLE';
COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED ADVANCED
SQL>

And we also can see that all rows are compressed.

SQL> col COMPRESSION_TYPE for a20
SQL> @comp_art.sql
COMPRESSION_TYPE NUM_ROWS
-------------------- ----------
Advanced compression 1048576
level

Conclusion : In this first part we have seen that how ADO can help us for compression. In a second post we will talk about data movement with ADO.