Oracle DBMS_HEAT_MAP_INTERNAL
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Internal support package for DBMS_HEAT_MAP_INTERNAL managing heat maps for Automatic Data Optimization (ADO) and Integrated Lifecycle Management (ILM).
AUTHID DEFINER
Dependencies
DBA_INDEXES DBMS_OUTPUT WRI$_HEATMAP_TOPN_DEP1
DBA_LOBS DBMS_STANDARD WRI$_HEATMAP_TOPN_DEP2
DBA_SEGMENTS HEAT_MAP_STAT$ WRI$_HEATMAP_TOP_OBJECTS
DBA_TABLES SEG$ WRI$_HEATMAP_TOP_TABLESPACES
DBA_TABLESPACES SYS_DBA_SEGS WRI$_TOPN_METADATA
DBA_TABLESPACE_USAGE_METRICS TS$ X$KTFSRI
DBMS_HEAT_MAP V$PARAMETER  
Documented No
First Available 12.2
Security Model Owned by SYS with no privileges granted.
Source {ORACLE_HOME}/rdbms/admin/prvtspcu.plb
Subprograms
AUTO_ADVISOR_HEATMAP_JOB EXTENT_HEAT_MAP SEGMENT_HEAT_MAP
BLOCK_HEAT_MAP OBJECT_HEAT_MAP TABLESPACE_HEAT_MAP
 
AUTO_ADVISOR_HEATMAP_JOB
Advisor job to materialize heat maps dbms_heat_map_internal.auto_advisor_heatmap_job(topn IN NUMBER);
exec dbms_heat_map_internal.auto_advisor_heatmap_job(100);

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_internal.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_tabidx;
DECLARE
 retVal dbms_heat_map.hm_bls_tabidx;
BEGIN
  retVal := dbms_heat_map_internal.block_heat_map('SYS', 'OBJ$');
  dbms_output.put_line(retVal(1).owner);
  dbms_output.put_line(retVal(1).segment_name);
  dbms_output.put_line(retVal(1).tablespace_name);
  dbms_output.put_line(retVal(1).file_id);
  dbms_output.put_line(retVal(1).relative_fno);
  dbms_output.put_line(retVal(1).block_id);
  dbms_output.put_line(retVal(1).writetime);
END;
/
 
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_internal.extent_heat_map(
owner          IN VARCHAR2,
segment_name   IN VARCHAR2,
partition_name IN VARCHAR2 DEFAULT NULL)
RETURN hm_els_tabidx;
DECLARE
 retVal dbms_heat_map.hm_els_tabidx;
BEGIN
  retVal := dbms_heat_map_internal.extent_heat_map('SYS', 'OBJ$');
  dbms_output.put_line(retVal(1).owner);
  dbms_output.put_line(retVal(1).segment_name);
  dbms_output.put_line(retVal(1).tablespace_name);
  dbms_output.put_line(retVal(1).file_id);
  dbms_output.put_line(retVal(1).relative_fno);
  dbms_output.put_line(retVal(1).block_id);
  dbms_output.put_line(retVal(1).blocks);
  dbms_output.put_line(retVal(1).bytes);
  dbms_output.put_line(retVal(1).min_writetime);
  dbms_output.put_line(retVal(1).max_writetime);
  dbms_output.put_line(retVal(1).avg_writetime);
END;
/
 
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_internal.object_heat_map(
object_owner IN VARCHAR2,
object_name  IN VARCHAR2)
RETURN hm_object_tabidx;
DECLARE
 retVal dbms_heat_map.hm_object_tabidx;
BEGIN
  retVal := dbms_heat_map_internal.object_heat_map('SYS', 'OBJ$');
  dbms_output.put_line(retVal(1).owner);
  dbms_output.put_line(retVal(1).segment_name);
  dbms_output.put_line(retVal(1).tablespace_name);
  dbms_output.put_line(retVal(1).segment_type);
  dbms_output.put_line(retVal(1).segment_size);
  dbms_output.put_line(retVal(1).min_writetime);
  dbms_output.put_line(retVal(1).max_writetime);
  dbms_output.put_line(retVal(1).avg_writetime);
  dbms_output.put_line(retVal(1).min_readtime);
  dbms_output.put_line(retVal(1).max_readtime);
  dbms_output.put_line(retVal(1).avg_readtime);
  dbms_output.put_line(retVal(1).min_ftstime);
  dbms_output.put_line(retVal(1).max_ftstime);
  dbms_output.put_line(retVal(1).avg_ftstime);
  dbms_output.put_line(retVal(1).min_lookuptime);
  dbms_output.put_line(retVal(1).max_lookuptime);
  dbms_output.put_line(retVal(1).avg_lookuptime);
END;
/
 
SEGMENT_HEAT_MAP
Returns the heatmap attributes for a named segment dbms_heat_map_internal.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 ts#
FROM ts$
WHERE name = 'SYSTEM';

 TS#
----
   1

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

TABLESPACE_NAME  HEADER_FILE HEADER_BLOCK
---------------- ----------- ------------
         SYSTEM            1         2760

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

DATA_OBJECT_ID
--------------
           356

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_internal.segment_heat_map(0, 1, 2760, 356, 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_internal.tablespace_heat_map(
tablepace_name IN VARCHAR2)
RETURN hm_tablespace_tabidx;
DECLARE
 retVal dbms_heat_map.hm_tablespace_tabidx;
BEGIN
  retVal := dbms_heat_map_internal.tablespace_heat_map('SYSAUX');
  dbms_output.put_line(retVal(1).tablespace_name);
  dbms_output.put_line(retVal(1).segment_count);
  dbms_output.put_line(retVal(1).allocated_bytes);
  dbms_output.put_line(retVal(1).min_writetime);
  dbms_output.put_line(retVal(1).max_writetime);
  dbms_output.put_line(retVal(1).avg_writetime);
  dbms_output.put_line(retVal(1).min_readtime);
  dbms_output.put_line(retVal(1).max_readtime);
  dbms_output.put_line(retVal(1).avg_readtime);
  dbms_output.put_line(retVal(1).min_ftstime);
  dbms_output.put_line(retVal(1).max_ftstime);
  dbms_output.put_line(retVal(1).avg_ftstime);
  dbms_output.put_line(retVal(1).min_lookuptime);
  dbms_output.put_line(retVal(1).max_lookuptime);
  dbms_output.put_line(retVal(1).avg_lookuptime);
END;
/

Related Topics
Automatic Data Optimization (ADO)
Built-in Functions
Built-in Packages
DBMS_HEAT_MAP
DBMS_ILM
DBMS_ILM_ADMIN
What's New In 21c
What's New In 23c

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