Automatic Data Optimization (ADO)

                Automatic Data Optimization (ADO) allows you to create policies for data compression (Smart Compression) and data movement, to implement storage and compression tiering. Smart Compression refers to the ability to utilize Heat Map information to associate compression policies, and compression levels, with actual data usage. Oracle Database periodically evaluates ADO policies, and uses the information collected by Heat Map to determine when to move and / or compress data. All ADO operations are executed automatically and in the background, without user intervention.

               ADO policies can be specified at the segment or row level for tables and table partitions. Policies will be evaluated and executed automatically in the background during the maintenance window. ADO policies can also be evaluated and executed anytime by a DBA, manually or via a script.

                ADO policies specify what conditions (of data access) will initiate an ADO operation – such as no access, or no modification, or creation time – and when the policy will take effect – for example, after n days or months or years. Conditions in ADO policies are not limited to Heat Map data: you can also create custom conditions using PL/SQL functions, extending the flexibility of ADO to use your own data and logic to determine when to move or compress data.

                ADO  work in conjunction with the Heat Map feature and allows us to create new policies which specify conditions to dictate when data will be moved or compressed based on statistics related to the data usage at the 1) tablespace, 2) object and even row level .

Real-time data access statistics are collected in memory in the V$HEAT_MAP_SEGMENT view and then those are regularly pushed by DBMS_SCHEDULER_JOBS to tables which residing on disk.

We have DBA views like  DBA_HEAT_MAP_SEG_HISTOGRAM and DBA_HEAT_MAP_SEGMENT.

Heat Map is enabled at the instance level by setting the pfile/sp-file parameter HEAT_MAP to ON.

Here's is an example.....

1) Connect to the noncdb database as sys.

sqlplus sys/oracle@localhost:1521/noncdb as sysdba

2) Create two tablespaces, . Each tablespace should have a 10M data file.

a) ilmtbs

b)  low_cost_store.

create tablespace ilmtbs datafile '/u01/app/oracle/oradata/noncdb/ilmtbs1.dbf' size 10m reuse autoextend off extent management local;

create tablespace low_cost_store datafile '/u01/app/oracle/oradata/noncdb/lowcoststore1.dbf' size 10m reuse
autoextend off extent management local;

3) Unlock the SCOTT account, grant it unlimited quota on the two tablespaces. Grant the necessary privileges to SCOTT.

alter user scott identified by tiger account unlock;

alter user scott quota unlimited on ilmtbs;

alter user scott quota unlimited on low_cost_store;

grant alter tablespace, select any dictionary to scott;

grant all on ts$ to scott;

grant all on dba_segments to scott;

4) Enable heat map tracking.

alter system set heat_map=on scope=both;

5) Create a table as below:

create table scott.employee (EMPNO NUMBER(4) NOT NULL,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,
SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2))tablespace ilmtbs;

6)  Insert about 3500 rows into SCOTT.employee table.

insert into scott.employee (empno, ename, job, mgr, hiredate, sal, comm, deptno)select empno, ename, job, mgr, hiredate, sal, comm, deptno from scott.emp;

declare
blowup PLS_INTEGER := 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;
end loop;
end;
/

select count(*) from scott.employee;

This should fill up more than 5% of the tablespace, so that there is less than 95% free space.

7) Verify that the table is stored in the ilmtbs tablespace.

select tablespace_name, segment_name from dba_segments where segment_name='EMPLOYEE';

8) Connect as scott to the database.

connect scott/tiger@localhost:1521/noncdb

9) Verify that heat map tracking collected statics for SCOTT.employee.

select OBJECT_NAME,SEGMENT_WRITE_TIME , SEGMENT_READ_TIME, FULL_SCAN

FROM user_heat_map_segment 

WHERE OBJECT_NAME='EMPLOYEE';

             10) Check the current freespace in the ilmtbs tablespace.

      col tablespace format A16

SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
Order by 4;

            Notice that %Free is already less than 95%.

           11)   Create a storage tiering policy on SCOTT.employee.

       alter table scott.employee ilm add policy tier to low_cost_store;

 If heat map tracking was not enabled, you will receive an error message when you try to create the policy.

           12)  Verify that the policy is added.

       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 fro

m u

ser_ilmdatamovementpolicies 

order by policy_name;

     select * from user_ilmobjects;

                 The ILM decision to move segments also depends on the default thresholds defined at the database   

                  level for all user-defined tablespaces. 

                   Set the TBS_PERCENT_FREE threshold  to 95% and the TBS_PERCENT_USED threshold to 5%.

         connect sys/oracle@localhost:1521/noncdb as sysdba

         col name format A20

         col value format 9999

         select * from dba_ilmparameters;

          EXEC dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,95)

          EXEC dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_USED,5)

          select * from dba_ilmparameters;

for the purposes of this tutorial, we cannot wait for the maintenance window to open that will trigger the automatic data optimization policies jobs. Instead, you are going to use the following PL/SQL block and trigger it as the table owner.

connect scott/tiger@localhost:1521/noncdb

      

      declare

      v_executionid number;

      begin

      dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA,

            execution_mode => dbms_ilm.ilm_execution_offline,

            task_id   => v_executionid);

      end;

      /

Check the current free space in ILMTBS tablespace. The LOW_COST_STORE may show a value for the column % Used, although the space used in ILMTBS may not have decreased. If this is the case, a few seconds later, run the same statement and you will see that the data dictionary has been updated to reflect the new situation.

      SELECT df.tablespace_name "Tablespace", 

df.bytes/(1024*

1024)"Size(MB)",   

      SUM(fs.bytes)/(1024 * 1024)"Free(MB)", 

Nvl(Round(SUM(fs.bytes)*100/    

      df.bytes),1)"% Free",

Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes)"%  

      Used" 

FROM dba_free_space fs, 

(SELECT tablespace_name,SUM(bytes) bytes

      FROM dba_data_files 

GROUP BY tablespace_name) df 

WHERE fs.tablespace_name    

      (+) = df.tablespace_name 

GROUP BY df.tablespace_name,df.bytes 

Order by 4;

             Verify the SCOTT.employee segment was moved to the low_cost_store tablespace.

       select tablespace_name, segment_name 

from dba_segments w

here     

       segment_name='EMPLOYEE';

View the results of the job that completed the movement operation.

        SELECT task_id, to_char(start_time, 'dd/mm/yyyy hh24:mi:ss') as    

        start_time  

FROM user_ilmtasks;

      select task_id,job_name,job_state,to_char(completion_time,'dd-MON-yyyy')    

      completion 

from user_ilmresults;

      SELECT * FROM user_ilmevaluationdetails;