Heat Map and Automatic Data Optimization : part-1
程序员文章站
2022-05-14 23:42:40
...
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, 感谢原作者分享。