8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Heat Map, Information Lifecycle Management (ILM) and Automatic Data Optimization (ADO) in Oracle Database 12c Release 2 (12.2)

In Oracle Database 12.1 the Heat Map and Automatic Data Optimization (ADO) functionality was only available when using the non-CDB architecture. In Oracle Database 12.2 this functionality is now supported in the multitenant architecture. This article gives an overview of Heat Map, Information Lifecycle Management (ILM) and Automatic Data Optimization (ADO) in Oracle Database 12c Release 2 (12.2). The examples are based around the multitenant architecture, but the information applies equally to the non-CDB architecture in Oracle Database 12.1 and 12.2.

Heat Map

The heat map functionality allows you to track data access at the segment level and data modification at the row and segment level, so you can identify the busy segments of the system. This functionality is controlled by the HEAT_MAP parameter, that can be set at the system or session level.

Display the current setting of the HEAT_MAP parameter at the PDB level.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

SHOW PARAMETER heat_map;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
heat_map                             string      OFF
SQL>

Enable the heat map for the PDB.

ALTER SYSTEM SET heat_map = ON;

SHOW PARAMETER heat_map;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
heat_map                             string      ON
SQL>

Notice that the heat map is still disabled at the CBD level.

CONN / AS SYSDBA

SHOW PARAMETER heat_map;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
heat_map                             string      OFF
SQL>

Once the heat map functionality is enable the database will track segment changes for all segments except for those in the SYSTEM and SYSAUX tablespaces. You can display the heat map information using the following views and pipelined table functions.

Do some work that will be tracked.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

CREATE USER test IDENTIFIED BY test QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE TO test;


CONN test/test@pdb1

CREATE TABLE t1 (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

INSERT INTO t1
SELECT level,
       'Description for ' || level
FROM   dual
CONNECT BY level <= 10;
COMMIT;

SELECT *
FROM   t1;

SELECT *
FROM   t1
WHERE  id = 1;

We can now run some queries to see the tracked information.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

COLUMN object_name FORMAT A20

SELECT track_time,
       object_name,
       n_segment_write,
       n_full_scan,
       n_lookup_scan
FROM   v$heat_map_segment
ORDER BY 1, 2;

TRACK_TIME           OBJECT_NAME          N_SEGMENT_WRITE N_FULL_SCAN N_LOOKUP_SCAN
-------------------- -------------------- --------------- ----------- -------------
25-FEB-2017 18:25:31 T1                                 1           2             1
25-FEB-2017 18:25:31 T1_PK                              1           0             1

SQL>


COLUMN owner FORMAT A20
COLUMN object_name FORMAT A20

SELECT track_time,
       owner,
       object_name,
       segment_write,
       full_scan,
       lookup_scan
FROM   dba_heat_map_seg_histogram
ORDER BY 1, 2, 3;

TRACK_TIME           OWNER                OBJECT_NAME          SEG FUL LOO
-------------------- -------------------- -------------------- --- --- ---
25-FEB-2017 18:26:15 TEST                 T1                   YES YES YES
25-FEB-2017 18:26:15 TEST                 T1_PK                YES NO  YES

SQL>


SET LINESIZE 100

COLUMN owner FORMAT A10
COLUMN segment_name FORMAT A20
COLUMN tablespace_name FORMAT A20

SELECT owner,
       segment_name, 
       segment_type,
       tablespace_name,
       segment_size
FROM   TABLE(DBMS_HEAT_MAP.object_heat_map('TEST','T1'));

OWNER      SEGMENT_NAME         SEGMENT_TYPE         TABLESPACE_NAME      SEGMENT_SIZE
---------- -------------------- -------------------- -------------------- ------------
TEST       T1                   TABLE                USERS                       65536
TEST       T1_PK                INDEX                USERS                       65536

SQL>

The heat map information can be really useful for identifying the busy and quiet segments in your database.

Automatic Data Optimization (ADO)

Enabling the heat map functionality also enables Automatic Data Optimimzation (ADO), part of Information Lifecycle Management (ILM). This allows the database to control compression and storage tiering of segments based on usage patterns. Although it can be used with regular table segments, it only really makes sense with partitioning, as it is unlikely you will have whole tables that are not accessed for long periods of time, whereas it can be very likely to have partitions for low-use data.

Create some tablespaces to represent the storage tiers. The following syntax uses Oracle Managed Files (OMF), hence no datafile names are needed.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

CREATE TABLESPACE fast_storage_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;
CREATE TABLESPACE medium_storage_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;
CREATE TABLESPACE slow_storage_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

A table can be created with an ADO ILM policy. The following example creates a partitioned invoices table. It manually allocates partitions to different storage tiers, and includes a tier policy on a partition basis to migrate unused segments to tablespaces on slower storage. There is a compression policy at the table-level, that is inherited by all partitions.

CONN test/test@pdb1

DROP TABLE invoices PURGE;

CREATE TABLE invoices (
  invoice_no    NUMBER NOT NULL,
  invoice_date  DATE   NOT NULL,
  comments      VARCHAR2(500)
)
PARTITION BY RANGE (invoice_date)
(
  PARTITION invoices_2016_q1 VALUES LESS THAN (TO_DATE('01/04/2016', 'DD/MM/YYYY')) TABLESPACE slow_storage_ts,
  PARTITION invoices_2016_q2 VALUES LESS THAN (TO_DATE('01/07/2016', 'DD/MM/YYYY')) TABLESPACE slow_storage_ts,
  PARTITION invoices_2016_q3 VALUES LESS THAN (TO_DATE('01/09/2016', 'DD/MM/YYYY')) TABLESPACE medium_storage_ts
    ILM ADD POLICY TIER TO slow_storage_ts READ ONLY SEGMENT AFTER 6 MONTHS OF NO ACCESS,
  PARTITION invoices_2016_q4 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')) TABLESPACE medium_storage_ts
    ILM ADD POLICY TIER TO slow_storage_ts READ ONLY SEGMENT AFTER 6 MONTHS OF NO ACCESS,
  PARTITION invoices_2017_q1 VALUES LESS THAN (TO_DATE('01/04/2017', 'DD/MM/YYYY')) TABLESPACE fast_storage_ts
    ILM ADD POLICY TIER TO medium_storage_ts READ ONLY SEGMENT AFTER 3 MONTHS OF NO ACCESS,
  PARTITION invoices_2017_q2 VALUES LESS THAN (TO_DATE('01/07/2017', 'DD/MM/YYYY')) TABLESPACE fast_storage_ts
    ILM ADD POLICY TIER TO medium_storage_ts READ ONLY SEGMENT AFTER 3 MONTHS OF NO ACCESS
)
ILM ADD POLICY ROW STORE COMPRESS BASIC SEGMENT AFTER 3 MONTHS OF NO ACCESS;

We can see the policies have been applied using the USER_ILMOBJECTS view.

SET LINESIZE 200

COLUMN policy_name FORMAT A20
COLUMN object_owner FORMAT A15
COLUMN object_name FORMAT A15

SELECT policy_name,
       object_owner,
       object_name,
       object_type,
       inherited_from,
       enabled,
       deleted
FROM   user_ilmobjects
ORDER BY 1;

POLICY_NAME          OBJECT_OWNER    OBJECT_NAME     OBJECT_TYPE        INHERITED_FROM       ENA DEL
-------------------- --------------- --------------- ------------------ -------------------- --- ---
P13                  SYS             INVOICES        TABLE              POLICY NOT INHERITED YES NO
P13                  SYS             INVOICES        TABLE PARTITION    TABLE                YES NO
P13                  SYS             INVOICES        TABLE PARTITION    TABLE                YES NO
P13                  SYS             INVOICES        TABLE PARTITION    TABLE                YES NO
P13                  SYS             INVOICES        TABLE PARTITION    TABLE                YES NO
P13                  SYS             INVOICES        TABLE PARTITION    TABLE                YES NO
P13                  SYS             INVOICES        TABLE PARTITION    TABLE                YES NO
P14                  SYS             INVOICES        TABLE PARTITION    POLICY NOT INHERITED YES NO
P15                  SYS             INVOICES        TABLE PARTITION    POLICY NOT INHERITED YES NO
P16                  SYS             INVOICES        TABLE PARTITION    POLICY NOT INHERITED YES NO
P17                  SYS             INVOICES        TABLE PARTITION    POLICY NOT INHERITED YES NO

SQL>

We can also add policies to an existing table. The following example repeats what we saw earlier by creating the table, then aplying the ADO ILM policies.

CONN test/test@pdb1

DROP TABLE invoices PURGE;

CREATE TABLE invoices (
  invoice_no    NUMBER NOT NULL,
  invoice_date  DATE   NOT NULL,
  comments      VARCHAR2(500)
)
PARTITION BY RANGE (invoice_date)
(
  PARTITION invoices_2016_q1 VALUES LESS THAN (TO_DATE('01/04/2016', 'DD/MM/YYYY')) TABLESPACE slow_storage_ts,
  PARTITION invoices_2016_q2 VALUES LESS THAN (TO_DATE('01/07/2016', 'DD/MM/YYYY')) TABLESPACE slow_storage_ts,
  PARTITION invoices_2016_q3 VALUES LESS THAN (TO_DATE('01/09/2016', 'DD/MM/YYYY')) TABLESPACE medium_storage_ts,
  PARTITION invoices_2016_q4 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')) TABLESPACE medium_storage_ts,
  PARTITION invoices_2017_q1 VALUES LESS THAN (TO_DATE('01/04/2017', 'DD/MM/YYYY')) TABLESPACE fast_storage_ts,
  PARTITION invoices_2017_q2 VALUES LESS THAN (TO_DATE('01/07/2017', 'DD/MM/YYYY')) TABLESPACE fast_storage_ts
);


ALTER TABLE invoices MODIFY PARTITION invoices_2016_q3
  ILM ADD POLICY TIER TO slow_storage_ts READ ONLY SEGMENT AFTER 6 MONTHS OF NO ACCESS;

ALTER TABLE invoices MODIFY PARTITION invoices_2016_q4
  ILM ADD POLICY TIER TO slow_storage_ts READ ONLY SEGMENT AFTER 6 MONTHS OF NO ACCESS;

ALTER TABLE invoices MODIFY PARTITION invoices_2017_q1 
  ILM ADD POLICY TIER TO medium_storage_ts READ ONLY SEGMENT AFTER 3 MONTHS OF NO ACCESS;

ALTER TABLE invoices MODIFY PARTITION invoices_2017_q2 
  ILM ADD POLICY TIER TO medium_storage_ts READ ONLY SEGMENT AFTER 3 MONTHS OF NO ACCESS;

ALTER TABLE invoices 
  ILM ADD POLICY ROW STORE COMPRESS BASIC SEGMENT AFTER 3 MONTHS OF NO ACCESS;

We can disable, delete or modify policies using the following commands.

-- Table-level.
ALTER TABLE <table-name> ILM DISABLE POLICY <policy-name>;
ALTER TABLE <table-name> ILM DELETE POLICY <policy-name>;
ALTER TABLE <table-name> ILM DISABLE_ALL;
ALTER TABLE <table-name> ILM DELETE_ALL;

-- Partition-level.
ALTER TABLE <table-name> MODIFY PARTITION <partition-name> ILM DISABLE POLICY <policy-name>;
ALTER TABLE <table-name> MODIFY PARTITION <partition-name> ILM DELETE POLICY <policy-name>;
ALTER TABLE <table-name> MODIFY PARTITION <partition-name> ILM DISABLE_all;
ALTER TABLE <table-name> MODIFY PARTITION <partition-name> ILM DELETE_ALL;

The following views are available to display policy details.

ILM ADO Parameters

The full list of ILM ADO Parameters are documented here. They can be displayed using the following query.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

COLUMN name FORMAT A20

SELECT name, value
FROM   dba_ilmparameters
ORDER BY name;

NAME                      VALUE
-------------------- ----------
ENABLED                       1
EXECUTION INTERVAL           15
EXECUTION MODE                2
JOB LIMIT                     2
POLICY TIME                   0
RETENTION TIME               30
TBS PERCENT FREE             25
TBS PERCENT USED             85

SQL>

These parameters can be altered using the DBMS_ILM_ADMIN.CUSTOMIZE_ILM procedure. There is a constant defined in the package for each parameter, with the name matching the parameter name with the whitespaces replaced by "_".

BEGIN
  DBMS_ILM_ADMIN.customize_ilm(DBMS_ILM_ADMIN.retention_time, 60);
END;
/

For more information see:

Hope this helps. Regards Tim...

Back to the Top.