Oracle Illustrated

Feb 21 2010   7:02AM GMT

Migrating from 9i to 11g – Compression



Posted by: Lakshmi Venkatesh
Tags:

Note: I have installed 11g on my laptop and tested the scripts from there – so the timing specified here might vary.

Compression is available in Oracle for sometime now. With Oracle 9i we can perform normal COMPRESSION – but, with DML operations the compression goes for a toss. Unless we perform direct path inserts, compression does not work as expected. In Oracle 11g COMPRESS FOR ALL OPERATIONS is available which enables the data to be still compressed with the DML operations also – even with 10 times data load !! This minimizes the overhead of using compression for OLTP tables. For partitioned tables compression can be controlled at the partition level – this feature allows the same table to have partitions that are compressed at very different levels. If there are two sets of compression one at table level and the other at partition level – the partition level compression overrides the compression at table level – this allows decently finer levels compressing and managing the tables.

Require additional license for Advanced Compression in 11g.

Here the block is compressed and not the row. This does not reduce the speed of DML’s because the compression does not happen when the row is inserted into the table. But actually happens as a trigger event (batch mode) – the rows are inserted un-compressed (normal way) – after certain number of rows are updated / inserted into the table then the compression happens on those inserted / updated rows in the block.

Important note: Compression is CPU intensive. So when the compression is enabled the CPU resource usage will be high.

How does compression work internally?

Oracle finds the repeating rows in the compressed table and puts them near the header of the block – “symbol table”. Each value in the column is assigned a symbol that replaces the actual value in the table. This symbol values size is smaller than the original data size. If there are more repeating data then the symbol table will be more compact. Net net, we can say that one of the key factors that drives this compression is repeating data in the table also.

Compressed table access via dblinks

It is said that it takes less time to fetch data across dblinks / network.

Note : The following is run from my PC

Oracle 9i (Normal compression) Oracle 11g (Advanced compression)
Create statement – 3072 Create statement -3072
Insert 10 times of load – 81920 Insert 10 times of load – 49152

Testing -

Oracle 9i

Table creation – DIRECT PATH COMPRESSION Vs NORMAL Vs OLTP COMPRESSION

DIRECT PATH COMPRESSION

CREATE TABLE obj_compress
COMPRESS
AS SELECT * FROM all_objects
/

Timing – Elapsed: 00:00:10.73

select sum(bytes)/1024 from user_segments where segment_name = ‘OBJ_COMPRESS’

3072

Uncompressed table

CREATE TABLE obj_uncompress
AS SELECT * FROM all_objects
/

select sum(bytes)/1024 from user_segments where segment_name = ‘OBJ_UNCOMPRESS’

8192

Timing : 00:00:09:79

OLTP Compression

CREATE TABLE obj_compress
COMPRESS FOR ALL OPERATIONS
AS SELECT * FROM all_objects
/

Timing – Elapsed: 00:00:08.57

select sum(bytes)/1024 from user_segments where segment_name = ‘OBJ_COMPRESS’

3072

Insertion based on select – Single load – DIRECT PATH COMPRESSION Vs NORMAL Vs OLTP COMPRESSION

DIRECT PATH COMPRESSION

Single Load

INSERT INTO obj_compress
SELECT * from all_objects

Timing – Elapsed: 00:00:08.43

select sum(bytes)/1024 from user_segments where segment_name = ‘OBJ_COMPRESS’

10240

Normal

Single Load

INSERT INTO obj_uncompress
SELECT * from all_objects

select sum(bytes)/1024 from user_segments where segment_name = ‘OBJ_UNCOMPRESS’

16384
OLTP Compression

Single Load

INSERT INTO obj_compress
SELECT * from all_objects

Timing – Elapsed: 00:00:13.29

select sum(bytes)/1024 from user_segments where segment_name = ‘OBJ_COMPRESS’

6144

Compression ratio for Single load: 1.6 : 2.7 : 1 (compressed vs OLTP compressed vs uncompressed ratio)

Insertion based on select 10 times load – DIRECT PATH COMPRESSION Vs NORMAL Vs OLTP COMPRESSION

DIRECT PATH COMPRESSION

INSERT INTO obj_compress
SELECT a.* from all_objects a, (select * from dual connect by level <= 10) b

Timing : Elapsed: 00:00:18.37

select sum(bytes)/1024 from user_segments where segment_name = ‘OBJ_COMPRESS’

81920

Normal

INSERT INTO obj_uncompress
SELECT a.* from all_objects a, (select * from dual connect by level <= 10) b

select sum(bytes)/1024 from user_segments where segment_name = ‘OBJ_UNCOMPRESS’

97280

Timing – Elapsed: 00:00:20.39

OLTP Compression

INSERT INTO obj_compress
SELECT a.* from all_objects a, (select * from dual connect by level <= 10) b
Timing – Elapsed: 00:02:11.39

select sum(bytes)/1024 from user_segments where segment_name = ‘OBJ_COMPRESS’

49152

Compression ratio for 10 times load: 1.18 : 1.97 : 1 (compressed vs OLTP compressed vs uncompressed ratio)

Note: OLTP UPDATE / DELETE / MERGE for 500 rows took too much of time – suspect mainly because I have installed 11g in my laptop and it has limited RAM and CPU. Test it in a environment with proper resource.

For one time load of data the data compression was close to 2.7 : 1. whereas with 10 times load of data the data compression ratio was 1.97:1. based on the above example at least 50-60% compression can be achieved. (90% performance gain is based on direct inserts. Real time scenarios we perform inserts quite differently).

Notice that the time taken to insert into COMPRESSED FOR ALL OPERATIONS table takes a little bit more time than inserting into DIRECT PATH ONLY compression table. This is due to the overhead in the CPU operations. Given the advantages of the compression – the slight minimal overhead for the DML operations are acceptable !!

Benefits -
1.Reduction of disk space
2.Extra savings on I/O and cache efficiency – Oracle operates directly on the compressed data without incurring the overhead to uncompress the data then use it.
3.Performance of full table scans where ever required also becomes more efficient
4.Fair reduction in the consistent gets as the blocks used to store the data is less

Net net – Oracle 11g’s OLTP compression is rocking !!

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: