I have working through some Oracle 12c ILM examples, I didn't find that many blog articles covering this feature but was happy to follow : http://gavinsoorma.com/2013/09/oracle-12c-new-feature-heat-map-and-automatic-data-optimization/
First of all as SYS enable heat mapping:
SQL> alter system set heat_map=on scope=both;
System altered.
SQL> exec dbms_ilm_admin.set_heat_map_start(start_date => sysdate - 30);
PL/SQL procedure successfully completed.
SQL> select OBJECT_NAME,SEGMENT_WRITE_TIME , SEGMENT_READ_TIME, FULL_SCAN FROM dba_heat_map_segment;
no rows selected
The following also didn't trigger any new records in dba_heat_map_segment:
SQL> select count(*) from scott.emp;
COUNT(*)
----------
14
SQL> select OBJECT_NAME,SEGMENT_WRITE_TIME , SEGMENT_READ_TIME, FULL_SCAN FROM dba_heat_map_segment;
no rows selected
However after adding a policy:
SQL> ALTER TABLE scott.myobjects ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;
Table altered.
and re-runing the the select count(*) from scott.myobjects:
SQL> col OBJECT_NAME form a30
SQL> select object_name, track_time, segment_write, FULL_SCAN, lookup_scan from DBA_HEAT_MAP_SEG_HISTOGRAM where object_name='MYOBJECTS' and owner = 'SCOTT';
OBJECT_NAME TRACK_TIME SEG FUL LOO
------------------------------ ------------------ --- --- ---
MYOBJECTS 10-may-15 09:35:28 NO YES NO
I also decide to reduce my policy from 30 days to 1 day:
SQL> alter table scott.myobjects ILM DELETE POLICY P1;
Table altered.
SQL> select policy_name, action_type, scope, compression_level, condition_type, condition_days from dba_ilmdatamovementpolicies order by policy_name;
no rows selected
SQL> ALTER TABLE scott.myobjects ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 2 DAYS OF NO MODIFICATION;
Table altered.
SQL> select policy_name, action_type, scope, compression_level, condition_type, condition_days from dba_ilmdatamovementpolicies order by policy_name;
POLICY_NAME
--------------------------------------------------------------------------------
ACTION_TYPE SCOPE COMPRESSION_LEVEL CONDITION_TYPE
----------- ------- ------------------------------ ----------------------
CONDITION_DAYS
--------------
P41
COMPRESSION SEGMENT ADVANCED LAST MODIFICATION TIME
2
Next manually kick off dbms_ilm.execute_ilm:
declare
v_executionid number;
begin
dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA, execution_mode => dbms_ilm.ilm_execution_offline, task_id => v_executionid);
end;
/
SQL>
PL/SQL procedure successfully completed
this ran with task_id=82:
SQL> select task_id, start_time as start_time from user_ilmtasks;
TASK_ID START_TIME
---------- ---------------------------------------------------------------------------
82 10-MAY-15 09.54.13.708088 PM
but didn't do anything
set line 150
col POLICY_NAME form a15
col OBJECT_NAME form a30
col SELECTED_FOR_EXECUTION form a10
col JOB_NAME form a10
select task_id, policy_name, object_name, selected_for_execution, job_name from user_ilmevaluationdetails;
SQL>
TASK_ID POLICY_NAME OBJECT_NAME SELECTED_FOR_EXECUTION JOB_NAME
---------- --------------- ------------------------------ ------------------------------ ----------
82 P41 MYOBJECTS PRECONDITION NOT SATISFIED
Let's give it 24 hours...
