Introduction  

Automatic Data Optimisation, which is part of Oracle’s Advanced Compression option, allows for smart compression and data movement using simple to write ILM policies.  Smart Compression refers to the ability to utilize Heat Map information to associate compression policies, and compression levels, with actual data usage. Note that the Advanced Compression Option is required to use these features.

Overview

Typically, how data is used can change dramatically over time. In many cases new data is frequently accessed queried and updated, but as the data grows older perhaps that data will then only be queried occasionally for reporting. As the data grows older still it may then only be kept for regulation compliance reasons and not actually be accessed at all. Information lifecycle management (ILM) identifies information in a database by usage frequency and assigns different types of storage and different levels of compression, based on the lifecycle stage of that information.

 

In the below blog we will look at a couple of simple examples. For these examples I have created a very simple partitioned table called EMPLOYEES

 

TABLE_NAME          PARTITION_NAME PCT_FREE SPC_MGMT BLOCKS USED_BLOCKS FREE_BLOCKS UNUSED_BLOCKS COMPRESS TYPE
EMPLOYEE               SYS_P1012                 10        ASSM             1,024                    82              942                        0.       DISABLED
EMPLOYEE              SYS_P1010                  10       ASSM             1,024                    82              942                          0.      DISABLED
EMPLOYEE              SYS_P1011                   10       ASSM             1,024                    82              942                        0.      DISABLED
EMPLOYEE              SYS_P1013                   10       ASSM             1,024                    82              942                        0.      DISABLED
EMPLOYEE              SYS_P1014                   10       ASSM             1,024                    82              942                        0.      DISABLED

 

Each partition has the same data in it, which uses 82 blocks, leaving 942 blocks free.

 

Heat Maps

Before we can implement our dynamic ILM model, we need to understand how data is being accessed. For this we need to enable heat maps. Oracle heat maps track how data is being accessed including write access, full table scans, and lookups. It is outside the scope of this blog entry to cover heat maps in depth but below is a highlevel overview of some of the features.

 

 

You can enable and disable heat map tracking at the system or session level with the ALTER SYSTEM or ALTER SESSION statement using the HEAT_MAP clause. To use Automatic Data Optimization the HEAT_MAP setting must be enabled at the system level.

 

 

alter system set heat_map=ON scope=both

 

With heat maps enabled Oracle tracks data accesses and this can be seen through a series of views. Initially as heat maps have just been enabled the below select from V$HEAT_MAP_SEGMENT returns no rows.

 

select OBJECT_NAME, SUBOBJECT_NAME, TRACK_TIME, SEGMENT_WRITE, FULL_SCAN, LOOKUP_SCAN from V$HEAT_MAP_SEGMENT​​​​​​

 

If we now issue a select * from one of the partitions in our employee table  and run the query again, we can see that the full scan has been recorded by the heat map.

 

select count(*) from employee partition (SYS_P1010);
 
  COUNT(*)
———-
      3584

 

If we run our query against v$heat_map_segment again we can see that the full scan was captured.

 

SQL> select OBJECT_NAME, SUBOBJECT_NAME, TRACK_TIME, SEGMENT_WRITE, FULL_SCAN, LOOKUP_SCAN from V$HEAT_MAP_SEGMENT;
 
OBJECT_NAME SUBOBJECT_NAME   TRACK_TIME       SEG FUL LOO
————— ————— ——————– — — —
EMPLOYEE    SYS_P1010   07-07-22 03:14.36    NO  YES NO

 

Note that heat maps can differentiate between a full scan and a lookup.

 

 

SQL> select * from employee where empno=25;
 
     EMPNO START_DAT   SALARY     DEPTID NAME
———- ——— ———- ———- ————
      25 22-NOV-07        9000     30 Johnson

 

Rerunning the query again shows up a lookup has been recorded:

 

SQL> select OBJECT_NAME, SUBOBJECT_NAME, TRACK_TIME, SEGMENT_WRITE, FULL_SCAN, LOOKUP_SCAN from V$HEAT_MAP_SEGMENT;
 
OBJECT_NAME SUBOBJECT_NAME   TRACK_TIME        SEG FUL LOO
————— ————— —————– — — —
EMPLOYEE    SYS_P1010   07-07-22 06:52.22      NO  YES NO
IND1                   07-07-22 06:52.22      NO  NO  YES

 

If we insert a row into the table and re-run the query we will see that the insert has been captured

 

SQL>  insert into EMPLOYEE values (26,TO_DATE(’07-11-22 12:49.24′,’YY-MM-DD hh:mi.ss’), 9000, 30, ‘Brown’);
 
1 row created.
 
SQL> commit;
 
Commit complete.

 

 

 

 

SQL> select OBJECT_NAME, SUBOBJECT_NAME, TRACK_TIME, SEGMENT_WRITE, FULL_SCAN, LOOKUP_SCAN from V$HEAT_MAP_SEGMENT;
 
OBJECT_NAME SUBOBJECT_NAME   TRACK_TIME            SEG FUL LOO
————— ————— —————– — — —
EMPLOYEE    SYS_P1012        07-07-22 06:53.54    YES NO  NO
EMPLOYEE    SYS_P1010         07-07-22 06:53.54    NO  YES NO
IND1                         07-07-22 06:53.54    YES NO  YES

Heat Maps works at different levels such as block, segment, and tablespace. For example the below is the block level heat map

 

OWNER    SEGMENT_NAME    PARTITION_NAME  TABLESPACE_NAME    FILE_ID   BLOCK_ID WRITETIME
———- ————— ————— ————— ———- ———- ——————-
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5728 07/07/2022 15:35:51
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5729 07/07/2022 15:35:51
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5730 07/07/2022 15:35:51
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5731 07/07/2022 15:35:51
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5732 07/07/2022 15:35:51
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5733 07/07/2022 15:35:51
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5734 07/07/2022 15:35:51
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5735 07/07/2022 15:35:51
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5736 07/07/2022 15:35:51
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5737 07/07/2022 15:35:51
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5738 07/07/2022 15:35:51
 
OWNER    SEGMENT_NAME    PARTITION_NAME  TABLESPACE_NAME    FILE_ID   BLOCK_ID WRITETIME
———- ————— ————— ————— ———- ———- ——————-
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5739 07/07/2022 15:35:51
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5740 07/07/2022 15:35:51
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5741 07/07/2022 15:35:51
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5742 07/07/2022 15:35:51
SCOTT    EMPLOYEE    P0         TIER1_STORE                   5743 07/07/2022 15:35:51
 

The DBMS_HEAT_MAP package provides additional flexibility for displaying heat map data using DBMS_HEAT_MAP subprograms.

DBMS_HEAT_MAP includes one set of APIs that externalize heat maps at various levels of storage such as block, extent, segment, object, and tablespace; and a second set of APIs that externalize the heat maps materialized by the background process for the top tablespaces.

For more information on heat maps views see:

https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/ilm-strategy-heatmap-ado.html#GUID-1E3F8295-56EE-4798-BDC0-BFC626168828

 

Automatic Data Optimization

With heat maps enabled, we can now configure an ILM policy using Automatic Data Optimisation. The parameters that control ILM are can be selected from the dba_ilmparameters  view.

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

 

 

For ILM to be active the ENABLED parameter must be set to 1. ILM policies, by default, work in days.

 

POLICY TIME can be set to seconds, this allows for policies to be tested, without having to wait long periods of time. The DBMS_ILM_admin package is used to change the parameter settings. We will use this to set POLICY TIME into seconds.

 

 

exec dbms_ilm_admin.customize_ilm(dbms_ilm_admin.POLICY_TIME, dbms_ilm_admin.ILM_POLICY_IN_SECONDS);
 
PL/SQL procedure successfully completed.

 

Displaying the parameters again shows that POLICY TIME has been updated.

 

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

 

 

For the first test we will configure an ILM policy that after a certain period of data inactivity will compress the partition using advanced row compression.

 

 

TABLE_NAME    PARTITION_NAME PCT_FREE SPC_MGMT BLOCKS USED_BLOCKS FREE_BLOCKS UNUSED_BLOCKS COMPRESS TYPE
EMPLOYEE      SYS_P1012          10 ASSM   1,024        82        942           0         DISABLED
EMPLOYEE       SYS_P1010         10 ASSM   1,024        82        942           0         DISABLED
EMPLOYEE       SYS_P1011         10 ASSM   1,024        82        942           0         DISABLED
EMPLOYEE       SYS_P1013         10 ASSM   1,024        82        942           0         DISABLED
EMPLOYEE       SYS_P1014         10 ASSM   1,024        82        942           0         DISABLED

 

We will use partition SYS_P1010 of the employee table. Let’s check the rows that are compressed within this partition currently:

 

 

col Compression_type format a50
SQL> SELECT
    CASE comp_type
       WHEN 1 THEN ‘No Compression’
       WHEN 2 THEN ‘Advanced compression level’
       WHEN 4 THEN ‘Hybrid Columnar Compression for Query High’
       WHEN 8 THEN ‘Hybrid Columnar Compression for Query Low’
       WHEN 16 THEN ‘Hybrid Columnar Compression for Archive High’
       WHEN 32 THEN ‘Hybrid Columnar Compression for Archive Low’
       WHEN 64 THEN ‘Compressed row’
       WHEN 128 THEN ‘High compression level for LOB operations’
       WHEN 256 THEN ‘Medium compression level for LOB operations’
       WHEN 512 THEN ‘Low compression level for LOB operations’
       WHEN 1000 THEN ‘Minimum required number of LOBs in the object for which LOB compression ratio is to be estimated’
       WHEN 4096 THEN ‘Basic compression level’
       WHEN 5000 THEN ‘Maximum number of LOBs used to compute the LOB compression ratio’
       WHEN 1000000 THEN ‘Minimum required number of rows in the object for which HCC ratio is to be estimated’
       WHEN -1 THEN ‘To indicate the use of all the rows in the object to estimate HCC ratio’
       WHEN 1 THEN ‘Identifies the object whose compression ratio is estimated as of type table’
       ELSE ‘Unknown Compression Type’
    END AS Compression_type,
    n as num_rows
   FROM
    (
       SELECT
         comp_type,
         count(*) n
       FROM
         (
           SELECT
             dbms_compression.get_compression_type( USER, ‘EMPLOYEE’, ROWID, ‘SYS_P1010’ ) AS comp_type
           FROM  EMPLOYEE partition(SYS_P1010)
         )
       GROUP     BY comp_type
    );
 
COMPRESSION_TYPE                       NUM_ROWS
————————————————– ———-
No Compression                               3584
 

 

We can see that all 3584 rows within the partition are not compressed.

 

Let’s create a segment policy on the partition

 

 

Create a segment policy on the partition
SQL> ALTER TABLE employee MODIFY PARTITION SYS_P949
   ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT
   AFTER 3 DAYS OF NO MODIFICATION;
 
Table altered.

By issuing the above statement we have added an ILM policy to the partition. we have set an instruction that Automatic Data Optimization (ADO) will automatically compress the whole partition at the segment level. This will happen if three days elapse with NO modification to the table.

There are various views that can be used to see the state of ILM policies

SQL> select * from user_ilmobjects;
 
POLICY_NAME OBJECT_OWN OBJECT_NAME     SUBOBJECT_NAME     OBJECT_TYPE          INHERITED_FROM     TBS_INHERITED_FROM           ENABLED DEL
———– ———- ——————– ——————– ——————– ——————– —————————— ——- —
P142        SCOTT      EMPLOYEE        SYS_P1010          TABLE PARTITION      POLICY NOT INHERITED                            YES    NO
 

User_ilmobjects shows the policy name, the owner and object that is associated with the policy. We can see in the last two columns that the policy is enabled, and not deleted.

SQL> select * from user_ilmpolicies;
 
POLICY_NAME POLICY_TYPE   TABLESPACE            ENABLED DEL
———– ————- ——————– ——- —
P142       DATA MOVEMENT                      YES     NO
 

The user_ilmdatamovementpolicies view shows a couple of important things:

  • The policy name
  • The compression level. In this case segment compression.
  • The source object that the policy is attached to.
  • The condition that needs to be met before the compression is applied. We are using 3 days without modification.

 

SQL> select * from user_ilmdatamovementpolicies;
 
POLICY_NAME ACTION_TYPE SCOPE       COMPRESSION_LEVEL    TIER_TABLESPACE         TIER_STATUS        CONDITION_TYPE             CONDITION_DAYS CUSTOM_FUNCTION   POLICY_SUB
———– ———– ——- ——————– ——————– ——————– ———————- ————– ——————– ———-
ACTION_CLAUSE                                                         TIER_TO
——————————————————————————– ———-
P142       COMPRESSION SEGMENT ADVANCED                                              LAST MODIFICATION TIME               3                         DISK

 

Although we have specified days, the underlying parameter was changed to seconds. This means three days will be evaulted as 3 seconds of non-modification.

The ILM parameter EXCUTION_INTERVAL which has a default setting 15 minutes determines when the system evaluates the ILM policies. Using the DBMS_ILM package we don’t need to wait and we can evaluate and execute the policy by hand.

Let’s check the heat maps one more time to make sure there has been no access to the partition we have the policy configured against in the last 3 seconds.

 

Show heat map data for the partition segments
SQL> SELECT
  2    owner, segment_name, partition_name, tablespace_name,
  3    segment_size,
  4    to_char(avg_ftstime,’MM/DD/YYYY HH24:MI:SS’) as “Avg FTS Time”
  5  FROM TABLE(DBMS_HEAT_MAP.OBJECT_HEAT_MAP(‘SCOTT’,’EMPLOYEE’));
 
OWNER    SEGMENT_NAME    PARTITION_NAME  TABLESPACE_NAME SEGMENT_SIZE Avg FTS Time
———- ————— ————— ————— ———— ——————-
SCOTT    EMPLOYEE    SYS_P1010        TIER1_STORE         8388608 07/14/2022 17:50:05
SCOTT    EMPLOYEE    SYS_P1011        TIER1_STORE         8388608 07/14/2022 17:50:05
SCOTT    EMPLOYEE    SYS_P1012        TIER1_STORE         8388608 07/14/2022 17:50:05
SCOTT    EMPLOYEE    SYS_P1013        TIER1_STORE         8388608 07/14/2022 17:50:05
SCOTT    EMPLOYEE    SYS_P1014        TIER1_STORE         8388608 07/14/2022 17:50:05
 

Next, we will force the running of the ADO policy and check to see if any rows were compressed.

SQL> declare
      v_executionid number;
    begin
      dbms_ilm.execute_ilm (
       owner=>’SCOTT’,
       object_name=>’EMPLOYEE’,
       subobject_name=>’P1010′,
       execution_mode=>dbms_ilm.ilm_execution_offline,
       task_id=>v_executionid);
   end;
   /
 
PL/SQL procedure successfully completed.
 
We can check the results of the procedure against the user_ilmevaluationdetails view.
 
SQL> select * from user_ilmevaluationdetails;
 
   TASK_ID POLICY_NAME OBJECT_OWNER    OBJECT_NAME     SUBOBJECT_NAME  OBJECT_TYPE     SELECTED_FOR_EXECUTION JOB_NAME        COMMENTS
———- ———– ————— ————— ————— ————— ———————- ————— ——————–
       442 P142        SCOTT        EMPLOYEE        SYS_P1010       TABLE PARTITION SELECTED FOR EXECUTION ILMJOB3562

 

As the policy met its condition, we can see the table partition has been selected for execution and a job number given. This would have happened automatically without intervention, at the next evaluation window. We used the DBMS_ILM package to run the evaluation so we did not have to wait.

 

If we re-run the statement to look at the rows within the partition now.

 

SQL> col Compression_type format a50
SQL> SELECT
      CASE comp_type
       WHEN 1 THEN ‘No Compression’
       WHEN 2 THEN ‘Advanced compression level’
       WHEN 4 THEN ‘Hybrid Columnar Compression for Query High’
       WHEN 8 THEN ‘Hybrid Columnar Compression for Query Low’
       WHEN 16 THEN ‘Hybrid Columnar Compression for Archive High’
       WHEN 32 THEN ‘Hybrid Columnar Compression for Archive Low’
       WHEN 64 THEN ‘Compressed row’
       WHEN 128 THEN ‘High compression level for LOB operations’
       WHEN 256 THEN ‘Medium compression level for LOB operations’
       WHEN 512 THEN ‘Low compression level for LOB operations’
       WHEN 1000 THEN ‘Minimum required number of LOBs in the object for which LOB compression ratio is to be estimated’
       WHEN 4096 THEN ‘Basic compression level’
       WHEN 5000 THEN ‘Maximum number of LOBs used to compute the LOB compression ratio’
       WHEN 1000000 THEN ‘Minimum required number of rows in the object for which HCC ratio is to be estimated’
       WHEN -1 THEN ‘To indicate the use of all the rows in the object to estimate HCC ratio’
       WHEN 1 THEN ‘Identifies the object whose compression ratio is estimated as of type table’
       ELSE ‘Unknown Compression Type’
     END AS Compression_type,
     n as num_rows
   FROM
     (
       SELECT
         comp_type,
         count(*) n
       FROM
         (
           SELECT
             dbms_compression.get_compression_type( USER, ‘EMPLOYEE’, ROWID, ‘SYS_P1010’ ) AS comp_type
           FROM  EMPLOYEE partition(SYS_P1010)
         )
       GROUP     BY comp_type
     );
 
