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/aaConnected.?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 a15SQL> 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 A14SQL> col "Full Scan" format A12SQL> col "Lookup Scan" format a12SQL>  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<mark style="color:transparent">来4源gaodaimacom搞#代%码*网</mark>---------------- ------------------------------------------------------------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.?查看policySQL> 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 a10SQL> /?POLICY_NAM ACTION_TYPE            SCOPE          COMPRESSION_LEVEL                                            CONDITION_TYPE                               CONDITION_DAYS---------- ---------------------- -------------- ------------------------------------------------------------ -------------------------------------------- --------------P1         COMPRESSION            SEGMENT        ADVANCED                                                     LAST MODIFICATION TIME                                   30?SQL> col COMPRESSION_LEVEL FOR a20SQL> /?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/aaConnected.?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 a20SQL> 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/aaERROR:ORA-28001: the password has expired??Changing password FOR c##travelNEW password: Retype NEW password: Password changedConnected.?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 3834338343, 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 sysdbaConnected.?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/aaConnected.?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      ONSQL>

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

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

喜欢 (0)赏

[搞代码]

分享 (0)