Heat Map -- Fine Grained Data Usage Tracking Heat Map is a new feature in Oracle Database 12c that automatically tracks usage information at the row and segment levels. Data modification times are tracked at the row level and aggregated to the block level, and modification times, full table scan times, and index lookup times are tracked at the segment level. Heat Map gives you a detailed view of how your data is being accessed, and how access patterns are changing over time. Programmatic access to Heat Map data is available through a set of PL/SQL table functions, as well as through data dictionary views
We can enable heat map feature by setting database parameter "heat_map=on”. This is dynamic parameter can be altered any time
SQL> alter system set heat_map=on scope=both;
System altered.
SQL> show parameter heat
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
heat_map string ON
Heat map statistics are collected in memory are flushed into View V$HEAT_MAP_SEGMENT
Scheduler jobs information is written to HEAT_MAP_STAT$, DBA_HEAT_MAP_SEG_HISTOGRAM and DBA_HEAT_MAP_SEGMENT
CREATE OR REPLACE PROCEDURE set_stat (object_id number,
data_object_id number,
n_days number,
p_ts# number,
p_segment_access number)
as
begin
insert into sys.heat_map_stat$
(obj#,
dataobj#,
track_time,
segment_access,
ts#)
values
(object_id,
data_object_id,
sysdate - n_days,
p_segment_access,
p_ts# );
commit;
end;
After enabling heat map tracking, we set the heat map tracking start time back 30 days to ensure statistics logged after this time are valid and considered by Automatic Data Optimization (ADO).
SQL> exec dbms_ilm_admin.set_heat_map_start(start_date => sysdate - 30)
Query's used to read heat map
select OBJECT_NAME,SEGMENT_WRITE_TIME , SEGMENT_READ_TIME, FULL_SCAN
FROM dba_heat_map_segment
WHERE OBJECT_NAME='WORLD'
AND OWNER = 'UNIVERSE';
col "Segment write" format A14
col "Full Scan" format A12
col "Lookup Scan" format a12
select object_name, track_time "Tracking Time",
segment_write "Segment write",
full_scan "Full Scan",
lookup_scan "Lookup Scan"
from DBA_HEAT_MAP_SEG_HISTOGRAM
where object_name='WORLD'
and owner = 'UNIVERSE';
By analyzing Query' s we can enable ILM ADO policies features for the table's
ADO operation will initiate where there is no access, or no modification to the tables, the row data is advanced compression on database which helps to reclaim space on table spaces
By analyzing Query' s we can enable ILM ADO policies features for the table's
ADO operation will initiate where there is no access, or no modification to the tables, the row data is advanced compression on database which helps to reclaim space on table spaces
ALTER TABLE world ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;
ALTER TABLE world ILM ADD POLICY
ROW STORE COMPRESS ADVANCED ROW
AFTER 3 DAYS OF NO MODIFICATION;
References:
---Nikhil Tatineni--
---12c--
