[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
--可以通过这个了解一些对象使用情况.确定优化的方式.