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

[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

 

--可以透過這個瞭解一些物件使用情況.確定最佳化的方式.