4.2 Setting Up Storage Tiering for Automatic Data Optimization

«« Previous
Next »»

1. Overview


Purpose

This tutorial covers how to setup information lifecycle management policies so that less frequently accessed tables are automatically moved to lower-cost storage.

Time to Complete

Approximately 20 minutes

Introduction

You can use automatic data optimization to automate the compression and movement of data between different tiers of storage within the database. The functionality includes the ability to create ILM policies that specify different compression levels for each tier, and to control when the data movement takes place. Automatic data optimization requires that heat map tracking be enabled for the database instance. Also, automatic data optimization can only be implemented in a non-container database.

Scenario

In this tutorial, you will create and enable an information lifecycle management tiering policy on the SCOTT.employee table. The policy uses automatic data optimization to move a table to another tablespace when the tablespace where the table resides on is less than 95% free.

Prerequisites

Before starting this tutorial, you should: 
  • Ensure that you have enough disk space to create additional tablespaces.
  • Oracle Database 12c should be installed.
The environment used in the development of this tutorial is as follows:
  • ORACLE_HOME: /u01/app/oracle/product/12.1.0
  • TNS Listener port: 1521
  • Container databases:
    • SID: cdb1
    • SID: cdb2
  • Pluggable databases (in cdb1):
    • pdb1
    • pdb2
  • Non-CDB SID: noncdb

2. Creating Tablespaces, Enabling Accounts, and Enabling Heat Map Tracking


1. Connect to the noncdb database as sys.

sqlplus sys/oracle@localhost:1521/noncdb as sysdba
2. Create two tablespaces, one called ilmtbs, and the other called low_cost_store. Each tablespace should have a 10M data file.

create tablespace ilmtbs datafile '/u01/app/oracle/oradata/noncdb/ilmtbs1.dbf' size 10m reuse
autoextend off extent management local;

create tablespace low_cost_store datafile '/u01/app/oracle/oradata/noncdb/lowcoststore1.dbf' size 10m reuse
autoextend off extent management local;

3. Unlock the SCOTT account, grant it unlimited quota on the two tablespaces. Grant the necessary privileges to SCOTT.

alter user scott identified by tiger account unlock;
alter user scott quota unlimited on ilmtbs;
alter user scott quota unlimited on low_cost_store;
grant alter tablespace, select any dictionary to scott;
grant all on ts$ to scott;
grant all on dba_segments to scott;

4. Enable heat map tracking.
alter system set heat_map=on scope=both;


3. Creating and Updating a Table in the ILMTBS Tablespace


1. Create the SCOTT.employee table in the ilmtbs tablespace. 
create table scott.employee (
        EMPNO     NUMBER(4) NOT NULL,
        ENAME     VARCHAR2(10),
        JOB       VARCHAR2(9),
        MGR       NUMBER(4),
        HIREDATE  DATE,
        SAL       NUMBER(7,2),
        COMM      NUMBER(7,2),
        DEPTNO    NUMBER(2)
)
tablespace ilmtbs;

2. Insert about 3500 rows into SCOTT.employee table.

insert into scott.employee (empno, ename, job, mgr, hiredate, sal, comm, deptno)
select empno, ename, job, mgr, hiredate, sal, comm, deptno from scott.emp
/
declare
blowup PLS_INTEGER := 8;
sql_test clob;
begin
for i in 1..blowup loop
sql_test := 'insert /*+ append */ into scott.employee select * from scott.employee';
execute immediate sql_test;
commit;
end loop;
end;
select count(*) from scott.employee;

Setting Up Storage Tiering for Automatic Data Optimization

This should fill up more than 5% of the tablespace, so that there is less than 95% free space.

3. Verify that the table is stored in the ilmtbs tablespace.

select tablespace_name, segment_name from dba_segments
where segment_name='EMPLOYEE';

Setting Up Storage Tiering for Automatic Data Optimization

4. Verifying Heat Map Tracking Collected Statistics for the Table


1. Connect as scott to the database.

connect scott/tiger@localhost:1521/noncdb

2. Verify that heat map tracking collected statics for SCOTT.employee.

select OBJECT_NAME,SEGMENT_WRITE_TIME , SEGMENT_READ_TIME, FULL_SCAN
FROM user_heat_map_segment
WHERE OBJECT_NAME='EMPLOYEE';

Setting Up Storage Tiering for Automatic Data Optimization

3. Check the current freespace in the ilmtbs tablespace.

col tablespace format A16

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
GROUP BY df.tablespace_name,df.bytes
Order by 4;

Setting Up Storage Tiering for Automatic Data Optimization

Notice that %Free is already less than 95%.

5. Creating a Storage Tiering Policy on the Table 


1. Create a storage tiering policy on SCOTT.employee.

alter table scott.employee ilm add policy tier to low_cost_store;

If heat map tracking was not enabled, you will receive an error message when you try to create the policy.

2. Verify that the policy is 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;

Setting Up Storage Tiering for Automatic Data Optimization

select * from user_ilmobjects;

Setting Up Storage Tiering for Automatic Data Optimization

6. Triggering the Table to Move to Low Cost Storage


1. The ILM decision to move segments also depends on the default thresholds defined at the database level for all user-defined tablespaces. Set the TBS_PERCENT_FREE threshold  to 95% and the TBS_PERCENT_USED threshold to 5%.

connect sys/oracle@localhost:1521/noncdb as sysdba

col name format A20
col value format 9999

select * from dba_ilmparameters;

Setting Up Storage Tiering for Automatic Data Optimization

EXEC dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,95)

EXEC dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_USED,5)

select * from dba_ilmparameters;

Setting Up Storage Tiering for Automatic Data Optimization

2. 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.

connect scott/tiger@localhost:1521/noncdb

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;
/

3. Check the current free space in ILMTBS tablespace. The LOW_COST_STORE may show a value for the column % Used, although the space used in ILMTBS may not have decreased. If this is the case, a few seconds later, run the same statement and you will see that the data dictionary has been updated to reflect the new situation.

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
GROUP BY df.tablespace_name,df.bytes
Order by 4;

Setting Up Storage Tiering for Automatic Data Optimization

4. Verify the SCOTT.employee segment was moved to the low_cost_store tablespace.

select tablespace_name, segment_name
from dba_segments
where segment_name='EMPLOYEE';

Setting Up Storage Tiering for Automatic Data Optimization

5. View the results of the job that completed the movement operation.

SELECT task_id, to_char(start_time, 'dd/mm/yyyy hh24:mi:ss') as start_time 
FROM user_ilmtasks;

Setting Up Storage Tiering for Automatic Data Optimization

select task_id, job_name, job_state, to_char(completion_time,'dd-MON-yyyy')completion
from user_ilmresults;

Setting Up Storage Tiering for Automatic Data Optimization

SELECT * FROM user_ilmevaluationdetails;

Setting Up Storage Tiering for Automatic Data Optimization

7. Resetting Your  Environment


Perform the following steps to reset your environment prior to repeating the activities covered in this OBE or starting another OBE.

1. Connect to the noncdb instance as sys.

connect sys/oracle@localhost:1521/noncdb as sysdba

2. Delete the ILM internal tables. This is not recommended practice in production environments.

delete ilm$;
delete ilmpolicy$;
delete ilmobj$;
delete ilm_results$;
delete ilm_execution$;
delete ilm_executiondetails$;

3. Drop the ilmtbs and low_cost_store tablespaces.

drop tablespace ilmtbs including contents and datafiles;
drop tablespace low_cost_store including contents and datafiles;

4. Reset the tablespace %Free and %Used thresholds.

exec dbms_ilm_admin.customize_ilm(dbms_ilm_admin.tbs_percent_used,85);
exec dbms_ilm_admin.customize_ilm(dbms_ilm_admin.tbs_percent_free,25);

5. Disable heatmap tracking.
alter system set heat_map=off scope=both;

«« Previous
Next »»