Setting Up Storage Tiering Policy for Automatic Data Optimization
Overview
- Ensure that you have enough disk space to create additional
tablespaces.
- Oracle Database 12c should be installed.
- 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
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:
The environment used in the development of this tutorial is as follows:
Creating Tablespaces, Enabling Accounts, and Enabling Heat Map
Tracking
Connect to the noncdb database as sys.
sqlplus
sys/oracle@localhost:1521/noncdb as sysdba
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;
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;
Enable heat map tracking.
alter system set heat_map=on scope=both;
Creating and Updating a Table in the ILMTBS Tablespace
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;
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;
![images/t30101a.jpg](images/t30101a.jpg)
This should fill up more than 5% of the tablespace, so that
there is less than 95% free space.
Verify that the table is stored in the ilmtbs tablespace.
select tablespace_name,
segment_name from dba_segments
where segment_name='EMPLOYEE';
![images/t30101b.jpg](images/t30101b.jpg)
Verifying Heat Map Tracking Collected Statistics for the
Table
Connect as scott to the database.
connect
scott/tiger@localhost:1521/noncdb
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';
![images/t30101.jpg](images/t30101.jpg)
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;
![images/t30102.jpg](images/t30102.jpg)
Notice that %Free is already less than 95%.
Creating a Storage Tiering Policy on the Table
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.
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;
![images/t40101.jpg](images/t40101.jpg)
select * from user_ilmobjects;
![images/t40102.jpg](images/t40102.jpg)
Triggering the Table to Move to Low Cost Storage
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;
![images/t50101.jpg](images/t50101.jpg)
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;
![images/t50102.jpg](images/t50102.jpg)
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;
/
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;
![images/t50103.jpg](images/t50103.jpg)
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';
![images/t50104.jpg](images/t50104.jpg)
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;
![images/t50105.jpg](images/t50105.jpg)
select task_id, job_name,
job_state, to_char(completion_time,'dd-MON-yyyy')completion
from user_ilmresults;
![images/t50106.jpg](images/t50106.jpg)
SELECT * FROM user_ilmevaluationdetails;
![images/t50107.jpg](images/t50107.jpg)
Resetting Your Environment
Perform the following steps to reset your environment prior to repeating the activities covered in this OBE or starting another OBE.
Connect to the noncdb instance as sys.
connect sys/oracle@localhost:1521/noncdb as sysdba
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$;
Drop the ilmtbs and low_cost_store tablespaces.
drop tablespace ilmtbs including
contents and datafiles;
drop tablespace low_cost_store including contents and
datafiles;
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);
Disable heatmap tracking.
alter system set heat_map=off scope=both;
Summary
- Enable heat map tracking statistics
- View heat map statistics
- Create storage tiering policies
- Triggering the storage tiering policy and verifying the
automatic optimization of data storage
- To learn more about Oracle Database 12c refer to additional OBEs in the Oracle Learning Library.
- Lead Curriculum Developer: Dominique Jeunot, Jean-Francois
Verrier
- Other Contributors: ILM PM Team
In this tutorial, you have learned how to: