Heat Map and Automatic Data Optimization : part-1

oracle 12c 提供了一个新特性叫 Heat Map,它跟踪和标记数据可以达到row和block level,此特性可以在system和session级别启用。如果要使用ADO(Automatic Data Optimization)必须要在system级别启用。但是此特性 only works in a non-CDB environment，not supp

oracle 12c 提供了一个新特性叫 Heat Map,它跟踪和标记数据可以达到row和block level,此特性可以在system和session级别启用。如果要使用ADO(Automatic Data Optimization)必须要在system级别启用。但是此特性only works in a non-CDB environment，not supported with a multitenant container database (CDB),并且提供了以下视图查看

• V$HEAT_MAP_SEGMENT:显示实时访问信息，包好object_name，object_number及容器ID
• DBA_HEAT_MAP_SEGMENT:Displays the latest segment access time for all segments visible to the specified user
• DBA_HEAT_MAP_SEG_HISTOGRAM:Displays access information for all segments visible to the specific user.
• DBA_HEATMAP_TOP_OBJECTS:Displays access information for the top 1,000 object
• DBA_HEATMAP_TOP_TABLESPACES:Displays access information for the top 100 tablespaces

Heat_map和ADO 结合使用示意图

CDB和non-CDB 测试

non-CDB

SQL> SELECT cdb FROM v$database ;
?
CDB
------
NO
?
--数据库NON-CDB
?
SQL> GRANT dba TO travel IDENTIFIED BY aa;
?
GRANT succeeded.
?
SQL> conn travel/aa
Connected.
?
USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
TRAVEL               noncdb       localhost.localdomain     33    11       12.1.0.1.0 20140525 4286            7     4259            000000009F68A408 000000009F9865B8
?
?
SQL> ALTER system SET heat_map=ON;
?
System altered.
?
SQL> CREATE TABLE heat_test AS SELECT * FROM all_objects;
?
TABLE created.
?
SQL> INSERT /*+ append */ INTO  heat_test SELECT * FROM heat_test;
?
88955 ROWS created.
?
SQL> commit;
?
Commit complete.
?
SQL>  INSERT /*+ append */ INTO  heat_test SELECT * FROM heat_test;
?
177910 ROWS created.
?
SQL> commit;
?
Commit complete.
?
--创建一张测试表
?
SQL> ALTER SESSION SET nls_date_format='yyyy-mm-dd hh:mi:ss';
?
SESSION altered.
?
SQL> col OBJECT_NAME FOR a15
SQL> SELECT OBJECT_NAME,SEGMENT_WRITE_TIME , SEGMENT_READ_TIME, FULL_SCAN FROM dba_heat_map_segment WHERE owner='TRAVEL';
?
OBJECT_NAME     SEGMENT_WRITE_TIME  SEGMENT_READ_TIME   FULL_SCAN
--------------- ------------------- ------------------- -------------------
HEAT_TEST                                               2014-05-25 05:44:00
?
SQL> col "Segment write" format A14
SQL> col "Full Scan" format A12
SQL> col "Lookup Scan" format a12
SQL>  SELECT object_name, track_time "Tracking Time",
  2   segment_write "Segment write",
  3   full_scan "Full Scan",
  4   lookup_scan "Lookup Scan"
  5   FROM DBA_HEAT_MAP_SEG_HISTOGRAM
  6   WHERE object_name='HEAT_TEST';
?
OBJECT_NAME     Tracking TIME       Segment WRITE  FULL Scan    Lookup Scan
--------------- ------------------- -------------- ------------ ------------
HEAT_TEST       2014-05-25 05:45:03 NO             YES          NO
?
SQL> SELECT compression, compress_for FROM dba_tables WHERE TABLE_NAME = 'HEAT_TEST';
?
COMPRESSION      COMPRESS_FOR
---------------- ------------------------------------------------------------
DISABLED
?
SQL> SELECT SUM(bytes)/1048576 FROM user_segments WHERE 
  2      segment_name='HEAT_TEST';
?
SUM(BYTES)/1048576
------------------
?
?
SQL> SELECT SUM(bytes)/1048576 FROM  dba_segments WHERE segment_name='HEAT_TEST';
?
SUM(BYTES)/1048576
------------------
                48
?
--查看了数据的heat_map情况和大小
?
添加ADO策略30天内没有修改进行压缩
SQL> ALTER TABLE  travel.HEAT_TEST  ILM ADD POLICY ROW STORE 
  2  COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;
