8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- V$HEAT_MAP_SEGMENT
- {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
- 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
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.
- {DBA|USER}_ILMDATAMOVEMENTPOLICIES
- {DBA|USER}_ILMTASKS
- {DBA|USER}_ILMEVALUATIONDETAILS
- {DBA|USER}_ILMOBJECTS
- {DBA|USER}_ILMPOLICIES
- {DBA|USER}_ILMRESULTS
- DBA_ILMPARAMETERS
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...