The Latest Oracle E-Business Suite Technology News direct from
Oracle E-Business Suite Development & Product Management

  • October 7, 2008

Three Options for Scaling Up E-Business Suite for Reporting

Steven Chan
Senior Director

[Oct 10, 2008 Update: Added link to article comparing the use of Oracle Active Data Guard with Oracle Data Guard for EBS environments]

The run-up to our annual OpenWorld conference consists of frenzied activities to ensure that all of our planned certifications wrap up in time to be announced at the conference.  The follow-up from OpenWorld consists of handling questions, bug reports,
and escalations from our sessions, panels, and private customer meetings.  Given that this is all on top of our regular day jobs, one day I'm going to print up some t-shirts that say, "I survived another Oracle OpenWorld."

So, back in the blogging saddle again. I'll address one of the architectural questions that seems to pop perennially:

How do I handle heavy reporting overhead without disrupting my E-Business Suite instance's transactional users?  Can I offload this to a separate reporting instance?


Reporting against E-Business Suite Global Single Instances

The Oracle E-Business Suite is explicitly designed to support a centralized operating model where business transactions from different geographic regions or organizational units can take place in a consolidated Global Single Instance.  We do
this ourselves
at Oracle with E-Business Suite Release 12, having consolidated 40 data centers from around the world into a single instance. The diagram above shows our internal deployment of the E-Business Suite Release 12 here in Oracle.

The primary advantage of centralizing as much of your data in a single database is clearly the ease of reporting on consolidated data.  I'm not a reporting or business intelligence specialist any more, so I'll leave it to other functional experts to
discuss the latest aspects of our analytic tools like BI Publisher, OLAP, and Oracle Business Intelligence Applications.  I'll going keep the scope of this article to the relative merits of different architectural approaches to handling this kind of load.

Options for Handling Reporting Loads

Depending upon the type of reporting that your end-users require and the resulting performance bottleneck, your architectural options fall into the following general categories:

  1. Mirror your production data to a reporting instance


  2. Scale up your application tier


  3. Scale up your database tier

1. Mirror Your Production Data to a Reporting Instance

The primary advantage of this approach is that reporting users can run whatever reports they wish, whenever they wish, without ever having to worry about affecting your transactional users.  This standalone environment can be scaled up as needed.

The disadvantages are numerous and non-trivial:

  • Cloning the database from production can be time-consuming and may require downtimes

  • Reporting instances can double your storage costs (or multiply them by an order of magnitude if you build custom star schemas or dimensional summaries on top of your production data)

  • Reporting instances need to be maintained, secured, and licensed separately

  • Reporting instances will not have up-to-the-minute data, since database cloning or replication will invariably be performed only periodically

  • Replicating specific data subsets from production requires custom development

  • Custom development requires data model synchronization whenever the source E-Business Suite data model changes

Some customers end up deploying reporting instances regardless of these disadvantages.  These users have consciously weighed the cost-benefit balance and have concluded that it's worth it.

Your mileage will vary.  Before you decide on to taking this approach, I would urge you to weigh your expected benefits against the operational and business costs carefully.

2. Scaling Up Your Application Tier

If you're running an analytic tool such as Discoverer, you may find that the performance bottleneck is on your application tier.  This was particularly acute with earlier versions of Discoverer 4i, which tended to merrily ignore configuration settings
for maximum memory usage.  This was a bug with fairly severe performance implications.  When lots of Discoverer 4i users ran heavy workbooks simultaneously, it wasn't uncommon to see application tier server CPU and memory usage peg at 100%.

If this is your only bottleneck, it's relatively simple to address:

First, if you're still on Discoverer 4i, you should immediately plan your upgrade to Discoverer 10g.  Discoverer 10g has made tremendous strides in respecting the memory limits you set.  Perhaps more notably, Discoverer 10g now automatically
trims unused columns out of its generated SQL statements, which dramatically reduces both your application and database tier loads.

Second, if you haven't already done so, you should consider moving your Discoverer instance onto a dedicated server.  It's technically feasible to run Discoverer off your existing E-Business Suite application tier server (in a separate ORACLE_HOME),
but doing so can sometimes feel like putting two sumo wrestlers on a single bicycle.

Third, if the number of concurrent Discoverer users warrants it, you can create a cluster of Discoverer servers behind a load-balancer. 

The advantages of this approach are:

  • Easier performance scaling:  you can add nodes as your application tier reporting loads increase. 

  • Improved fault tolerance:  if one application server node goes down, the other nodes can take over the load.

  • Finer-grained load-monitoring and tuning.

  • No custom development required:  this is a fully certified, documented, and supported architectural option for E-Business Suite environments.

The disadvantages of this approach are:

  • Separate application tier nodes need to maintained, secured, and licensed separately

I've been talking about Discoverer up until now, but I should emphasize that this general approach applies to all reporting or analytic tools that generate load at the application tier level.  This includes products such as BI Publisher, Oracle
Business Intelligence Enterprise Edition (OBIEE), Oracle Business Intelligence Applications (OBIA), and third-party reporting products.

3. Scaling Up Your Database Tier

Even if you've carefully separated your middle tier OLTP transaction load from your middle tier reporting load, everything still ultimately boils down to running queries against your E-Business Suite database.  At some point, your database server may
start to look a little weary of the additional load from your reporting users.

The first step for many customers is to add memory and additional CPUs to their existing servers.  That's fine until you hit the ceiling for your existing hardware.  The next step is to consider getting a bigger server with the latest smoking
multi-core CPUs and more expansion options.  At some point, though, you're still going to max out a single database node, regardless of the size of that database server.

This is where Real Application Clusters (RAC) comes in.  In addition to the obvious advantage of improved scalability, the other major advantage of using Real Application Clusters is that it allows you to segregate your reporting load onto a separate
database node from the rest of your transactional users.  For example, in a three-node RAC-enabled EBS environment, your HRMS Payroll users can be handled by DatabaseNode1, your Financials users can be handled by DatabaseNode2, and your reporting users
can be handled by DatabaseNode3.

Other advantages are:

  • Improved fault tolerance:  if one of the database nodes goes down, the others can pick up the load.

  • Finer-grained load-monitoring and tuning.

  • No custom development required:  this is a fully certified, documented, and supported architectural option for E-Business Suite environments.

The disadvantages are:

  • Increased licencing costs for Real Application Clusters

  • Increased database maintenance overhead and complexity

Get a Second Opinion

Naturally, the goal of this article was to provide you with a decision-making framework rather than making a set of specific recommendations for your needs.  It never hurts to let your Oracle account manager -- or the ERP consultancy of your choice --
know that you're debating various architectural options.  If they're doing their job right, they should be able to engage some  specialists to discuss your requirements with you in more detail. 

Related Database Tier Articles

Related Application Tier Articles

Join the discussion

Comments ( 16 )
  • Jay Weinshenker Tuesday, October 7, 2008

    Many of the companies I have done Apps DBA consulting for had a standby instance. With 11g you now have the ability to open that standby for reads (like before) and writes and then flashback that instance and roll forward on archivelogs.

    Additional cost? Zero. You're fully leveraging your standby database that you're paying licensing for already.

    It was one of the major reasons my current client moved to 11g as soon as it was certified for Apps 11.

    p.s. Sorry I didn't get to meet you at OpenWorld. I figured I'd get more bang for my buck at VMWorld. Maybe next year.

  • Steven Chan Wednesday, October 8, 2008

    Hi, Jay,

    Yes, the new 11g support in this area makes things much easier for customers who've moved to that version and have licenced the appropriate new database options. The cost-benefit equation tips in different directions based on whatever you might have already purchased, which is why it's so hard to make blanket recommendations that cover everyone's situations.

    I'd be interested in hearing more about your client's experiences with E-Business Suite and the 11g Database. If you have the time and inclination, feel free to drop me a private email with more details.

    I look forward to meeting you at some future conference. My next scheduled stop will be at the upcoming OAUG Collaborate 2009 conference in Florida, I believe.



  • Zsolt Kövesi Thursday, October 9, 2008

    Hi Steven,

    There is another aspect that you could have mentioned: using Database Resource Plans to put on-line users, Concurrent Manager queues, say Discoverer users, etc. into separate groups. This improves experience of on-line users while still fully using processing capabilities of each database server. It works on single or RAC instances, on RAC you have the additional advantage of load balancing.

    I have implemented this for a few Customers since 9i and up with considerable success.

    Kind regards,


  • Steven Chan Thursday, October 9, 2008

    Hi, Zsolt,

    Thanks for pointing that out. We haven't done any formal certification testing with Database Resource Plans with the E-Business Suite, so I'm very pleased to hear that you've had good experiences with this feature. I'll be sure to pass your comments on to our Applications Performance Group, too.



  • Jeff Ritschel Friday, October 10, 2008

    I'm on the tech side, so I'm not a familiar with replicating data from EBS, but could you take advantage of Active DataGuard in 11g to provide a secondary instance for reporting?

  • Steven Chan Friday, October 10, 2008

    Hi, Jeff,

    The discussion about Active Data Guard requires a bit of insight into how the E-Business Suite works. I've just published the following article with a more in-depth discussion about this:

    Comparing Oracle Data Guard vs. Active Data Guard for EBS Environments - http://blogs.oracle.com/stevenChan/2008/10/comparing_oracle_data_guard_vs_active_data_guard_f.html



  • sutanu das Friday, May 28, 2010


    Can you advise if/when Oracle's Discoverer can report off (as officially supported) from an Active Data Guard standby site?

  • Steven Chan Friday, May 28, 2010

    Not yet. We're still working directly with the Active Data Guard on enhancements to support EBS reporting against ADG standby databases. Stay tuned; I'll share more news here as soon as things develop further.



  • Vasu Ranganathan Monday, October 10, 2011

    Hi Steve,

    Thanks in advance.

    I have read with great interest the current article as well the article on Data

    Guard Vs Active Data Guard, posted on your blog.

    You mentioned above (on 28-May-2010) that Oracle is still working with Active Data Guard on enhancements to support EBS reporting against ADG standby databases. Any progress on that since it's been more than a year since this blog was updated?

    Best Regards,


  • Steven Chan Tuesday, October 11, 2011

    Hi, Vasu,

    Yes, we've made some progress on this, but the project isn't completed yet. I'll post an announcement on this blog as soon as it's ready.



  • Fred Thursday, September 27, 2012

    Is this available yet?

  • Steven Chan Thursday, September 27, 2012

    Hi, Fred,

    Not yet. I'm afraid that a number of other critical priorities interceded, so we had to divert resources to handle those first. We've restarted this project now, but I don't have any ETA that I can share at this point.

    I'll post updates on this blog as soon as they're available.



  • guest Monday, February 25, 2013

    Hi Steven,

    Is there any upadte now on EBS reporting with ADG?



  • Steven Chan Monday, February 25, 2013

    Hi, Anish,

    Oracle Business Intelligence Enterprise Edition (OBIEE) has been certified to run reports and ETL against an E-Business Suite Active Data Guard database. I'm told that you can refer to the generic OBIEE documentation for procedures (I haven't seen these myself). If you need help with OBIEE + ADG + EBS, your best bet would be to log a Service Request against OBIEE.

    We're still working through the details for running Discoverer against an EBS + ADG database. I'll post more details on this blog as they become available.



  • guest Tuesday, August 2, 2016

    Hi Steven,

    We are going to deploy ODI and Oracle BI analytics with EBS R12 instance. There are plan 2 install 2 test instance along with the PROD reporting instance. Both the BI test instance will be integrated with the related EBS test instance.

    For data replication from PROD, is it worth to consider Golden Gate rather than clone the test instance with PROD.

    Please advise.



  • Steven Chan Wednesday, August 3, 2016

    Hi, Deb,

    It's certainly worth comparing the two options in more detail. It's beyond the scope of a blog comment to give you an answer for your case, specifically, but, in general, you should consider the balance between setup costs, operational costs, and your end-users' business requirements. Oracle Consulting can help with that detailed analysis.

    A complete clone of your production system would consume more storage and resources. We offer EBS cloning tools, of course, so this is the easy route. The downside is that the data is only current as of the last cloning.

    Using Golden Gate to create a smaller operational data store of a subset of that data would take less space and consume fewer resources. In addition, the data will always be current. That's a custom development that would require more expertise to set up.



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