Oracle DBMS_HEAT_MAP
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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_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_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_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;
Dependencies
ALL_HEAT_MAP_SEGMENT DBA_LOBS SYS_DBA_SEGS
ALL_HEAT_MAP_SEG_HISTOGRAM DBA_SEGMENTS TS$
CDB_HEATMAP_TOP_OBJECTS DBA_TABLES USER_HEAT_MAP_SEGMENT
CDB_HEAT_MAP_TOP_TABLESPACES DBA_TABLESPACES USER_HEAT_MAP_SEG_HISTOGRAM
CDB_HEAT_MAP_SEGMENT DBA_TABLESPACE_USAGE_METRICS V$HEAT_MAP_SEGMENT
CDB_HEAT_MAP_SEG_HISTOGRAM DBMS_OUTPUT WRI$_HEATMAP_TOPN_DEP1
DBA_HEAT_MAP_TOP_OBJECTS DBMS_SPACE WRI$_HEATMAP_TOPN_DEP2
DBA_HEAT_MAP_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 SEG$ X$KTFSRI
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 10);
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
 maxwt DATE;
 minwt DATE;
 avgwt DATE;
 maxrt DATE;
 minrt DATE;
 avgrt DATE;
 x     DATE;
BEGIN
  dbms_heat_map.segment_heat_map(0, 1, 2296, 294, minwt, maxwt, avgwt, minrt, maxrt, avgrt, x, x, x, x, x, x);

  dbms_output.put_line(minwt);
  dbms_output.put_line(maxwt);
  dbms_output.put_line(avgwt);
  dbms_output.put_line(x);
END;
/

-- for reasons under investigation no data is returned
 
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_ILM
DBMS_ILM_ADMIN
Packages
Segments
Tablespaces

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-2014 Daniel A. Morgan All Rights Reserved