Compression in Oracle DB

Compression has been around for quite a while in the Oracle database, but it received signficant improvements in version 11g. For example, now it also works on transaction level, which makes it an option for OLTP databases. It will improve both disk space requirement & db performance, as we see in below example.

1) Create test environment: Create two identical tables, one compressed other uncompressed. Then fill them with approx 150000 rows:

CREATE TABLE OBJ_COMP AS SELECT * FROM DBA_OBJECTS WHERE 1=0;
  CREATE TABLE OBJ_UNCOMP AS SELECT * FROM DBA_OBJECTS WHERE 1=0;
  ALTER TABLE OBJ_COMP COMPRESS FOR OLTP
  INSERT INTO OBJ_COMP SELECT * FROM DBA_OBJECTS;
  INSERT INTO OBJ_COMP SELECT * FROM DBA_OBJECTS;
  INSERT INTO OBJ_UNCOMP SELECT * FROM DBA_OBJECTS;
  INSERT INTO OBJ_UNCOMP SELECT * FROM DBA_OBJECTS;  --To check compression
SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM USER_TABLES;
TABLE_NAME   COMPRESS COMPRESS_FOR
OBJ_COMP     ENABLED  OLTP 
OBJ_UNCOMP   DISABLED  

2) Check occupied space:

SELECT SEGMENT_NAME, BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'COMP%';
 SEGMENT_NAME   BLOCKS
 OBJ_COMP       896
 OBJ_UNCOMP     2176

The compressed table saves almost 60% on blocks. In “reality” this depends on how the data is structured. The more identical table cells, the better the compression rate.

3) Performance: Lets do a full table scan on both tables. To avoid any confusion on buffering, flush the buffer cache.

ALTER SYSTEM FLUSH BUFFER_CACHE;
 System altered.
SELECT * FROM OBJ_UNCOMP WHERE OBJECT_ID < 800;
 4554 rows selected.
 Elapsed: 00:00:02.72
SELECT * FROM OBJ_COMP WHERE OBJECT_ID < 800;
 4554 rows selected.
 Elapsed: 00:00:03.75

As you can see, Select on the compressed table took a bit longer. When we enable tracing and analyze the tracefile with tkprof, we can see the reason for this. First, the uncompressed table:

SELECT * FROM OBJ_UNCOMP WHERE OBJECT_ID < 800
 call     count       cpu    elapsed       disk      query    current        rows
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 Parse        2      0.00       0.00          0          1          0           0
 Execute      2      0.00       0.00          0          0          0           0
 Fetch      610      0.17       0.14       2261       5136          0        9108
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 total      614      0.17       0.14       2261       5137          0        9108

And, for comparison, the compressed table:

SELECT * FROM OBJ_COMP WHERE OBJECT_ID < 800
 call     count       cpu    elapsed       disk      query    current        rows
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 Parse        2      0.01       0.00          0          1          0           0
 Execute      2      0.00       0.00          0          0          0           0
 Fetch      610      0.19       0.19        846       2302          0        9108
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 total      614      0.20       0.19        846       2303          0        9108

So we do, in fact save a lot of disk reads here, but for the price of spent CPU time. That makes sense, because oracle needs the CPU to uncompress the data before presenting it. If you are low on CPU as it seemed to be the case here, the prize surpasses the gain. We get similar results on DML:

INSERT INTO OBJ_UNCOMP SELECT * FROM DBA_OBJECTS WHERE ROWNUM < 2001;
 2000 rows created.
 Elapsed: 00:00:00.09
INSERT INTO OBJ_COMP SELECT * FROM DBA_OBJECTS WHERE ROWNUM < 2001;
 2000 rows created.
 Elapsed: 00:00:00.19
DELETE OBJ_UNCOMP WHERE OWNER='SOMEONE';
 69634 rows deleted.
 Elapsed: 00:00:03.86
DELETE OBJ_COMP WHERE OWNER='SOMEONE';
 69633 rows deleted.
 Elapsed: 00:00:05.19

Therefore it is a struggle between savings in Disk I/O versus overhead in CPU time. And in above case, CPU lost.

4) Conclusion: If your performance bottleneck is disk I/O and you have ample cpu cores, you almost certainly will benefit from using compression, because it saves a lot of disk reads. If you are on the other hand low on CPU, you might not always.

Compression Advisor: With Compression Advisor, a FREE database tool from Oracle, we can use our real data to estimate the compression results while using Oracle Advanced Compression Option. Find instructions for using Compression Advisor when you watch this quick overview about Oracle Advanced Compression

Advertisements