Heat Maps – 12c Edition

Over the last few months, I’ve been slowly playing with Oracle Database 12c and various features within it.  One such feature has been the heat maps.  I was curious about heat maps during beta testing but didn’t have a chance to test it while there.  Since beta testing, I’ve been extremely busy with work items and project.  I’ve finally had a day where I could quickly test the heat map option.  What turns it on, turns it off and how is it used.  Like many of my other posts, hopefully, this will give you some insight to what it does and how it can be used.

To turn on heat maps is simple!  Much simpler than I had expected.  If you want to turn on heat maps it can be done either at the system or session level with a simple ALTER SYSTEM or ALTER SESSION command.  The parameter that needs to be used is HEAT_MAP. How much simpler can it be?  

alter system set heat_map=on scope=both;

or

alter session set heat_map=on;

To turn heat maps off is just a simple.  Just change ON to OFF.

Once you have heat maps turned on how can we tell if they are being used.  There are a series of views that can be used to track the statistics on heat maps.  These views  consist of V$*, ALL*, DBA* and USER* views.  The view names to correspond with these views are *HEAT_MAP_SEGMENT, *_HEAT_MAP_SEQ_HISTOGRAM, DBA_HEATMAP_TOP_OBJECTS, and DBA_HEATMAP_TOP_TABLESPACES.  the DBA_HEATMAP_TOP_OBJECTS view displays information related to the last 1000 objects and the DBA_HEATMAP_TOP_TABLESPACE shows information for the top 100 tablespaces;

Since we know the views that we can use to identify heat map information, lets take a look at an example or two.  The examples which I’m providing here are ran against a traditional 12c database (Non-CDB).  I’m going to turn on heat maps for the whole instance first.

[oracle@oel6 Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 1 12:19:36 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show parameter heat_map;

NAME TYPE VALUE
------------------------
heat_map string OFF

SQL> alter system set heat_map=ON scope=both;

System altered.

SQL> show parameter heat_map;

NAME TYPE VALUE
------------------------
heat_map string ON

Heat maps have been turned on at the system level now.  Using the scope=BOTH option set the HEAT_MAP parameter both in memory and in the SPFILE, preventing the need to bounce the database.  Lets take a look at the V$HEAT_MAP_SEGMENT view now.  For this example, we will use the example schema SCOTT.  

Notice that when running a query against the V$HEAT_MAP_SEGMENT view for the first time, there is nothing in the view.

SQL> SELECT SUBSTR(OBJECT_NAME,1,20), SUBSTR(SUBOBJECT_NAME,1,20), TRACK_TIME, SEGMENT_WRITE,
 FULL_SCAN, LOOKUP_SCAN FROM V$HEAT_MAP_SEGMENT; 2

no rows selected

Lets run a select statement against the table SCOTT.EMP then run the same query against the V$HEAT_MAP_SEGMENT view again.

SQL> select * from scott.emp;

rows returned 

SQL> SELECT SUBSTR(OBJECT_NAME,1,20), SUBSTR(SUBOBJECT_NAME,1,20), TRACK_TIME, SEGMENT_WRITE,
 FULL_SCAN, LOOKUP_SCAN FROM V$HEAT_MAP_SEGMENT; 2

no rows selected

Wait a minute, what happen, why didn’t work?  The parameter is set to ON?  Ok, lets bounce the database and try again.

SQL> select * from scott.emp;

rows returned

SQL> SELECT SUBSTR(OBJECT_NAME,1,20), SUBSTR(SUBOBJECT_NAME,1,20), TRACK_TIME, SEGMENT_WRITE,
FULL_SCAN, LOOKUP_SCAN FROM V$HEAT_MAP_SEGMENT; 2

SUBSTR(OBJECT_NAME,1 SUBSTR(SUBOBJECT_NAM TRACK_TIM SEG FUL LOO
-------------------- -------------------- --------- --- --- ---
TSM_SRC$ 01-SEP-13 NO NO NO
SYS_IOT_TOP_19282 01-SEP-13 NO NO NO
AQ$_ALERT_QT_L 01-SEP-13 NO NO NO
EMP 01-SEP-13 NO YES NO
SYS_IOT_TOP_17632 01-SEP-13 NO NO NO
AQ$_STREAMS_QUEUE_TA 01-SEP-13 NO NO NO
TSM_DST$ 01-SEP-13 NO NO NO
CONFIG$ 01-SEP-13 NO NO NO
SYS_IOT_TOP_16945 01-SEP-13 NO NO NO
WRM$_SNAPSHOT 01-SEP-13 NO NO NO
AQ$_ORDERS_QUEUETABL 01-SEP-13 NO NO NO

SUBSTR(OBJECT_NAME,1 SUBSTR(SUBOBJECT_NAM TRACK_TIM SEG FUL LOO
-------------------- -------------------- --------- --- --- ---
HEAT_MAP_STAT$ 01-SEP-13 NO NO NO
SYS_IOT_TOP_19065 01-SEP-13 NO NO NO
AQ$_SYS$SERVICE_METR 01-SEP-13 NO NO NO
AQ$_WM$EVENT_QUEUE_T 01-SEP-13 NO NO NO
XDB$SCHEMA_URL 01-SEP-13 NO NO NO
AQ$_CHANGE_LOG_QUEUE 01-SEP-13 NO NO NO
SYS_IOT_TOP_92365 01-SEP-13 NO NO NO
I_HEATMAPSTAT$ 01-SEP-13 NO NO NO
SYS_IOT_TOP_92369 01-SEP-13 NO NO NO

20 rows selected.

Notice, that after the bounce of the database and selecting everything from SCOTT.EMP we now have information in the V$HEAT_MAP_SEGMENT view.  You are probably wondering, why are there 20 object the view is tracking when I only selected from SCOTT.EMP?  You have to remember that heat maps are used to provided data access tracking at the segment level and data modification tracking at the segment and row level.  Performing a select from SCOTT.EMP, the heat map view shows all the segments that SCOTT.EMP touches; in this case 20 different objects.

I see heat maps being helpful in a few different stages of the development lifecycle.  For a DBA that is coming into a new environment and knows nothing of the environment, heat maps are going to provide a way for them to quickly identify what pieces of the application/database is being used.  For developers it is going to show them what they are frequently hitting and what they may want to look at as they develop.  Overall, heat maps are going to become a valuable tool for everyone to use at some stage in the development process.

Enjoy!

twitter: @curtisbl

blog: http://dbasolved.com

Please follow and like: