HeatMapandAutomaticDataOptimization: part-1

Oracle12c provides a new feature called HeatMap, which tracks and marks data to row and blocklevel. This feature can be enabled at the system and session levels. To use ADO (AutomaticDataOptimization), you must enable it at the system level. However, onlyworksinanon-CDBenvironment, notsupp

Oracle 12c provides a new feature called Heat Map, which tracks and marks data at row and block levels. This feature can be enabled at the system and session levels. To use ADO (Automatic Data Optimization), you must enable it at the system level. However, this feature only works in a non-CDB environment, not supp

Oracle 12c provides a new feature called Heat Map, which tracks and marks data at row and block levels. This feature can be enabled at the system and session levels. To use ADO (Automatic Data Optimization), you must enable it at the system level. However, this featureOnly works in a non-CDB environment, not supported with a multitenant container database (CDB)And provides the following view

• V $ HEAT_MAP_SEGMENT: displays real-time access information, including object_name, object_number, and container 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 and ADO

CDB and non-CDB tests

Non-CDB

SQL> SELECT cdb FROM v $ database ;? CDB------NO? -- Database 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 certificate -------------- certificate ----- -------- ---------- -------- ----------------- --------------- ------------------ ---------------- TRAVEL noncdb localhost. localdomain 33 11 12.1.0.1.0 20140525 4286 7 4259 running route 9f68a408 running route 9f9865b8 ?? 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 .? -- Create a test table? 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 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 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? -- Check the heat_map and size of the data? The ado policy was not modified for Compression Within 30 DAYS. SQL> ALTER TABLE travel. HEAT_TEST ILM ADD POLICY ROW STORE 2 COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION ;? TABLE altered .? View 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 condition --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------- -------------------------------------------- -------------- 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 ---------- ---------------------- -------------- when else -------------- 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 ??? Since it takes 30 days, how many days does it take to modify low-table data? 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. 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 certificate -------------- certificate ----- -------- ---------- -------- ----------------- --------------- ------------------ ---------------- TRAVEL noncdb localhost. localdomain 1 7 12.1.0.1.0 20140525 4916 20 4553 then 9f6ca108 then 9f994798 ?? SQL> manually execute SQL> DECLARE 2 v_executionid NUMBER; 3 BEGIN 4 catalog (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA, 5 execution_mode => catalog, 6 task_id => v_executionid); 7 END; 8 /? PL/SQL PROCEDURE successfully completed .?? View the task execution SQL> SELECT task_id, start_time AS start_time FROM user_ilmtasks ;? TASK_ID START_TIME ---------- ----------------------------- 2 25-MAY-14 05.52.39.712742 PM ?? View the task details for washing 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 ------------ ------------------- ------------------------ running 2 P1 HEAT_TEST selected for execution ILMJOB42 view results? SQL> SELECT task_id, job_name, job_state, completion_time completion FROM user_ilmresults ;? TASK_ID JOB_NAME JOB_STATE COMPLETION ---------- ----------------------- ------------------------ limit 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 ---------- -------------------- --------------------------- limit 2 ILMJOB42 completed successfully 25-MAY-14 05.52.43.834452 PM? View table size SQL> SELECT SUM (bytes)/1048576 FROM user_segments WHERE segment_name = 'heat _ test ';? SUM (BYTES)/1048576 ------------------ 13 Data Compression 35 M

Use of CDB in testing

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>

ProvedOnly works in a non-CDB environment, not supported with a multitenant container database (CDB)

Original article address: Heat Map and Automatic Data Optimization: part-1. Thank you for sharing it with me.