12c Database : Automatic Data Optimization II – Practice

In the last post you have seen how ADO helps us to cut down the storage costs and also improvise the efficiency.

In this post we will test and Before to drill down to practice, ADO works in two steps.

1. When the instance level heatmap is on, the real time statistics i.e data usage is marked and collected as the data life cycle goes on. The statistics will be stored in v$heat_map_segment and periodically flushes to table like heat_map_stats$ through scheduler jobs. There on the data usage tracking can be viewed by dba_views like dba_heat_map_segment and dba_heat_map_seg_histogram
2. Once the heat map tracking is on, you will need to create policies called ADO policies via alter statement to the tablespace/table etc "ADD POLICY". Once the policy is added, the jobs will be created in scheduler and periodically an policy run i.e compress or move to different tier will be taken care depends on the data age. The package name for ADO api is dbms_ilm

To simulate the case, Let's create the following (the following is based on Oracle Learning library example written here)

Enable Heatmap tracking on at database level

grant execute on set_stat to scott;

alter system set heat_map=on scope=both;

Create a procedure to simulate the data to be aged

CREATE OR REPLACE PROCEDURE set_stat (object_id      number,
data_object_id number,
n_days         number,
p_ts#            number,
p_segment_access number)
as
begin
insert into sys.heat_map_stat$
(obj#,
dataobj#,
track_time,
segment_access,
ts#)
values
(object_id,
data_object_id,
sysdate - n_days,
p_segment_access,
p_ts# );
commit;
end;
/

Set the days for heat map tracking of data

exec dbms_ilm_admin.set_heat_map_start(start_date => sysdate - 30);

Create two tablespaces (one on tier 1 and another cheap storage tier i.e tier 2, for this example i created the tablespaces on same tier ofcourse)

create tablespace high_perf_tbs datafile '/oradata/Geek DBA12c/pdb1/high_perf_tbs1.dbf';

create tablespace low_perf_tbs datafile '/oradata/Geek DBA12c/pdb1/low_perf_tbs1.dbf';

Create the test object and simulate the data

create table myobjects (owner varchar2(30), object_name varchar2(30), object_type varchar2(25), created date) Partition by range (created) (partition p_old values less than (to_date ('01-JUL-2013','DD-MON-YYYY')) tablespace high_perf_tbs, partition p_new values less than (maxvalue)) tablespace high_perf_tbs;

insert into myobjects (select owner,object_name,object_type,created from all_objects);

Check the status of the object and tablespace

SQL> select sum(bytes)/1048576 from user_segments where
    segment_name='MYOBJECTS';

SUM(BYTES)/1048576
------------------
320

SQL> select count(*) from myobjects;

COUNT(*)
----------
2360288

SQL> select table_name,compress,compress_for from dba_tables where table_name='MY_OBJECTS';

TABLE_NAME    COMPRESS    COMPRESS_FOR
-----------    --------    ------------
MY_OBJECTS    DISABLED   

Add the policy to the object

1. for advanced row compression for the data that older than 7 days
2. for advanced query compression for the data older than 15 days
3. for moving to different tier storage tablespace to low cost tbs if tablespace reaches 85%

ALTER TABLE scott.myobjects ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;

ALTER TABLE SCOTT.MYOBJECTS ADD POLICY COMPRESS FOR QUERY LOW AFTER 30 DAYS OF NO MODIFICATION;

ALTER TABLE myobjects MODIFY PARTITION p_old ILM ADD POLICY TIER TO low_cost_tbs;

Access the data.

1. simulate that data is older than 7 days and run the policy job, verify the row compression in place

alter session set nls_date_format='dd-mon-yy hh:mi:ss';

declare
v_obj# number;
v_dataobj# number;
v_ts#      number;
begin
select object_id, data_object_id into v_obj#, v_dataobj#
from all_objects
where object_name = 'MYOBJECTS'
and owner = 'SCOTT';
select ts# into v_ts#
from sys.ts$ a,
dba_segments b
where  a.name = b.tablespace_name
and  b.segment_name = 'MYOBJECTS';
commit;
sys.set_stat
(object_id         => v_obj#,
data_object_id    => v_dataobj#,
n_days            => 7,
p_ts#             => v_ts#,
p_segment_access  => 1);
end;
/

 

SQL> conn scott/tiger
Connected.
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;
/

We can now query the USER_ILMTASKS view and see that the ADO job has been started and the ILM policy we have defined has been earmarked for execution.

select task_id, start_time as start_time from user_ilmtasks;
TASK_ID        START_TIME
----------    ---------------------------------------------------------------------------
7        09-SEP-13 12.25.24.768834 PM

select task_id, job_name, job_state, completion_time completion from user_ilmresults;
TASK_ID        JOB_NAME    JOB_STATE    COMPLETION
----------    --------------------------------------------------------------------------------
8        ILMJOB1116    JOB CREATED

select task_id, policy_name, object_name, selected_for_execution, job_name from user_ilmevaluationdetails
where task_id=11;

TASK_ID        POLICY_NAME    OBJECT_NAME    SELECTED_FOR_EXECUTION    JOB_NAME
----------   --------------------------------------------------------------------------------
8        P3        MYOBJECTS                ILMJOB1116

We can now see that the table has been compressed and the ADO job has been completed.
select task_id, job_name, job_state, completion_time completion from user_ilmresults;
TASK_ID        JOB_NAME        JOB_STATE        COMPLETION
----------  -------------------------------------------------------------------------------
11        ILMJOB1116        COMPLETED SUCCESSFULLY    01-DEC-13 12.49.55.678970 PM

          2.  simulate that data is older than 15 days and run the policy job, verify the query compression in place(restart the db, or manual run the job)

alter session set nls_date_format='dd-mon-yy hh:mi:ss';

declare
v_obj# number;
v_dataobj# number;
v_ts#      number;
begin
select object_id, data_object_id into v_obj#, v_dataobj#
from all_objects
where object_name = 'MYOBJECTS'
and owner = 'SCOTT';
select ts# into v_ts#
from sys.ts$ a,
dba_segments b
where  a.name = b.tablespace_name
and  b.segment_name = 'MYOBJECTS';
commit;
sys.set_stat
(object_id         => v_obj#,
data_object_id    => v_dataobj#,
n_days            => 15,
p_ts#             => v_ts#,
p_segment_access  => 1);
end;
/

We can now query the USER_ILMTASKS view and see that the ADO job has been started and the ILM policy we have defined has been earmarked for execution.

select task_id, start_time as start_time from user_ilmtasks;
TASK_ID        START_TIME
----------    ---------------------------------------------------------------------------
7        01-DEC-13 13.10.24.654834 PM

select task_id, job_name, job_state, completion_time completion from user_ilmresults;
TASK_ID        JOB_NAME    JOB_STATE    COMPLETION
----------    --------------------------------------------------------------------------------
8        ILMJOB1210    JOB CREATED

select task_id, policy_name, object_name, selected_for_execution, job_name from user_ilmevaluationdetails
where task_id=11;

TASK_ID        POLICY_NAME    OBJECT_NAME    SELECTED_FOR_EXECUTION    JOB_NAME
----------   --------------------------------------------------------------------------------
8        P4        MYOBJECTS                ILMJOB1210

We can now see that the table has been compressed and the ADO job has been completed.
select task_id, job_name, job_state, completion_time completion from user_ilmresults;
TASK_ID        JOB_NAME        JOB_STATE        COMPLETION
----------  -------------------------------------------------------------------------------
11        ILMJOB1210        COMPLETED SUCCESSFULLY    01-DEC-13 13.26.44.98070 PM

         Check the size of the table after compression

select compression, compress_for FROM user_tables where table_name = 'MYOBJECTS';

COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED  ADVANCED

select sum(bytes)/1048576 from user_segments where segment_name='MYOBJECTS';
SUM(BYTES)/1048576
------------------
60

          3. Move the table partition to low cost storage, when the tablespace is 85% full

Tablespace sizes

Tablespace                      Size (MB)  Free (MB)     % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
LOW_COST_TBS                           25         24         96          4
HIGH_PERF_TBS                          25          8         32         68

Check ILM Params , the below shows the tablespace percent used 85 and free 25, means if tablespace usage came to 85% then the policy will execute the partition will move to low cost storage

col name format A20
col value format 9999
select * from dba_ilmparameters;

NAME                 VALUE
-------------------- -----
ENABLED                  1
JOB LIMIT               10
EXECUTION MODE           3
EXECUTION INTERVAL      15
TBS PERCENT USED        85
TBS PERCENT FREE        25

Insert more data into the table so that tablespace get full  and check user_ilmobjects

insert into myobjects  (select owner,object_name,object_type,created from all_objects);

select * from user_ilmobjects where object_type='TABLE PARTITION'

POLICY_NAME    OBJECT_OWNER    OBJECT_NAME    SUBOBJECT_NAME    OBJECT_TYPE        INHERITED_FROM       ENA
-------------    -------------    -------------    -------------    -------------    -------------    -------------
P25        SCOTT        MYOBJECTS    P_OLD        TABLE PARTITION    POLICY NOT INHERITED YES

Execute the policy

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

select task_id, job_name, job_state,
to_char(completion_time,’dd-MON-yyyy’)completion
from user_ilmresults;

TASK_ID    JOB_NAME    JOB_STATE COMPLETION
--------------------------------------------
97    ILMJOB1204      COMPLETED SUCCESSFULLY 10-SEP-2013

select SELECTED_FOR_EXECUTION from user_ilmevaluationdetails  where task_id=97;

SELECTED_FOR_EXECUTION
---------------------
SELECTED FOR EXECUTION

Now check that the partition P_OLD has been relocated.

select partition_name,tablespace_name from user_tab_partitions where table_name='MYOBJECTS';

PARTITION_ TABLESPACE_NAME
---------- ------------------------------
P_NEW      HIGH_PERF_TBS
P_OLD      LOW_COST_TBS

Tablespace sizes

Tablespace                      Size (MB)  Free (MB)     % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
LOW_COST_TBS                           25          8         32         68
HIGH_PERF_TBS                          25         16         64         36