DBA Hub

📋Steps in this guide1/3

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

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).

oracle 12cconfigurationintermediate
by OracleDba
12 views
1

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 parameter, that can be set at the system or session level. Display the current setting of the parameter at the PDB level. Enable the heat map for the PDB. Notice that the heat map is still disabled at the CBD level. Once the heat map functionality is enable the database will track segment changes for all segments except for those in the and 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. We can now run some queries to see the tracked information. The heat map information can be really useful for identifying the busy and quiet segments in your database.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

SHOW PARAMETER heat_map;

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

ALTER SYSTEM SET heat_map = ON;

SHOW PARAMETER heat_map;

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

CONN / AS SYSDBA

SHOW PARAMETER heat_map;

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

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;

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>
2

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. 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. We can see the policies have been applied using the view. 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. We can disable, delete or modify policies using the following commands. 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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
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;

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
;

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>

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;

-- 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;
3

ILM ADO Parameters

The full list of ILM ADO Parameters are documented here . They can be displayed using the following query. These parameters can be altered using the procedure. There is a constant defined in the package for each parameter, with the name matching the parameter name with the whitespaces replaced by "_". For more information see: - Implementing an ILM Strategy With Heat Map and ADO - HEAT_MAP - DBMS_HEAT_MAP - DBMS_ILM - DBMS_ILM_ADMIN Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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>

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!