X

Database Storage Optimization best practices, tips and tricks and guidance from Database Compression Product Management

  • ILM
    June 24, 2014

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

Andy Rivenes
Product Manager

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.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.