Apr 02 2020

Configure Heat Map & Automatic Data Optimization (ADO) in Oracle 12c

Nowadays Data is growing very rapidly at enterprises – various industry estimates indicate that data volume is doubling every 2-3 years. The rapid growing data is going to be challenged for IT, both in cost and performance.

Although the cost of storage keeps declining, fast-growing data volumes make storage one of
the costliest elements of most IT budgets. In addition, the accelerating growth of data makes it
difficult to meet performance requirements while staying within budget.

Information Lifecycle Management (ILM) addresses these challenges by storing data in
different storage and compression tiers, according to the enterprise’s current business and
performance needs. This approach offers the possibility of optimizing storage for both cost
savings and maximum performance.

Supported Version: The Heat Map and Automatic Data Optimization (ADO) functionality was only available for non-CDB in Oracle Database 12.1. These functionalities are now supported in the Multitenant architecture in Oracle 12.2 and onward version.

Heat Map and Automatic Data Optimization are features of Oracle Advanced Compression.

Heat Map:

Heat Map automatically tracks modification and query timestamps at the row and segment
levels, providing detailed insights into data access.

To enables Heat Map tracking for the database instance:

SQL> SHOW PARAMETER HEAT_MAP

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
heat_map                             string      OFF
SQL> ALTER SYSTEM SET HEAT_MAP = ON;

System altered.

SQL> SHOW PARAMETER HEAT_MAP

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
heat_map                             string      ON

To disables Heat Map tracking for the database instance:

SQL> SHOW PARAMETER HEAT_MAP

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
heat_map                             string      ON
SQL> ALTER SYSTEM SET HEAT_MAP = OFF;

System altered.

SQL> SHOW PARAMETER HEAT_MAP

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
heat_map                             string      OFF

How Heat Map Track Works:

SQL> ALTER SYSTEM SET HEAT_MAP = ON;

System altered.

SQL> CREATE TABLESPACE TBS_TEST
DATAFILE '/u02/oradata/NONCDB/datafile/tbs_test_01.dbf' SIZE 50M AUTOEXTEND ON NEXT 2M MAXSIZE 100M
LOGGING
DEFAULT
  NO INMEMORY
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;  

Tablespace created.

SQL> CREATE USER JOHN IDENTIFIED BY oracle;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO JOHN;

Grant succeeded.

SQL> ALTER USER JOHN QUOTA UNLIMITED ON TBS_TEST;

User altered.

SQL> ALTER USER JOHN DEFAULT TABLESPACE TBS_TEST;

User altered.

Heat Map keeps track when read or write any objects. Following example for reading an segments:

SQL> conn john/oracle
Connected.

SQL> CREATE TABLE JOHN.TBL_ADO AS SELECT * FROM ALL_OBJECTS;

Table created.
SQL> CONN / AS SYSDBA
Connected.
SQL> SELECT OBJECT_NAME,TRACK_TIME,SEGMENT_WRITE,SEGMENT_READ,FULL_SCAN,LOOKUP_SCAN
  FROM V$HEAT_MAP_SEGMENT
  WHERE object_name = 'TBL_ADO';  

no rows selected

SQL> select count(*) from john.TBL_ADO;

  COUNT(*)
----------
     73599

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> SELECT OBJECT_NAME,TRACK_TIME,SEGMENT_WRITE,SEGMENT_READ,FULL_SCAN,LOOKUP_SCAN
  FROM V$HEAT_MAP_SEGMENT
  WHERE object_name = 'TBL_ADO'; 

OBJECT_NAME                    TRACK_TIME          SEG SEG FUL LOO
------------------------------ ------------------- --- --- --- ---
TBL_ADO                        2020-09-11 16:03:40 NO  NO  YES NO
SQL> INSERT INTO JOHN.TBL_ADO (SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 10);

9 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT OBJECT_NAME,TRACK_TIME,SEGMENT_WRITE,SEGMENT_READ,FULL_SCAN,LOOKUP_SCAN
  FROM V$HEAT_MAP_SEGMENT
  WHERE object_name = 'TBL_ADO';   

OBJECT_NAME                    TRACK_TIME          SEG SEG FUL LOO
------------------------------ ------------------- --- --- --- ---
TBL_ADO                        2020-09-11 16:27:35 YES NO  YES NO
SQL> CREATE INDEX JOHN.IDX_ADO_OBJECT_ID ON JOHN.TBL_ADO (OBJECT_ID);

Index created.

SQL> SELECT OBJECT_NAME FROM JOHN.TBL_ADO WHERE OBJECT_ID=142;

OBJECT_NAME
--------------------------------------------------------------------------------
DUAL

SQL> SELECT OBJECT_NAME,TRACK_TIME,SEGMENT_WRITE,SEGMENT_READ,FULL_SCAN,LOOKUP_SCAN
  FROM V$HEAT_MAP_SEGMENT
  WHERE object_name = 'TBL_ADO';   

OBJECT_NAME                    TRACK_TIME          SEG SEG FUL LOO
------------------------------ ------------------- --- --- --- ---
TBL_ADO                        2020-09-11 17:07:05 YES NO  YES YES

SQL> COLUMN owner FORMAT A20
SQL> COLUMN object_name FORMAT A20

SQL> SELECT OWNER,OBJECT_NAME,TRACK_TIME,SEGMENT_WRITE,FULL_SCAN,LOOKUP_SCAN FROM DBA_HEAT_MAP_SEG_HISTOGRAM WHERE OWNER='JOHN';

OWNER                OBJECT_NAME          TRACK_TIME          SEG FUL LOO
-------------------- -------------------- ------------------- --- --- ---
JOHN                 TBL_ADO              2020-09-11 16:11:28 NO  YES NO
JOHN                 TBL_ADO              2020-09-11 17:28:30 NO  YES YES
JOHN                 IDX_ADO_OBJECT_ID    2020-09-11 17:28:30 NO  NO  YES

Note: After rebooting the database or no access for certain time, these information will not be available V$HEAT_MAP_SEGMENT but some information till available on DBA_HEAT_MAP_SEG_HISTOGRAM.

After enabling the heat map functionality on database will track segment changes for all segments except for those in the SYSTEM and SYSAUX tablespaces. We can monitor Heat Map information by using following views and DBMS packages:

  • V$HEAT_MAP_SEGMENT
  • DBMS_HEAT_MAP.BLOCK_HEAT_MAP
  • DBMS_HEAT_MAP.EXTENT_HEAT_MAP
  • DBMS_HEAT_MAP.OBJECT_HEAT_MAP
  • DBMS_HEAT_MAP.SEGMENT_HEAT_MAP
  • DBMS_HEAT_MAP.TABLESPACE_HEAT_MAP
  • Below views are applicable for USER|ALL|DBA
    • {USER|ALL|DBA}_HEAT_MAP_SEG_HISTOGRAM
    • {USER|ALL|DBA}_HEAT_MAP_SEGMENT
    • {USER|ALL|DBA}_HEATMAP_TOP_OBJECTS
    • {USER|ALL|DBA}_HEATMAP_TOP_TABLESPACES

Few useful queries to view the heat map tracking information:

SELECT SUBSTR(OBJECT_NAME,1,20), SUBSTR(SUBOBJECT_NAME,1,20), TRACK_TIME, SEGMENT_WRITE,
  FULL_SCAN, LOOKUP_SCAN FROM V$HEAT_MAP_SEGMENT;

SELECT SUBSTR(OBJECT_NAME,1,20), SUBSTR(SUBOBJECT_NAME,1,20), SEGMENT_WRITE_TIME,
  SEGMENT_READ_TIME, FULL_SCAN, LOOKUP_SCAN FROM USER_HEAT_MAP_SEGMENT;

SELECT SUBSTR(OBJECT_NAME,1,20), SUBSTR(SUBOBJECT_NAME,1,20), TRACK_TIME, SEGMENT_WRITE, FULL_SCAN,
  LOOKUP_SCAN FROM USER_HEAT_MAP_SEG_HISTOGRAM;

SELECT SUBSTR(OWNER,1,20), SUBSTR(OBJECT_NAME,1,20), OBJECT_TYPE, SUBSTR(TABLESPACE_NAME,1,20),
    SEGMENT_COUNT FROM DBA_HEATMAP_TOP_OBJECTS ORDER BY SEGMENT_COUNT DESC;

SELECT SUBSTR(TABLESPACE_NAME,1,20), SEGMENT_COUNT 
    FROM DBA_HEATMAP_TOP_TABLESPACES ORDER BY SEGMENT_COUNT DESC;

SELECT relative_fno, block_id, blocks, TO_CHAR(min_writetime, 'mm-dd-yy hh-mi-ss') Mintime,
   TO_CHAR(max_writetime, 'mm-dd-yy hh-mi-ss') Maxtime, 
   TO_CHAR(avg_writetime, 'mm-dd-yy hh-mi-ss') Avgtime 
   FROM TABLE(DBMS_HEAT_MAP.EXTENT_HEAT_MAP('JOHN','TBL_ADO')) WHERE ROWNUM < 10;

Automatic Data Optimization (ADO):

Heat Map and ADO are parts of Information Lifecycle Management (ILM) and when we enable the heat map functionality, it is also enabling the Automatic Data Optimization (ADO) automatically. ADO automatically moves and compresses data according to user-defined policies based on the information collected by Heat Map.

Sometimes, DBA need to compress and move history or non-frequently used tables to slow performed storage. Up to 11g, these tasks were done manually by DBA with downtime (for these segments). But with this feature (from 12c), this can be done automatically without any impact of availability of DB.

Advantages of Oracle Database ILM:

  • Application Transparency
  • Fine-grained data
  • Low-Cost Storage
  • Enforceable Compliance Policies

ILM allows to archive data online. Consider an online archive of your data into Oracle Database for the following reasons:

  • The cost of disk is approaching that of tape, so you can eliminate the time to find the tape that contains the data and the cost of restoring that data
  • Data remains online when needed, providing you faster access to meet business requirements
  • Data online means immediate access, so fines by regulatory body for failing to produce data are less likely
  • The current application can be used to access the data, so you do not need to waste resources to build a new application
CREATE TABLESPACE TBS_ORDER DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;
CREATE TABLESPACE TBS_ORDER_MEDIUM DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;
CREATE TABLESPACE TBS_ORDER_SLOW DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER POS IDENTIFIED BY oracle;
GRANT CREATE SESSION, CREATE TABLE TO POS;

ALTER USER POS QUOTA UNLIMITED ON TBS_ORDER;
ALTER USER POS QUOTA UNLIMITED ON TBS_ORDER_MEDIUM;
ALTER USER POS QUOTA UNLIMITED ON TBS_ORDER_SLOW;
CREATE TABLE pos.order_details (
  order_no    NUMBER NOT NULL,
  order_date  DATE   NOT NULL,
  Item_no     VARCHAR2(50),
  amount 	number (10,2)
)
PARTITION BY RANGE (order_date)
(
  PARTITION order_2018_q1 VALUES LESS THAN (TO_DATE('01/04/2018', 'DD/MM/YYYY')) TABLESPACE TBS_ORDER_SLOW,
  PARTITION order_2018_q2 VALUES LESS THAN (TO_DATE('01/07/2018', 'DD/MM/YYYY')) TABLESPACE TBS_ORDER_SLOW,
  PARTITION order_2018_q3 VALUES LESS THAN (TO_DATE('01/09/2018', 'DD/MM/YYYY')) TABLESPACE TBS_ORDER_SLOW,
  PARTITION order_2018_q4 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY')) TABLESPACE TBS_ORDER_SLOW,
  PARTITION order_2019_q1 VALUES LESS THAN (TO_DATE('01/04/2019', 'DD/MM/YYYY')) TABLESPACE TBS_ORDER_MEDIUM
    ILM ADD POLICY TIER TO TBS_ORDER_SLOW READ ONLY SEGMENT AFTER 6 MONTHS OF NO ACCESS,
  PARTITION order_2019_q2 VALUES LESS THAN (TO_DATE('01/07/2019', 'DD/MM/YYYY')) TABLESPACE TBS_ORDER_MEDIUM
    ILM ADD POLICY TIER TO TBS_ORDER_SLOW READ ONLY SEGMENT AFTER 6 MONTHS OF NO ACCESS,
  PARTITION order_2019_q3 VALUES LESS THAN (TO_DATE('01/09/2019', 'DD/MM/YYYY')) TABLESPACE TBS_ORDER_FAST
    ILM ADD POLICY TIER TO TBS_ORDER_SLOW READ ONLY SEGMENT AFTER 6 MONTHS OF NO ACCESS,
  PARTITION order_2019_q4 VALUES LESS THAN (TO_DATE('01/01/2020', 'DD/MM/YYYY')) TABLESPACE TBS_ORDER_FAST
    ILM ADD POLICY TIER TO TBS_ORDER_SLOW READ ONLY SEGMENT AFTER 6 MONTHS OF NO ACCESS,
  PARTITION order_2020_q1 VALUES LESS THAN (TO_DATE('01/04/2020', 'DD/MM/YYYY')) TABLESPACE TBS_ORDER_FAST,
  PARTITION order_2020_q2 VALUES LESS THAN (TO_DATE('01/07/2020', 'DD/MM/YYYY')) TABLESPACE TBS_ORDER_FAST,
  PARTITION order_2020_q3 VALUES LESS THAN (TO_DATE('01/09/2020', 'DD/MM/YYYY')) TABLESPACE TBS_ORDER_FAST,
  PARTITION order_2020_q4 VALUES LESS THAN (TO_DATE('01/01/2021', 'DD/MM/YYYY')) TABLESPACE TBS_ORDER_FAST
)
ILM ADD POLICY ROW STORE COMPRESS BASIC SEGMENT AFTER 6 MONTHS OF NO ACCESS;
SQL> col POLICY_NAME for A20
SQL> col OBJECT_OWNER for A10
SQL> col OBJECT_NAME for A15
SQL> SELECT POLICY_NAME,OBJECT_OWNER,OBJECT_NAME,OBJECT_TYPE,INHERITED_FROM,ENABLED,DELETED 
FROM USER_ILMOBJECTS;

POLICY_NAME          OBJECT_OWN OBJECT_NAME     OBJECT_TYPE        INHERITED_FROM       ENA DEL
-------------------- ---------- --------------- ------------------ -------------------- --- ---
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE              POLICY NOT INHERITED YES NO
P12                  POS        ORDER_DETAILS   TABLE PARTITION    POLICY NOT INHERITED YES NO
P13                  POS        ORDER_DETAILS   TABLE PARTITION    POLICY NOT INHERITED YES NO
P14                  POS        ORDER_DETAILS   TABLE PARTITION    POLICY NOT INHERITED YES NO
P15                  POS        ORDER_DETAILS   TABLE PARTITION    POLICY NOT INHERITED YES NO

17 rows selected.

SQL> ALTER TABLE pos.order_details MODIFY PARTITION order_2020_q1
  ILM ADD POLICY TIER TO TBS_ORDER_SLOW READ ONLY SEGMENT AFTER 6 MONTHS OF NO ACCESS;

SQL> ALTER TABLE pos.order_details MODIFY PARTITION order_2020_q2
  ILM ADD POLICY TIER TO TBS_ORDER_SLOW READ ONLY SEGMENT AFTER 6 MONTHS OF NO ACCESS;
SQL> SELECT POLICY_NAME,OBJECT_OWNER,OBJECT_NAME,OBJECT_TYPE,INHERITED_FROM,ENABLED,DELETED
FROM USER_ILMOBJECTS;  

POLICY_NAME          OBJECT_OWN OBJECT_NAME     OBJECT_TYPE        INHERITED_FROM       ENA DEL
-------------------- ---------- --------------- ------------------ -------------------- --- ---
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE PARTITION    TABLE                YES NO
P11                  POS        ORDER_DETAILS   TABLE              POLICY NOT INHERITED YES NO
P12                  POS        ORDER_DETAILS   TABLE PARTITION    POLICY NOT INHERITED YES NO
P13                  POS        ORDER_DETAILS   TABLE PARTITION    POLICY NOT INHERITED YES NO
P14                  POS        ORDER_DETAILS   TABLE PARTITION    POLICY NOT INHERITED YES NO
P15                  POS        ORDER_DETAILS   TABLE PARTITION    POLICY NOT INHERITED YES NO
P16                  POS        ORDER_DETAILS   TABLE PARTITION    POLICY NOT INHERITED YES NO
P17                  POS        ORDER_DETAILS   TABLE PARTITION    POLICY NOT INHERITED YES NO

19 rows selected.
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.

These parameters can be altered using the DBMS_ILM_ADMIN.CUSTOMIZE_ILM procedure:

BEGIN
  DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.retention_time, 30);
END;
/
SQL> exec dbms_ilm_admin.customize_ilm(dbms_ilm_admin.tbs_percent_free,20);

PL/SQL procedure successfully completed.

SQL> exec dbms_ilm_admin.customize_ilm(dbms_ilm_admin.tbs_percent_used, 80);

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             80
TBS PERCENT FREE             20
POLICY TIME                   0

8 rows selected.

To disable/delete/modify IML Policy at 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;

To disable/delete/modify IML Policy at 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;

1 comments

    • Nadim on at
    • #

    Very nice and useful.
    Thanks a lot

Comments have been disabled.