Automatic Data Optimization – How Do You Know It's Working?

This post is about Automatic Data Optimization (ADO) and how to tell if your policies are working. It's fairly easy to display ADO policies and even to see if they've run. It's not quite as straight forward though, to see if anything actually happened. Unfortunately there is no "log" of what events or actions actually occurred when the policy ran. There are a couple of events that can be set, but I've had only hit or miss success with them detailing what actually happened. For segment level compression and storage tiering it's fairly easy to just look at the results. It's a little harder with policies involving advanced row compression. That's because Oracle is intermixing compressed blocks with uncompressed ones. The easiest way that I've found is to use the function dbms_compression.get_compression_type. This function will return a value that can be used to determine the compression for each row input[1]. This may not be practical on a very large number of rows, but I think that once you've prototyped the behavior you can be pretty sure that the feature is actually working in practice.

The following example was run on database version 12.1.0.1.0 and will create a table similar to emp, add some rows to it, enable an ADO policy to compress rows that have not been modified, and then invoke that policy and show what happens. The one "trick" performed is to set the "POLICY CONDITION" to 1 to change the interpretation of DAYS to SECONDS. This is documented in the PL/SQL Packages and Types Reference for the DBMS_ILM_ADMIN package. The CUSTOMIZE_ILM procedure can be used to set the POLICY CONDITION parameter to a 1. Note that this is only done to make testing simpler.

SQL> col name format a40;
SQL> 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
POLICY CONDITION                                  0

7 rows selected.

SQL> exec dbms_ilm_admin.customize_ilm(dbms_ilm_admin.POLICY_TIME,
 dbms_ilm_admin.ILM_POLICY_IN_SECONDS);

PL/SQL procedure successfully completed.

SQL> 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
POLICY CONDITION                                  1

7 rows selected.

SQL>

Next a new table is created, populated, row compression status is checked and then an ADO policy is created.

 
CREATE TABLE emp3
  ( empno number,
    salary number,
    deptid number,
    name VARCHAR2(100)
  )
/

SQL> desc emp3
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPNO                                              NUMBER
SALARY                                             NUMBER
DEPTID                                             NUMBER
NAME                                               VARCHAR2(100)

SQL>

insert into emp3
(empno, salary, deptid, name)
select 
  empno, sal, deptno, ename 
  from emp;
commit;

begin
  for i in 1..5 loop
    INSERT INTO emp3
    SELECT
      empno,
      salary,
      deptid,
      name
    FROM emp3;
    commit;
  end loop;
end;
/

SQL> select count(*) from emp3;

  COUNT(*)
----------
       448

SQL> @row_compression

COMPRESSION_TYPE                                     NUM_ROWS
-------------------------------------------------- ----------
No Compression                                            448

SQL>

alter table emp3 ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW 
AFTER 1 DAY OF NO MODIFICATION;


set lines 200
set pages 9999
col policy_name format a11;
col object_owner format a10;
col object_name format a20;
col object_type format a20;
col subobject_name format a20;
col enabled format a7;
select * from user_ilmobjects;

POLICY_NAME OBJECT_OWN OBJECT_NAME          SUBOBJECT_NAME       OBJECT_TYPE          
----------- ---------- -------------------- -------------------- -------------------- 
P137        SCOTT      EMP3                                      TABLE                

  INHERITED_FROM       ENABLED
  -------------------- -------
  POLICY NOT INHERITED YES

col policy_name format a11;
col tablespace format a20;
col enabled format a7;
select * from user_ilmpolicies;

POLICY_NAME POLICY_TYPE   TABLESPACE           ENABLED
----------- ------------- -------------------- -------
P137        DATA MOVEMENT                      YES

col policy_name format a11;
col tier_tablespace format a20;
col compression_level format a20;
col tier_status format a20;
col custom_function format a20 wrapped;
select * from user_ilmdatamovementpolicies;

POLICY_NAME ACTION_TYPE SCOPE   COMPRESSION_LEVEL    TIER_TABLESPACE      TIER_STATUS          
----------- ----------- ------- -------------------- -------------------- -------------------- 
P137        COMPRESSION ROW     ADVANCED                                                       

  CONDITION_TYPE         CONDITION_DAYS CUSTOM_FUNCTION
  ---------------------- -------------- --------------------
  LAST MODIFICATION TIME              1

Next we'll force the running of the ADO policy and check to see if any rows were compressed. Remember that we specified no modifications in the last 1 day and that days are being interpreted as seconds.

 
declare
  v_executionid number;
begin
  dbms_ilm.execute_ilm (
    owner=>'SCOTT',
    object_name=>'EMP3',
    policy_name=>'P137',
    execution_mode=>dbms_ilm.ilm_execution_online,
    task_id=>v_executionid);
end;
/

SQL> @row_compression

COMPRESSION_TYPE                                     NUM_ROWS
-------------------------------------------------- ----------
Basic compression level                                   448

SQL>

We see that all of the rows have been compressed. Based on our policy this is what we would expect, but why does the compression type specify "Basic compression level"? Apparently there is a bug, bug number 17947871, which reports basic level compression rather than advanced row compression. If the policy is defined at the segment level then "Advanced compression level" is reported which is more along the lines of what you would expect. However, since the two compression types are equivalent in practice this should not be a problem.

One other comment is that my example wound up choosing all of the rows to compress. In reality we would expect that only some of the table blocks would have rows that would meet the policy criteria. Remember that the entire block is compressed so all of the rows in the block must meet the policy criteria for the block to be eligible for compression.

NOTE: The row_compression.sql script is available here.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

The Database Storage Optimization blog is written by the Oracle product management team and highlights features and issues associated with database storage optimization. The views expressed on this blog are those of the author(s) and do not necessarily reflect the views of Oracle.

Search

Archives
« April 2015
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
  
       
Today