Oracle Automatic Data Optimization

From Robs_Wiki
Jump to: navigation, search

Introduction

Oracle Automatic Data Optimization (ADO) is aimed to implement Information Life Cycle Management (ILM) into the database, with automatic ILM policies. ADO uses Heat Maps to track and marks data as it goes through life cycle changes: data access at segment-level and data modifications at block and segment-level. Block-level and segment-level statistics are collected in memory are stored in tables in the SYSAUX tablespace.

Row level policies are evaluated and executed every 15 mins by the MMON background process. Segment level policies are evaluated and executed daily once during maintenance window using a scheduler job. This scheduler job uses DBMS_ILM.EXECUTE_ILM_TASK procedure to evaluate and execute ADO policies.

Every task that is executed manually or automatically as scheduled will be recorded in DBA_ILMTASKS view. It stored creation time, start time and completion time of task and if task is completed, failed or still active.

Automatic Data Optimization is part of the seperately licensed Avanced Compression option.

View ILM settings in the database

Is ILM active?

 
show parameter heat_map

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
heat_map                             string      ON

Which ILM views are present in the database:

SQL> select view_name from dba_views where view_name like 'DBA_ILM%' order by 1;

VIEW_NAME
--------------------------------------------------------------------------------------------------------------------------------
DBA_ILMDATAMOVEMENTPOLICIES
DBA_ILMEVALUATIONDETAILS
DBA_ILMOBJECTS
DBA_ILMPARAMETERS
DBA_ILMPOLICIES
DBA_ILMRESULTS
DBA_ILMTASKS

What are the default ILM settings?

col name format A20
col value format 9999

select * from dba_ilmparameters;

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