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;
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;
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;
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.
-- 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;