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),并且提供了以下视图查看
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; ? 855 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; ? 347 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, 感谢原作者分享。
下载本文