Recente bloggers:

Hans van Hoogstraten
Berichten: 3
Sterren: 0
Datum: 18-7-13

 

Tag cloud:

« Terug

Using Advanced Compression

 

When used in combination with the Oracle E-Business Suite, Oracle databases can become very large. Take the Vision-database for instance, this is a preconfigured EBS-environment with demo-data. The R12-version of the demo-database is very large, on our Linux-servers it is taking a whopping 135 GB of diskspace.

Recently, Oracle certified its Advanced Compression technology in combination with EBS, this technology could prove to be very usefull when used in combination with EBS. But there are many questions to be answered, like: what will the impact be on performance and how much diskspace is gained exactly? Time for a case-study, we will implement Advanced Compression in the following setup:

  • Platform: Oracle Enterprise Linux 4 Update 5, 32-bit
  • 8 GB internal memory, 2x QuadCore Xeon CPU's
  • EBS R12.0.4 Vision
  • RDBMS 11g

Advanced Compression can be configured on a table-level: when creating a table a "COMPRESS"-clause can be added to the create-statement. For existing tables the "ALTER TABLE"-statement can be used with the same "COMPRESS"-clause. When activating compression on an existing table it is important to realise that the only data that is compressed is the data inserted or updated after compression was enabled. And when compression is disabled with "ALTER TABLE........NOCOMPRESS", all data that was already compressed remains compressed, new data that is inserted will not be compressed.

Compression can be enabled for all table operations, or enabled for direct-path inserts only. With the direct-path method data is only compressed when it is inserted with a bulk-insert operation.

Test-results

Let's try out the AC-feature on the Vision-database. Before each measurement was taken the buffer_cache was flushed; results where verified by performing a second run of the steps described below. During measurements no O.S.-swapping took place.

The largest table in the 12.0.4 Vision-database is the gl.gl_balances table:

SQL> select count(*) from gl_balances;

  COUNT(*)
----------
   6479628

 And currently this table uses approx. 650 MB of diskspace:

SQL> select bytes/1024/1024 from dba_segments
  2  where segment_name = 'GL_BALANCES'
  3  and owner = 'GL'
  4  /

BYTES/1024/1024
---------------
          652.5

First let's measure how much time it takes to make a copy of this table without compression:

SQL> set timing on

SQL> create table gl_balances_noac as select * from gl_balances;

Table created.

Elapsed: 00:00:23.98

It took 23 seconds to create the table with 6.5 million records without compression.

Now, let's create a table with advanced compression activated and perform the same insert:

SQL> create table gl_balances_ac compress for all operations as select * from gl_balances;

Table created.

Elapsed: 00:00:43.08

Now it took 43 seconds to complete. It should come as no surprise that the effort to compress all these newly inserted records takes some extra time, the compression adds some extra CPU-overhead.

The following query gives us an impression how much diskspace was gained by compressing the gl_balances table:

SQL> select segment_name, bytes/1024/1024
  2  from dba_segments
  3  where segment_name like 'GL_BALANCES%AC'
  4  /

SEGMENT_NAME         BYTES/1024/1024
-------------------- ---------------
GL_BALANCES_AC               180.25
GL_BALANCES_NOAC             633.25

 

The compression has done it's work and reduced the disk-usage of gl_balances by 70%!

We now have two identical tables, GL_BALANCES_AC and GL_BALANCES_NOAC, both without indexes. Let's see how they compare when queried, we will use the following query:

SQL> l 
  1  select ledger_id, period_name,
  2  avg((sysdate-last_update_date))
  3  from gl_balances_noac
  4  group by ledger_id, period_name   5* order by ledger_id, period_name

 The execution-plan for the above query on the noac-table looks like this:

---------------------------------------------------------------------------------------
| Id  | Operation	   | Name	      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		      |  5851K|   172M| 22982	(5)| 00:04:36 |
|   1 |  SORT GROUP BY	   |		      |  5851K|   172M| 22982	(5)| 00:04:36 |
|   2 |   TABLE ACCESS FULL| GL_BALANCES_NOAC |  5851K|   172M| 22217	(2)| 00:04:27 |
---------------------------------------------------------------------------------------  
 
 

The execution-plan for the same query on the ac-table:

-------------------------------------------------------------------------------------
| Id  | Operation	   | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		    |  6368K|	188M|  7311  (15)| 00:01:28 |
|   1 |  SORT GROUP BY	   |		    |  6368K|	188M|  7311  (15)| 00:01:28 |
|   2 |   TABLE ACCESS FULL| GL_BALANCES_AC |  6368K|	188M|  6473   (4)| 00:01:18 |
-------------------------------------------------------------------------------------

The query benefits from the compression, as can also be concluded by evaluating the following statistics:

Without compression:

CPU_CONSUMED
------------
	1032
LOGICAL_READS
-------------
	81427

With compression:

CPU_CONSUMED
------------
	1193
LOGICAL_READS
-------------
	23221

 

Cleary it takes a bit more CPU to query the compressed-table, but the I/O is dramatically reduced. And because each block in the compressed table now contains more rows, a smaller SGA can handle the same amount of data. Or, if the SGA-size is kept the same, less physical reads will be necessary, which will further improve I/O and result in better performance.

Using Advanced Compression with EBS

Advanced Compression is a native RDBMS-feature that is completely transparent to applications that make use of the database. That's why Advanced Compression can be implemented in an EBS-environment without additional interoperability-steps. As a final test, lets compress the data of the original gl_balances and run a GL-report in EBS. For this test we use the "Cumulative Balance Sheet" and run this report for the "Vision Operations" ledger.

Before compression:

 After compression:

 

With the original gl_balances table, the report takes 6 seconds to complete, when the report is run with the exact same parameters on the compressed table, the report takes 3 seconds to complete, making the report 2 times as fast!

Conclusion

Advanced Compression is a technique with interesting possibilities. In the right situation (read: lot's of queries, much less inserts and updates) Advanced Compression can not only result in diskspace-savings, but also improve I/O and memory-efficiency, resulting in better performance.

Volgende
Opmerkingen
Link naar weblog-URL:

Er zijn nog geen opmerkingen. Word de eerste.