ILM – is it possible to mix ADO policies for compression and storage?

Never thought I would write much about Information Lifecycle Management, as I am actually a developer and not a DBA. I think, it is indeed a topic mostly relevant for DBA’s. But it is generally a good thing, if developers and DBA’s have a deep understanding of each others job, isn’t it? We are giving an overview of the ILM features in our training “12c New Features for Developers” and I’m one of the course instructors for it. That’s the reason, why I’m writing meanwhile the third post about it. Just to clarify some questions, which are not so obviously documented.

After looking at ADO conditions for storage tiering policies and using user defined PL/SQL conditions in previous posts, I was curious whether it is possible to mix storage and compression policies for the same segment? Wouldn’t it for example make sense to move the segment to a low cost tablespace and compress the data within the same action as well? I’ve sometimes heard the opposite statement. But it is very simple to test it, not just trust it. Let’s try it.

So we start again with the same setup as in previous posts ( 1 and 2) on ILM and then define both policies on the table EMPLOYEE1: we would like to compress the data (basic) after ten days of no access while moving it to LOW_COST_STORE tablespace at the same time. To proof the compression let’s also show the initial segment size.

SYS> SELECT owner, segment_name, tablespace_name, bytes, blocks
  2    FROM dba_segments
  3   WHERE segment_name = 'EMPLOYEE1';

OWNER   SEGMENT_NAME  TABLESPACE_NAME   BYTES    BLOCKS
------- ------------- ----------------- -------- --------
SCOTT   EMPLOYEE1     USERS             327680   40

SCOTT> ALTER TABLE scott.employee1
  2  ILM ADD POLICY ROW STORE COMPRESS BASIC
  3  SEGMENT AFTER 10 DAYS OF NO ACCESS;

Table altered.

SCOTT> 
SCOTT> -- Storage tiering with condition only to move to read-only tablespace
SCOTT> ALTER TABLE scott.employee1
  2  ILM ADD POLICY TIER TO low_cost_store READ ONLY
  3  SEGMENT AFTER 10 DAYS OF NO ACCESS;

Table altered.

SCOTT> SELECT policy_name, action_type, scope, tier_tablespace, condition_type, condition_days
  2    FROM user_ilmdatamovementpolicies;

POLICY_NAME ACTION_TYPE SCOPE	TIER_TABLESPACE   CONDITION_TYPE    CONDITION_DAYS
----------- ----------- ------- ----------------- ----------------- --------------
P307	    COMPRESSION SEGMENT                   LAST ACCESS TIME  10
P308	    STORAGE     SEGMENT LOW_COST_STORE    LAST ACCESS TIME  10

SCOTT> 
SCOTT> SELECT policy_name, object_name, object_type, enabled
  2    FROM user_ilmobjects;

POLICY_NAME OBJECT_NAME           OBJECT_TYPE	    ENABLED
----------- -------------------- ------------------ -------
P307	    EMPLOYEE1             TABLE	            YES
P308	    EMPLOYEE1             TABLEE            YES

At least, there is no error message. Policies are there and enabled. Seems to work. But does it really work? Set the evaluation time in seconds, set the start time, flush Heat Map information and wait more than ten seconds:

SCOTT> ----------------------------------------------------------------------------------------------------
SCOTT> -- Triggering the Table to Move to Low Cost Storage
SCOTT> ----------------------------------------------------------------------------------------------------
SCOTT> -- 1. Switch ILM unit to seconds
SCOTT> EXECUTE dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.POLICY_TIME, DBMS_ILM_ADMIN.ILM_POLICY_IN_SECONDS);

PL/SQL procedure successfully completed.

SCOTT> -- 2. set start date
SCOTT> EXECUTE DBMS_ILM_ADMIN.SET_HEAT_MAP_START(sysdate -50);

PL/SQL procedure successfully completed.

SCOTT> EXECUTE dbms_ilm.flush_all_segments;

PL/SQL procedure successfully completed.

SCOTT> EXECUTE  dbms_lock.sleep(15);

PL/SQL procedure successfully completed.

Now we start the ILM execution manually and see what happens:

SCOTT> declare
  2  v_executionid number;
  3  begin
  4    dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA,
  5  		 execution_mode => dbms_ilm.ilm_execution_offline,
  6  		 task_id   => v_executionid);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SCOTT> ------------------------------------------------------------------------------
SCOTT> -- Table EMPLOYEE1 is now moved to tablespace LOW_COST_STORE and compressed
SCOTT> ------------------------------------------------------------------------------
SCOTT> select tablespace_name, segment_name, bytes, blocks
  2  from   user_segments
  3  where  segment_name='EMPLOYEE1';

TABLESPACE_NAME   SEGMENT_NAME	 BYTES	  BLOCKS
----------------- -------------- -------- ----------
LOW_COST_STORE    EMPLOYEE1      131072	  16

SCOTT> SELECT compression, compress_for
  2  FROM user_tables WHERE table_name = 'EMPLOYEE1';

COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED  BASIC

And voilá, the table is moved and compressed. Note how the size dropped by almost two-thirds. The execution information about the policies confirms that:

SCOTT> SELECT task_id, to_char(start_time, 'dd/mm/yyyy hh24:mi:ss') as start_time, u.STATE
  2  FROM user_ilmtasks u;

   TASK_ID START_TIME          STATE
---------- ------------------- ---------
       276 31/10/2017 17:14:38 COMPLETED

SCOTT> 
SCOTT> select task_id,	job_name, job_state, to_char(completion_time, 'dd/mm/yyyy hh24:mi:ss') completion,
  2  from user_ilmresults;

   TASK_ID JOB_NAME     JOB_STATE                COMPLETION
---------- ------------ ------------------------ ------------------------------
       276 ILMJOB4438   COMPLETED SUCCESSFULLY   31/10/2017 17:14:39

SCOTT> 
SCOTT> select task_id, policy_name, object_name, selected_for_execution from user_ilmevaluationdetails;

   TASK_ID POLICY_NAME OBJECT_NAME     SELECTED_FOR_EXECUTION
---------- ----------- --------------- ------------------------------------------
       276 P307        EMPLOYEE1       SELECTED FOR EXECUTION
       276 P308        EMPLOYEE1       SELECTED FOR EXECUTION

Obviously, it works. Of course the rules for multiple policies apply. A good overview about the rules and restrictions can be found here.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *