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及容器iddba_heat_map_segment:displays the latest segment access time for all segments visible to the specified userdba_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 objectdba_heatmap_top_tablespaces:displays access information for the top 100 tablespacesheat_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---------------- ------------------------------------------------------------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, 感谢原作者分享。