Oracle | ADO: Automatic Data Optimization

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;