Heat map tracking data is viewed with V$*, ALL*, DBA*, and USER* heat map views.
Example 5-1 shows examples of information provided by heat map views. The V$HEAT_MAP_SEGMENT view displays real-time segment access information. The ALL_, DBA_, and USER_HEAT_MAP_SEGMENT views display the latest segment access time for all segments visible to the user. The ALL_, DBA_, and USER_HEAT_MAP_SEG_HISTOGRAM views display segment access information for all segments visible to the user. The DBA_HEATMAP_TOP_OBJECTS view displays heat map information for the top most active objects. The DBA_HEATMAP_TOP_TABLESPACES view displays heat map information for the top most active tablespaces.
Oracle Database Reference for information about Heat Map views
/* enable heat map tracking if necessary*/
SELECT SUBSTR(OBJECT_NAME,1,20), SUBSTR(SUBOBJECT_NAME,1,20), TRACK_TIME, SEGMENT_WRITE,
FULL_SCAN, LOOKUP_SCAN FROM V$HEAT_MAP_SEGMENT;
SUBSTR(OBJECT_NAME,1 SUBSTR(SUBOBJECT_NAM TRACK_TIM SEG FUL LOO
-------------------- -------------------- --------- --- --- ---
SALES SALES_Q1_1998 01-NOV-12 NO NO NO
SALES SALES_Q3_1998 01-NOV-12 NO NO NO
SALES SALES_Q2_2000 01-NOV-12 NO NO NO
SALES SALES_Q3_1999 01-NOV-12 NO NO NO
SALES SALES_Q2_1998 01-NOV-12 NO NO NO
SALES SALES_Q2_1999 01-NOV-12 NO NO NO
SALES SALES_Q4_2001 01-NOV-12 NO NO NO
SALES SALES_Q1_1999 01-NOV-12 NO NO NO
SALES SALES_Q4_1998 01-NOV-12 NO NO NO
SALES SALES_Q1_2000 01-NOV-12 NO NO NO
SALES SALES_Q1_2001 01-NOV-12 NO NO NO
SALES SALES_Q2_2001 01-NOV-12 NO NO NO
SALES SALES_Q3_2000 01-NOV-12 NO NO NO
SALES SALES_Q4_2000 01-NOV-12 NO NO NO
EMPLOYEES 01-NOV-12 NO NO NO
...
SELECT SUBSTR(OBJECT_NAME,1,20), SUBSTR(SUBOBJECT_NAME,1,20), SEGMENT_WRITE_TIME,
SEGMENT_READ_TIME, FULL_SCAN, LOOKUP_SCAN FROM USER_HEAT_MAP_SEGMENT;
SUBSTR(OBJECT_NAME,1 SUBSTR(SUBOBJECT_NAM SEGMENT_W SEGMENT_R FULL_SCAN LOOKUP_SC
-------------------- -------------------- --------- --------- --------- ---------
SALES SALES_Q1_1998 30-OCT-12 01-NOV-12
SALES SALES_Q1_1998 30-OCT-12 01-NOV-12
SALES SALES_Q1_1998 30-OCT-12 01-NOV-12
SALES SALES_Q1_1998 30-OCT-12 01-NOV-12
SALES SALES_Q1_1998 30-OCT-12 01-NOV-12
SALES SALES_Q1_1998 30-OCT-12 01-NOV-12
...
SELECT SUBSTR(OBJECT_NAME,1,20), SUBSTR(SUBOBJECT_NAME,1,20), TRACK_TIME, SEGMENT_WRITE, FULL_SCAN,
LOOKUP_SCAN FROM USER_HEAT_MAP_SEG_HISTOGRAM;
SUBSTR(OBJECT_NAME,1 SUBSTR(SUBOBJECT_NAM TRACK_TIM SEG FUL LOO
-------------------- -------------------- --------- --- --- ---
SALES SALES_Q1_1998 31-OCT-12 NO NO YES
SALES SALES_Q1_1998 01-NOV-12 NO NO YES
SALES SALES_Q1_1998 30-OCT-12 NO YES YES
SALES SALES_Q2_1998 01-NOV-12 NO NO YES
SALES SALES_Q2_1998 31-OCT-12 NO NO YES
SALES SALES_Q2_1998 30-OCT-12 NO YES YES
SALES SALES_Q3_1998 01-NOV-12 NO NO YES
SALES SALES_Q3_1998 30-OCT-12 NO YES YES
SALES SALES_Q3_1998 31-OCT-12 NO NO YES
SALES SALES_Q4_1998 01-NOV-12 NO NO YES
SALES SALES_Q4_1998 31-OCT-12 NO NO YES
SALES SALES_Q4_1998 30-OCT-12 NO YES YES
SALES SALES_Q1_1999 01-NOV-12 NO NO YES
SALES SALES_Q1_1999 31-OCT-12 NO NO YES
...
SELECT SUBSTR(OWNER,1,20), SUBSTR(OBJECT_NAME,1,20), OBJECT_TYPE, SUBSTR(TABLESPACE_NAME,1,20),
SEGMENT_COUNT FROM DBA_HEATMAP_TOP_OBJECTS ORDER BY SEGMENT_COUNT DESC;
SUBSTR(OWNER,1,20) SUBSTR(OBJECT_NAME,1 OBJECT_TYPE SUBSTR(TABLESPACE_NA SEGMENT_COUNT
-------------------- -------------------- ------------------ -------------------- -------------
SH SALES TABLE EXAMPLE 96
SH COSTS TABLE EXAMPLE 48
PM ONLINE_MEDIA TABLE EXAMPLE 22
OE PURCHASEORDER TABLE EXAMPLE 18
PM PRINT_MEDIA TABLE EXAMPLE 15
OE CUSTOMERS TABLE EXAMPLE 10
OE WAREHOUSES TABLE EXAMPLE 9
HR EMPLOYEES TABLE EXAMPLE 7
OE LINEITEM_TABLE TABLE EXAMPLE 6
IX STREAMS_QUEUE_TABLE TABLE EXAMPLE 6
SH FWEEK_PSCAT_SALES_MV TABLE EXAMPLE 5
SH CUSTOMERS TABLE EXAMPLE 5
HR LOCATIONS TABLE EXAMPLE 5
HR JOB_HISTORY TABLE EXAMPLE 5
SH PRODUCTS TABLE EXAMPLE 5
...
SELECT SUBSTR(TABLESPACE_NAME,1,20), SEGMENT_COUNT
FROM DBA_HEATMAP_TOP_TABLESPACES ORDER BY SEGMENT_COUNT DESC;
SUBSTR(TABLESPACE_NA SEGMENT_COUNT
-------------------- -------------
EXAMPLE 351
USERS 11
SELECT COUNT(*) FROM DBA_HEATMAP_TOP_OBJECTS;
COUNT(*)
----------
64
SELECT COUNT(*) FROM DBA_HEATMAP_TOP_TABLESPACES;
COUNT(*)
----------
2