Oracle Information Lifecycle Management (ILM) is the processes by which we can manage data from creation to deletion. It allows us a method to increase speed of access and to acquire metadata about use. The two features which support ILM in Oracle 12c are Heat Maps and Automatic Data Optimization. This procedure will cover the use of Heat Maps within a 12c RDBMS. Heat Maps track use information about data at the row and segment level. NOTE: Heat Maps are not supported in CDB multitenant databases.
- Logon to your Oracle server as the Oracle software owner and logon to SQLPLUS
[root@linux2 oracle]# su – oracle
Last login: Wed Sep 7 14:47:35 EST 2016 on pts/1
Enter database to use:
1 – ORCL
2 – CDB1
Option >
1
[oracle@linux2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 7 14:50:11 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
- Check to see if Heat Maps is enabled for your database and if not turn it on with the command: “alter system set heat_map=on;”
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
SQL>
- Create user test with default tablespace of users.
create user test identified by test
default tablespace users quota unlimited on users;
grant dba to test;
- Create table test_hm with column col_a and col_b.
create table test.test_hm(col_a number,
col_b varchar2(10))
partition by range (col_a)
(partition less_then_50 values less than (‘50000000’)
tablespace users);
- Insert 20 million records to test_hm with the following pl/sql block.
Declare
val_a number:=1;
val_b varchar2(10):=’a’;
begin
while val_a<20000001
loop
insert into test.test_hm(col_a, col_b)
values(val_a, val_b);
val_a:=val_a+1;
if val_b=’z’
then
val_b:=’a’;
else
val_b:=chr(ascii(val_b) + 1);
end if;
end loop;
commit;
end;
/
- Check records for current heat maps in the view
column NAME format a10
column SUB_NAME format a15
column WRITE format a14
column READ format a14
select object_name Name,
SUBOBJECT_NAME SUB_NAME,
to_char(segment_write_time,’DD-MON HH:MI’) write,
to_char(segment_read_time,’DD-MON HH:MI’) read
from dba_heat_map_segment where object_name=’TEST_HM’;
SQL> SQL> column NAME format a10
SQL> column SUB_NAME format a15
SQL> column WRITE format a14
SQL> column READ format a14
SQL> select object_name Name,
2 SUBOBJECT_NAME SUB_NAME,
3 to_char(segment_write_time,’DD-MON HH:MI’) write,
4 to_char(segment_read_time,’DD-MON HH:MI’) read
5 from dba_heat_map_segment where object_name=’TEST_HM’;
NAME SUB_NAME WRITE READ
———- ————— ————– ————–
TEST_HM LESS_THEN_50 13-SEP 01:54
SQL>
- Create sub_partitions for table test_hm for col_a values ( <5 million, < 10 million, < 15 million, and < 20 million)
ALTER TABLE test.test_hm split PARTITION less_then_50 at (5000000) into
(PARTITION less_than_5, PARTITION greater_than_5);
ALTER TABLE test.test_hm split PARTITION greater_than_5 at (10000000) into
(PARTITION less_than_10, PARTITION greater_than_10);
ALTER TABLE test.test_hm split PARTITION greater_than_10 at (15000000) into
(PARTITION less_than_15, PARTITION greater_than_15);
ALTER TABLE test.test_hm split PARTITION greater_than_15 at (20000000) into
(PARTITION less_than_20, PARTITION greater_than_20);
- No perform a select on data where a sort operation would be require, we used the below statement.
select * from test.test_hm where col_a between 12000000 and 19000000 order by col_b;
- Now execute the statement from step 6 to see heat map information about the scans which are occurring in our table.
SQL> column NAME format a10
SQL> column SUB_NAME format a15
SQL> column WRITE format a14
SQL> column READ format a14
SQL> select object_name Name,
2 SUBOBJECT_NAME SUB_NAME,
to_char(segment_write_time,’DD-MON HH:MI’) write,
to_char(segment_read_time,’DD-MON HH:MI’) read
3 4 5 from dba_heat_map_segment where object_name=’TEST_HM’;
NAME SUB_NAME WRITE READ
———- ————— ————– ————–
TEST_HM LESS_THAN_20
TEST_HM LESS_THAN_15
TEST_HM LESS_THAN_5 13-SEP 02:02
SQL>
- This completes our coverage of Heat Maps in Oracle 12c
Larry Catt
OCP