News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Oracle 12c New Feature Heat Map and Automatic Data Optimization (ADO) – Part 2

  • Posted by Gavin Soorma
  • On September 12, 2013
  • 0 Comments
  • ADO, Automatic Data Optimization, Heat Map, ILM, new feature, Oracle 12c

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

 

0 Comments

Leave Reply

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