Oracle 12c ILM compression and tearing on heap tables

References: here and here and here

Example 1

Using heap table with ADVANCED ROW COMPRESSION and no movement to tier.

SQL> 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 USERS;

Table created.

SQL> insert into scott.employee (empno, ename, job, mgr, hiredate, sal, comm, deptno)
select empno, ename, job, mgr, hiredate, sal, comm, deptno
from scott.emp;
14 rows created.
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;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from scott.employee;

COUNT(*)
----------
3584

 

We check the tracking on the table

SQL> alter session set nls_date_format='dd-mon-yy hh:mi:ss';
Session altered.
select OBJECT_NAME,SEGMENT_WRITE_TIME , SEGMENT_READ_TIME, FULL_SCAN
FROM dba_heat_map_segment WHERE OBJECT_NAME='EMPLOYEE' AND OWNER = 'SCOTT';

OBJECT_NAME SEGMENT_WRITE_TIME SEGMENT_READ_TIME FULL_SCAN
-------------- ------------------ ------------------ ------------------
EMPLOYEE 01-jul-14 09:18:35 01-jul-14 09:18:35


select object_name, track_time "Tracking Time",
segment_write "Segment write",
full_scan "Full Scan",
lookup_scan "Lookup Scan"
from DBA_HEAT_MAP_SEG_HISTOGRAM
where object_name='EMPLOYEE'
and owner = 'SCOTT';

OBJECT_NAME Tracking Time Segment write Full Scan Lookup Scan
-------------------- ------------------ -------------- ------------ ------------
EMPLOYEE 01-jul-14 YES YES NO

SQL> select compression, compress_for from user_tables where table_name = 'EMPLOYEE';

COMPRESS COMPRESS_FOR
-------- ------------------------------
DISABLED


SQL> analyze table SCOTT.EMPLOYEE compute statistics;

Table analyzed.

SQL>select object_name, nrows_uncmp, nrows_oltp, nrows_ehcc from sys.user_compression_stats;

no rows selected

We check the table space usage

SQL> select BYTES ,BLOCKS,TABLESPACE_NAME from user_segments where SEGMENT_NAME='EMPLOYEE';

BYTES BLOCKS TABLESPACE_NAME
---------- ---------- ------------------------------
327680 40 USERS

Adding ILM policy for advanced row compression

SQL> alter table scott.employee
ILM ADD POLICY
ROW STORE COMPRESS ADVANCED
SEGMENT
AFTER 30 DAYS OF NO MODIFICATION;
Table altered.

Checking ILM policy for advanced row compression

SQL> select policy_name, action_type, scope, compression_level, condition_type, condition_days
from user_ilmdatamovementpolicies
order by policy_name;

POLICY_NAME ACTION_TYPE SCOPE COMPRESSION_LEVEL CONDITION_TYPE CONDITION_DAYS
-------------- ----------- ------- -------------------- ---------------------- --------------
P49 COMPRESSION SEGMENT ADVANCED LAST MODIFICATION TIME 30

SQL> select policy_name, object_name, inherited_from, enabled from user_ilmobjects;

POLICY_NAME OBJECT_NAME INHERITED_FROM ENABLED
-------------------- -------------------- -------------------- ---------
P49 EMPLOYEE POLICY NOT INHERITED YES

SQL> select * from user_ilmpolicies;

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


SQL> connect / as sysdba
Connected.
alter session set nls_date_format='dd-mon-yy hh:mi:ss';

Session altered.

 

Simulate the passage of time

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 = 'EMPLOYEE'
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 = 'EMPLOYEE';
commit;
sys.set_stat
(object_id => v_obj#,
data_object_id => v_dataobj#,
n_days => 30,
p_ts# => v_ts#,
p_segment_access => 1);
end;
/
PL/SQL procedure successfully completed.

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size 2287816 bytes
Variable Size 452986680 bytes
Database Buffers 771751936 bytes
Redo Buffers 8933376 bytes
Database mounted.
Database opened.

SQL> select object_name, segment_write_time
from dba_heat_map_segment
where object_name='EMPLOYEE';
OBJECT_NAME SEGMENT_W
-------------------- ---------
EMPLOYEE 01-JUN-14

Trigger the table to compress

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

PL/SQL procedure successfully completed.


SQL> select task_id, start_time as start_time from user_ilmtasks;

TASK_ID START_TIME
---------- ---------------------------------------------------------------------------
862 01-JUL-14 09.23.08.792230 PM

1 rows selected.


SQL> select task_id, job_name, job_state, completion_time completion,comments from user_ilmresults;

TASK_ID JOB_NAME JOB_STATE COMPLETION COMMENTS
-------- ------------ ------------------------ ---------------------------- -----------

862 ILMJOB940 COMPLETED SUCCESSFULLY 01-JUL-14 09.23.10.630807 PM

1 rows selected.

SQL> select task_id, policy_name, object_name,
selected_for_execution, job_name
from user_ilmevaluationdetails;
TASK_ID POLICY_NAME OBJECT_NAME SELECTED_FOR_EXECUTION JOB_NAME
---------- ---------------- ----------------- --------------------------------- --------------------
862 P49 EMPLOYEE SELECTED FOR EXECUTION ILMJOB940

Check the result

SQL> analyze table scott.employee compute statistics;

Table analyzed.

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

COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED ADVANCED <-- Compression is active

SQL> select object_name, nrows_uncmp, nrows_oltp, nrows_ehcc from sys.user_compression_stats;

OBJECT_NAME NROWS_UNCMP NROWS_OLTP NROWS_EHCC
----------------- ----------- ---------- ----------
EMPLOYEE 0 3584 0


SQL> select BYTES ,BLOCKS,TABLESPACE_NAME from user_segments where SEGMENT_NAME='EMPLOYEE';

BYTES BLOCKS TABLESPACE_NAME
---------- ---------- ------------------------------

131072 16 USERS <-- Space saving factor 40%

 

 

Example 2

Using heap table with basic segment COMPRESSION and no movement to tier.

The only change is the add policy command as follow:

alter table employee ilm add policy compress basic segment after 1 months of no modification;


The result is the following after the whole test case:

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

COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED BASIC <-- Compression is active

SQL> select BYTES ,BLOCKS,TABLESPACE_NAME from user_segments where SEGMENT_NAME='EMPLOYEE';

BYTES BLOCKS TABLESPACE_NAME
---------- ---------- ------------------------------

131072 16 USERS <-- Same space saving factor 40%

Example 3

Using heap table with basic segment COMPRESSION and movement to tier.

The only change is the add policy command as follow:

alter table employee ilm add policy compress basic segment after 1 months of no modification;

alter table employee ilm add policy tier to ARCH;

 

Set parameters for tiering

Connect / as sysdba

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

 

The result is the following after the whole test case:

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

COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED BASIC <-- Compression is active


SQL> select BYTES ,BLOCKS,TABLESPACE_NAME from user_segments where SEGMENT_NAME='EMPLOYEE';

BYTES BLOCKS TABLESPACE_NAME
---------- ---------- ------------------------------

131072 16 ARCH <-- Same space saving factor 40% table is now moved to arch tablespace

Example 4

Using heap table with COMPRESSION for archive high segment and no movement to tier.

The only change is the add policy command as follow:

alter table employee ilm add policy compress for archive high segment after 1 months of no modification;


The result is the following after the whole test case:

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

COMPRESS COMPRESS_FOR
-------- ------------------------------
DISABLED <-- Compression is NOT active Why

Checking job gives the following message…

select task_id, job_name, job_state, completion_time completion, comments from user_ilmresults;

TASK_ID JOB_NAME JOB_STATE COMPLETION COMMENTS
---------- --------------- ---------- ----------------------------- ------------------------------

882 ILMJOB1024 FAILED 01-JUL-14 11.32.33.179943 PM ORA-64307: Exadata Hybrid

Columnar Compression is not

not supported for tablespaces in this storage...

But the documentation is unclear. You have to go to compression technique definition to, at the end, understand that this mode requires HCC!

 

The following two tabs change content below.

Jacques

I am Oracle Certified Master 11g & 12c database architect with significant experience in heterogeneous environments, and strong ability to lead complex and critical projects requiring multiple technical implementations. at Trivadis SA

Leave a Reply

Your email address will not be published. Required fields are marked *