Tuesday Aug 25, 2009

Distinguishing Between E-Business Suite 12.1 and 12.0 Patches

Sometimes our release processes get ahead of our internal infrastructure.  This happened when we released Oracle E-Business Suite Release 12.1.1.  We discovered post hoc that it was not possible to distinguish between patches intended for Apps 12.1 and those intended for Apps 12.0 in the "Patches & Updates" function in My Oracle Support and Metalink.  Whoops -- clearly suboptimal.

This issue was resolved in mid-July, when the Patches & Updates function got a brand-new "Compatible With" column:

The new "Compatible With" column clearly distinguishes between the patches for each Release 12 codeline now.

Don't Mix and Match R12.0 and R12.1 Patches

This distinction is important.  You shouldn't attempt to apply an R12.1 patch against your R12.0 instance, or vice versa.  If you tried to do that, you'd see a somewhat-cryptic error message like this:
AutoPatch error:

This patch is not compatible with your current codelines.

This patch is compatible with: entity 'gl' - codeline 'R12.GL.A'.
Your current on-site codeline for the entity 'gl' is: 'R12.GL.B'.

You should not apply this patch.

Apply an equivalent patch that is compatible with your
current codelines instead.
Sorting Patches Out After They've Been Downloaded

It's now easy to tell patches apart on the "Patches & Updates" download screen, but how can you tell them apart after they've been downloaded?  It's pretty simple. You can identify the codeline for a given patch by the letter in the third-digit:
  • Patches for EBS 12.0 will show an 'A' (e.g. "8414069.R12.AR.A")
  • Patches for EBS 12.1 will show a 'B' (e.g. "8414069.R12.AR.B")
For more tips about telling these patches apart, see:

Monday Aug 17, 2009

Evolutionary Steps for Automated Testing for E-Business Suite

My, how time flies.  It's been over two years since I last covered automated regression testing for the E-Business Suite. Our strategy for this area continues to evolve, so it's high time for an update.

The E-Business Suite Test Starter Kits consist of sample test scripts from our own E-Business Suite QA group, along with a Starter Guide, Best Practices Guide, and Installation instructions.  The test scripts were created against an EBS Vision Demo database displaying American English.  You can use these kits as a model for building out your own automated regression tests for your Apps environment.

Back in 2006, you could download our Quality Assurance teams' automated WinRunner QA scripts for Oracle E-Business Suite Release 11i via a Test Starter Kit.  A few things have changed since then:

Test Starter Kits for WinRunner are Still Available

If you're still using WinRunner, you'll be pleased to learn that you can still download:

  • WinRunner Test Starter Kit for Release 12.0.4 - Patch 6799654
  • WinRunner Test Starter Kit for Release 12.0 - Patch 5845794
  • WinRunner Test Starter Kit for Release - Patch 4520701
  • WinRunner Test Starter Kit for Release 11.5.10 - Patch 4064542
  • WinRunner Test Starter Kit for Release 11.5.9 - Patch 2983563
  • WinRunner Test Starter Kit for Release 11.5.8 - Patch 2739616
  • WinRunner Test Starter Kit for Release 11.5.7 - Patch 2471695

New Test Starter Kits for QTP are Available

If you've switched over to HP's QuickTest Professional (QTP) testing set of tools, you can now download the following new Test Starter Kits:

  • QTP Test Starter Kit for Release 12.1.1 - Patch 8408886
  • QTP Test Starter Kit for Release 12.0.4 - Patch 6845309
  • QTP Test Starter Kit for Release 12.0 - Patch 5845799
  • QTP Test Starter Kit for Release - Patch 4611398
  • QTP Test Starter Kit for Release 11.5.10 - Patch 4355248
  • QTP Test Starter Kit for Release 11.5.9 - Patch 3313315

Only a subset of the E-Business Suite products with automated WinRunner scripts have QTP equivalents; the READMEs for the respective kits have more details about their contents.

The master list of Test Starter Kits available for Apps is published in:

Related Articles

Friday Mar 28, 2008

Downtime and Apache Restricted Mode in E-Business Suite Release 11i

As I started writing down the steps for my recent post Downtime and Apache Restricted Mode in Release 12,  Steven and I exchanged some conversations which made me realize that there would be questions about the availability of the same feature Release 11i. And, it turns out to be fairly accurate realization. So, this one is for those enthusiastic pals of mine on this community. I will try to do this differently, hence making it relatively shorter in content.

Is this feature available in 11i?

Yes. Check out the documentation in the References section.

Is the procedure to configure the same?

Yes. As you might have understood already, there are two pieces in this puzzle.
  1. Creation of downtime
  2. Managing Apache in Restricted Mode
Answering the question based on these two tasks,
  • Step '1' is pretty much the same in 11i and Release 12. Hence, I am not recreating the screen shots for you here. Check out the steps in the table below.
  • Step '2' is automated in Release using the perl script where as in Release 11i, the administrator has to go through some manual steps and run Autoconfig.

What are the exact steps in Release 11i?.

Thanks to our OAM team, these are well documented. And, they work like charm. Below are the sequence of steps for my DBA friends.

Note: Navigation in 11i for the downtime page creation is slightly different than Release 12.

How to?
Schedule System Downtime and warn your end users of an impending downtime Use OAM to schedule downtime:
  • ( Navigation: Sitemap=>Maintenance=>Patching and utilities=>Schedule Downtime)
Complete the required one-time setups to monitor patch in progress
  • Use OAM Autoconfig editor to edit the variable < s_trusted_admin_client_nodes > to include the list of hosts that can access OAM in restricted mode. Run autoconfig to ensure that the new settings take effect.
  • Ensure that you have enabled the the monitoring user account by unlocking the ad_monitor user account and setting the password by using the following commands:
    • alter user ad_monitor account unlock;
    • Login to SQL*Plus as user ad_monitor. Default password is lizard. Reset the password.
Shutdown all Applications services
Use the standard ad script from $COMMON_TOP/admin/scripts/<context_name> directory:
  • adstpall.sh <apps user>/<password>
Enable maintenance mode for your Applications system
Run adadmin, and:

  1. Select option 5 'Change Maintenance Mode'
  2. Select option 1 'Enable Maintenance Mode'
Start OAM in restricted mode to monitor patching in progress
From the $COMMON_TOP/admin/scripts/<context_name> directory, run:

  • adaprstctl.sh start
Begin applying patch
Run adpatch (hotpatch=n)
Monitor patching in progress
  1. Access OAM in restricted mode from the following URL : http://host:port/servlets/weboamLocal/oam/oamLogin
  2. Login into OAM using the ad_monitor user
  3. Navigation: Sitemap=>Maintenance=>Patching and utilities=>Timing Reports
Confirm the end of scheduled downtime upon patch completion
From within OAM in restricted mode:
  • Navigation: Sitemap=>Maintenance=>Patching and utilities=>Manage Downtime Schedules=>Select "Complete" button.
Bring your Applications System to normal mode.
Run adadmin, and:
  1. Select option 5 'Change Maintenance Mode'
  2. Select option 2 'Disable Maintenance Mode'
Shutdown Apache in restricted mode and Restart all services
From the $COMMON_TOP/admin/scripts/<context_name> directory, run:
  • adaprstctl.sh stop
  • adstrtall.sh <apps user>/<password>


  1. About Oracle Applications Manager Mini-pack 11i.OAM.H
  2. Chapter 6, Section 'Managing Downtime in Restricted Mode' of Oracle Applications System Administrator's Guide - Maintenance - Release 11i (Part No B13924-04)

Monday Mar 24, 2008

Downtime and Apache Restricted Mode in Release 12

E-Business Suite Release 12 provides a useful mechanism for the Applications administrators to start the Apache on the applications tier during down time. Applications administrator can start the apache in a mode called restricted mode. Down time tasks like patching can continue to be performed while the Apache is in restricted mode. Restricted access to Oracle Applications Manager (OAM) is available in this mode. This allows the system administrator to monitor tasks like patching from OAM. When the Apache is started in restricted mode, normal users are redirected to a downtime URL containing downtime details.

Enabling Apache in Restricted Mode in Release 12

I. Creation of downtime schedule

1. Login to OAM as administrator and create a downtime schedule as follows.
    1. Click on Sitemap -->  Maintenance --> Maintain downtime schedules
    2. Click on Schedule Downtime link

downtime wizard1:

2. Enter the details in the page and click submit

downtime wizard2:

The above step creates a downtime.html page that will be used as redirect page when apache is started in restricted mode

3. The downtime schedule can be viewed, edited, or cancelled from the wizard.

downtime wizard3:

II. Configuring and starting restricted mode Apache

  1. Login to your applications tier and source the environment file so that all the environment variables are set
  2. Stop your application tier services by running adstpall.sh from $ADMIN_SCRIPTS_HOME directory.

  3. Run the command 'txkrun.pl -script=ChangeApacheMode' from the <FND_TOP>/bin directory:
  4. This prompts for the following inputs:
    1. full path for the Applications Context file
    2. Enter the mode for Apache. Type 'Restrict'
    3. Confirmation of whether you have stopped your applications tier services
  5. Once you enter the above details, the configuration script
    1. sets the respective context variables in the context file required to configure the Restricted mode
    2. instantiates the configuration files for the HTTP Server and OC4J in the <INST_TOP>.
  6. Restart the services on the applications tier upon successful completion of the above command.
During the configuration, the utility makes a copy of your context file in case you hit any issues. This backup file can always be restored and autoconfig be run to restore to the original state.

Now, you are ready to use the restricted mode of Apache and plan your downtime activities!!.

When the users try to access the Applications home page, they get redirected to the downtime page generated when you schedule downtime.

The URL for the down time page is:


Sample downtime page:


Access to OAM

For system administrators, a user named ad_monitor is available to access OAM during the restricted mode. Login from this user provides access to the maintenance wizards and status pages in the Oracle Applications Manager.

Disabling Restricted Mode of Apache

Once you are done with your downtime activities, Restricted mode of Apache can be disabled i.e., switching back to NORMAL mode by running the following steps:
  1. Login to your applications tier and source the environment file so that all the environment variables are set
  2. Stop your application tier services by running adstpall.sh from $ADMIN_SCRIPTS_HOME directory.

  3. Run the command 'txkrun.pl -script=ChangeApacheMode' from the <FND_TOP>/bin directory:
  4. This prompts for the following inputs:
    1. full path for the Applications Context file
    2. Enter the mode for Apache. Type 'Normal'
    3. Confirmation of whether you have stopped your applications tier services
  5. Once you enter the above details, the configuration script sets the respective context variables in the context file required to reset the mode back to Normal
  6. Restart the services on the applications tier upon successful completion of the above command.
You are all set for normal user activity now.



Tuesday Jun 19, 2007

Top 7 Ways of Reducing Patching Downtimes for Apps

[June 20 Update:  Our blogging software temporarily lost its mind and somehow misnumbered the list at the heart of this article.  There are only seven tips here, not ten.]

I've previously discussed the Top 5 Myths About Patching Apps Environments.  One of the most commonly-cited reasons for not patching E-Business Suite environments is that it takes too much downtime.  If you're relatively new to Apps system administration, here's a primer on the key techniques compiled by our Applications Release Engineering group for reducing patching downtimes.  Although some of the linked documents are written specifically for Release 11i, these techniques may be used for both Apps 11i and 12.

  1. Use a staged applications system

    This major time-saver hinges on a key principle:  all of your applications filesystem patches are applied to a clone of your production Apps environment.  This can be done while your production system is still running.  Your production system is down only for the time needed to apply database patches.  For details, see:
  2. Use a shared application-tier file system

    If you have a pool of application-tier servers set up for load-balancing, make sure that all of the individual servers share a single application filesystem.  Patches applied to this central shared filesystem are instantly available to all application-tier servers.  I've previously given an overview of this technique in this article.

  3. Distribute worker processes across multiple servers

    When applying a patch that includes a large number of processes, you can reduce the downtime even further by distributing the worker processes across multiple servers on multiple nodes. Using the Distributed AD feature of AutoPatch and AD Controller, you can assign workers to run on the primary node and on other nodes that share the filesystem. See:

    Distributed AD (Metalink Note 236469.1)

  4. Merge multiple patches using AD Merge Patch

    Merging patches saves time because the AutoPatch overhead of starting a new session is eliminated for those patches that are consolidated.  Duplicate linking, generating or database actions are run once only.  If two patches update the same file, AD Merge Patch will save time by applying only the latest one.  Patches can -- and should -- be merged with their listed prerequisite patches. 

    For more details about this AD utility, see the Oracle Applications Maintenance Procedures guide for your Apps release.

  5. Run AD Patch in non-interactive mode

    Applying a set of patches using AD Patch in non-interactive mode eliminates the delay between successive tasks.

  6. Defer system-wide database tasks until the end

    Using adpatch options=nocompiledb,nomaintainmrc defers system-wide database tasks such as "Compile APPS schema" and "Maintain MRC" until after all patches have been applied.  As of AD.H, AutoPatch automatically compiles the APPS schema and maintains MRC when applying standard patches.
  7. Avoid resource-related bottlenecks

    Patching can grind to a halt if you bump into the ceiling on your system.  Before patching, make sure that you've enabled automatic tablespace management, and that you have sufficient hardware and free disk and temp space.
The cumulative downtime reductions of all of these techniques can be quite significant.  I've touched on some of the biggest timesavers, but this short article isn't comprehensive, by any means.  The linked Notes in this article and below discuss a number of other tips for shrinking your patching downtimes.  A small investment in learning these techniques can pay off in large reductions in patching times, and is well worth your time.


Thursday Jun 07, 2007

Top 5 Myths About Patching Apps Environments

When I was younger, I thought I could change this world.  Now I no longer think so but for emotional reasons I must keep on fighting a holding action.
~ Robert Anson Heinlein

A rational person might contend that actions follow attitudes which follow beliefs.  Bad news:  there's a substantial amount of psychological literature that suggests that this isn't how we really tick.

There's an equally large body of empirical data that suggest that beliefs are relatively intractable.  Once established, certain beliefs don't change, regardless of the best data, clear reasoning, and eloquence that can brought to bear on the subject.  Recent fMRI studies suggest that brain structures may develop in such a way that people with strongly-held beliefs are actually unable to process new information that contradicts those beliefs.

The broader implications of this are kind of depressing to contemplate.  But I digress.

For the narrow purposes of this discussion, I'm compelled to make an admittedly-quixotic attempt to persuade you that the benefits of keeping your E-Business Suite environment up-to-date far outweigh the costs.

"We Can't Upgrade Because..."
  1. It requires too much downtime
  2. Testing is too expensive for end-users
  3. It's too complicated
  4. We don't have enough staff
  5. It ain't broken; why fix it?
Myth #1:  It Requires Too Much Downtime

This seems rational on the surface.  After all, a downtime for patching seems worse than no downtimes.  

Remember that we issue patches for four major reasons: 
  1. To add new functionality
  2. To improve stability
  3. To improve performance
  4. To improve security
The corollary is that an unpatched system may have fewer or less-sophisticated features, and be slower, less stable, and less secure than a fully-patched one.  Questions to consider if you believe this myth:
  • How much downtime is currently caused by unplanned outages due to unpatched stability bugs?
  • How much downtime is needed to bounce your application servers due to unpatched JVM memory leaks?
  • How much downtime would be required if an attacker takes advantage of an unpatched security risk?
There are a number of key ways to reduce downtimes.  One of these is to use shared filesystems in environments with multiple application servers.  There are a number of other ways, too.  These are beyond the scope of this article, so I've covered the top seven way of reducing downtimes in this article.

Myth #2:  Testing is Too Expensive For End-Users

Many organizations recruit business users to participate in User Acceptance Tests for patches.  If you do this, it's true that this may reduce participants' productivity for the duration of your testing phase. 

If you don't keep your environment up-to-date, the key consideration are: 
  • How much productivity is lost for all end-users -- not just the testers -- due to unpatched performance, stability, or security bugs?
  • How much could productivity be improved by new features?

Myth #3:  It's Too Complicated

All DBAs know the drill:  before patching, print all patch READMEs and spread them in neat piles across a big surface.  Read them, highlight them, then reread them.  Download more patches.  Repeat. 

Every patch has some set of prerequisites.  These prerequisites lead to other prerequisites.  The key thing to remember is that it's always easier to patch an up-to-date system than something that's much older.  The bigger the gap between your current system and target patch level, the more complex the upgrade will be. 

The best way of reducing the complexity of upgrades is to keep your system up-to-date.

Myth #4:  We Don't Have Enough Staff

I need to be candid here:  there's a kernel of truth in this one.  This is a direct outcome of believing Myth #3.  If you haven't patched your E-Business Suite Release 11i environment since it was installed in 2001, then you may have a big and complex upgrade project on your hands.  This might take more staff than you have.

So, the best way of avoiding the truth of this one is to keep up-to-date with your patching.  If you're up-to-date, applying a given patch is less complex and requires fewer staff.

Myth #5:   It Ain't Broken; Why Fix It?

With apologies to English teachers everywhere, that old axiom has a seductive ring of truth to it.  After all, your system seems to be running just fine, thank you.  Why bother potentially destabilizing something that everyone's happy with?

Repeating the key points made in Myth #1:  new releases are issued to provide new functionality and improve stability, performance, and security.  If you're running an older release, it has -- by definition -- issues in these areas that you may not have noticed yet.

The Worst-Case Scenario

If I were an IT manager running an older E-Business Suite release, say 11.5.2, this would be the nightmare scenario that would keep me awake at night:

My business requirements changes due to an acquisition or a change in business practices.  These changes trigger a different load or usage profile on my E-Business Suite environment.  This triggers a Severity 1 outage.  My production system is down.

I call Oracle Support for help.  Good news: they can reproduce the problem on 11.5.10.CU2.  Bad news: due to technical dependencies (and supported by the fact that 11.5.2 is out of Premier Support status), they can only issue patches on top of 11.5.10.CU2.  There's no way of getting that patch backported to 11.5.2.

Now I'm faced with a huge upgrade from 11.5.2 to 11.5.10.CU2.  My production system is still down, and it will be down for the duration.  Users are burning me in effigy in the parking lot.  Business impact analysis, user acceptance testing, load-testing, fail-over testing -- all of those activities are jettisoned in my panicked attempts to get my environment running again.

It's Never Too Late

At this point, I suspect I've lost most of my readers.  By now, they've clicked on the latest YouTube video or something more cheerful. 

For the handful of my remaining readers:  if you're in a hole, it's never too late to stop digging.  Less metaphorically:  just because you're behind in patching doesn't mean that you should just give up entirely. 

We can help you put together a strategy for getting up-to-date.  Your first call should be to your Oracle account manager.  He or she can engage specialists in Sales Consulting or Consulting to help.  There are also excellent groups in our Advanced Customer Services organization (a.k.a. Field Support, On-Site Support, Premium Support) who offer a number of of packaged and customizable support offerings in the patching realm. Alternately, there are a number of excellent third-party consultants who can help you with this.  Or, if you're really intent on doing it yourself, there are forums on the Oracle Technology Network and elsewhere where you can brainstorm possible upgrade strategies.

Whatever path you choose:  start now.  It's never too late.


Monday May 21, 2007

Preventing Apps 11i Performance Issues in Four Steps

In my previous article, I talked about what to look for once you have a performance issue with Apps 11i.  In this article, I'll discuss four maintenance activities that you can do proactively to reduce the chances of encountering certain types of performance issues:

  1. Check performance against a baseline
  2. Follow a regular purging schedule
  3. Gather schema statistics regularly
  4. Follow a systematic pinning strategy


1.  Check Performance Against a Baseline

  • Create a baseline so you can monitor performance in response to changes or over time. For example, you may create 6-10 repeatable short transactions of at least 10 seconds each, which represent common functions and/or areas of particular concern.
  • Always execute this baseline test from the same PC and the same location in order to get consistent results.
  • Rerun the test as part of your normal User Acceptance Testing for any system changes.
  • Review and update your performance baseline as part of any upgrade project.

2.  Follow a Regular Purging Schedule

