[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?

:Architecture%20Diagram%20showing%20Oracle's%EBS%20Instance

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