Advanced Compression - Part 3

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Oct 21, 2015

Advanced Compression - Part 3


It can be easily seen, that the compressed table saves almost 60% on blocks compared to the uncompressed table in practical scenarios, this depends on how the data is structured.

Link to Part 1

Link to Part 2

In the third and final installment of this blog series, we will go through an actual implementation and see how much we benefit from Oracle Advanced Compression.

A Quick look on results of advanced compression:

Advanced Compression, as discussed in the second blog helps in saving space and optimizing query performance. We’ll demonstrate results for both below. First let’s create two identical tables, one with advanced compression activated, and the other one uncompressed, and fill them with approximately with 150,000 sets of data from dba_objects.

SQL> create table tab_comp as select * from dba_objects where 1=0;

SQL> create table tab_uncomp as select * from dba_objects where 1=0;

SQL> alter table tab_comp compress for oltp;

SQL> insert into tab_comp select * from dba_objects;

SQL> insert into tab_comp select * from dba_objects;
 

Now let’s check the OLTP compression is properly set.

SQL> select table_name

 ,      compression

 ,      compress_for

 from   user_tables; 

 TABLE_NAME             COMPRESS           COMPRESS_FOR

 ------------ -------- ------------------------------- -

 OBJ_COMP                 ENABLED  OLTP

 OBJ_UNCOMP          DISABLED 

Space Utilization Test:

Now check the consumed space by the two tables.

SQL> SELECT segment_name

 ,      blocks

 from   user_segments

 WHERE  segment_name LIKE 'COMP%';

 

 SEGMENT_NAME              BLOCKS

 ------------- --------

 OBJ_COMP             896

 OBJ_UNCOMP       2176

 

It can be easily seen, that the compressed table saves almost 60% on blocks compared to the uncompressed table in practical scenarios, this depends on how the data is structured. The more identical table cells there are, the better the compression rate. However, overall any compression is advantageous.

Query Optimization Test:

Let’s enable trace on database sessions and do a full table scan or a range scan and see the output. 

SQL> alter system flush buffer_cache;

 System altered.

 

SQL> select * from tab_comp where object_id < 800;

 4554 rows selected.

 Elapsed: 00:00:02.72 

SQL> select * from tab_uncomp where object_id < 800;

 4554 rows selected.

 Elapsed: 00:00:03.75

We’ve flushed the database buffer cache to ensure that I/O happens from the physical disk space and not from the cached memory. 

Below, take a look at the tables to see the analysis of the trace files. 

The uncompressed table

select *

 from

 obj_uncomp where object_id < 800

 Table_compressed

Rows     Row Source Operation

 ---------------------------------------------------------

 4554  TABLE ACCESS FULL OBJ_UNCOMP (cr=2568 pr=0 pw=0 time=258870 us cost=617 size=2346552 card=11336)

The compressed table

select *

 from

 obj_comp where object_id < 800 

compressed_data_2

 Rows     Row Source Operation

 -------  ---------------------------------------------------

 4554  TABLE ACCESS FULL OBJ_COMP (cr=1151 pr=846 pw=0 time=14959 us cost=240 size=367632 card=1776) 

As per the results, one can ascertain that the query performance is better in the example of the compressed object because it has to make less physical I/O within the compressed data block, which in turn, attributes to less CPU consumption as well. 

Conclusion

As outlined in the above examples, on can see that by enabling Advanced Compression, one can reduce a 10TB database to a 4TB database, and improve CPU utilization by approximately 10-12%. It can be said that Advanced Compression is a technique with interesting possibilities. Not only does it help save storage, it also improves I/O and memory-efficiency, resulting in better performance. Before executing this it is recommended that ample consideration is given to determine and identify the best suitable approach for each specific environment.

For any questions on the topic click below:

Book an Appointment with Kevin

 

References:

http://www.oracle.com/technetwork/database/options/compression/advanced-compression-wp-12c-1896128.pdf

http://www.dba-oracle.com/oracle11g/sf_Oracle_11g_Data_Compression_Tips_for_the_DBA.html

 

Link to Part 1

Link to Part 2