[20140218]12c 新特性heat map.txt

12c 新特性,可以记录那些对象经常使用.
它可以在session或者system打开,不过我的测试好像在cdb数据库无效.

SCOTT@ztest> @ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@ztest> show parameter heat_map
NAME      TYPE    VALUE
--------- ------- ------
heat_map  string  OFF

--相关视图:
SCOTT@ztest> select owner,view_name from dba_views where view_name like '%HEAT_MAP%' and owner='SYS';
OWNER  VIEW_NAME
------ ----------------------------
SYS    ALL_HEAT_MAP_SEGMENT
SYS    ALL_HEAT_MAP_SEG_HISTOGRAM
SYS    CDB_HEAT_MAP_SEGMENT
SYS    CDB_HEAT_MAP_SEG_HISTOGRAM
SYS    DBA_HEAT_MAP_SEGMENT
SYS    DBA_HEAT_MAP_SEG_HISTOGRAM
SYS    GV_$HEAT_MAP_SEGMENT
SYS    USER_HEAT_MAP_SEGMENT
SYS    USER_HEAT_MAP_SEG_HISTOGRAM
SYS    V_$HEAT_MAP_SEGMENT
SYS    _SYS_HEAT_MAP_SEG_HISTOGRAM

11 rows selected.

--先查看DBA_HEAT_MAP_SEGMENT视图:
SCOTT@ztest> select * from DBA_HEAT_MAP_SEGMENT;
no rows selected

--打开heat_map如下:
--alter system set heat_map=on scope=both;
--or
--alter session set heat_map=on;

在会话级别打开:
SCOTT@ztest> alter session set heat_map=on;
Session altered.

--执行多次.
select * from emp;
select * from emp where empno=7369;

SCOTT@ztest> select * from DBA_HEAT_MAP_SEGMENT;
OWNER  OBJECT_NAME          SUBOBJECT_ SEGMENT_WRITE_TIME  SEGMENT_READ_TIME   FULL_SCAN           LOOKUP_SCAN
------ -------------------- ---------- ------------------- ------------------- ------------------- -------------------
SCOTT  EMP                                                                     2014-02-18 15:57:06
SCOTT  PK_EMP                                                                                      2014-02-18 15:57:06

SCOTT@ztest> SELECT *  FROM V$HEAT_MAP_SEGMENT;
OBJECT_NAME          SUBOBJECT_       OBJ#   DATAOBJ# TRACK_TIME          SEG SEG FUL LOO     CON_ID
-------------------- ---------- ---------- ---------- ------------------- --- --- --- --- ----------
PK_EMP                               91948      91948 2014-02-18 15:59:57 NO  NO  NO  YES          0
EMP                                  91947      91947 2014-02-18 15:59:57 NO  NO  YES NO           0

--可以通过这个了解一些对象使用情况.确定优化的方式.