Oracle | DBMS_HEAT

Oracle DBMS_HEAT_MAP 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 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_bls_tabidx IS TABLE OF hm_bls_record INDEX BY PLS_INTEGER; 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_els_tabidx IS TABLE OF hm_els_record INDEX BY PLS_INTEGER; 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_object_tabidx IS TABLE OF hm_object_row INDEX BY PLS_INTEGER; 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; TYPE hm_tablespace_tabidx IS TABLE OF hm_tablespace_row INDEX BY PLS_INTEGER;

Dependencies

ALL_HEAT_MAP_SEGMENT	DBA_LOBS	SEG$
ALL_HEAT_MAP_SEG_HISTOGRAM	DBA_SEGMENTS	SYS_DBA_SEGS
ALL_OBJECTS	DBA_TABLES	USER_HEAT_MAP_SEGMENT
CDB_HEATMAP_TOP_OBJECTS	DBA_TABLESPACES	USER_HEAT_MAP_SEG_HISTOGRAM
CDB_HEATMAP_TOP_TABLESPACES	DBA_TABLESPACE_USAGE_METRICS	USER_TABLESPACES
CDB_HEAT_MAP_SEGMENT	DBMS_HEAT_MAP_INTERNAL	V$HEAT_MAP_SEGMENT
CDB_HEAT_MAP_SEG_HISTOGRAM	DBMS_PRIV_CAPTURE	WRI$_HEATMAP_TOPN_DEP1
DBA_HEATMAP_TOP_OBJECTS	DBMS_SPACE	WRI$_HEATMAP_TOPN_DEP2
DBA_HEATMAP_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	PLITBLM

Documented Yes: Packages and Types Reference

First Available 12.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	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.auto_advisor_heatmap_job(topn IN NUMBER DEFAULT 100);

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

       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.segment_heat_map(0, 1, 2296, 294, 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.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'));