Practice: Implementing Storage Tiering ADO Policy for Indexes

Overview

This practice shows how to automate the movement of indexes to another tablespace depending on certain conditions defined in Automatic Data Optimization policies.

Before starting any new practice, refer to the Practices Environment recommendations.

Step 1 : Set up the environment for testing

  • Execute the shell script that cleans up any existing ADO policies, creates two tablespaces for moving indexes from the ADOTBSINDX tablespace to the LOW_COST_STORE_INDX tablespace, and creates the HR.EMP table with a primary key PK_EMPLOYEE_ID whose index is stored in the ADOTBSINDX. It also starts collecting the heat map statistics.

    
    $ cd /home/oracle/labs/M104783GC10
    $ /home/oracle/labs/M104783GC10/ADO_setup.sh
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    Connected to:
    
    SQL> set feedback off
    SQL> delete ilm_results$;
    SQL> delete ilm_execution$;
    SQL> delete ilm_executiondetails$;
    SQL> DROP TABLESPACE adotbsindx INCLUDING CONTENTS AND DATAFILES;
    DROP TABLESPACE adotbsindx INCLUDING CONTENTS AND DATAFILES
    *
    ERROR at line 1:
    ORA-00959: tablespace 'ADOTBSINDX' does not exist
    ...
    SQL> INSERT INTO hr.emp
      2      SELECT employee_id*7, first_name,last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id
      3          FROM hr.emp;
    
    214 rows created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> exit
    $

Step 2 : Display the space used and freed by the table index in the tablespace

  • Display the tablespace in which the index of the primary key for the HR.EMP table is stored and how much space the segment is using.

    
    $ sqlplus system@PDB21
    
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    Enter password:
    
    Connected to:
    
    SQL> COL tablespace_name FORMAT A20
    SQL> COL index_name FORMAT A20
    SQL> COL owner FORMAT A10
    SQL> SELECT tablespace_name, index_name, owner FROM dba_indexes WHERE table_name='EMP';
    
    TABLESPACE_NAME      INDEX_NAME           OWNER
    -------------------- -------------------- ----------
    ADOTBSINDX           PK_EMPLOYEE_ID       HR
    
    SQL>
    SQL> SELECT bytes FROM dba_segments WHERE segment_name='PK_EMPLOYEE_ID';
    
         BYTES
    ----------
         65536
    
    SQL>
  • Display the space used and free in the tablespace in which the index of the primary key for the HR.EMP table is stored.

    
    SQL> 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;
    
    Tablespace                      Size (MB)  Free (MB)     % Free     % Used
    ------------------------------ ---------- ---------- ---------- ----------
    SYSTEM                                310     11.625          4         96
    SYSAUX                                450    25.4375          6         94
    USERS                              671.25    37.8125          6         94
    TBS_FOR_ADO                             4     1.3125         33         67
    ADOTBSINDX                              2      .9375         47         53
    UNDOTBS1                              250     210.75         84         16
    LOW_COST_STORE_INDX                   100         99         99          1
    
    7 rows selected.
    
    SQL>

Step 3 : Create a storage tiering ADO policy on the index

  • Create a storage tiering ADO policy on the index so that when the percentage of empty space in ADOTBSINDX tablespace is less than 90%, the ILM policy being evaluated triggers an ADO action to move the index to the LOW_COST_STORE_INDX tablespace.

    
    SQL> ALTER INDEX hr.pk_employee_id ILM ADD POLICY TIER TO low_cost_store_indx;
    
    Index altered.
    
    SQL>
  • Display the policy in the data dictionary view.

    
    SQL> CONNECT hr@PDB21
    Enter password:
    Connected.
    SQL> SELECT  policy_name, action_type, scope,
                 tier_tablespace "TIER_TBS"
         FROM    user_ilmdatamovementpolicies
         ORDER BY policy_name;
    
    POLI ACTION_TYPE SCOPE   TIER_TBS
    ---- ----------- ------- --------------------
    P2   STORAGE     SEGMENT LOW_COST_STORE_INDX
    
    SQL>

Step 4 : Test the storage tiering ADO policy

  • Insert rows into HR.EMP until the index entries inserted raise the percentage of empty space in ADOTBSINDX tablespace to less than 90%.

    
    SQL> INSERT INTO hr.emp 
                  SELECT employee_id*101, first_name,last_name, email, 
                         phone_number, hire_date, job_id, salary, commission_pct, 
                         manager_id, department_id 
           	FROM hr.emp;
    
    428 rows created.
    
    SQL> INSERT INTO hr.emp
                  SELECT employee_id+436926 , first_name,last_name, email, 
                         phone_number, hire_date, job_id, salary, commission_pct, 
                         manager_id, department_id
                  FROM hr.emp;    
    
    856 rows created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> 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;
    
    Tablespace                      Size (MB)  Free (MB)     % Free     % Used
    ------------------------------ ---------- ---------- ---------- ----------
    SYSTEM                                310     11.625          4         96
    SYSAUX                                450    25.4375          6         94
    USERS                              671.25    37.8125          6         94
    TBS_FOR_ADO                             4       1.25         31         69
    ADOTBSINDX                              2       .875         44         56
    UNDOTBS1                              250     210.75         84         16
    LOW_COST_STORE_INDX                   100         99         99          1
    
    7 rows selected.
    
    SQL>

    The index entries inserted raise the percentage of empty space in ADOTBSINDX tablespace to less than 90%.

  • Display the tablespace in which the index of the primary key for the HR.EMP table is now stored. Did the index move to the LOW_COST_STORE_INDX tablespace?

    
    SQL> SELECT tablespace_name, index_name, owner FROM dba_indexes WHERE table_name='EMP';
    
    TABLESPACE_NAME      INDEX_NAME           OWNER
    -------------------- -------------------- ----------
    ADOTBSINDX           PK_EMPLOYEE_ID       HR
    
    SQL>

    The index has not moved to the other tablespace although the percentage of empty space in ADOTBSINDX tablespace to less than 90%.

  • The ADO 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 90% and the TBS_PERCENT_USED threshold to 30% .

      
      SQL> CONNECT sys@PDB21 AS SYSDBA
      Enter password:
      Connected.
      SQL> COL name FORMAT A40
      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                                       0
      
      8 rows selected.
      
      SQL> EXEC dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,90) 
      
      PL/SQL procedure successfully completed.
      
      SQL> EXEC dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_USED,30) 
      
      PL/SQL procedure successfully completed.
      
      SQL> SELECT * FROM dba_ilmparameters;
      
      NAME                                          VALUE
      ---------------------------------------- ----------
      ENABLED                                           1
      RETENTION TIME                                   30
      JOB LIMIT                                         2
      EXECUTION MODE                                    2
      EXECUTION INTERVAL                               15
      TBS PERCENT USED                                 30
      TBS PERCENT FREE                                 90
      POLICY TIME                                       0
      
      8 rows selected.
      
      SQL>
    • Also, specify that seconds (rather than days) should be used, to test ADO policy evaluation quickly instead of waiting for the policy duration.

      
      SQL> EXEC dbms_ilm_admin.customize_ilm(dbms_ilm_admin.POLICY_TIME,dbms_ilm_admin.ILM_POLICY_IN_SECONDS) 
      
      PL/SQL procedure successfully completed.
      
      SQL> SELECT * FROM dba_ilmparameters;
      
      NAME                                          VALUE
      ---------------------------------------- ----------
      ENABLED                                           1
      RETENTION TIME                                   30
      JOB LIMIT                                         2
      EXECUTION MODE                                    2
      EXECUTION INTERVAL                               15
      TBS PERCENT USED                                 30
      TBS PERCENT FREE                                 90
      POLICY TIME                                       1
      
      8 rows selected.
      
      SQL>
    • For the purpose of the practice, you will not wait for the maintenance window to open to trigger the ADO policies jobs. Instead, you are going to execute the following commands and PL/SQL block, connected as the ADO policy owner HR.

      
      SQL> CONNECT hr@PDB21
      Enter password:
      Connected.
      SQL> ALTER SESSION SET nls_date_format='dd-mon-yy hh:mi:ss';
      
      Session altered.
      
      SQL> 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.
      
      SQL>
    • Check again whether the index has moved to the LOW_COST_STORE_INDX tablespace.

      
      SQL> COL object_type FORMAT A10
      SQL> COL object_name FORMAT A14
      SQL> COL selected_for_execution FORMAT A28
      SQL> COL job_name FORMAT A9
      SQL> SELECT OBJECT_TYPE, OBJECT_NAME, SELECTED_FOR_EXECUTION, JOB_NAME
           FROM   user_ilmevaluationdetails;
      
      OBJECT_TYP OBJECT_NAME    SELECTED_FOR_EXECUTION       JOB_NAME
      ---------- -------------- ---------------------------- ---------
      INDEX      PK_EMPLOYEE_ID SELECTED FOR EXECUTION       ILMJOB100
       
      SQL> SELECT task_id, job_name, job_state FROM user_ilmresults;
      
      TASK_ID JOB_NAME   JOB_STATE
      ------- ---------- -----------------------------------
            1 ILMJOB100  COMPLETED SUCCESSFULLY
      
      SQL>
  • Display the tablespace in which the index of the primary key for the HR.EMP table is now stored. Has it moved to the LOW_COST_STORE_INDX tablespace?

    
    SQL> SELECT tablespace_name, index_name, owner FROM dba_indexes WHERE table_name='EMP';
    
    TABLESPACE_NAME      INDEX_NAME           OWNER
    -------------------- -------------------- ----------
    LOW_COST_STORE_INDX  PK_EMPLOYEE_ID       HR
    
    SQL>

    The index has moved to the other tablespace.

Step 5 : Drop the ADO policy

  • Delete the ADO policy on the index.

    
    SQL> CONNECT system@PDB21
    Enter password:
    Connected.
    SQL> ALTER INDEX hr.pk_employee_id ILM DELETE POLICY p2;
    
    Index altered.
    
    SQL>
  • Stop heat map statistics collection and clean up all heat map statistics.

    
    SQL> CONNECT / AS SYSDBA
    Connected.
    SQL> ALTER SYSTEM SET heat_map=off SCOPE=BOTH;
    
    System altered.
    
    SQL> EXEC dbms_ilm_admin.clear_heat_map_all
    
    PL/SQL procedure successfully completed.
    
    SQL> EXIT
    $