Three Options for Scaling Up E-Business Suite for Reporting

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:

Mirror your production data to a reporting instance

Scale up your application tier

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

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.

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.

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.

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.

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.

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.

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.

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.

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?

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.

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.