ILM in 12c - ADO (automatic data optimization)

In Oracle Database 12c, it is possible to define a policy, which specifies how/where data should be managed and stored as it ages, such policies are known as ILM ADO policies.

Automatic Data Optimization (ADO):

Note: ADO and HEAT MAP are not supported for multitenant container databases.

Oracle Database 12c introduces a new, policy driven way to manage long term data storage and retention needs. In an Oracle 12c (noncdb) database, a dba can define a policy that specifies

• How data is stored – by specifying the type of compression used to store it.
• Where it gets stored - by specifying a tablespace to move older data to.
• When it gets moved – 
• by requiring a period of low or no access to that data to elapse, before the move.
• by further qualifying the condition using a boolean condition (pl/sql function).

For example:

An rule can be defined on a table stating that

§        Rows which are rarely accessed should be compressed after a certain age (COMPRESSION TIERING).

§        Rows should be moved to bulk storage after it reaches a certain age (STORAGE TIERING).

§        Rows should be treated using a combination of the above (COMPRESSION + STORAGE TIERING)

This is made possible by a new feature called HEAT MAP.  When enabled, the database tracks the types of table data access.

The type of the information tracked are

• Date/times of access
• Nature of access - read or write
• Access path - full scan, or an index probe.

This metadata can be queried through various *_HEAT_MAP_SEGMENT and *_HEAT_MAP_HISTOGRAM views.

Using the accumulated heat map metadata, Oracle will evaluate and enforce the ILM rules defined - these rules are known as ILM ADO policies. The policies are automatically evaluated, and system jobs are auto-spawned to treat the data according to the policy's specification. This capability is called Automatic Data Optimization (ADO).

ADO policies can be defined at different levels of granularity

The table below gives an idea.

Level	Description	Syntax example
Tablespace	Applies to all tables in a specific tablespace.	ALTER TABLESPACE sales DEFAULT   ROW STORE COMPRESS BASIC   SEGMENT AFTER 6 MONTHS OF NO MODIFICATION;
Segment	Applies to a specific segment only.  (useful for partitioned tables)	ALTER TABLE sales_ado ILM ADD POLICY   COMPRESS FOR ARCHIVE HIGH SEGMENT   AFTER 6 MONTHS OF NO MODIFICATION;
Row	Applies to specific rows in a table.	ALTER TABLE sales MODIFY PARTITION sales_q1_2002   ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW   AFTER 30 DAYS OF NO MODIFICATION;

ADO policy evaluation and enforcement

While the ADO environment is self-managing and does its actions in maintenance windows, a couple of packages are useful to the administrator who wishes to do an ad hoc inspection or wants to tweak the ADO environment.

Procedure call	Useful for
dbms_ilm_admin.CUSTOMIZE_ILM	Customization of ILM policy evaluation to adjust and policies are evaluated.
dbms_ilm.PREVIEW_ILM	Evaluation of an ILM policy to see if its triggering conditions are met.
Dbms_ilm.EXECUTE_ILM	To execute an ADO task.

Example of ILM (storage tiering):

Reference: Oracle 12c obe

A storage tiering policy specifies that a table’s data will be migrated to a named tablespace, when the amount of free space in the containing tablespace goes down below a threshold (due to data growth).

We will create a test table and attach an ILM policy to it, pump it with data, and observe how ILM treats it in response to its growth.

About configuring the ILM environment for testing

The threshold at which an ILM action is launched in response to free space reduction can be customized, using the DBMS_ILM_ADMIN package (dbms_ilm_admin.customize_ilm).

We will configure the ILM environment such that ILM policy that we define will be triggered as soon as the amount of free space in the tablespace becomes less than or equal to 95% (meaning 5% or more of the tablespace’s available space is already used up).

SQL> begin

  2   dbms_ilm_admin.customize_ilm(parameter => dbms_ilm_admin.TBS_PERCENT_USED,value => 5);

  3   dbms_ilm_admin.customize_ilm(parameter => dbms_ilm_admin.TBS_PERCENT_FREE,value =>  95);

  4  end;

  5  /

PL/SQL procedure successfully completed

This sets up the ilm threshold to an suitably low value so that the ilm policies and actions trigger without having to pump a lot of data into the test table.

Further, we will use a test tablespace called users, which have only 5 MB of data and cannot auto extend.

Creating a test table and populating it with bulk data:

create table employees (
  emp_id number, name varchar(20),
  join_date date, mgr number, dept_id number)
 tablespace users;

Inserting about 20,000 records in it, to use up about all the space in the users tablespace.

SQL> insert into employees

  2   select rownum, 'emp' || ltrim(to_char(rownum,'0999999')) , sysdate, 123, 100 from dual

  3   connect by level<=20000

  4  ;

20000 rows inserted

SQL> commit;

Commit complete

Then it is necessary to attach a storage-tiering policy to the table. The policy will specify that the table’s rows must be moved to a different tablespace named LOW_COST_STORE (when the tablespace usage reaches the thresholds set up for ilm as mentioned above).

SQL> alter table employees

  2   ilm add policy

  3   tier to low_cost_store;

Table altered.

Now, it is time for the database to evaluate the policy, which we will do (manually in this case) with this script (as the scott user). Normally the database would evaluate the policies in the maintenance windows.

The script example below, invokes the dbms_ilm.preview_ilm procedure, which will evaluate all the ADO policies in the current schema, and creates a task to enforce it (but in the inactive state). The user may execute the created task immediately with a script (or else it would execute in a maintenance window).

SQL> set serveroutput on

SQL>

SQL> declare

  2   v_task_id number:=0;

  3  BEGIN

  4   dbms_ilm.preview_ilm(task_id => v_task_id,ilm_scope => dbms_ilm.SCOPE_SCHEMA);

  5   dbms_output.put_line('task id = ' || v_task_id);

  6   if v_task_id is null then

  7      dbms_output.put_line('task id is null');

  8   end if;

  9  END;

 10  /

task id =

task id is null

PL/SQL procedure successfully completed

However, the task id that it is supposed to return to the caller is NULL (I do not know why).

So I query the user_ilmtasks view to see the task that it created, and I retrieve its id from there (task id = 128).

SQL> select task_id, policy_name, object_owner, object_name, selected_for_execution

  from user_ilmevaluationdetails order by task_id desc  ;

  2 

TASK_ID POLICY_NAM OBJECT_OWNER    OBJECT_NAME     SELECTED_FOR_EXECUTION

------- ---------- --------------- --------------- ------------------------------

    128 P203       SCOTT           EMPLOYEES       SELECTED FOR EXECUTION

SQL>

Then query the user_ilmtasks to examine the task status (INACTIVE)

SQL> select task_id, state, to_char(creation_time,'dd-mon-yyyy hh24:mi:ss') creation_time

     from user_ilmtasks where task_id=128;

  2 

TASK_ID STATE     CREATION_TIME

------- --------- --------------------

    128 INACTIVE  27-nov-2014 13:17:52

Finally we can execute the ilm task using a script. The dbms_ilm.execute_ilm_task executes a previously evaluated ADO task. In the example below, I supply the previously retrieved task id to the dbms_ilm.execute_task procedure.

SQL> declare

 v_task_id number:=128;

begin

 dbms_ilm.execute_ilm_task(task_id => v_task_id,

  execution_mode => dbms_ilm.ILM_EXECUTION_ONLINE,

  execution_schedule => dbms_ilm.SCHEDULE_IMMEDIATE);

end;

  2    3    4    5    6    7    8  /

PL/SQL procedure successfully completed.

The results of the task execution are available in the user_ilmresults

SQL> select task_id, job_name, job_state,

to_char(start_time,'dd-mon-yyyy hh24:mi:ss') start_time,

to_char(completion_time,'dd-mon-yyyy hh24:mi:ss') completion_time

from user_ilmresults where task_id=128;  2    3    4 

TASK_ID JOB_NAME        JOB_STATE                 START_TIME           COMPLETION_TIME

------- --------------- ------------------------- -------------------- --------------------

    128 ILMJOB318       COMPLETED SUCCESSFULLY    27-nov-2014 13:19:29 27-nov-2014 13:19:29

After the task completed, we can see that the table employees (which was originally in the USERS tablespace) is now contained in the LOW_COST_STORE tablespace.

SQL>  select tablespace_name from user_tables where table_name='EMPLOYEES';

TABLESPACE_NAME

------------------------------

LOW_COST_STORE

This was an example of storage tiering where the database has moved the table data to a different class of storage (presumably low-cost, bulk storage) in response to a space pressure condition.

Example of ILM (compression tiering):

In case of compression tiering, specific rows of data (with different ages) are compressed in-place, when policy-specified conditions are satisfied, like a certain amount of time has passed since those rows were accessed or modified. Older rarely accessed rows can be compressed to a greater degree to save space, whereas newer rows may not be compressed so that they can be accessed without having to de-compress them.

An example of a compression tiering policy would typicially specify at the row level, for example

Alter table employees

 ilm add policy

  row store compress advanced  row

   after 10 days of no modification;

This is a row level ado policy, which means the policy will continue to apply to future rows so that they may be compressed when they become eligible. Further, as per the Oracle 12c obe page, for a table with a row level policy, compression can take place only if all the rows in a block satisfy the policy criteria.

On the other hand, a segment level policy applies to the entire segment, and will become automatically disabled after it executes at least once. Segment level compression tiering policies are meant for partitioned tables.

Demonstrating below a row level compression tiering policy

Re-creating the same employee table…

SQL>

SQL> drop table employees;

Table dropped

SQL> create table employees (

  2    emp_id number, name varchar(20),

  3    join_date date, mgr number, dept_id number)

  4  /

Table created

SQL>

SQL>  insert /*+ APPEND */into employees

  2       select rownum, 'emp' || ltrim(to_char(rownum,'0999999')) , sysdate, 123, 100 from dual

  3       connect by level<=20000;

20000 rows inserted

SQL>      commit;

Commit complete

SQL>

Attaching the row level policy to the table…

SQL>

SQL> Alter table employees

  2  Ilm add policy

  3  Row store compress advanced

  4  row after 10 days of no modification;

Table altered

Flushing the heat map metadata to disk :

SQL>

SQL> begin

  2   dbms_ilm.flush_all_segments;

  3   end;

  4  /

PL/SQL procedure successfully completed.

Evaluating the ILM policy…

SQL> set serveroutput on

SQL>

SQL> declare

  2       v_task_id number:=0;

  3      BEGIN

  4       dbms_ilm.preview_ilm(task_id => v_task_id,ilm_scope => dbms_ilm.SCOPE_SCHEMA);

  5       dbms_output.put_line('task id = ' || v_task_id);

  6       if v_task_id is null then

  7          dbms_output.put_line('task id is null');

  8       end if;

  9  END;

 10  /

task id =

task id is null

PL/SQL procedure successfully completed

Retrieving the task id…

SQL>

SQL> select task_id, policy_name, object_owner, object_name, selected_for_execution

  2    from user_ilmevaluationdetails order by task_id desc  ;

   TASK_ID POLICY_NAME                                                                      OBJECT_OWNER                                                                     OBJECT_NAME                                                                      SELECTED_FOR_EXECUTION

---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------

       164 P305                                                                             SCOTT                                                                            EMPLOYEES                                                                        SELECTED FOR EXECUTION

Executing the ilm task with id=164

SQL> declare

  2   v_task_id number:=164;

  3  begin

  4   dbms_ilm.execute_ilm_task(task_id => v_task_id,

  5    execution_mode => dbms_ilm.ILM_EXECUTION_ONLINE,

  6    execution_schedule => dbms_ilm.SCHEDULE_IMMEDIATE);

  7  end;

  8  /

PL/SQL procedure successfully completed

Gathering optimizer stats (to collect metadata into user_tables)

SQL> BEGIN

  2   DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMPLOYEES');

  3   END;

  4  /

PL/SQL procedure successfully completed

Analyzing the compression stats for the table’s rows… (as per Oracle docs, compression_type=4096 is basic compression, compression_type=1 is No compression ref DBMS_COMPRESSION).

I am not getting advanced compression; and do not know the reason at this time, but compression tiering is demonstrated here.

SQL> select compression_type, COUNT(*)

  2    from (select dbms_compression.get_compression_type(ownname => 'SCOTT',

  3                                                       tabname => 'EMPLOYEES',

  4                                                       row_id  => t.rowid) compression_type

  5            from scott.employees t)

  6   GROUP BY COMPRESSION_TYPE

  7  /

COMPRESSION_TYPE   COUNT(*)

---------------- ----------

               1         56

            4096      19944

About 56 rows are non-compressed, whereas about 19,944 rows are advanced-compressed.

If more rows were to be added, they’d be stored  uncompressed, until the ADO policy kicks in again.

Adding a few more rows…

insert              into employees
     select rownum, 'emp' || ltrim(to_char(rownum,'0999999')) , sysdate, 123, 100 from dual
     connect by level<=500;

…

Checking the compression statistics again…

SQL> select compression_type, COUNT(*)

  from (select dbms_compression.get_compression_type(ownname => 'SCOTT',

                                                     tabname => 'EMPLOYEES',

                                                     row_id  => t.rowid) compression_type

          from scott.employees t)

 GROUP BY COMPRESSION_TYPE  2    3    4    5    6  ;

COMPRESSION_TYPE   COUNT(*)

---------------- ----------

               1        556

            4096      19944

Again this table has few rows uncompressed whereas others are compressed.