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.