An exciting feature of 12c is Automatic Data Optimization (ADO). It can automate Compression Tiering and Storage Tiering. The documentation mentions that Storage Tiering can be based on a customized function, but there is no example for it. Therefore this posting.

[oracle@uhesse ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Dec 9 10:33:17 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam
Connected.
SQL> create tablespace tbs1 datafile '/home/oracle/prima/tbs1.dbf' size 10m;

Tablespace created.

SQL> create tablespace tbs2 datafile '/home/oracle/prima/tbs2.dbf' size 10m;

Tablespace created.

SQL> create table t (n number) partition by range (n)
     (partition p values less than (2) tablespace tbs1);  

Table created.

Nothing special so far, just two tablespaces and a partitioned table. My customized ADO policy is very simple and uses a working table:

SQL> create table adoflag (n number);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into adoflag values (0);

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace function adofunc
(object_number in number)
return boolean
as
v_n number;
begin
select n into v_n from adoflag;
if (v_n=1) then return true; else return false; end if;
end;
/

Function created.

Now that function is attached to an ADO policy that is placed on the partition p – notice that Storage Tiering policies can be on the segment level only:

SQL> alter table t modify partition p ilm add policy tier to tbs2 on adofunc;

Table altered.

SQL> col policy_name for a10
SQL> select policy_name,enabled from dba_ilmpolicies;

POLICY_NAM ENABLE
---------- ------
P1         YES

The name of the policy is provided automatically. ADO Tiering requires the following initialization parameter setting, regardless that the customized policy is not based on access respectively modification frequency of the partition p:

SQL> alter system set heat_map=on;

System altered.

Attention: Setting this already means you need the Advanced Compression Option! And to me it seems funny that this parameter can be modified on the session level, but that’s how it is. I want to cause now the Storage Tiering, so I update the flag to 1, but that would lead to automatic action only when the next Auto Task maintenance window opens. Therefore I trigger the ADO Tiering manually.

SQL> update adoflag set n=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select tablespace_name from user_tab_partitions where table_name='T';

TABLESPACE_NAME
------------------------------
TBS1

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> select task_id,policy_name,selected_for_execution from dba_ilmevaluationdetails;

   TASK_ID POLICY_NAM SELECTED_FOR_EXECUTION
---------- ---------- ------------------------------------------
         7 P1         SELECTED FOR EXECUTION

SQL> select tablespace_name from user_tab_partitions where table_name='T';

TABLESPACE_NAME
------------------------------
TBS2

It is possible to add a Storage Tiering policy to a non-partitioned table, but that is probably not a good idea. My observation at least with the current 12.1.0.1 is that open transactions on the non-partitioned table will cause the Storage Tiering to fail silently. Usually, the Storage Tiering will be a unique action on the segment. For testing purposes, though, you may want to trigger it multiple times. But the policy gets silently disabled after the first tiering although dba_ilmpolicies shows it as enabled:

SQL> select policy_name,enabled from dba_ilmpolicies;

POLICY_NAM ENABLE
---------- ------
P1         YES

SQL> alter table t move partition p tablespace tbs1 online;

Table altered.

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> select tablespace_name from user_tab_partitions where table_name='T';

TABLESPACE_NAME
------------------------------
TBS1

SQL> select task_id,policy_name,selected_for_execution from dba_ilmevaluationdetails;

   TASK_ID POLICY_NAM SELECTED_FOR_EXECUTION
---------- ---------- ------------------------------------------
        44 P1         POLICY DISABLED
         7 P1         SELECTED FOR EXECUTION

SQL> alter table t modify partition p ilm enable_all;

Table altered.

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> select tablespace_name from user_tab_partitions where table_name='T';

TABLESPACE_NAME
------------------------------
TBS2

SQL> select task_id,policy_name,selected_for_execution from dba_ilmevaluationdetails;

   TASK_ID POLICY_NAM SELECTED_FOR_EXECUTION
---------- ---------- ------------------------------------------
        45 P1         SELECTED FOR EXECUTION
        44 P1         POLICY DISABLED
         7 P1         SELECTED FOR EXECUTION

I hope this helps to get an understanding of customized ADO policy functions and to stay clear of some of the pitfalls associated with that topic. As always: Don’t believe it, test it! 🙂