?
TABLE altered.
?
查看policy
SQL> 
SQL> SELECT policy_name, action_type, scope, compression_level,
  2   condition_type, condition_days
  3   FROM   dba_ilmdatamovementpolicies
  4  ORDER BY policy_name;
?
POLICY_NAME                                                                                                                                                                                                                                                      ACTION_TYPE            SCOPE          COMPRESSION_LEVEL                                            CONDITION_TYPE                               CONDITION_DAYS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- -------------- ------------------------------------------------------------ -------------------------------------------- --------------
P1                                                                                                                                                                                                                                                               COMPRESSION            SEGMENT        ADVANCED                                                     LAST MODIFICATION TIME                                   30
?
SQL> col policy_name FOR a10
SQL> /
?
POLICY_NAM ACTION_TYPE            SCOPE          COMPRESSION_LEVEL                                            CONDITION_TYPE                               CONDITION_DAYS
---------- ---------------------- -------------- ------------------------------------------------------------ -------------------------------------------- --------------
P1         COMPRESSION            SEGMENT        ADVANCED                                                     LAST MODIFICATION TIME                                   30
?
SQL> col COMPRESSION_LEVEL FOR a20
SQL> /
?
POLICY_NAM ACTION_TYPE            SCOPE          COMPRESSION_LEVEL    CONDITION_TYPE                               CONDITION_DAYS
---------- ---------------------- -------------- -------------------- -------------------------------------------- --------------
P1         COMPRESSION            SEGMENT        ADVANCED             LAST MODIFICATION TIME                                   30
?
SQL> SELECT policy_name, object_name, inherited_from, enabled FROM dba_ilmobjects;
?
POLICY_NAM OBJECT_NAME     INHERITED_FROM                           ENABLED
---------- --------------- ---------------------------------------- --------------
P1         HEAT_TEST       POLICY NOT INHERITED                       YES
?
?
?
这里由于需要30天，所以通过修改低成表数据实现30天
?
SQL> CREATE OR REPLACE PROCEDURE set_stat (object_id      NUMBER,
  2   data_object_id NUMBER,
  3   n_days         NUMBER,
  4   p_ts#            NUMBER,
  5   p_segment_access NUMBER)
  6   AS
  7   BEGIN
  8   INSERT INTO sys.heat_map_stat$
  9   (obj#,
 10   dataobj#,
 11   track_time,
 12   segment_access,
 13   ts#)
 14   VALUES
 15   (object_id,
 16   data_object_id,
 17   sysdate - n_days,
 18   p_segment_access,
 19   p_ts# );
 20   commit;
 21   END;
 22   /
?
PROCEDURE created.
?
SQL> DECLARE
  2   v_obj# NUMBER;
  3   v_dataobj# NUMBER;
  4   v_ts#      NUMBER;
  5   BEGIN
  6   SELECT object_id, data_object_id INTO v_obj#, v_dataobj#
  7   FROM dba_objects
  8   WHERE object_name = 'HEAT_TEST'
  9   AND owner = 'TRAVEL';
 10   SELECT ts# INTO v_ts#
 11   FROM sys.ts$ a,
 12   dba_segments b
 13   WHERE  a.name = b.tablespace_name
 14   AND  b.segment_name = 'HEAT_TEST';
 15   commit;
 16   sys.set_stat
 17   (object_id         => v_obj#,
 18   data_object_id    => v_dataobj#,
 19   n_days            => 30,
 20   p_ts#             => v_ts#,
 21   p_segment_access  => 1);
 22   END;
 23   /
?
PL/SQL PROCEDURE successfully completed.
?
SQL> conn travel/aa
Connected.
?
USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
TRAVEL               noncdb       localhost.localdomain     1     7        12.1.0.1.0 20140525 4916            20    4553            000000009F6CA108 000000009F994798
?
?
SQL> 
手工执行
SQL> DECLARE
  2  v_executionid NUMBER;
  3  BEGIN
  4  dbms_ilm.execute_ILM (ILM_SCOPE      => dbms_ilm.SCOPE_SCHEMA,
  5                        execution_mode => dbms_ilm.ilm_execution_offline,
  6                        task_id        => v_executionid);
  7  END;
  8  /
?
PL/SQL PROCEDURE successfully completed.
?
?
查看任务执行
SQL> SELECT task_id, start_time AS start_time FROM user_ilmtasks;
?
   TASK_ID START_TIME
---------- -----------------------------
         2 25-MAY-14 05.52.39.737942 PM
?
?
查看任务详细洗洗
SQL> SELECT task_id, policy_name, object_name, selected_for_execution, job_name
  2  FROM user_ilmevaluationdetails
  3  WHERE task_id=2;
?
   TASK_ID POLICY_NAM OBJECT_NAME     SELECTED_FOR_EXECUTION   JOB_NAME
---------- ---------- --------------- ------------------------ ---------------------------------
         2 P1         HEAT_TEST       SELECTED FOR EXECUTION   ILMJOB42
查看结果
?
SQL> SELECT task_id, job_name, job_state, completion_time completion FROM user_ilmresults;
?
   TASK_ID JOB_NAME                JOB_STATE                  COMPLETION
---------- ----------------------- -------------------------- ---------------------------------------
         2 ILMJOB42                COMPLETED SUCCESSFULLY     25-MAY-14 05.52.43.834452 PM
?
SQL> col JOB_NAME FOR a20
SQL> SELECT task_id, job_name, job_state, completion_time completion FROM user_ilmresults;
?
   TASK_ID JOB_NAME             JOB_STATE                     COMPLETION
---------- -------------------- ----------------------------- ---------------------------------------------------------------------------
         2 ILMJOB42             COMPLETED SUCCESSFULLY        25-MAY-14 05.52.43.834452 PM
?
查看表大小
SQL> SELECT SUM(bytes)/1048576 FROM user_segments WHERE segment_name='HEAT_TEST';
?
SUM(BYTES)/1048576
------------------
                13
数据压缩了35M

测试下CDB情况下的使用

SQL> SELECT cdb FROM v$database;
?
CDB
---
YES
?
SQL> ALTER system SET heat_map=ON;
?
System altered.
?
SQL> conn c##travel/aa
ERROR:
ORA-28001: the password has expired
?
?
Changing password FOR c##travel
NEW password: 
Retype NEW password: 
Password changed
Connected.
?
USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
C##TRAVEL            orcl         localhost.localdomain     57    11       12.1.0.1.0 20140525 5370            7     5067            000000009F711DA8 000000009FA3EB88
?
?
SQL>  CREATE TABLE heat_test AS SELECT * FROM all_objects;
?
TABLE created.
?
SQL> INSERT /*+ append */ INTO  heat_test SELECT * FROM heat_test;
?
89347 ROWS created.
?
SQL> commit;
?
Commit complete.
?
SQL> ALTER TABLE  HEAT_TEST  ILM ADD POLICY ROW STORE 
  2  COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;
ALTER TABLE  HEAT_TEST  ILM ADD POLICY ROW STORE
*
ERROR at line 1:
ORA-38343: ADO online mode NOT supported WITH supplemental logging enabled
?
?
SQL> !oerr ora 38343
38343, 00000, "ADO online mode not supported with supplemental logging enabled"
// *Cause: An attempt was made TO perform an automatic DATA optimization (ADO)
//         operation WITH supplemental logging enabled.
// *Action: Disable supplemental logging OR switch TO ADO offline mode AND retry.
?
SQL> conn  / AS sysdba
Connected.
?
USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
SYS                  orcl         localhost.localdomain     57    13       12.1.0.1.0 20140525 5455            7     5067            000000009F711DA8 000000009FA3EB88
?
?
SQL> ALTER DATABASE DROP supplemental log ;
ALTER DATABASE DROP supplemental log
                                    *
ERROR at line 1:
ORA-00905: missing keyword
?
?
SQL> ALTER DATABASE DROP supplemental log DATA;
?
DATABASE altered.
?
SQL> conn c##travel/aa
Connected.
?
USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
C##TRAVEL            orcl         localhost.localdomain     57    15       12.1.0.1.0 20140525 5467            7     5067            000000009F711DA8 000000009FA3EB88
?
?
SQL> ALTER TABLE  HEAT_TEST  ILM ADD POLICY ROW STORE 
  2  COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;
ALTER TABLE  HEAT_TEST  ILM ADD POLICY ROW STORE
*
ERROR at line 1:
ORA-38342: heat map NOT enabled
?
?
SQL> SHOW parameter heat_map
?
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
heat_map                             string      ON
SQL>

证明了only works in a non-CDB environment，not supported with a multitenant container database (CDB)

原文地址：Heat Map and Automatic Data Optimization : part-1, 感谢原作者分享。