Setting Up Storage Tiering Policy for Automatic Data Optimization

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

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;

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

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

    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;

    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;

    select * from user_ilmobjects;

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;

    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;

    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;

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

    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;

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

    SELECT * FROM user_ilmevaluationdetails;

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

    In this tutorial, you have learned how to:

    • 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

    Resources

    Credits

    • Lead Curriculum Developer: Dominique Jeunot, Jean-Francois Verrier
    • Other Contributors: ILM PM Team

To help navigate this Oracle by Example, note the following:

Hiding Header Buttons:
Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
Topic List Button:
A list of all the topics. Click one of the topics to navigate to that section.
Expand/Collapse All Topics:
To show/hide all the detail for all the sections. By default, all topics are collapsed
Show/Hide All Images:
To show/hide all the screenshots. By default, all images are displayed.
Print:
To print the content. The content currently displayed or hidden will be printed.

To navigate to a particular section in this tutorial, select the topic from the list.