In a previous post Oracle 12c New Feature Heat Map and ADO we had looked at the 12c new feature called Heat Map and ADO (Automatic Data Optimization) and how we were using compression level tiering policy to compress and relocate dormant or less active data to a low cost storage tier.
In addition to compression, we can also use the storage tiering policy where we can create ILM policies in 12c to move data at the segment level (table or partition) to different storage tiers – for example move old data from the table to a different tablespace which could be based on low cost storage which is considered adequate by the business to store the older and less active data.
In this example we have two tablespaces – one located on a high performance disk where we want to store all our current and volatile data and then we have another tablespace located on low cost storage to store all our older and archived data.
So in this case we create two tablespaces of 25 MB each and we call them HIGH_PERF_TBS and LOW_COST_TBS.
We then create a range partitioned table in the SCOTT schema called MYOBJECTS.
create table myobjects
(owner varchar2(30),
object_name varchar2(30),
object_type varchar2(25),
created date)
Partition by range (created)
(partition p_old values less than (to_date ('01-JUL-2013','DD-MON-YYYY'))
tablespace high_perf_tbs,
partition p_new values less than (maxvalue))
tablespace high_perf_tbs;
We then populate this table with some test data.
insert into myobjects (select owner,object_name,object_type,created from all_objects); 73765 rows created. SQL> commit; Commit complete.
We then check the free space in the tablespace and see than the HIGH_PERF_TBS is mainly used as the partitioned table has been initially created in that tablespace. The tablespace is about 68% full.
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
and fs.tablespace_name in ('HIGH_PERF_TBS','LOW_COST_TBS')
GROUP BY df.tablespace_name,df.bytes
Order by 4;
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- ---------- ----------
LOW_COST_TBS 25 24 96 4
HIGH_PERF_TBS 25 8 32 68
By default the ILM threshold to move objects is set to 85% full for a tablespace and it will continue moving objects until the tablespace is back to less than 75% full.
We can check these default thresholds via the query shown below.
col name format A20 col value format 9999 select * from dba_ilmparameters; NAME VALUE -------------------- ----- ENABLED 1 JOB LIMIT 10 EXECUTION MODE 3 EXECUTION INTERVAL 15 TBS PERCENT USED 85 TBS PERCENT FREE 25
Now create a storage tiering policy for the MYOBJECTS partitioned table.
Using the default threshold values, we want to create an ILM policy where if the tablespace gets more than 85% full, we want to move the partition with the older data P_OLD to a tablespace hosted on low cost storage LOW_COST_TBS while retaining the partition with current data in the existing tablespace.
ALTER TABLE myobjects MODIFY PARTITION p_old ILM ADD POLICY TIER TO low_cost_tbs;
Verify that the storage ILM policy has been added.
select cast(policy_name as varchar2(30)) policy_name,
action_type, scope, compression_level, cast(tier_tablespace as
varchar2(30)) tier_tbs, condition_type, condition_days
from user_ilmdatamovementpolicies
order by policy_name;
POLICY_NAME ACTION_TYPE SCOPE
------------------------------ ----------- -------
COMPRESSION_LEVEL TIER_TBS
------------------------------ ------------------------------
CONDITION_TYPE CONDITION_DAYS
---------------------- --------------
P25 STORAGE SEGMENT
LOW_COST_TBS
0
select * from user_ilmobjects where object_type='TABLE PARTITION'
POLICY_NAME
--------------------------------------------------------------------------------
OBJECT_OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE INHERITED_FROM ENA
------------------ -------------------- ---
P25
SCOTT
MYOBJECTS
POLICY_NAME
--------------------------------------------------------------------------------
OBJECT_OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE INHERITED_FROM ENA
------------------ -------------------- ---
P_OLD
TABLE PARTITION POLICY NOT INHERITED YES
We now insert some more data into the table which will fill the tablespace more than the default 85% threshold and trigger the ILM storage policy to execute which will move the P_OLD partition to the tablespace LOW_COST_TBS.
insert into myobjects (select owner,object_name,object_type,created from all_objects);
For the purposes of this tutorial, we cannot wait for the maintenance window to open that will trigger the automatic data optimization policies jobs.
Instead, you are going to use the following PL/SQL block and trigger it as the table owner.
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;
/
select task_id, job_name, job_state,
to_char(completion_time,’dd-MON-yyyy’)completion
from user_ilmresults;
TASK_ID
———-
JOB_NAME
——————————————————————————–
JOB_STATE COMPLETION
———————————– ———–
97
ILMJOB1204
COMPLETED SUCCESSFULLY 10-SEP-2013
select SELECTED_FOR_EXECUTION from user_ilmevaluationdetails
where task_id=97;
SELECTED_FOR_EXECUTION
——————————————
SELECTED FOR EXECUTION
Now check that the partition P_OLD has been relocated.
select partition_name,tablespace_name from user_tab_partitions where table_name='MYOBJECTS'; PARTITION_ TABLESPACE_NAME ---------- ------------------------------ P_NEW HIGH_PERF_TBS P_OLD LOW_COST_TBS
If we now check the free space in the two tablespaces, we can see that the used space in the original tablespace HIGH_PERF_TBS has come down and the used space in the tablespace LOW_COST_TBS has increased after the partition got relocated.
The task status also shows that the job has been completed successfully.
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
and fs.tablespace_name in ('HIGH_PERF_TBS','LOW_COST_TBS')
GROUP BY df.tablespace_name,df.bytes
Order by 4;
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- ---------- ----------
LOW_COST_TBS 25 8 32 68
HIGH_PERF_TBS 25 16 64 36
select task_id, job_name, job_state,
to_char(completion_time,'dd-MON-yyyy') completion
from user_ilmresults where task_id=97;
TASK_ID
----------
JOB_NAME
--------------------------------------------------------------------------------
JOB_STATE COMPLETION
----------------------------------- -----------
97
ILMJOB1204
COMPLETED SUCCESSFULLY 10-SEP-2013
Last Update: September 12, 2013
