Oracle DBMS_HEAT_MAP_INTERNAL
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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 19c
What's New In 20c-21c

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