COMPRESSION_TYPE                       NUM_ROWS
————————————————– ———-
Advanced compression level                    3584

 

We can see that all the rows within the partition have been compressed.

 

If we look at the space usage we can see that partition has compressed down to 24 blocks (from 82) and that compression is marked as ‘ENABLED ADVANCED’

 

TABLE_NAME    PARTITION_NAME PCT_FREE SPC_MGMT BLOCKS USED_BLOCKS FREE_BLOCKS UNUSED_BLOCKS COMPRESS TYPE
EMPLOYEE      SYS_P1012        10 ASSM   1,024        82        942           0 DISABLED
EMPLOYEE       SYS_P1010         10 ASSM   1,024        24          0        1,000 ENABLED  ADVANCED
EMPLOYEE       SYS_P1011         10 ASSM   1,024        82        942           0 DISABLED
EMPLOYEE       SYS_P1013         10 ASSM   1,024        82        942           0 DISABLED
EMPLOYEE       SYS_P1014         10 ASSM   1,024        82        942           0 DISABLED
PL/SQL procedure successfully completed.

Storage Tiering

One of the other great features of Automatic Data Optimization (ADO) is storage tiering. This allows objects to be moved between storage types

Imagine that we have some expensive fast storage and some cheaper slow archive storage. Typically new data is accessed more frequently than older data. With ADO you can automatically move data between the fast and slow storage based on the heat maps data for the object. As the data in particular partitions are no longer accessed, perhaps only held for compliance purposes we could write a policy that detects this and moves the partition to the cheaper archive storage.

We have seen how heat maps can be used in the last example. For the storage tiering example I will use the other metric that can be used. Looking at the ILM parameters:

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

We can see that tablespace free space can be monitored using TBS PERCENT FREE and TBS PERCENT USED.

In our environment we have TBS PERCENT USED set at 85%. We can use ADO to move objects, based on policies, to free up storage when this threshold is crossed.

Let’s list our tablespaces:

SQL> SELECT data_files.tablespace_name,
          ts.status,
          NVL(ROUND(tot_alloc_byt/1024/1024,2),0) alloc,
          NVL(ROUND(max_free_byt/1024/1024,2),0) maxfr,
          NVL(ROUND(tot_free_byt/1024/1024,2),0) totfr,
          NVL(ROUND( ( (NVL(tot_extnd_blk,0) * ts.block_size) + NVL(tot_alloc_byt,0) )/1024/1024,2 ),0) xtnd,
          ROUND( ( (DECODE( (NVL(tot_alloc_byt,0) + ( NVL(tot_extnd_blk,0) * ts.block_size)),0,0,
               NVL(tot_free_byt,0) + DECODE( tot_extnd_blk,NULL,0,(tot_extnd_blk * ts.block_size) )
              ) / (NVL(tot_alloc_byt,0.1) + ( NVL(tot_extnd_blk,0.1) * ts.block_size) )) * 100 ), 2) pctfr
     FROM ( SELECT tablespace_name,
                SUM(bytes) tot_alloc_byt
           FROM sys.dba_data_files
             GROUP BY tablespace_name
             UNION ALL
            SELECT tablespace_name,
                SUM(bytes) tot_alloc_byt
           FROM sys.dba_temp_files
             GROUP BY tablespace_name ) data_files,
          ( SELECT tablespace_name,
                MAX(bytes) max_free_byt,
                SUM(bytes) tot_free_byt
           FROM sys.dba_free_space
             GROUP BY tablespace_name ) free_space,
          ( SELECT tablespace_name,
                SUM(DECODE(SIGN(maxblocks – blocks),-1,0,(maxblocks – blocks))) tot_extnd_blk
           FROM sys.dba_data_files
             GROUP BY tablespace_name
             UNION ALL
            SELECT tablespace_name,
                SUM(DECODE(SIGN(maxblocks – blocks),-1,0,(maxblocks – blocks))) tot_extnd_blk
           FROM sys.dba_temp_files
             GROUP BY tablespace_name ) extnd,
          sys.dba_tablespaces ts
    WHERE data_files.tablespace_name = free_space.tablespace_name(+)
      AND data_files.tablespace_name = extnd.tablespace_name(+)
      AND data_files.tablespace_name = ts.tablespace_name
    ORDER BY pctfr DESC;
 
                                        Max Contig Total Free    Extends  Pct Free
Tablespace              Status       Alloc(MB) Alloc Free(MB)  Alloc(MB)    To(MB) Spc Avail
—————————— —— ————– ————– ————– ————– ———
USERS                   ONLINE             5         3        3       32,768    100.00
SCRATCH                 ONLINE          10,240.   3,968      10,239     10,240     99.99
USERS2                 ONLINE          10,240.   3,968      10,238     10,240     99.98
UNDOTBS1                ONLINE          350       298          312     32,768     99.88
SYSTEM                 ONLINE          440       15        16       32,768     98.70
TIER2_STORE             ONLINE            94       92        92          100     98.38
SYSAUX                 ONLINE          690       42        60       32,768     98.08
TEMP                    ONLINE          977         0        0       32,768     97.02
SOE                     ONLINE          20,480.   3,968      10,570     32,768     69.76
SOE2                    ONLINE          20,480.   3,968      10,466     32,768     69.44
TIER1_STORE             ONLINE            50         8        9           50     18.00
 
11 rows selected.

 

 

In this example we will use one of the other partitions from the Employee table, SYS_P1011. We can see that this partition is located in the TIER1_STORE tablespace.

 

 

SQL> select segment_name, partition_name, segment_type, tablespace_name
    from user_segments
    where segment_name = ‘EMPLOYEE’
    and partition_name = ‘SYS_P1011’;
 
SEGMENT_NAME     PARTITION_NAME   SEGMENT_TYPE     Tablespace
————— ————— ————— —————
EMPLOYEE       SYS_P1011      TABLE PARTITION       TIER1_STORE

 

Let’s create a tiering policy on this partition. When the condition is met the partition will be moved to the TIER2_STORE tablespace

 

SQL> ALTER TABLE employee MODIFY PARTITION SYS_P1011 ILM ADD POLICY TIER TO tier2_store;

 

The user_ilmobjects view shows us all the policies that have been created for the user.

 

select * from user_ilmobjects
POLICY_NAME OBJECT_OWN OBJECT_NAME     SUBOBJECT_NAME        OBJECT_TYPE     INHERITED_FROM TBS_INHERITED_FROM                  ENABLED DEL
———– ———- ————— ——————– ————— ——————– —————————— ——- —
P142       SCOTT      EMPLOYEE        SYS_P1010              TABLE PARTITION POLICY NOT INHERITED                               NO       NO
P161       SCOTT      EMPLOYEE        SYS_P1011              TABLE PARTITION POLICY NOT INHERITED                               YES      NO

The new policy is named P161. The policy is marked as enabled. We can see policy P161 is working on the employee table’s partition SYS_P1011.

The policy from the previous example is still listed (P142) note that the policy has been disabled as the segment compression has already occured.

The TIER1_STORE tablespace has 18% free space left, meaning it is 82% full. Our ILM tablespace full parameter is set at 85%

We will add more rows to the partition to take the amount of consumed storage above 85%

SQL> insert into employee select * from employee partition(SYS_P1011);
SQL> insert into employee select * from employee partition(SYS_P1011);
SQL> insert into employee select * from employee partition(SYS_P1011);
SQL> insert into employee select * from employee partition(SYS_P1011);
SQL> insert into employee select * from employee partition(SYS_P1011);
SQL> insert into employee select * from employee partition(SYS_P1011);
SQL> insert into employee select * from employee partition(SYS_P1011);
 
229376 rows created.
 
SQL> commit;
Commit complete.

 

Checking the tablespace usage again we can see that TIER1_STORE now only has 2% free.

