Using Advanced Compression with E-Business Suite Databases

I'm very pleased to let you know that Oracle Advanced Compression is supported for use with Oracle E-Business Suite Release 11i and 12.  Advanced Compression is one of the new Oracle Database 11gR1 Enterprise Edition options that seems to generate the most interest amongst E-Business Suite customers.  After all, your production E-Business Suite database doesn't exist in isolation. 

You've got copies in testbed sandboxes, internal development environments, User Acceptance Testing (UAT) environments, and staging environments, too.  Reducing your EBS database size by even a small amount can result in big savings when added up across all of your non-production E-Business Suite instances.

Diagram showing how Oracle Advanced Compression reduces block usage through a local symbol table

As an aside, I can't resist telling you about the most extreme case of this that I've encountered.  An Apps DBA for a Canadian federal agency once told me that they have over fifty intermediary instances standing between their Development and Production stages.  The mind boggles.

Works Transparently with the E-Business Suite

Advanced Compression is completely transparent to the E-Business Suite.  In other words, you can follow the generic Oracle Advanced Compression documentation to enable this feature for E-Business Suite databases:

As far as E-Business Suite databases are concerned :

  • Advanced Compression works with transparently with both Oracle E-Business Suite Release 11i and 12.
  • No additional database or E-Business Suite patches are required.
  • No special configuration options are required.

Considerations for EBS Environments

Given the wholly transparent nature of this database option, we haven't published any special whitepapers or Metalink Notes for E-Business Suite environments.  Aside from the obvious aspect of reducing your E-Business Suite database's size, here are some things that you can expect when enabling this option:

  1. Enabling compression doesn't compress existing data.  You must do a table level reorg to free up space.  In practice, you're more likely to do this at the partition level, especially with time-based partitioning.
     
  2. The time required to do any reorganizations will depend upon the amount of real data that you have.  Your mileage will vary (and we'd like to hear about it -- see below). 
     
  3. In general, EBS performance is expected to improve, since the queries will benefit from improved I/O and memory efficiency. 
     
    That said, your users' transactional mix, the amount of historical production data, and your database server and storage configurations will all affect overall performance.  Our Applications Performance Group plans to do detailed performance benchmarking with this option and Apps reference data.  Those benchmarks haven't been scheduled yet, but I'll post those benchmarks here as soon as they're published.

What Have You Found?

We're eager to hear about your experiences with Advanced Compression in your E-Business Suite environments.  If you have anecdotal remarks about how well it's worked for you, please drop me a line or post a comment here.  Any statistics about the amount of compression you've achieved or performance benchmarks would be even more welcome.

Related Articles

Comments:

Hi! Steven,
Is compression certified for 11i even for 10g database?
http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_data_compression_10gr2_0505.pdf

Thanks
Zia

Posted by Zia Hameed on November 13, 2008 at 06:24 AM PST #

Hi, Zia,

We haven't explicitly tested the compression features listed in that whitepaper in E-Business Suite environments. Our default position would be that we would assume that these technologies work... and that you should test thoroughly before deploying this in a production EBS environment.

If you encounter any EBS-related issues with this, Support may ask you to replicate the issue in an environment without those compression features enabled, in an attempt to narrow down the root cause of the issue.

Regards,
Steven

Posted by Steven Chan on November 13, 2008 at 08:33 AM PST #

Hi,
Do you have the details of the experiment conducted, like tables compressed in E-Business Suite, size of the tables, storage space savings, performance impact if any and cpu/ram (resource) usage.

Thanks,
Sandeep

Posted by Sandeep on December 04, 2008 at 01:41 AM PST #

Hi, Sandeep,

Our Applications Performance Group is working on formal benchmarks for EBS environments right now.

Until then, you might be interested in some preliminary benchmarks performed on an EBS Vision database by one of our readers:

Early Benchmarks: Using Advanced Compression with Apps 12 - http://blogs.oracle.com/stevenChan/2008/11/early_benchmarks_using_advanced_compression_with_ebs.html

Regards,
Steven

Posted by Steven Chan on December 04, 2008 at 03:21 AM PST #

Hi Steven,

The Advanced Compression in 11g appears very interesting. I am exploring few possiblities of implementing this feature for our Customers'.
I have few queries regarding this feature:-

1) Do we have any database parameter that also need to be tuned during compression for better performance ... that is to avoid even the minimal overhead during DML operations ? ( Specific to Advanced Compression )

