The Latest Technology Stack News Directly from EBS Development

  • October 22, 2008

Using Advanced Compression with E-Business Suite Databases

Steven Chan
Senior Director

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

Join the discussion

Comments ( 26 )
  • Zia Hameed Thursday, November 13, 2008

    Hi! Steven,

    Is compression certified for 11i even for 10g database?




  • Steven Chan Thursday, November 13, 2008

    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.



  • Sandeep Thursday, December 4, 2008


    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.



  • Steven Chan Thursday, December 4, 2008

    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



  • Smita Madhur Wednesday, March 25, 2009

    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,


  • Steven Chan Wednesday, March 25, 2009

    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.



  • Smita Madhur Wednesday, March 25, 2009

    Thanks Steven.

  • Balaji Desai Monday, April 27, 2009

    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.


    Balaji Desai

  • Steven Chan Monday, April 27, 2009

    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.



  • Smita Madhur Thursday, July 23, 2009

    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,


  • Mike Shaw Thursday, July 23, 2009

    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


  • Dallas Scott Monday, August 10, 2009

    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)

  • Steven Chan Monday, August 10, 2009

    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.



  • Suresh Talasila Tuesday, September 21, 2010

    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.



  • Lester Gutierrez Friday, September 24, 2010

    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.



  • Marcel Djaskemdet Thursday, October 14, 2010

    Hi Steven,

    "Is compression certified for 11i even for 10g database?".



  • Steven Chan Thursday, October 14, 2010

    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.



  • Shwetank Upadhyay Thursday, December 23, 2010

    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.


    · 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

  • Steven Chan Monday, December 27, 2010

    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.



  • Jaruwan Na Ranong Thursday, June 23, 2011

    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.

  • Ajay Amberkar Monday, July 30, 2012


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

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



  • Steven Chan Monday, August 6, 2012


    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



  • guest Wednesday, August 28, 2013


    The size of AX_SLE_LINES without advanced compression is 48674.125MB.

    I ran statement as shown below :


    it is amazing how size it decreased !

    AX_SLE_LINES is now 10366.125MB.


    Mohamed CHERIF

  • Anton Thursday, September 15, 2016

    What's the best approach for OEBS tables compression - for all operations or for direct load operations? Especialy for DML-loaded concurrents?

  • Zia Hameed Monday, April 23, 2018
    Hi! Steven
    We considering Advanced compression as we have issues with APPS_TS_MEDIA tablespace growth.
    SR 3-17339053951
    SR 3-17342399858

    Does Compression work with LOBs?
  • Steven Chan Monday, May 14, 2018
    Hi, Zia,

    Sorry for the delay in responding to this.

    No, my understanding is that Advanced Compression does not compress LOBs. You would need to use SecureFiles LOB Storage for that:


    SecureFiles is compatible with EBS databases. See this old article:


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.