The Latest Technology Stack News Directly from EBS Development
-
Certifications
August 5, 2015
Using Oracle Database In-Memory with Oracle E-Business Suite
Authors: Andy Tremayne and Deepak Bhatnagar, Oracle Development
Database In-Memory is one of a number of options that can be deployed to address Oracle E-Business Suite performance concerns and scalability requirements without the need for any application changes.
We are pleased to announce the publication of a new white paper that can help with this (including links to patches that you will need to apply):
- Using Oracle Database In-Memory with Oracle E-Business Suite (Note 2025309.1)
The paper starts by providing strategic advice and guidelines that help you decide which objects to populate into Database In-Memory (DBIM), and how to size the In-Memory Column Store (IMCS). It provides a list of best practices and explains the complexities and limitations of using DISTRIBUTE/DUPLICATE with Oracle RAC.
The examples in this paper take a step away from the classic headline feature of analytical reports that you might have been expecting. Instead, they show how DBIM can be applied to three novel use cases:
- The Order Organizer Form has a huge number of queryable fields that would all need to be indexed for optimal performance. DBIM provides a 10X improvement in the end-user response time.
- The Initialize Credit Summaries concurrent program is unusual the optimization is based on an INSERT statement. It is also interesting as the example shows why simply populating objects into the IMCS can result in a performance reduction – in this case spilling to disk. Simply populating the objects into DBIM improved the time by 1.5X; further tuning improved on this to provide an overall gain of 3.5X.
- The Receiving Transaction Processor had two long-running queries that reduced from 7.8 hrs to 4.85 mins representing about a 100X increase in performance. The overall runtime was about 4X faster.
One of the key aspects in the paper is describing how to use Oracle E-Business Suite Application Affinity with Oracle RAC. It describes how to overcome some of the limitations of Non-Engineered Systems (commodity hardware), and explains how it can also be used to benefit Oracle Engineered Systems.
Other References
The Oracle Database In-Memory blog contains a wealth of generic information, technical details, ideas and news on Oracle Database In-Memory from the author of the Oracle White Paper on Oracle Database In-Memory.
Related Articles
Hi Steve,
I have gone through the links and I have a question. Say in ebs, I have a table of size 300Gb with no partitions. My SGA size is 200GB include the in memory size.
In DBIM and IMCS, entri object has to be loaded into SGA. In my case how can a object greater than SGA will fit into in-memory and what will be the performance issue.
Thanks,
Romit
Hi Steve,
Incase db object in size is far greated than the SGA, then how will the entire object fit into DBIM.
There will be performance issue for sure. This case will be faced by most of the clients running ebs, certainly we will face.
Please suggest how the DBIM and IMCS will work, with a limited SGA.
Thanks,
Romit
Normally the In-Memory feature is a extra license.
I have just been pointed to Using Oracle Database In-Memory with Oracle E-Business Suite (Doc ID 2025309.1) from Steven Chan's blog
:https://blogs.oracle.com/stevenChan/entry/dbim_ebs?utm_source=feedburner&utm_medium=email&utm_campaign=Feed%3A+OracleE-BusinessSuiteTechnology+%28Oracle+E-Business+Suite+Technology%29
Does the E-business suite license cover it's use ?
Neither auther mentions an extra license cost.
thanks, Chris
Chris,
You can contact your Oracle account manager for details for your specific organization contracts, but -- generally speaking -- EBS customers who wish to use Database In-Memory must purchase a license for that database option.
Regards,
Steven
If you don't have enough memory this will be a problem, and many will not have enough memory for your largest tables. You could partition the table (as it not already partitioned), but of course, the effectiveness of a query not only depends on whether all the data is contained within the partition that has been populated into the In-Memory Column Store, but also whether the necessary predicates are used in the SQL to restrict the query to that partition(s).
Various levels of compression can also be used to reduce the amount of memory required. Refer to Table 2: Example Compression Levels. The average default compression ratio for the main application tables is approximately 6, whereas it was 46 for the interface table (RA_INTERFACE_LINES_ALL), which may have had sparsely populated blocks.