HeatMapandAutomaticDataOptimization:part-1

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

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>