You need to ensure that any data that needs to be purged is scheduled on a regular basis. The data that can (or should) be purged will vary between different products, so confirm the recommendations for the specific products you are using.  All customers will generally need to schedule purging activities for the FND and Workflow products.

Concurrent Jobs to Purge Data

Most customers will need to schedule these Concurrent Purge processes:

  • Purge Obsolete Workflow Runtime Data (FND)
  • Purge Debug Log and System Alerts (FND)
  • Purge Signon Audit data (FND)
  • Workflow Control Queue Cleanup (FND)
  • Delete Data from Temporary Tables (ICX)
Naturally, your business requirements may be unique; review our purging documentation for suitability and establish your own list of jobs to run.

Workflow Specific Purging Tips

Review the following documents for Workflow-specific tips on purging:
Also keep track of number of rows in underlying Workflow tables to ensure they are not continually increasing, to ensure the data really is being purged

3.  Gather Schema Statistics Regularly

In general, running this monthly to bi-weekly should be sufficient with 10%, unless there is any known data skew. As with any generic suggestion, this would need to be proven for suitability on your own environment. For example, it is more important to run this when your data distribution changes, rather than when the amount of data changes.

If your environment is a 24x7 system, you should pick "N" for "Invalidate Dependent Cursors" to prevent fragmentation of the shared pool

For more details about gathering statistics, see: 4. Follow a Systematic Pinning Strategy

Despite new 10g features making ORA-4031 errors a rare occurrence, it is still recommended to have a pinning strategy, even with Apps 11.5.10 running on 10gR2 databases.

General guidance

  • Monitor X$KSMLRU for candidates to pin (> 4100 bytes)
  • Do not pin more than 20% of the Shared Pool
  • Review your Pinning Strategy for changing business cycle, such as month end or overnight batch runs
  • No need to pin objects used only for batch jobs
  • Also have an "un-pinning" strategy
For more information about pinning in Apps environments, see:


Running regular maintenance tasks and ongoing monitoring are essential activities to ensure that your system is performing to the best of its ability. This article highlights a few of the areas that are sometimes overlooked in Apps DBA schedules.


Thursday May 17, 2007

Performance Tuning the Apps Database Layer

Performance tuning is an art that should be executed systematically.  I assume almost everyone has heard this from others talking about tuning, but it's often repeated because of its fundamental truth.

Whenever there's a response time issue for the E-Business Suite, it is initially treated as a performance problem.  I would go a step further and say that poor performance is not a problem by itself.  It is a result of a root cause which lies somewhere else.

Let's dig into each of the possible places for this root cause in the E-Business Suite's technology stack.  Oracle Applications has the following layers:

  • Operating system
  • Database
  • Techstack components (Concurrent tier, Forms tier and iAS techstack)
  • Application code (Forms, Jsp etc)

This article will touch on performance-related issues for the database layer. 

Possible Causes for Database Layer Performance Issues

Possible issues include:

  • Core optimizer issues
  • Known performance-related database bugs
  • Incorrect statistics to the cost-based optimizer (CBO), which is responsible for SQL optimization
  • Incorrect System Global Area or Program Global Area sizing

Other areas like locking and latching affect performance, too.  For the limited purposes of this article, we'll focus on these four setup-related areas.

Core Optimizer Issues

Most of the E-Business Suite database's recommended parameters are listed in:

Setting the values of memory-related parameters as is not a one-time job.  We recommend that you periodically review your Statspack and AWR reports to find if the your current settings meet your current load requirements.

For recommended database init.ora parameters, refer to Note 216205.1.  Pay attention to mandatory parameters.  Based on our benchmarks, we recommend that your Apps setup adhere to the init.ora parameters listed in the note.

Known Performance-Related Database Bugs

For issues with the Oracle optimizer and database, most of the known performance-related bugs and their recommended patches for E-Business Suite environments are listed in:

Incorrect Statistics to the Cost-Based Optimizer (CBO)

Now comes the most complex -- and paradoxically, the easiest to solve -- area:  ensuring that the cost-based optimizer works with accurate metadata.

Before going into the details, I'll remind you that the rule-based optimizer (RBO) is no longer supported.  Don't use it in your E-Business Suite environments.  If you have an optimizer-related issue that appears in the cost-based optimizer but not the rule-based optimizer, log a Service Request with Oracle Support.

Maximizing the Benefits of Cost-Based Optimization

The cost-based optimizer is not infallible, but it's a DBA's responsibility to ensure that correct metadata is available to the optimizer.  There are two things that system administrators should do:

    1. Set all of the optimizer-related parameters, following Note 216205.1
    2. Gather statistics for all database objects

Gathering Statistics for Database Objects

There are many different ways of gathering statistics:

  • Use the Analyze command
  • Use Dbms_stats
  • Use Fnd_stats

For Oracle E-Business Suite environments, we recommend using fnd_stats.

A Short Digression:  What is fnd_stats?

Fnd_stats is a wrapper around dbms_stats that suits most of the E-Business Suite's requirements.

We recommend using fnd_stats over dbms_stats for Apps environments because of the former's support for restarts.  Starting with the 10g version of the database, dbms_stats has also this feature.

If the Gather Schema Statistics concurrent program is used, fnd_stats does the bookkeeping for the run.  Should the run fail for any reason, the next run of the program starts from where the previous run was stopped. This saves lot of time.

We also recommend  fnd_stats because of its support for histograms.  Histograms are useful when:

  1. A table's column is used in an equality or equi-join predicate AND there are  skews in the column.
  2. A table's column is used in a range or like predicate AND there are  skews in the column.

Oracle Applications' data distribution is dependent on the functionality of the specific product modules.  Based on our benchmarks, a number of columns are useful for histograms.  These columns are listed in the FND_HISTOGRAM_COLS table.

When Gather Schema Statistics is executed, it reads FND_HISTOGRAM_COLS and builds the histograms.  

gather schema/table statistics:

Back to Gathering Database Statistics

When gathering the statistics for the entire applications database, we must use the Gather Schema Statistics concurrent program.  Only the Gather Schema Statistics and Gather Table Statistics should be used. Do not use the Gather Column Statistics program.

When Should You Gather Statistics?

There is again no hard and fast rule for the interval between gathering statistics.  A general rule-of-thumb is is to run the statistics collection after a 10% increase in the database size. 

Having said that, other factors may come into play.  From 11.5.10 onwards, fnd_stats was enhanced to gather only the statistics for those objects, which have undergone a predefined percentage of data increase, or for objects that have no statistics.  The latest versions of fnd_stats identifies STALE and EMPTY statistics and gather the statistics for those objects only.  This saves lot of time and allows sysadmins to set flexible statistics gathering intervals. 

gather auto:

Although there are a number of options that you can set, we recommend selecting  the GATHER AUTO option.

Incorrect System Global Area and Program Global Area Sizing

SGA_TARGET sets the maximum size of the System Global Area (SGA) for your E-Business Suite instance.  Parameters like db_cache_size and shared_pool_size affect the database buffer cache and shared pool for that instance.   PGA_AGGREGATE_TARGET determines the maximum value for the Program Global Area (PGA) for the instance.

Note 216205.1 lists the minimum values for these parameters. These values might need to be varied depending on the actual load on your environment.  In the same way.  If these are values are:

  • Set too high:   memory on the server is not utilised to the maximum
  • Set too low:  there will be performance issues

To find out whether these parameters are set properly on your system, you can run a Statspack or AWR report.

Interpreting Statspack or AWR Reports

Covering this area comprehensively is beyond the scope of this article, but here's some things to remember about interpreting your Statspack report:

Total Response time = Service time + Wait time

Service Time is generally the amount of "CPU used by this session".  From the 9.2 database version and upwards, this is reported in the Statspack.  You can use this to derive the Wait Time.

For example:

Using a Statspack report with a 9.2 or higher database, examine the figures reported for CPU time.  If it's 70% of the total time, then the wait time is 100-70=30%.

Total Response time = 70% CPU time + 30% wait time

Once we have identified the time taking component, look into the specific component.  For memory-related areas, look at the Advisory Statistics section.  This shows details about the sizing of SGA (cache and shared pool) and PGA (M-pass executes should be as small as possible for a optimal PGA). 

Based on this type of analysis, you can vary your SGA and PGA settings accordingly.

Getting Support for Performance Issues

As you can see, investigating performance issues can be tricky, especially if you find the topics briefly described here to be daunting.  Take heart:  you're not on your own.  We have teams that specialize in these areas, so if you run into any performance-related issues, log a Service Request via Metalink and we'll jump in to help.

In Summary

A quick recap of database setup-related issues and recommendations for tracking down performance-related issues:

  • Database Parameters:  Refer to Note 216205.1 for mandatory settings, and then check your Statspack/AWR reports periodically to see whether your memory-related parameters are set correctly.

  • Apply the Recommend Performance-Related Database Patches:    Refer to note 244040.1

  • Keep Your Statistics Fresh: Use the Gather Schema/Table Statistics concurrent program regularly.  The GATHER AUTO option can be of great help here.

It's possible to dive much deeper into database or instance tuning topics.  If you'd like me to go into more details, feel free to post a comment here.


Wednesday May 09, 2007

Reducing Patching Downtimes via Shared Apps File Systems

I had a chance conversation with an Apps sysadmin late in the evening at the Collaborate conference.  He wearily noted that he had to somehow cover increasing maintenance costs with reduced levels of staffing.  The conversation suggested that our support for shared E-Business Suite application tier file systems may be one of our better-kept secrets.  If you haven't come across this yet, here's a way of reducing the amount of time that you spend patching.

Scaling Up with Load-Balancers

When your E-Business Suite user base grows beyond a certain size, it's likely that you'll look into deploying multiple application servers in a load-balanced pool of nodes.  Your deployment topology might look like this:

Generic Apps Load-balancing:

Regardless of whether you're running Release 11i or 12, each of these nodes would need its own disk space and would to be maintained and patched separately.  What a pain.

Different File System Structures in Release 11i and 12

In Release 11i, the Applications tier file system includes the APPL_TOP, the COMMON_TOP, and the Applications technology stack (8.0.6 and iAS ORACLE_HOMEs).  In a traditional deployment, each one of these application servers would have its own Applications file system, like this:

Distributed Application Tier Filesystem:

In Release 12, the Applications tier file system includes the APPL_TOP, the COMMON_TOP, and the Applications technology stack (10.1.2 and 10.1.3 ORACLE_HOMEs), plus a new INST_TOP.  Each node would have the following:

Release 12 application tier structure 2:

Enter the Shared Application Tier File System

For Release 11i, starting with 11.5.10, it's possible to put the Applications tier file system on a shared disk resource mounted to each Application tier server node in the system, like this:

Shared Application tier file system:

Similarly, in Release 12, you could put the Applications tier file system on a shared disk resource mounted to each Application tier server node, like this:

Release 12 shared filesystem:

Migrating to Shared File Systems

There are a few prerequisites:
  • Different nodes must be running the same operating system and the same O/S patches
  • You must be on a UNIX platform (Windows doesn't support shared file systems)
Certification of Shared File System Solutions

If you've gotten to this point, you're probably wondering, "Is my _____ SAN/NAS shared file system software certified with the E-Business Suite?"  File system solutions that customers have recently asked about include:
Supported but not Certified

The short answer is that your shared file system solution is supported but not certified with the E-Business Suite, for either Release 11i or 12.  Remember that there's a key distinction between support and certification, which I've covered in detail in this article:
The complexities of whatever shared disk resource management solution that you're using must be transparent to the E-Business Suite.  Aside from that, there aren't any special requirements for shared disk resources.  They can be local to the server or on a standalone disk array.

Almost Irresistible

Regardless of which E-Business Suite release you're running, the main advantage of using an Application shared file system is simplicity and ease of patching:  when you apply patches or changes to the shared disk resource, they're immediately visible on all application tier server nodes.  You patch in a single place and deploy those changes across multiple servers.  You save disk space for each additional application node you deploy, and it's easier to deploy additional nodes, too.

If you've been wondering how to squeeze more productivity out a packed roster of administration activities, I'd recommend setting up a testbed to try this out.  Feel free to post a comment with your experiences with this; I'll make sure your feedback gets back our team.

Related Articles


« July 2016