SQL> SELECT data_files.tablespace_name,
          ts.status,
          NVL(ROUND(tot_alloc_byt/1024/1024,2),0) alloc,
          NVL(ROUND(max_free_byt/1024/1024,2),0) maxfr,
          NVL(ROUND(tot_free_byt/1024/1024,2),0) totfr,
          NVL(ROUND( ( (NVL(tot_extnd_blk,0) * ts.block_size) + NVL(tot_alloc_byt,0) )/1024/1024,2 ),0) xtnd,
          ROUND( ( (DECODE( (NVL(tot_alloc_byt,0) + ( NVL(tot_extnd_blk,0) * ts.block_size)),0,0,
               NVL(tot_free_byt,0) + DECODE( tot_extnd_blk,NULL,0,(tot_extnd_blk * ts.block_size) )
              ) / (NVL(tot_alloc_byt,0.1) + ( NVL(tot_extnd_blk,0.1) * ts.block_size) )) * 100 ), 2) pctfr
     FROM ( SELECT tablespace_name,
                SUM(bytes) tot_alloc_byt
           FROM sys.dba_data_files
             GROUP BY tablespace_name
             UNION ALL
            SELECT tablespace_name,
                SUM(bytes) tot_alloc_byt
           FROM sys.dba_temp_files
             GROUP BY tablespace_name ) data_files,
          ( SELECT tablespace_name,
                MAX(bytes) max_free_byt,
                SUM(bytes) tot_free_byt
           FROM sys.dba_free_space
             GROUP BY tablespace_name ) free_space,
          ( SELECT tablespace_name,
                SUM(DECODE(SIGN(maxblocks – blocks),-1,0,(maxblocks – blocks))) tot_extnd_blk
           FROM sys.dba_data_files
             GROUP BY tablespace_name
             UNION ALL
            SELECT tablespace_name,
                SUM(DECODE(SIGN(maxblocks – blocks),-1,0,(maxblocks – blocks))) tot_extnd_blk
           FROM sys.dba_temp_files
             GROUP BY tablespace_name ) extnd,
          sys.dba_tablespaces ts
    WHERE data_files.tablespace_name = free_space.tablespace_name(+)
      AND data_files.tablespace_name = extnd.tablespace_name(+)
      AND data_files.tablespace_name = ts.tablespace_name
    ORDER BY pctfr DESC;
 
                                        Max Contig Total Free    Extends  Pct Free
Tablespace              Status       Alloc(MB) Alloc Free(MB)  Alloc(MB)    To(MB) Spc Avail
—————————— —— ————– ————– ————– ————– ———
USERS                   ONLINE             5             3        3         32,768      1.00
SCRATCH                 ONLINE         10,240.        3,968      10,239     10,240      99.99
USERS2                  ONLINE         10,240.        3,968      10,238     10,240      99.98
UNDOTBS1                ONLINE            350           298          312     32,768      99.88
SYSTEM                  ONLINE            440            15        16       32,768       98.70
TIER2_STORE             ONLINE             94            92        92          100       98.38
SYSAUX                  ONLINE            690            42        60       32,768       98.08
TEMP                    ONLINE            977             0        0       32,768        97.02
SOE                     ONLINE         20,480.        3,968      10,570     32,768       69.76
SOE2                    ONLINE         20,480.        3,968      10,466     32,768       69.44
TIER1_STORE             ONLINE             50             1           1           50      2.00

 

Like the last example we will force the evaluation and execution of the policy so we don’t have to wait for the system.

SQL> declare
      v_executionid number;
    begin
      dbms_ilm.execute_ilm (
       owner=>’SCOTT’,
       object_name=>’EMPLOYEE’,
       execution_mode=>dbms_ilm.ilm_execution_offline,
       task_id=>v_executionid);
    end;
   /
PL/SQL procedure successfully completed.
 

Using the view user_ilmevaluations we can see that policy P161 has been selected for execution. This is because the TIER1_STORE tablespace is now more than the configured 85% full.

SQL> select * from user_ilmevaluationdetails order by task_id;
 
   TASK_ID POLICY_NAME OBJECT_OWNER    OBJECT_NAME     SUBOBJECT_NAME  OBJECT_TYPE     SELECTED_FOR_EXECUTION JOB_NAME        COMMENTS
———- ———– ————— ————— ————— ————— ———————- ————— ——————
       442 P142        SCOTT        EMPLOYEE        SYS_P1010       TABLE PARTITION SELECTED FOR EXECUTION ILMJOB3562
       459 P142        SCOTT        EMPLOYEE        SYS_P1010       TABLE PARTITION POLICY DISABLED
       459 P161        SCOTT        EMPLOYEE        SYS_P1011       TABLE PARTITION SELECTED FOR EXECUTION ILMJOB3610

 

Checking we can see the partition with the policy attached has been moved online to the TIER2_STORE tablespace.

SQL> select segment_name, partition_name, segment_type, tablespace_name
    from user_segments
    where segment_name = ‘EMPLOYEE’
    and partition_name = ‘SYS_P1011’;
 
SEGMENT_NAME          PARTITION_NAME      SEGMENT_TYPE          Tablespace
——————– ——————– ——————– ——————————
EMPLOYEE               SYS_P1011          TABLE PARTITION      TIER2_STORE
 

 

If we check one final time at the tablespace storage, we can see that the TIER1_STORE tablespace has increased to 34% free, at the same time the TIER2_STORE tablespace has decreased from 98% free to 82% free.

 

SQL> SELECT data_files.tablespace_name,
          ts.status,
          NVL(ROUND(tot_alloc_byt/1024/1024,2),0) alloc,
          NVL(ROUND(max_free_byt/1024/1024,2),0) maxfr,
          NVL(ROUND(tot_free_byt/1024/1024,2),0) totfr,
          NVL(ROUND( ( (NVL(tot_extnd_blk,0) * ts.block_size) + NVL(tot_alloc_byt,0) )/1024/1024,2 ),0) xtnd,
          ROUND( ( (DECODE( (NVL(tot_alloc_byt,0) + ( NVL(tot_extnd_blk,0) * ts.block_size)),0,0,
               NVL(tot_free_byt,0) + DECODE( tot_extnd_blk,NULL,0,(tot_extnd_blk * ts.block_size) )
              ) / (NVL(tot_alloc_byt,0.1) + ( NVL(tot_extnd_blk,0.1) * ts.block_size) )) * 100 ), 2) pctfr
     FROM ( SELECT tablespace_name,
                SUM(bytes) tot_alloc_byt
           FROM sys.dba_data_files
             GROUP BY tablespace_name
             UNION ALL
            SELECT tablespace_name,
                SUM(bytes) tot_alloc_byt
           FROM sys.dba_temp_files
             GROUP BY tablespace_name ) data_files,
          ( SELECT tablespace_name,
                MAX(bytes) max_free_byt,
                SUM(bytes) tot_free_byt
           FROM sys.dba_free_space
             GROUP BY tablespace_name ) free_space,
          ( SELECT tablespace_name,
                SUM(DECODE(SIGN(maxblocks – blocks),-1,0,(maxblocks – blocks))) tot_extnd_blk
           FROM sys.dba_data_files
             GROUP BY tablespace_name
             UNION ALL
            SELECT tablespace_name,
                SUM(DECODE(SIGN(maxblocks – blocks),-1,0,(maxblocks – blocks))) tot_extnd_blk
           FROM sys.dba_temp_files
             GROUP BY tablespace_name ) extnd,
          sys.dba_tablespaces ts
    WHERE data_files.tablespace_name = free_space.tablespace_name(+)
      AND data_files.tablespace_name = extnd.tablespace_name(+)
      AND data_files.tablespace_name = ts.tablespace_name
   —   AND ts.status = ‘ONLINE’
    ORDER BY pctfr DESC;
 
                                        Max Contig Total Free    Extends  Pct Free
Tablespace              Status       Alloc(MB) Alloc Free(MB)  Alloc(MB)    To(MB) Spc Avail
—————————— —— ————– ————– ————– ————– ———
USERS                   ONLINE             5         3        3       32,768    100.00
SCRATCH                 ONLINE          10,240.   3,968      10,239     10,240     99.99
USERS2                 ONLINE          10,240.   3,968      10,238     10,240     99.98
UNDOTBS1                ONLINE          350       298          312     32,768     99.88
SYSTEM                 ONLINE          440       15        16       32,768     98.70
SYSAUX                 ONLINE          690       42        60       32,768     98.08
TEMP                    ONLINE          977         0        0       32,768     97.02
TIER2_STORE             ONLINE            94       76        76          100     82.38
SOE                     ONLINE          20,48     3,968      10,570     32,768     69.76
SOE2                    ONLINE          20,480.   3,968      10,466     32,768     69.44
TIER1_STORE             ONLINE            50       16        17           50     34.00
 
11 rows selected.

 

 

Conclusion

Automatic Data Optimization in the Advanced Compression option makes it trivial to create powerful ILM configurations that can compress, or tier objects based on access patterns (using heat maps) or storage capacity. For more information see

https://www.oracle.com/assets/automatic-data-optimization-wp-12c-1896120.pdf