Oracle Automatic Data Optimization (ADO)
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"We don't live in a dictatorship or a monarchy. I swore an oath in the military and in the Senate to preserve, protect and defend the Constitution of the United States, not to mindlessly cater to the whims of Cadet Bone Spurs and clap when he demands I clap,"
~ Sen. Tammy Duckworth
Purpose Work is still in progress on this page
Description We just won the acronym sweepstakes with a number of new capabilities added to our favorite database each with its very own acronym and no shortage of confusion as to how all of these pieces work together.

ILM stands for Information Lifecycle Management and to quote the Oracle docs:

"To implement your ILM strategy, you use Heat Maps in the database to track data access and modification. You can also use Automatic Data Optimization (ADO) to automate the compression and movement of data between different tiers of storage within the database. The DBMS_ILM package supports immediate evaluation or execution of Automatic Data Optimization (ADO) related tasks. The package supports the following two ways for scheduling ADO actions.
  • A database user schedules immediate ADO policy execution on a set of objects.
  • A database user views the results of evaluation of ADO policies on a set of objects.
The user then adds or deletes objects to this set and reviews the results of ADO policy evaluation again. The user repeats this step to determine the set of objects for ADO execution. The user can then perform immediate scheduling of ADO actions on this set of objects.

The following procedures support the two usage modes. Before describing the procedures, we introduce the notion of an ADO task as an entity that helps to track a particular evaluation or (an evaluation and execution) of ADO policies. A particular ADO task could be in one of the following states.
  • Inactive
  • Active
  • Completed
Use the links below to explore the components of Oracle ADO.

ADO policies can be specified at the segment level for tables and partitions, or at the row level for tables and materialized views.

Segment level policies are evaluated and executed automatically in the background or on demand. Storage tiering can only be specified at the segment level and is triggered by a space shortage based on DBA set rules. Row level policies are evaluated and executed automatically in the background or on demand.
Dependencies
ALL_HEAT_MAP_SEGMENT DBMS_ILM I_ILMOBJ_OBJ$
ALL_HEAT_MAP_SEG_HISTOGRAM DBMS_ILM_ADMIN I_ILMOBJ_POL$
CDB_HEATMAP_TOP_OBJECTS DBMS_ILM_LIB I_ILMPOLICY$
CDB_HEATMAP_TOP_TABLESPACES GV$HEAT_MAP_SEGMENT I_ILMRESULTS_STATUS
CDB_HEAT_MAP_SEGMENT GV_$HEAT_MAP_SEGMENT KU$_ILM_POLICY_LIST_T
CDB_HEAT_MAP_SEG_HISTOGRAM HEAT_MAP_EXTENT_STAT$ KU$_ILM_POLICY_T
CDB_ILMDATAMOVEMENTPOLICIES HEAT_MAP_STAT$ KU$_ILM_POLICY_VIEW
CDB_ILMEVALUATIONDETAILS ILM$ KU$_ILM_POLICY_VIEW2
CDB_ILMOBJECTS ILMOBJ$ KU$_TBS_ILM_POLICY_T
CDB_ILMPARAMETERS ILMPOLICY$ KU$_TBS_ILM_POLICY_VIEW
CDB_ILMPOLICIES ILM_CONCURRENCY$ PRVT_ILM
CDB_ILMRESULTS ILM_DEPENDANT_OBJ$ RDF_ENTAILMENTS
CDB_ILMTASKS ILM_DEP_EXECUTIONDETAILS$ SDO_RDF_ENTAILMENTS
C_ILM_ATTRIBUTE ILM_EXECUTION$ SEM_ENTAILMENTS
C_ILM_PARAM ILM_EXECUTIONDETAILS$ USER_HEAT_MAP_SEGMENT
DBA_HEATMAP_TOP_OBJECTS ILM_EXECUTIONID USER_HEAT_MAP_SEG_HISTOGRAM
DBA_HEATMAP_TOP_TABLESPACES ILM_PARAM$ USER_ILMDATAMOVEMENTPOLICIES
DBA_HEAT_MAP_SEGMENT ILM_RESULTS$ USER_ILMEVALUATIONDETAILS
DBA_HEAT_MAP_SEG_HISTOGRAM ILM_RESULT_STAT$ USER_ILMOBJECTS
DBA_ILMDATAMOVEMENTPOLICIES ILM_SEQ$ USER_ILMPOLICIES
DBA_ILMEVALUATIONDETAILS I2_HEATMAPSTAT$ USER_ILMRESULTS
DBA_ILMOBJECTS I_HEATMAPEXTSTAT$ USER_ILMTASKS
DBA_ILMPARAMETERS I_HEATMAPSTAT$ V$HEAT_MAP_SEGMENT
DBA_ILMPOLICIES I_ILM$ V_$HEAT_MAP_SEGMENT
DBA_ILMRESULTS I_ILMEXECDET_EXECID WRI$_HEATMAP_TOPN_DEP1
DBA_ILMTASKS I_ILMEXECDET_JOBNAME WRI$_HEATMAP_TOPN_DEP2
DBMS_FEATURE_HEATMAP I_ILMEXECDET_OBJ WRI$_HEATMAP_TOP_OBJECTS
DBMS_FEATURE_ILM I_ILMEXECDET_POL WRI$_HEATMAP_TOP_TABLESPACES
DBMS_HEAT_MAP I_ILMOBJ$ _SYS_HEAT_MAP_SEG_HISTOGRAM
DBMS_HEAT_MAP_INTERNAL    
Initialization Parameters For heat maps to be collected you must set the HEAT_MAP startup parameter to TRUE
 
Header
Segment Level Policy ALTER TABLE [<schema_name.>]<table_name> ILM ADD POLICY <policy_name>;
ALTER TABLE uwclass.servers ILM ADD POLICY tier to ilmtbs;
Compress a table after there have been no modifications for one week ALTER TABLE [<schema_name.>]<table_name> ILM ADD POLICY ROW STORE COMPRESS
ADVANCED <ROW | SEGMENT> AFTER <integer> DAYS OF NO MODIFICATIONS;
ALTER TABLE uwclass.servers ILM ADD POLICY ROW STORE COMPRESS
ADVANCED segment AFTER 7 DAYS OF NO MODIFICATIONS;
Compress rows after one week of not being updated ALTER TABLE uwclass.servers ILM ADD POLICY ROW STORE COMPRESS
ADVANCED row AFTER 7 DAYS OF NO MODIFICATIONS;
Create tablespace and table for ADO CREATE TABLESPACE tier1_ts DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/tier1.dbf'
SIZE 10M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE tier2_ts DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/tier2.dbf'
SIZE 10M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE tier3_ts DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/tier3.dbf'
SIZE 50M AUTOEXTEND ON NEXT 5M;

CREATE TABLE order_hdr(
order_no NUMBER NOT NULL,
order_date DATE NOT NULL,
order_note VARCHAR2(500))
PARTITION BY RANGE (order_date)
(
PARTITION orders_2016_q3 VALUES LESS THAN (TO_DATE('01/10/2016', 'DD/MM/YYYY'))
TABLESPACE tier3_ts,
PARTITION orders_2016_q4 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY'))
TABLESPACE tier3_ts,
PARTITION orders_2017_q1 VALUES LESS THAN (TO_DATE('01/04/2017', 'DD/MM/YYYY'))
TABLESPACE tier2_ts
ILM ADD POLICY TIER TO tier3_ts READ ONLY SEGMENT AFTER 3 MONTHS OF NO ACCESS,
PARTITION orders_2017_q2 VALUES LESS THAN (TO_DATE('01/07/2017', 'DD/MM/YYYY'))
TABLESPACE tier2_ts
ILM ADD POLICY TIER TO tier3_ts READ ONLY SEGMENT AFTER 3 MONTHS OF NO ACCESS,
PARTITION orders_2017_q3 VALUES LESS THAN (TO_DATE('01/10/2017', 'DD/MM/YYYY'))
TABLESPACE tier1_ts
ILM ADD POLICY TIER TO tier2_ts READ ONLY SEGMENT AFTER 1 MONTHS OF NO ACCESS,
PARTITION orders_2017_q4 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY'))
TABLESPACE tier1_ts
ILM ADD POLICY TIER TO medium_storage_ts READ ONLY SEGMENT AFTER 2 MONTHS OF NO ACCESS)
ILM ADD POLICY ROW STORE COMPRESS BASIC SEGMENT AFTER 1 MONTHS OF NO ACCESS;
 
Header
Tiering Demo: Set Up conn sys@pdbdev as sysdba

CREATE TABLESPACE ssd_tbs DATAFILE 'C:\APP2\ORACLE\ORADATA\ORABASE2\PDBDEV\flash_array.dbf' SIZE 10M
AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE legacy_tbs DATAFILE 'C:\APP2\ORACLE\ORADATA\ORABASE2\PDBDEV\legacy_array.dbf' SIZE 10M
AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL;

ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;
ALTER USER scott QUOTA 8M ON ssd_tbs;
ALTER USER scott QUOTA 8M ON legacy_tbs;

GRANT select any dictionary TO scott;
GRANT alter tablespace TO scott;
GRANT select ON ts$ TO scott;
GRANT select ON dba_segments TO scott;

ALTER SYSTEM SET heat_map=on SCOPE=BOTH;

CREATE TABLE table scott.employee
TABLESPACE ssd_tbs AS
SELECT * FROM scott.emp WHERE 1 = 2;

-- insert test rows into the demo table
INSERT INTO scott.employee
SELECT * FROM scott.emp;

DECLARE
  blowup   CONSTANT POSITIVE := 8;
  sql_test          CLOB;
BEGIN
  FOR i IN 1..blowup LOOP
    sql_test := 'INSERT /*+ APPEND */ INTO scott.employee SELECT * FROM scott.employee';
    EXECUTE IMMEDIATE sql_test;
    COMMIT; -- other than in a demo ... never incrementally commit inside a loop
  END LOOP;
END;
/

SELECT COUNT(*)
FROM scott.employee;

SELECT tablespace_name, segment_name
FROM user_segments
WHERE segment_name='EMPLOYEE';

SELECT object_name, segment_write_time, segment_read_time, full_scan
FROM user_heat_map_segment
WHERE object_name='EMPLOYEE';
Tiering Demo: Set Up SQL>  SELECT tablespace_name, ROUND(SUM(MB_USED),2) AS MB_USED, ROUND(SUM(MB_FREE),2) AS MB_FREE
  2   FROM (
  3     SELECT tablespace_name, (bytes/(1024*1024)) AS MB_USED, NULL AS MB_FREE FROM dba_data_files
  4     UNION
  5     SELECT tablespace_name, NULL AS MB_USED, (bytes/(1024*1024)) AS MB_FREE FROM dba_free_space)
  6   WHERE tablespace_name IN ('SSD_TBS', 'LEGACY_TBS')
  7*  GROUP BY tablespace_name;

TABLESPACE_NAME                   MB_USED    MB_FREE
------------------------------ ---------- ----------
LEGACY_TBS                             10       3.94
SSD_TBS                                10       3.63
Tiering: Create Storage Policy SQL> ALTER TABLE scott.employee ilm ADD POLICY TIER TO legacy_tbs;

Table altered.

col policy_name format a12
col compression_level format a20
col tier_tbs format a20
col condition_type format a20

SELECT CAST(policy_name AS VARCHAR2(30)) policy_name, action_type, scope, compression_level,
CAST(tier_tablespace AS VARCHAR2(30)) tier_tbs, condition_type, condition_days
FROM dba_ilmdatamovementpolicies
ORDER BY policy_name;

POLICY_NAME          ACTION_TYPE SCOPE   COMPRESSION_LEVEL    TIER_TBS             CONDITION_TYPE       CONDITION_DAYS
-------------------- ----------- ------- -------------------- -------------------- -------------------- --------------
P1                   STORAGE             SEGMENT              LEGACY_TBS                                             0

col object_owner format a20
col object_type format a12
col object_name format a12
col tbs_inherited_from format a20

SQL> SELECT policy_name, object_owner, object_name, object_type, inherited_from, tbs_inherited_from, enabled, deleted
  2  FROM dba_ilmobjects;

POLICY_NAME OBJECT_OWNER          OBJECT_NAME  OBJECT_TYPE  INHERITED_FROM       TBS_INHERITED_FROM   ENA DEL
------------ -------------------- ------------ ------------ -------------------- -------------------- --- ---
P1           SCOTT                EMPLOYEE     TABLE        POLICY               NOT INHERITED        YES NO
Tiering: Initiative Movement SQL> col name format A20
SQL> col value format 9999

SQL> SELECT * FROM dba_ilmparameters ORDER BY 1;

NAME                 VALUE
-------------------- -----
ENABLED                  1
EXECUTION INTERVAL      15
EXECUTION MODE           2
JOB LIMIT                2
POLICY TIME              0
RETENTION TIME          30
TBS PERCENT FREE        25
TBS PERCENT USED        85

SQL> exec dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,95);

PL/SQL procedure successfully completed.

SQL> exec dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_USED,5);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM dba_ilmparameters ORDER BY 1;

NAME                 VALUE
-------------------- -----
ENABLED                  1
EXECUTION INTERVAL      15
EXECUTION MODE           2
JOB LIMIT                2
POLICY TIME              0
RETENTION TIME          30
TBS PERCENT FREE        95
TBS PERCENT USED         5

-- For this demo, we cannot wait for the maintenance window to open that will trigger the automatic data optimization policies jobs.
-- Instead, dbms_ilm.execute_ilm runs to force immediate execution.


SQL> DECLARE
  2   retVal NUMBER;
  3  BEGIN
  4    dbms_ilm.execute_ilm(ilm_scope => dbms_ilm.scope_schema, execution_mode => dbms_ilm.ilm_execution_offline, task_id => retVal);
  5    dbms_output.put_line(retVal);
  6  END;
  7  /
4

PL/SQL procedure successfully completed.

SQL> SELECT tablespace_name, segment_name
  2  FROM dba_segments
  3  WHERE owner = 'SCOTT'
  4  AND segment_name='EMPLOYEE';

TABLESPACE_NAME                SEGMENT_NAME
------------------------------ ------------------------------
SSD_TBS                        EMPLOYEE

col start_time format a30

SQL> SELECT task_id, start_time
  2  FROM dba_ilmtasks
  3  ORDER BY 2;

  TASK_ID  START_TIME
---------- ------------------------------
        1  17-DEC-17 06.01.02.061000 PM
        2  17-DEC-17 06.16.02.333000 PM
        3  17-DEC-17 06.37.00.360000 PM
        4  17-DEC-17 06.40.57.270000 PM
        5  17-DEC-17 07.23.11.922000 PM
        6  17-DEC-17 07.38.12.827000 PM

col job_name format a15
col selected_for_execution format a30
col comments format a30

SQL> SELECT task_id, object_owner, object_name, object_type, selected_for_execution
  2* FROM dba_ilmevaluationdetails;

TASK_ID  OBJECT_OWNER         OBJECT_NAME  OBJECT_TYPE  SELECTED_FOR_EXECUTION
-------- -------------------- ------------ ------------ ------------------------------
      1  SCOTT                EMPLOYEE     TABLE        PRECONDITION NOT SATISFIED
Tiering: Demo Clean-up -- remove policies and demo metadata
DELETE ilm$;
DELETE ilmpolicy$;
DELETE ilmobj$;
DELETE ilm_results$;
DELETE ilm_execution$;
DELETE ilm_executiondetails$;

-- drop the ilmtbs and low_cost_store tablespaces
DROP TABLSPACE ssd_tbs INCLUDING CONTENTS AND DATAFILES;
DROP TABLSPACE legacy_tbs INCLUDING CONTENTS AND DATAFILES;

-- return tablespace %Free and %Used thresholds to their default
exec dbms_ilm_admin.customize_ilm(dbms_ilm_admin.tbs_percent_used,85);
exec dbms_ilm_admin.customize_ilm(dbms_ilm_admin.tbs_percent_free,25);

-- disable heatmap tracking
ALTER SYSTEM SET heat_map=off SCOPE=both;

Related Topics
Built-in Functions
Built-in Packages
DBMS_HEAT_MAP
DBMS_ILM
DBMS_ILM_ADMIN
Startup Parameters
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved