Oracle | DBMS_HEAT_MAP

Oracle DBMS_HEAT_MAP_INTERNAL Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

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	SEG$
DBA_LOBS	SYS_DBA_SEGS
DBA_SEGMENTS	TS$
DBA_TABLES	V$PARAMETER
DBA_TABLESPACES	WRI$_HEATMAP_TOPN_DEP1
DBA_TABLESPACE_USAGE_METRICS	WRI$_HEATMAP_TOPN_DEP2
DBMS_ASSERT	WRI$_HEATMAP_TOP_OBJECTS
DBMS_HEAT_MAP	WRI$_HEATMAP_TOP_TABLESPACES
DBMS_OUTPUT	WRI$_TOPN_METADATA
DBMS_STANDARD	X$KTFSRI
HEAT_MAP_STAT$

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_CURSOR_CLOSE	GET_ORDER BY	TABLESPACE_HEAT_MAP
BLOCK_HEAT_MAP_CURSOR_OPEN	OBJECT_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_CURSOR_CLOSE (new 23ai)

Undocumented dbms_heat_map_internal.block_heat_map_cursor_close(block_hm_cursor IN REF CURSOR);

TBD

BLOCK_HEAT_MAP_CURSOR_OPEN (new 23ai)

Undocumented dbms_heat_map_internal.block_heat_map_cursor_open( owner_name IN VARCHAR2, seg_name IN VARCHAR2, part_name IN VARCHAR2, sort_columnid IN NUMBER, sort_order IN VARCHAR2);

TBD

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;

      /

GET_ORDER_BY (new 23ai)

Undocumented dbms_heat_map_internal.get_order_by( sort_columnid IN NUMBER, sort_order IN VARCHAR2) RETURN VARCHAR2;

TBD

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;

      /