New Whitepaper: Advanced Compression 11gR1 Benchmarks with EBS 12

[June 23, 2010 Update:  Our Server Technologies team has just released Note 1061366.1 that lists recommended Advanced Compression patches applicable to all customers, including E-Business Suite environments]

In my opinion, if there's any reason to upgrade an E-Business Suite environment to the 11gR1 or 11gR2 database, it's the Advanced Compression database option.  Oracle Advanced Compression was introduced in Oracle Database 11g, and allows you to compress structured data (numbers, characters) as well as unstructured data (documents, spreadsheets, XML and other files).  It provides enhanced compression for database backups and also includes network compression for faster synchronization with standby databases.

advanced-compression-tables.png
In other words, the promise of Advanced Compression is that it can make your E-Business Suite database smaller and faster.  But how well does it actually deliver on that promise?

Apps 12 + Advanced Compression Benchmarks now available


Three of my colleagues, Uday Moogala, Lester Gutierrez, and Andy Tremayne, have been benchmarking Oracle E-Business Suite Release 12 with Advanced Compression 11gR1.  They've just released a detailed whitepaper with their benchmarking results and recommendations.

This whitepaper is available in two locations:
Vision Database:  Up to 68% compression and up to 36% improved performance

The results, even with our EBS Vision demonstration database, were impressive:
  • Reduced 21.9 GB of tables down to 6.9 GB (68% reduction, average 3:1 compression ratio)
  • Self-service performance improved up to 36%
  • Purchasing performance improved up to 10%
  • Order-to-Cash batch flow performance improved up to 33%
  • Payroll performance improved up to 25%
They also found some other interesting effects:
  • Some SQL execution plans changed, reducing performance
  • Performance was improved at the cost of up to 6% increased CPU usage, with the variance depending upon how much DML is performed
  • In the case of the Order-to-Cash batch flow, the average CPU consumption actually decreased by 7%
Line chart showing order to cash batch flow cpu usage for Oracle 
E-Business Suite Release 12 and advanced compression

Oracle using this in production with EBS 12 today

We can now share the news that we've been using Advanced Compression in our Oracle Global Single Instance (GSI) production environment since 2009.  This same team has been working to analyse the results of using Advanced Compression with our Oracle E-Business Suite Release 12 global single instance that we use to run Oracle's own business. 

The results there are very impressive:
  • Reduced EBS 12 database size from 17 Terabytes to 13 Terabytes
  • Compressed ~260 tables and over 700 associated indices and LOBs after migrating to SecureFiles
  • Total space saved: 68 TB across our primary, standby, and associated testbeds
Other E-Business Suite-specific observations about Advanced Compression

This whitepaper makes detailed E-Business Suite-specific observations in the following areas:
  1. Index compression
  2. Recommended patches
  3. SQL Plan regression analysis
  4. DML intensive operations
  5. Row chaining on UPDATE transactions
  6. ITL contention
  7. High transaction tables
  8. SecureFiles LOB compression
The whitepaper concludes with specific recommendations on planning your Advanced Compression conversion in your E-Business Suite environment, and then lists all of the tables compressed in the Vision testbed as well as Oracle's own EBS 12 production instance.

Have you used Advanced Compression with EBS?

Naturally, compression ratios and performance impacts or benefits will vary for every Apps environment.  If you've used this technology, we're very interested in hearing about your experiences with Advanced Compression in your environment.  You're invited to share your experiences here or drop me an email directly.

Related Articles

Comments:

Hi Steve, a very interesting fact about the cost saving and performance improvement from Oracle Advanced Compression.

But I wonder how SQL execution plans changed hence reducing performance. Is it because the compression reduce the cost of the query, since the table block is getting smaller?

Posted by The Guru on June 14, 2010 at 11:43 AM PDT #

This article is interesting. Can Oracle Advanced Compression be turned on for a Oracle 11g database used by a Siebel application - has a similar study been conducted that suggests similar benefits from a capacity or performance perspective ?

Posted by Adrian Stage on June 16, 2010 at 03:24 PM PDT #

Hi, Adrian,

I don't think anyone from Siebel Development monitors this blog (we're all E-Business Suite staff). Your best bet would be to log a formal Service Request via My Oracle Support (formerly Metalink) to get one of our Siebel specialists engaged.

Regards,
Steven

Posted by Steven Chan on June 17, 2010 at 04:14 AM PDT #

Hi, "The Guru",

One of our Applications Performance Group architects added this to another discussion about how the benefits are actually achieved:

1. In general , in the absence of contention related wait events in SQL processing , the amount logical IOs (buffer gets) and physical IOs drive for the most part the elapsed time taken in executing SQL

2. ACO is reducing by 3 to 1 on average the number of blocks in the large tables compressed -- and so for SQLs accessing this data, in many cases a similar reduction in both logical and physical IO is obtained. So this is the largest performance benefit of this, and it is reaped for both queries and dml sqls (in the sense that dml sqls have to also read data before modifying it)

3. Since nothing is free, ACO does have a cost, the "read" cost is small, just burning a little more cpu to translate the "compressed" data as it is processed (data is not uncompressed for reads, just translated based on the local (block level) symbol table). The DML cost is larger though ...more so for updates than inserts (insert compression overhead is deferred and batched so it is well optimized, but relatively less so for updates)

4. So that net effect on programs then depends on where you end up with all these performance gains and losses of the different operations -- so for example, the SS flows we tested are very read intensive, and reading mostly data that we had compressed as part of moving to ACO -- so this was the best case scenario. The fact that most applications are read intensive (even when massively inserting/updating data, code tends to re-read it many times) helps the ACO case.

5. Still, some dml intensive programs, specially update intensive do see regressions due to the costs mentioned above.

Regards,
Steven

Posted by Steven Chan on June 17, 2010 at 05:34 AM PDT #

All,

Looks like a nice feature, but how much time for implementing this cost saver.

maurice

Posted by Maurice on December 07, 2010 at 09:49 PM PST #

Hi Steve,

Is it possible to compress tables at schema level ? I mean is it possible to compress schema in 11gr2 database ?

Thanks,
Suman

Posted by guest on June 14, 2012 at 04:18 PM PDT #

Suman,

Compression commands are not available for a schema, but are available for tables.

The syntax for the CREATE or ALTER TABLE statements may be located here:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables002.htm#ADMIN11630

Thanks.
Elke

Posted by Elke Phelps (Oracle Development) on June 15, 2012 at 09:28 AM PDT #

Hi suman,

can you tell me what are all the demerits for the use of compression techie????

merits are:-

1.cost saving
2.cpu memory saving

etc...

demerits are:-

Posted by srinivasan on July 03, 2013 at 03:38 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
4
5
6
7
8
9
10
11
12
13
14
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today