2) For an existing EBS database, what should be our best approach to use Advanced Compression feature for betterment. Most of our Customers' are on 9i and 10g database. Is there any way that Advanced Compression feature of 11g be used without actually upgrading the database to 11g?

3) "Existing data in the database can also be compressed by moving it into compressed form through ALTER TABLE…MOVE COMPRESS statement". Statement found in "http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_data_compression_10gr2_0505.pdf"
Is this applicable for both 10g and 11g? If Yes, why everywhere for 11g, we are seeing statement that says- "Existing Data cannot be compressed"? Please clarify my doubt.

Thanks & Regards,
Smita

Posted by Smita Madhur on March 25, 2009 at 02:02 AM PDT #

Hi, Smita,

1. There are no EBS-specific database parameters that need to be tweaked for Advanced Compression. I don't have a lot of hands-on experience with this technology, but I think this feature is already optimized for general use. It's worth checking the 11gR1 Advanced Compression documentation for more tuning tips.

2. No, this feature is for 11gR1 databases only. If I were a data center manager struggling to contain my EBS Database growth, this feature would make me seriously consider an upgrade to 11gR1.

3. Are you experiencing issues with an existing environment? It's unclear what might be going on here. I'd advise logging a formal Service Request via Metalink to get one of the Server Technologies' Advanced Compression specialists engaged.

Regards,
Steven

Posted by Steven Chan on March 25, 2009 at 06:40 AM PDT #

Thanks Steven.

Posted by Smita Madhur on March 25, 2009 at 04:01 PM PDT #

Hi Steven,

Is use of Advanced compression with e-biz 11i for experimental purposes of for Dev and Test environments only?

Should we suggest and implement at Customer Production environment without authentic Metalink note or certification from Oracle?

Will Oracle take the responsibility of any issues arising during implementation and provide the solution?

I am asking this question because some of our customers insist on this feature implementation with Production environment.

Regards

Balaji Desai

Posted by Balaji Desai on April 27, 2009 at 12:27 AM PDT #

Hi, Balaji,

The use of Advanced Compression is certified for production EBS 11i and 12 environments.

The generic Advanced Compression documentation can safely be used for E-Business Suite databases. Oracle Support will help investigate and resolve any issues encountered with this configuration for production environments.

We'd be interested in hearing your customer's feedback on this database option. We'd especially be interested in getting specific benchmarks for the amount of compression your customer was able to achieve, and the performance effects of enabling compression in your environment.

Regards,
Steven

Posted by Steven Chan on April 27, 2009 at 03:31 AM PDT #

Hi Steven,

How do we recover a corrupted compressed datablock( compressed using Advanced Compression Feature) in 11g? Do we have any specific procedures? Could you please point me to the link where i can get more details on this.

Thanks & Regards,
Smita

Posted by Smita Madhur on July 23, 2009 at 10:53 AM PDT #

Hello Smita,

If the Database 11g documentation (http://www.oracle.com/pls/db111/portal.portal_db?selected=4&frame=#backup_and_recovery) doesn't cover it, then I would recomend you raise a Service Request with the RDBMS team to have them give you specific advise about your particular case

Hope this helps

Mike

Posted by Mike Shaw on July 23, 2009 at 03:50 PM PDT #

Hi Steven,

I am wondering if there will be a list, for eBiz, of recommended objects that should/could be compressed and a recommended list of objects to avoid?

I have heard that certain objects that are constantly updated cause quite a bit of fragmentation and they require maintenance on a regular basis (e.g. reorgs)

Posted by Dallas Scott on August 09, 2009 at 09:04 PM PDT #

Hi, Dallas,

We haven't put together any recommendations or lists of EBS objects to avoid. My impression is that the generic Advanced Compression documentation's guidelines have been useful for Apps DBAs so far.

What I've heard from EBS sysadmins to date is that they're initially targetting their biggest tables that have been the root of performance or space management issues. This selectivity -- rather than a blanket approach -- seems to have been working well.

If you encounter any compression issues with specific EBS objects, I would be eager to get your SR numbers to investigate with our Dev team.

Regards,
Steven

Posted by Steven Chan on August 10, 2009 at 03:15 AM PDT #

Hi Steve,

When table compression is enabled would the free space be released back to oracle free list blocks or the actual datafile is resized automatically by the amount of free space obtained? I don't have one 11g db my self to check this out, any real examples you have will be a great help for us to consider it.

Thanks
Suresh

Posted by Suresh Talasila on September 20, 2010 at 09:17 PM PDT #

Hi Suresh,

You enable and convert an existing table to use Advanced compression with the "alter table ... move compress for all operations" command. This command will allocate new extents from the table's tablespace (or any new tablespace you may have optionally specified) to "move" the data into in it's new compressed format. After this part of the operation is finished, the existing extents , which held the uncompressed data are marked as free and can be be reused by future space allocation operations by any segment in that tablespace, in other words they become free, unallocated space in the respective datafile(s) -- these datafiles however, are not resized automatically.

thanks,
-lester

Posted by Lester Gutierrez on September 24, 2010 at 07:46 AM PDT #

Hi Steven,
"Is compression certified for 11i even for 10g database?".
Regards,

Marcel

Posted by Marcel Djaskemdet on October 13, 2010 at 06:16 PM PDT #

Hi, Marcel,

We have not tested that particular combination here in the E-Business Suite division but I would expect it to work.

You should note that the 10gR2 database left Premier Support in July 2010. I would recommend that you plan for an upgrade to the 11gR1 or 11gR2 database at your earliest convenience.

Regards,
Steven

Posted by Steven Chan on October 14, 2010 at 01:24 AM PDT #

Hi Steven

We have an acute shortage of Oracle test and dev environment.We need to get creative in how we create and manage Oracle environments.
Issues:
· Production data is growing at a large pace which means we need more space for dev and test environments.
· Creation of environments calls for copying over data and then unzipping them which needs even more space
· More data means more time required to create environments
· Projects wanting dedicated environments to de-risk hygiene issues with their environment
· We cannot add any more space unless we change our space management strategy.

Can you help

Posted by Shwetank Upadhyay on December 22, 2010 at 04:19 PM PST #

Hi, Shwetank,

I haven't met a DBA yet who has enough servers or diskspace. You're in good company.

Advanced Compression is an excellent option for reducing your EBS database sizes. I'd recommend doing a test on a copy of your production database to see how much diskspace you could recover.

Another option which I'm sensing is increasingly appealing is to explore cloud-based alternatives such as Amazon's EC2 platform. This might be useful for short-term projects.

Regards,
Steven

Posted by Steven Chan on December 27, 2010 at 05:20 AM PST #

For advanced compression, if the block require DML (to be insert or update or delete)
- Will that block remain compressed after the DML completed?
- How Oracle handle block to perform DML on the compressed block (before/during/after DML, i.e. will it uncompress first and then DML and then compress again?)
- Should we implement in on OBIEE DW database? Thinking to implement it on Fact, Dimension and Aggregate table. What is the best practice?

Thank you.

Posted by Jaruwan Na Ranong on June 23, 2011 at 12:07 AM PDT #

Greetings,

What is the impact of advance compression in 11.5.10.2 during the upgrade to R12.1.3 process?

Does upgrade takes more time than usual (meaning when not compressed)?

Thanks
Ajay

Posted by Ajay Amberkar on July 30, 2012 at 01:49 PM PDT #

Ajay,

We haven't done any benchmarks comparing upgrades with Advanced Compression enabled to non-compressed environments. Offhand, I would expect that the incremental overhead to be nominal for most databases.

Most customers plan to upgrade their database servers as part of the same overall project. Our standing recommendation is to upgrade your database server to your new hardware prior to the E-Business Suite upgrade to EBS 12. This will allow you to take advantage of the new server's additional horsepower during the EBS upgrade itself.

Also see:

Best Practices for Combining EBS Upgrades with Platform Migrations https://blogs.oracle.com/stevenChan/entry/oracle_e_business_suite_upgrades

Regards,
Steven

Posted by Steven Chan on August 06, 2012 at 11:28 AM PDT #

Hi,
The size of AX_SLE_LINES without advanced compression is 48674.125MB.
I ran statement as shown below :
ALTER TABLE AX.AX_SLE_LINES MOVE COMPRESS FOR OLTP;
it is amazing how size it decreased !
AX_SLE_LINES is now 10366.125MB.

BR,
Mohamed CHERIF

Posted by guest on August 28, 2013 at 05:58 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
23
24
25
26
27
28
29
30
   
       
Today