Oracle DBMS_HEAT_MAP
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose This package provides heatmap information at block/extent/segment object and tablespace levels. It contains the definitions for processing heatmap for top N objects and tablespaces.

The execution privilege is granted to PUBLIC. Procedures in this package run under the caller security. The user must have ANALYZE privilege on the object.
AUTHID CURRENT_USER
Data Types TYPE hm_bls_record IS RECORD (
owner           VARCHAR2(128),
segment_name    VARCHAR2(128),
partition_name  VARCHAR2(128),
tablespace_name VARCHAR2(30),
file_id         NUMBER,
relative_fno    NUMBER,
block_id        NUMBER,
writetime       DATE);

TYPE hm_bls_row IS TABLE OF hm_bls_record;
TYPE hm_bls_tabidx IS TABLE OF hm_bls_record INDEX BY PLS_INTEGER;

TYPE hm_els_record IS RECORD (
owner           VARCHAR2(128),
segment_name    VARCHAR2(128),
partition_name  VARCHAR2(128),
tablespace_name VARCHAR2(30),
file_id         NUMBER,
relative_fno    NUMBER,
block_id        NUMBER,
blocks          NUMBER,
bytes           NUMBER,
min_writetime   DATE,
max_writetime   DATE,
avg_writetime   DATE);

TYPE hm_els_row IS TABLE OF hm_els_record;
TYPE hm_els_tabidx IS TABLE OF hm_els_record INDEX BY PLS_INTEGER;

TYPE hm_object_row IS RECORD (
owner           VARCHAR2(100),
segment_name    VARCHAR2(100),
partition_name  VARCHAR2(100),
tablespace_name VARCHAR2(100),
segment_type    VARCHAR2(20),
segment_size    NUMBER,
min_writetime   DATE,
max_writetime   DATE,
avg_writetime   DATE,
min_readtime    DATE,
max_readtime    DATE,
avg_readtime    DATE,
min_ftstime     DATE,
max_ftstime     DATE,
avg_ftstime     DATE,
min_lookuptime  DATE,
max_lookuptime  DATE,
avg_lookuptime  DATE);

TYPE hm_object_table IS TABLE OF hm_object_row;
TYPE hm_object_tabidx IS TABLE OF hm_object_row INDEX BY PLS_INTEGER;

TYPE hm_tablespace_row IS RECORD (
tablespace_name VARCHAR2(30),
segment_count   NUMBER,
allocated_bytes NUMBER,
min_writetime   DATE,
max_writetime   DATE,
avg_writetime   DATE,
min_readtime    DATE,
max_readtime    DATE,
avg_readtime    DATE,
min_ftstime     DATE,
max_ftstime     DATE,
avg_ftstime     DATE,
min_lookuptime  DATE,
max_lookuptime  DATE,
avg_lookuptime  DATE);

TYPE hm_tablespace_table IS TABLE OF hm_tablespace_row;
TYPE hm_tablespace_tabidx IS TABLE OF hm_tablespace_row INDEX BY PLS_INTEGER;
Dependencies
ALL_HEAT_MAP_SEGMENT DBA_LOBS SEG$
ALL_HEAT_MAP_SEG_HISTOGRAM DBA_SEGMENTS SYS_DBA_SEGS
ALL_OBJECTS DBA_TABLES USER_HEAT_MAP_SEGMENT
CDB_HEATMAP_TOP_OBJECTS DBA_TABLESPACES USER_HEAT_MAP_SEG_HISTOGRAM
CDB_HEATMAP_TOP_TABLESPACES DBA_TABLESPACE_USAGE_METRICS USER_TABLESPACES
CDB_HEAT_MAP_SEGMENT DBMS_HEAT_MAP_INTERNAL V$HEAT_MAP_SEGMENT
CDB_HEAT_MAP_SEG_HISTOGRAM DBMS_PRIV_CAPTURE WRI$_HEATMAP_TOPN_DEP1
DBA_HEATMAP_TOP_OBJECTS DBMS_SPACE WRI$_HEATMAP_TOPN_DEP2
DBA_HEATMAP_TOP_TABLESPACES DBMS_STANDARD WRI$_HEATMAP_TOP_OBJECTS
DBA_HEAT_MAP_SEGMENT HEAT_MAP_EXTENT_STAT$ WRI$_HEATMAP_TOP_TABLESPACES
DBA_HEAT_MAP_SEG_HISTOGRAM HEAT_MAP_STAT$ WRI$_TOPN_METADATA
DBA_INDEXES PLITBLM  
Documented Yes
First Available 12.1.0.1
Initialization Parameter ALTER SESSION SET heat_map = <OFF | ON>;
ALTER SYSTEM SET heat_map = <OFF | ON> SCOPE=<BOTH | MEMORY | SPFILE> ....;
ALTER SESSION SET heat_map = ON;

ALTER SYSTEM SET heat_map = on SCOPE=BOTH SID='*';
Security Model Owned by SYS with execute granted to PUBLIC. To run the user must have the ANALYZE privilege on the object.
Source {ORACLE_HOME}/rdbms/admin/dbmsspu.sql
Subprograms
 
