Compress for OLTP

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Fri, 28 Dec 2012 04:13:03 +0000 (UTC)
Message-ID: <pan.2012.12.28.04.13.03_at_gmail.com>

There is a lengthy discussion on oracle-l about "COMPRESS FOR OLTP" option which costs approximately $10k per CPU thread. Being a curious George that I am, I decided to test. Here are two SQL script that I used to test:

• DDL.SQL *** drop table tst_compress purge; drop table tst_nocompress purge;

CREATE TABLE SCOTT.TST_COMPRESS
   (ID NUMBER(15,0) NOT NULL,
    VAL VARCHAR2(10),
    CONSTRAINT TST_COMPRESS_PK PRIMARY KEY (ID)    )
SEGMENT CREATION IMMEDIATE
STORAGE (INITIAL 100M NEXT 100M)
COMPRESS FOR OLTP NOLOGGING
TABLESPACE USERS; CREATE TABLE SCOTT.TST_NOCOMPRESS
   (ID NUMBER(15,0) NOT NULL,
   VAL VARCHAR2(10),
   CONSTRAINT TST_NOCOMPRESS_PK PRIMARY KEY (ID)    )
SEGMENT CREATION IMMEDIATE
STORAGE (INITIAL 100M NEXT 100M)
NOLOGGING TABLESPACE USERS;

• TEST.SQL *** alter session set tracefile_identifier=CTEST; alter session set events='10046 trace name context forever, level 12';

insert /*+ APPEND */into tst_nocompress(id,val) select level,dbms_random.string('A',10)
from dual
connect by level <= 1000000;
commit;

insert /*+ APPEND */ into tst_compress(id,val) select level,dbms_random.string('A',10)
from dual
connect by level <= 1000000;
commit;

analyze table tst_compress compute statistics; analyze table tst_nocompress compute statistics;

alter system flush buffer_cache;

select val from tst_compress where id=123456; select val from tst_nocompress where id=123456;

select /*+ FULL(t) */ avg(id) from tst_compress t; select /*+ FULL(t) */ avg(id) from tst_nocompress t;

The idea was to test the speed of the table load, unique index scan and a full table scan. The hint was necessary to prevent optimizer from choosing the full index scan, which would defeat the purpose. Results were strangely similar, there was no advantage on either side. Frankly, I was perplexed by the results, I couldn't figure out why was the difference so minimal. Here is what solved the problem:

SQL> select table_name,compression,compress_for,blocks   2 from user_tables
  3 where table_name like 'TST%';

TABLE_NAME COMPRESS COMPRESS_FOR BLOCKS

------------------------------ -------- ------------ ----------
TST_COMPRESS		       ENABLED	OLTP		   2940
TST_NOCOMPRESS		       DISABLED 		   2940

Elapsed: 00:00:00.13

The tables are exactly equal in size, there is no effect of the compression for OLTP on the overall size of the table. Of course that there will be no difference in full table scan, the tables are equal in size. I then tested the basic compression and it result in some compression:

SQL> select table_name,compression,compress_for,blocks   2 from user_tables
  3 where table_name like 'TST%';

TABLE_NAME COMPRESS COMPRESS_FOR BLOCKS

------------------------------ -------- ------------ ----------
TST_NOCOMPRESS		       DISABLED 		   2940
TST_COMPRESS		       ENABLED	BASIC		   2644

Elapsed: 00:00:00.14
SQL> Oracle's compression algorithm simply isn't very efficient despite the fact that the table used is not very good for compression because it's generated using random strings which do not repeat themselves.

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

Received on Fri Dec 28 2012 - 05:13:03 CET