Automatic Data Optimization Part II : StorageTiering

By Mouhamadou Diaw

In first a previous blog, we saw how Automatic Data Optimization can be used to compress data under predefined conditions. In this blog we will see that another possible action with ADO is to move data to another storage. Indeed we will demonstrate how it is possible to move a table to another tablespace based on predefined conditions.
We are using oracle a 12.1.0.2 non-CDB database.
Let’s considerer following table ARTICLE in the tablespace USERS

SQL> col TABLE_NAME for a20
SQL> select table_name,tablespace_name from user_tables where table_name='ARTICLE';
TABLE_NAME TABLESPACE_NAME
-------------------- ------------------------------
ARTICLE USERS

Now let’s create a target tablespace named COOLTAB. The table ARTICLE will be moved to this new COOLTAB tablespace depending on conditions we will define.

SQL> create tablespace COOLTAB datafile '/u01/app/oracle/oradata/NONCONT/cooltab01.dbf' size 50M;
Tablespace created.

As specified in the previous blog, the Heat Map must be enabled by setting the parameter HEAT_MAP to ON.

SQL> show parameter heat_map;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
heat_map string ON

We can verify that heat map statistics are collected for table ARTICLE. It can be useful to gather statistics for ARTICLE using DBMS_STATS.

SQL> SELECT OBJECT_NAME, to_char(TRACK_TIME,'DD-MON-YYYY HH24:MI:SS'), SEGMENT_WRITE "Seg_write",SEGMENT_READ "Seg_read", FULL_SCAN, LOOKUP_SCAN FROM v$heat_map_segment WHERE object_name='ARTICLE';
OBJECT_NAME TO_CHAR(TRACK_TIME,'DD-MON-YY Seg Seg FUL LOO
------------------------------ ----------------------------- --- --- --- ---
ARTICLE 10-JAN-2018 09:40:48 NO NO YES NO

Before creating the storage tiering policy, let’s verify the status of our tablespaces. We can see that all the tablespaces are online

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
COOLTAB ONLINE
7 rows selected.

Now we are going to define a tiering policy which will move table ARTICLE from tablespace USERS to COOLTAB if there is no access to ARTICLE in the last 30 days. The policy will also put the tablespace COOLTAB in READ ONLY status.

SQL> ALTER TABLE ARTICLE ILM ADD POLICY TIER TO COOLTAB READ ONLY SEGMENT AFTER 30 DAYS OF NO ACCESS;
Table altered.

If we query the dba_ilmparameters we can see that for the demonstration the policy_time is set to 1. This means that 30 days are considered as 30 seconds due to the policy time

SQL> select * from dba_ilmparameters;
NAME VALUE
-------------------- ----------
ENABLED 1
RETENTION TIME 30
JOB LIMIT 2
EXECUTION MODE 2
EXECUTION INTERVAL 15
TBS PERCENT USED 85
TBS PERCENT FREE 25
POLICY TIME 1
8 rows selected.

We can verify that the policy is created

SELECT policy_name, action_type, condition_type, condition_days FROM user_ilmdatamovementpolicies where action_type='STORAGE';
POLICY_NAM ACTION_TYPE CONDITION_TYPE CONDITION_DAYS
---------- ----------- ---------------------- --------------
P65 STORAGE LAST ACCESS TIME 30

And that the policy is enabled

SQL> SELECT policy_name, object_name, enabled FROM user_ilmobjects where policy_name='P65';
POLICY_NAM OBJECT_NAME ENA
---------- --------------- ---
P65 ARTICLE YES

By default ADO policies are automatically triggered during maintenance window. But in this demonstration we are going to manually execute the policy without waiting the maintenance window. So after 30 days of non-access (in fact 30 seconds) let’s trigger the policy

SQL> EXEC dbms_ilm.flush_all_segments;
PL/SQL procedure successfully completed.
.
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.

Let’s verify the result of the job. We can see that the job was successful

SQL> SELECT task_id, job_name, job_state, completion_time completion FROM user_ilmresults ORDER BY 1 ;
TASK_ID
----------
JOB_NAME
--------------------------------------------------------------------------------
JOB_STATE
-----------------------------------
COMPLETION
---------------------------------------------------------------------------
86
ILMJOB232
COMPLETED SUCCESSFULLY
10-JAN-18 02.28.31.506590 PM

And if we query again the USER_TABLES, we can see that the table ARTICLE were moved from tablespace USERS to tablespace COOLTAB.

SQL> col TABLE_NAME for a20
SQL> select table_name,tablespace_name from user_tables where table_name='ARTICLE';
TABLE_NAME TABLESPACE_NAME
-------------------- ------------------------------
ARTICLE COOLTAB

And if we query the dba_tablespaces, we can see that now the tablespace COOLTAB is now in READ ONLY STATUS

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
COOLTAB READ ONLY
7 rows selected.

Conclusion: We have seen how ADO can help us to move data. Note that this movement can also be based on the fullness threshold of the source tablespace. One condition should be for example to move the table if the source tablespace is full at 65%.