AUTO_ADVISOR_HEATMAP_JOB
Advisor job to materialize heat maps dbms_heat_map.auto_advisor_heatmap_job(topn IN NUMBER DEFAULT 100);
exec dbms_heat_map.auto_advisor_heatmap_job;

SELECT * FROM wri$_heatmap_top_tablespaces;

SELECT * FROM wri$_heatmap_top_objects;
 
BLOCK_HEAT_MAP
Returns the block level  ILM statistics for a table segment. It returns no information for segment types that are not data. The stat returned today is the latest modification time of the block. dbms_heat_map.block_heat_map(
owner          IN VARCHAR2,
segment_name   IN VARCHAR2,
partition_name IN VARCHAR2 DEFAULT NULL,
sort_columnid  IN NUMBER   DEFAULT NULL,
sort_order     IN VARCHAR2 DEFAULT NULL)
RETURN hm_bls_row PIPELINED;
SELECT * FROM TABLE(dbms_heat_map.block_heat_map('SYS', 'OBJ$'));

SELECT relative_fno, block_id
FROM TABLE(dbms_heat_map.block_heat_map('SYS', 'OBJ$'));
 
EXTENT_HEAT_MAP
Returns the extent level ILM statistics for a table segment. It returns no information for segment types that are not data. Aggregates at extent level including minimum modification time and maximum modification time are returned. dbms_heat_map.extent_heat_map(
owner          IN VARCHAR2,
segment_name   IN VARCHAR2,
partition_name IN VARCHAR2 DEFAULT NULL)
RETURN hm_els_row PIPELINED;
SELECT * FROM TABLE(dbms_heat_map.extent_heat_map('SYS', 'SOURCE$'));

SELECT relative_fno, block_id, blocks, bytes
FROM TABLE(dbms_heat_map.extent_heat_map('SYS', 'SOURCE$'));
 
OBJECT_HEAT_MAP
Returns the minimum, maximum and average access times for all the segments belonging to the object. The object must be a table. dbms_heat_map.object_heat_map(
object_owner IN VARCHAR2,
object_name  IN VARCHAR2)
RETURN hm_object_table PIPELINED;
SELECT * FROM TABLE(dbms_heat_map.object_heat_map('SYS', 'SOURCE$'));

SELECT tablespace_name, segment_name, segment_type, segment_size, avg_readtime, max_lookuptime
FROM TABLE(dbms_heat_map.object_heat_map('SYS', 'SOURCE$'));
 
SEGMENT_HEAT_MAP
Returns the heatmap attributes for a named segment dbms_heat_map.segment_heat_map(
tablespace_id  IN  NUMBER,
header_file    IN  NUMBER,
header_block   IN  NUMBER,
segment_objd   IN  NUMBER,
min_writetime  OUT DATE,
max_writetime  OUT DATE,
avg_writetime  OUT DATE,
min_readtime   OUT DATE,
max_readtime   OUT DATE,
avg_readtime   OUT DATE,
min_ftstime    OUT DATE,
max_ftstime    OUT DATE,
avg_ftstime    OUT DATE,
min_lookuptime OUT DATE,
max_lookuptime OUT DATE,
avg_lookuptime OUT DATE);
conn / as sysdba

SELECT tablespace_name, header_file, header_block
FROM dba_segments
WHERE segment_name = 'SOURCE$';

SELECT ts#
FROM ts$
WHERE name = 'SYSTEM';

SELECT data_object_id
FROM dba_objects
WHERE object_name = 'SOURCE$';

DECLARE
 minwr  DATE;
 maxwr  DATE;
 avgwr  DATE;
 minrd  DATE;
 maxrd  DATE;
 avgrd  DATE;
 minfts DATE;
 maxfts DATE;
 avgfts DATE;
 minlu  DATE;
 maxlu  DATE;
 avglu  DATE;
BEGIN
  dbms_heat_map.segment_heat_map(0, 1, 2296, 294, minwr, maxwr, avgwr, minrd, maxrd, avgrd, minfts, maxfts, avgfts, minlu, maxlu, avglu);

  dbms_output.put_line(minwr);
  dbms_output.put_line(maxwr);
  dbms_output.put_line(avgwr);
  dbms_output.put_line(minrd);
  dbms_output.put_line(maxrd);
  dbms_output.put_line(avgrd);
  dbms_output.put_line(minfts);
  dbms_output.put_line(maxfts);
  dbms_output.put_line(avgfts);
  dbms_output.put_line(minlu);
  dbms_output.put_line(maxlu);
  dbms_output.put_line(avglu);
END;
/
 
TABLESPACE_HEAT_MAP
Returns the minimum, maximum and average access times for all the segments in  the tablespace dbms_heat_map.tablespace_heat_map(tablespace_name IN VARCHAR2)
RETURN hm_tablespace_table PIPELINED;
conn / as sysdba

SELECT * FROM TABLE(dbms_heat_map.tablespace_heat_map('SYSTEM'));

SELECT segment_count, allocated_bytes
FROM TABLE(dbms_heat_map.tablespace_heat_map('SYSTEM'));

Related Topics
Automatic Data Optimization (ADO)
Data Files
DBMS_HEAT_MAP_INTERNAL
DBMS_ILM
DBMS_ILM_ADMIN
Packages
Segments
Tablespaces
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved