Home >Database >Mysql Tutorial >Heat Map and Automatic Data Optimization : part-1
Heat Map and Automatic Data Optimization : part-1
- WBOYOriginal
- 2016-06-07 16:36:451284browse
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, 感谢原作者分享。
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：Oracle在无备份情况下通过PRM恢复被截断truncate掉的表Next article：【转】Oracle Parallel 多线程