Oracle Automatic Data Optimization (ADO)
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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
ADO_IMCSEQ$ DBMS_FEATURE_ILM I_ILMEXECDET_EXECID
ADO_IMPARAM$ DBMS_FEATURE_IM_ADO I_ILMEXECDET_JOBNAME
ADO_IMSEGSTAT$ DBMS_HEAT_MAP I_ILMEXECDET_OBJ
ADO_IMSEGTASKDETAILS$ DBMS_HEAT_MAP_INTERNAL I_ILMEXECDET_POL
ADO_IMSTAT$ DBMS_ILM I_ILMOBJ$
ADO_IMTASKS$ DBMS_ILM_ADMIN I_ILMOBJ_OBJ$
ALL_HEAT_MAP_SEGMENT DBMS_ILM_LIB I_ILMOBJ_POL$
ALL_HEAT_MAP_SEG_HISTOGRAM GV$HEAT_MAP_SEGMENT I_ILMPOLICY$
CDB_HEATMAP_TOP_OBJECTS GV$IM_ADOELEMENTS I_ILMRESULTS_STATUS
CDB_HEATMAP_TOP_TABLESPACES GV$IM_ADOTASKDETAILS KU$_ILM_POLICY_LIST_T
CDB_HEAT_MAP_SEGMENT GV$IM_ADOTASKS KU$_ILM_POLICY_T
CDB_HEAT_MAP_SEG_HISTOGRAM HEAT_MAP_EXTENT_STAT$ KU$_ILM_POLICY_VIEW
CDB_ILMDATAMOVEMENTPOLICIES HEAT_MAP_STAT$ KU$_ILM_POLICY_VIEW2
CDB_ILMEVALUATIONDETAILS ILM$ KU$_TBS_ILM_POLICY_T
CDB_ILMOBJECTS ILMOBJ$ KU$_TBS_ILM_POLICY_VIEW
CDB_ILMPARAMETERS ILMPOLICY$ PRVT_ILM
CDB_ILMPOLICIES ILM_CONCURRENCY$ USER_HEAT_MAP_SEGMENT
CDB_ILMRESULTS ILM_DEPENDANT_OBJ$ USER_HEAT_MAP_SEG_HISTOGRAM
CDB_ILMTASKS ILM_DEP_EXECUTIONDETAILS$ USER_ILMDATAMOVEMENTPOLICIES
C_ADO_IMPARAM ILM_EXECUTION$ USER_ILMEVALUATIONDETAILS
C_ILM_ATTRIBUTE ILM_EXECUTIONDETAILS$ USER_ILMOBJECTS
C_ILM_PARAM ILM_EXECUTIONID USER_ILMPOLICIES
DBA_HEATMAP_TOP_OBJECTS ILM_PARAM$ USER_ILMRESULTS
DBA_HEATMAP_TOP_TABLESPACES ILM_RESULTS$ USER_ILMTASKS
DBA_HEAT_MAP_SEGMENT ILM_RESULT_STAT$ V$HEAT_MAP_SEGMENT
DBA_HEAT_MAP_SEG_HISTOGRAM ILM_SEQ$ V$IM_ADOELEMENTS
DBA_ILMDATAMOVEMENTPOLICIES I2_HEATMAPSTAT$ V$IM_ADOTASKDETAILS
DBA_ILMEVALUATIONDETAILS I_ADOIMSEGTD_ID V$IM_ADOTASKS
DBA_ILMOBJECTS I_ADOIMSEGTD_OBJ WRI$_HEATMAP_TOPN_DEP1
DBA_ILMPARAMETERS I_ADOIMTASKS_ID WRI$_HEATMAP_TOPN_DEP2
DBA_ILMPOLICIES I_HEATMAPEXTSTAT$ WRI$_HEATMAP_TOP_OBJECTS
DBA_ILMRESULTS I_HEATMAPSTAT$ WRI$_HEATMAP_TOP_TABLESPACES
DBA_ILMTASKS I_ILM$ _SYS_HEAT_MAP_SEG_HISTOGRAM
DBMS_FEATURE_HEATMAP    
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 'tier1.dbf'
SIZE 10M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE tier2_ts DATAFILE 'tier2.dbf'
SIZE 10M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE tier3_ts DATAFILE 'tier3.dbf'
SIZE 10M 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_2018_q3 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY'))
TABLESPACE tier3_ts,
PARTITION orders_2019_q4 VALUES LESS THAN (TO_DATE('01/01/2020', 'DD/MM/YYYY'))
TABLESPACE tier3_ts,
PARTITION orders_2020_q1 VALUES LESS THAN (TO_DATE('01/01/2021', 'DD/MM/YYYY'))
TABLESPACE tier2_ts
ILM ADD POLICY TIER TO tier3_ts READ ONLY SEGMENT AFTER 3 MONTHS OF NO ACCESS,
PARTITION orders_2021_q2 VALUES LESS THAN (TO_DATE('01/07/2021', 'DD/MM/YYYY'))
TABLESPACE tier2_ts
ILM ADD POLICY TIER TO tier3_ts READ ONLY SEGMENT AFTER 3 MONTHS OF NO ACCESS,
PARTITION orders_2021_q3 VALUES LESS THAN (TO_DATE('01/10/2021', 'DD/MM/YYYY'))
TABLESPACE tier1_ts
ILM ADD POLICY TIER TO tier2_ts READ ONLY SEGMENT AFTER 1 MONTHS OF NO ACCESS,
PARTITION orders_2021_q4 VALUES LESS THAN (TO_DATE('01/01/2022', '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 'flash_array.dbf' SIZE 10M
AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE legacy_tbs DATAFILE '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 SELECT tablespace_name, ROUND(SUM(MB_USED),2) AS MB_USED, ROUND(SUM(MB_FREE),2) AS MB_FREE
FROM (
      SELECT tablespace_name, (bytes/(1024*1024)) AS MB_USED, NULL AS MB_FREE
      FROM dba_data_files
      UNION
      SELECT tablespace_name, NULL AS MB_USED, (bytes/(1024*1024)) AS MB_FREE
      FROM dba_free_space)
      WHERE tablespace_name IN ('SSD_TBS', 'LEGACY_TBS')
GROUP BY tablespace_name;

TABLESPACE_NAME      MB_USED    MB_FREE
----------------- ---------- ----------
LEGACY_TBS                10       3.94
SSD_TBS                   10       3.63
Tiering: Create Storage Policy 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 cond_days
FROM dba_ilmdatamovementpolicies
ORDER BY policy_name;

POLICY_NAME ACTION_TYPE SCOPE   COMPRESSION_LEVEL TIER_TBS   CONDITION_TYPE COND_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

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

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

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


exec dbms_ilm_admin.customize_ilm(dbms_ilm_admin.tbs_percent_free,95);

PL/SQL procedure successfully completed.

exec dbms_ilm_admin.customize_ilm(dbms_ilm_admin.tbs_percent_used,5);

PL/SQL procedure successfully completed.

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.


DECLARE
 retVal NUMBER;
BEGIN
  dbms_ilm.execute_ilm(ilm_scope => dbms_ilm.scope_schema, execution_mode => dbms_ilm.ilm_execution_offline, task_id => retVal);
  dbms_output.put_line(retVal);
END;
/
4

PL/SQL procedure successfully completed.

SELECT tablespace_name, segment_name
FROM dba_segments
WHERE owner = 'SCOTT'
AND segment_name='EMPLOYEE';

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


col start_time format a30

SELECT task_id, start_time
FROM dba_ilmtasks
ORDER BY 2;

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


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

SELECT task_id, object_owner, object_name, object_type, selected_for_execution
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
Database Security
DBMS_HEAT_MAP
DBMS_ILM
DBMS_ILM_ADMIN
Startup Parameters
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx