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 will be available on generic Linux platforms in January and soon on AIX and Windows

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;

      /