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.
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:
-
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.
-
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).
-
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 (13)
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 | November 13, 2008 2:24 PM
Posted on November 13, 2008 14:24
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 | November 13, 2008 4:33 PM
Posted on November 13, 2008 16:33
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 | December 4, 2008 9:41 AM
Posted on December 4, 2008 09:41
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 | December 4, 2008 11:21 AM
Posted on December 4, 2008 11:21
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 | March 25, 2009 9:02 AM
Posted on March 25, 2009 09:02
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 | March 25, 2009 1:40 PM
Posted on March 25, 2009 13:40
Thanks Steven.
Posted by Smita Madhur | March 25, 2009 11:01 PM
Posted on March 25, 2009 23:01
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 | April 27, 2009 7:27 AM
Posted on April 27, 2009 07:27
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 | April 27, 2009 10:31 AM
Posted on April 27, 2009 10:31
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 | July 23, 2009 5:53 PM
Posted on July 23, 2009 17:53
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 | July 23, 2009 10:50 PM
Posted on July 23, 2009 22:50
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 | August 10, 2009 4:04 AM
Posted on August 10, 2009 04:04
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
|
August 10, 2009 10:15 AM
Posted on August 10, 2009 10:15