This document offers strategic guidance on performance best practices for Oracle E-Business Suite (EBS) environments. Unless specifically noted otherwise, the content applies to on-premises, Oracle Cloud Infrastructure (OCI) and Exadata Cloud@Customer EBS instances.
The contents are divided into a number of separate sections and subsections that address a variety of common issues and questions about Oracle E-Business Suite performance. You may either read through the document sequentially, or refer to specific areas of interest as needed.
There is a change log at the end of this document.
Note: To receive automatic notifications of updates, subscribe to the Hot Topics Notification feature in My Oracle Support, as described in
Document 793436.2,
Subscribing to Hot Topic E-Mails.
In This Document
This document is divided into the following sections:1. Introduction
This document provides a set of best practices to optimize the performance of Oracle E-Business Suite. It can be regarded as a form of FAQ.
Note: The sections and topics are ordered and numbered solely for ease of reference, and do not necessarily reflect the order in which they may apply to your environment or should be acted on.
Section 2. Oracle Cloud Infrastructure
The guidance in this section applies specifically to Oracle E-Business Suite on Oracle Cloud Infrastructure (OCI).
Oracle E-Business Suite Release 12.2.3 (and higher) and Oracle E-Business Suite 12.1.3 are currently certified with Oracle Cloud Infrastructure (OCI). Oracle E-Business Suite Cloud Manager offers a graphical user interface for creating, managing, and configuring Oracle E-Business Suite environments on Oracle Cloud Infrastructure. This includes the capability to provision environments from backups created using the Oracle E-Business Suite Cloud Backup Module as part of a lift and shift operation.
For more information about Oracle E-Business Suite on OCI, refer to My Oracle Support Knowledge Document 2517025.1, Getting Started with Oracle E-Business Suite on Oracle Cloud Infrastructure.
2.1 Pre OCI-Migration Best Practices
- Before migrating Oracle E-Business Suite to OCI, you should determine the required Oracle Cloud Infrastructure shape, including such items as number of OCPUs, amount of memory, storage volume capacity, and network connectivity.
- Reducing the size of the database will reduce the time to move (lift and shift) an instance either from on-premises to cloud or from one cloud/region to another. Reducing the volume of the data has other advantages, including improved performance, reduced storage costs, reduced backup time, and—particularly important for many customers—reduced upgrade time and associated downtime.
2.2. Oracle Cloud Infrastructure Best Practices
- Oracle E-Business Suite performance is significantly affected by the network latency between the database and application tiers. These should therefore be co-located in the same (regional) data centre to minimize network latency.
- The
AcceptMutex directive sets the method HTTP uses to serialize multiple children accepting requests on network sockets. For improved performance on application tiers, you should set AcceptMutex to sysvsem instead of fnctl. Setting AcceptMutex to sysvsem avoids lock file issues for NFS mounts. To deploy FSS with Oracle E-Business Suite on OCI, follow the guidelines in the Oracle by Example (OBE) tutorial: Sharing the Application Tier File System in Oracle E-Business Suite Release 12.2 or 12.1.3 Using the Oracle Cloud Infrastructure File Storage Service.
- For mount options for Network File Systems (NFS), refer to My Oracle Support Knowledge Document 1375769.1, Sharing The Application Tier File System in Oracle E-Business Suite Release 12.2. This provides valuable guidance on setting NFS parameters appropriately for your environment. On the application tier, the read and write sizes should typically both be set to at least 64 KB. On OCI, these settings will be managed automatically by Oracle E-Business Suite Cloud Manager: if you wish to set them manually, refer to the same My Oracle Support Knowledge Document 1375769.1.
- Use the SQL Performance Analyzer (SPA) database feature to compare the performance of Oracle E-Business Suite before and after your migration to cloud. For further information, refer to the Oracle White Paper: Migrating to the Oracle Database Cloud with SQL Performance Analyzer.
Section 3. Database Best Practices
This section provides guidance on Oracle Database settings and options that are particularly relevant to performance.
- Refer to My Oracle Support Knowledge Document 244040.1 Oracle E-Business Suite Recommended Performance Patches for known performance related bugs and their recommended patches for Oracle E-Business Suite environments. Identify the necessary patches/fixes and apply them.
- Use the EBS Technology Codelevel Checker (ETCC) to ensure all that all required database tier patches have been applied. Refer to My Oracle Support Knowledge Document 1594274.1 Consolidated List of patches and Technology Bug Fixes for more information about ETCC and the appropriate set of recommended patches to install.
- Refer to My Oracle Support Knowledge Document 396009.1 Database Initialization Parameter Settings for Oracle E-Business Suite for a list of recommended database parameters. Ensure all the mandatory parameters are set appropriately. These settings are also applicable to OCI.
- Set the parameter FILESYSTEMIO_OPTIONS=SETALL. Ensure that asynchronous I/O is enabled for kernel operations. I/O performance can be severely degraded if you do not set this parameter.
- For Oracle E-Business Suite Release 12.2.x, set the parameter RESULT_CACHE_MAX_SIZE to 600 MB to make optimal use of result cache memory. This will reduce the DML activity on the tables, as a single DML action could invalidate the result cache and might result in latch free waits, potentially affecting the overall performance of the database. Example: FND_PROFILE table.
- Configure
HugePages on the database server to improve SGA management. This enables the SGA to be pinned in memory, insuring against page-in or page-out operations and reducing the system resources needed to manage page table entries. Be aware that only the SGA (and not the PGA) can utilize and benefit from HugePages. Refer to My Oracle Support Knowledge Document 1392497.1 USE_LARGE_PAGES To Enable HugePages for information about enabling HugePages for the Oracle E-Business Suite database.
- Set the parameter USE_LARGE_PAGES=’only’ on each instance, so that the instance will only start if sufficient
HugePages are available, and thereby avoid out of memory errors.
- If you are using Oracle Database Release 12.1.x on an Exadata Linux environment, set the parameter EXAFUSION_ENABLED=0 in your database initialization parameter file. This will ensure the parameter is disabled (the default). Exafusion is a Direct-to-Wire protocol on Exadata Linux environments that allows database processes to receive and transmit Oracle RAC messages directly over the Infiniband network, bypassing the overhead of entering the OS kernel. For more information on Exafusion and the database versions it supports, refer to My Oracle Support Knowledge Document 2037900.1, EXAFUSION Details and Supported Versions.
- Pin selected PL/SQL packages into the shared pool, to ensure that the packages remain in memory and are thereby prevented from being paged out and then reparsed upon reload. You can use the DBMS_SHARED_POOL package to pin large or frequently stored programs into the shared pool. The best time to pin an object is immediately after the database has been started. This increases the likelihood that enough contiguous memory will be available to store the object. Having objects pinned will reduce shared pool fragmentation and the chances of receiving an ORA-04031 error. Refer to My Oracle Support Knowledge Document 69925.1 Pinning Oracle E-Business Suite Objects Into The Shared Pool.
- Set the Sequence Object Cache Size to 1000 or higher for gapless sequences utilized by bulk DML operations. Having a larger sequence cache will improve performance, especially in cases where there is contention on a particular sequence.
Use a command such as shown in this example:SQL> ALTER SEQUENCE <sequence name> CACHE 1000;
Note: Cached sequence numbers will be lost when the database is restarted.
- Use native compilation to compile PL/SQL programs into native code that does not need to be interpreted at runtime. Refer to My Oracle Support Knowledge Document 1086845.1 PLSQL Native Compilation (NCOMP) for more details.
- For Oracle RAC instances:
- Configure Jumbo Frames for the private Cluster Interconnects, This requires careful configuration and testing to realize its benefits. Refer to My Oracle Support Knowledge Document 341788.1 Recommendation for the Real Application Cluster Interconnect and Jumbo Frames for current recommendations.
- Set the parameter PARALLEL_FORCE_LOCAL = TRUE so that the parallel query and parallel server processes are restricted and can only operate on the same Oracle RAC node where the query coordinator resides (the node on which the SQL statement was run). For a list of the top database and instance performance issues and solutions in Oracle RAC environments, refer to My Oracle Support Knowledge Document 1373500.1 Top 5 Database and/or Instance Performance Issues in RAC Environment.
- Run the Gather Schema Statistics concurrent program to ensure that the database optimizer has up-to-date statistics available when it formulates an execution plan. This program should be run whenever there have been significant changes to data, such as data incorporation or integration from a large batch job. This program can be run for all schemas or a subset of schemas. Monitoring end-user transaction times and database performance will help you identify applicable schemas, and the interval needed to help ensure peak performance. For more information, refer to My Oracle Support Knowledge Document 1586374.1, Best Practices for Gathering Statistics with Oracle E-Business Suite.
Table 1: Do's and Don'ts When Gathering Object Statistics
Do's
| Don'ts
|
|---|
- Gather statistics for the application tables at regular intervals, as well as whenever there is more than 10% of data change. For data dictionary objects, gather statistics when there is a significant change in the dictionary, such as when many new objects are created after the upgrade or major patching cycle.
- The automatic statistics gathering job does not gather dictionary and fixed object statistics. Use the DBMS_STATS.GATHER_DICTIONARY_STATS package for gathering dictionary object statistics. In Multitenant Database environment the statistics should be gathered in both PDBs as well as CDB. Consider gathering dictionary statistics after any of:
- A database upgrade
- A platform change
- An Oracle E-Business Suite upgrade
- Use the DBMS_STATS.GATHER_FIXED_OBJECT_STATS package for gathering fixed object statistics. For optimal performance gather statistics when there is a representative workload on the system. In Multitenant Database environment the statistics should be gathered in both PDBs as well as CDB. Consider gathering fixed object statistics after any of:
- A database upgrade
- A platform change
- Changes to database parameters
- Gather statistics whenever a large of volume of data is inserted into or deleted from the tables, or when the maximum or minimum values change.
- Use either the FND_STATS package or Gather Schema/Table statistics concurrent programs to generate statistics.
- Specify the GATHER_AUTO option to gather statistics incrementally. This option collects statistics for objects with either stale or missing statistics.
- Using AUTO_SAMPLE_SIZE instead of a specific percentage is highly recommended when gathering statistics. This automatically determines the appropriate sampling percentage.
- Consider gathering and locking statistics for volatile tables (such as intermediate or interface tables) when representative data is populated into the tables. Examples include tables used in batch processes, such as AP_SELECTED_INVOICES and WSH_PR_WORKERS.
- To generate histograms for non-seeded columns, populate the FND_STATS.LOAD_HISTOGRAM_COLS table with columns so that it generates the histograms required.
- Note that if the Gather Schema Statistics concurrent program is stopped or terminated, when it is resubmitted it will restart from where it left off.
- While submitting the concurrent request to Gather Schema Statistics, ensure the 'Invalidate Dependent Cursors” parameter' is set to 'No' to avoid invalidating cursors. This ensures that performance is maintained for the current session, and prevents excessive library cache waits due to hard parsing.
- Seed the table FND_EXCLUDE_TABLE_STATS using the API fnd_stats.load_xclud_tab to exclude the table from statistics being generated when the Gather Schema Statistics program runs.
| - Do not gather statistics during peak business hours, as such operations can be a resource-intensive.
- Do not gather statistics excessively: for example, on either entire schemas or the entire database regularly, such as nightly or weekly.
- Do not gather statistics on Global Temporary Tables.
- Do not use the standard Oracle database ANALYZE or DBMS_STATS commands to gather statistics, as they may result in suboptimal plans and are not supported for Oracle E-Business Suite.
|
Section 4. Applications Best Practices
The following guidance for the application tier is divided into subsections for general points, types of application, and individual components and technologies.
4.1. General
The guidance here applies across the application tier.- Use the EBS Technology Codelevel Checker (ETCC) to ensure all that all required application tier patches have been applied. Refer to My Oracle Support Knowledge Document 1594274.1 Consolidated List of patches and Technology Bug Fixes for more information about ETCC and the appropriate set of recommended patches to install.
- Refer to My Oracle Support Knowledge Document 1617461.1 Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2 for the latest AD and TXK patches to apply.
- Refer to My Oracle Support Knowledge Document 244040.1 Recommended Performance patches Oracle E-Business Suite for the latest performance patches to apply.
- Excessive logging, and enabling debugging, will both have a considerable impact on application performance. The best practice is to set only the minimum logging and debugging levels, and disable them when they are no longer needed. Ensure that the following profiles are set:
- FND: Debug Log Enabled - set to ‘Yes’ (Releases 12.0 RUP3+, 12.1.x, 12.2)
- FND: Debug Log Level - Set to 'Unexpected' (Level=6)
In addition, you can query the activity on FND_LOG_MESSAGES to establish whether logging has been set (or perhaps inadvertently left on). if the query returns a significant number of rows, there could be exceptions or errors that need to be investigated.
- Set the Debug Profile options at User level unless otherwise specifically required. Do not forget to reset the debug profiles once a debugging exercise is completed.
- Use Sign-On Audit levels judiciously in order to avoid performance issues. Refer to Oracle E-Business Suite Security Guide for a description of auditing and logging features.
- Establish a strategy to compress large files or heap dumps before transferring them, or when they are not in use. This will reduce both transfer time and disk space needed.
4.2. HTML-Based Applications
These applications are based on OA Framework, and were originally known as Self-Service applications.
- For sizing guidelines on the number of JVM processes and their JVM capacity requirements, refer to the section "JVM Parameter Settings for Java on WLS Web Tier" in Oracle E-Business Suite Upgrade Guide.
- Configure 1 JVM per 1-2 CPUs (depending on CPU speed).
- For the OACORE JVM, starts with Xms 2048 MB and Xmx 2048 MB. Set the same values for Xms and Xmx to avoid heap memory reallocation during runtime.
- For Oracle E-Business Suite Release 12.2 in a multi-tier application server topology, consider configuring WebLogic Administration Server on a smaller shape as there is no requirement to set a large heap size here.
- Set the maximum heap sizes for the JVM processes according to maximum operating system physical memory available in the shape of your cloud instance. Configuring large heap sizes for managed servers may result in swapping in the OS, which can significantly degrade application performance.
- Configure more managed servers to scale up to concurrency levels. Consider the number of CPUs available in the cloud instance shape selected when configuring additional managed servers.
- Size the JVMs optimally to match with concurrency levels required. Total memory allocated to the JVMs is the most important factor affecting the garbage collection (GC) performance. Monitor the frequency of collections, especially major collections (full GCs). Enable verbose GC in the JVM configuration parameters, to facilitate tuning heap sizes based on GC traffic.
- On 64-bit instances, we do not recommend allocating huge heap sizes, but rather having additional managed instances in the cluster to scale up to the target concurrency levels.
- In Oracle E-Business Suite Release 12.2, the default 512 MB size is not enough for Admin Server. We recommend setting the JVM parameters XMS to 1 GB and XMX to 2 GB.
- For Oracle E-Business Suite Release 12.2, consider additional sizing requirements for online patching. For initial sizing guidance, refer to Database and Application Tier Sizing Guidelines in Oracle E-Business Suite Installation Guide: Using Rapid Install.
- For more details on configuring the application tier, refer to My Oracle Support Knowledge Document 1905593.1 Managing Configuration of Oracle HTTP Server and Web Application Services in Oracle E-Business Suite Release 12.2.
4.3. Forms-Based Applications
These traditional applications are based on Oracle Forms.
- Check applicable sizing guidelines on the number of Forms Server processes and their JVM capacity requirements by referring to the section "Planning Oracle E-Business Suite 12.2 Deployment Architecture" in Oracle E-Business Suite Upgrade Guide.
- Train application users to optimally utilize Oracle Forms so that they avoid blind queries, or queries with unselective criteria. Encourage users to provide selective criteria in Find Windows and LOVs to enable index usage, as this will return their result set more quickly and reduce usage of system resources.
- Ask users to avoid opening and closing forms across transactions. Combine forms from multiple products into a single form to reduce network traffic and form opening times.
- Reduce the system load and network overhead to help maintain proper performance with high concurrency levels and high latency connections.
- Generate SQL trace and Forms Runtime Diagnostic (FRD) report to debug Oracle Forms performance issues. For more information on how to set up FRD logging, refer to My Oracle Support Knowledge Document 445166.1, R12: How To Create An FRD (Forms Runtime Diagnostic) Log Using Forms 10g.
4.4. Oracle Workflow
Oracle Workflow is used across a number of Oracle E-Business Suite application modules, and its role means performance tuning can be particularly important.- Once a flow is complete, much of the associated data becomes obsolete, not only taking space needlessly but also potentially affecting application runtime performance. You can purge obsolete data using the 'Purge Obsolete Workflow Runtime Data (FNDWFPR)' concurrent program. Optionally, you can use the 'Item Type' parameter to specify a particular item type to purge, or otherwise leave this parameter blank to purge runtime data for all item types. Failure to remove obsolete data may result in extended processing times. Refer to Purge Obsolete Workflow Runtime Data, Oracle Workflow Administrator's Guide.
- Workflow Background Process is a concurrent program that is run for processing deferred and timeout activities, and stuck processes. Set the Oracle Workflow background engine ‘Process Stuck’ parameter to 'No'. Ensure there is at least one background engine that can handle deferred and timeout and activities, as well as stuck processes. Where possible, use deferred activities to speed up online response times for flows such as scheduling PO Document Approval. Refer to Setting Up Background Workflow Engines, Oracle Workflow Administrator's Guide.
- Use database partitioning to partition runtime Oracle Workflow tables such as WF_ITEM_TYPES and WF_ITEM_ATTRIBUTES for improved performance and scalability. Refer to Partitioning Tables in Oracle Workflow Administrator's Guide.
- On Oracle RAC instances, employ node affinity for improved performance and scalability. Use the ITEM_TYPE column for node affinity. Refer to Setting Up Workflow RAC Affinity, Oracle Workflow Administrator's Guide.
- Disable retention (number of days for which processed messages are retained in an Oracle Workflow queue) using the command:
SQL> DBMS_AQADM.ALTER_QUEUE (queuename=>:b1,retention_time=>0);
Do not use the DBMS_AQADM PL/SQL procedure to change any other aspects of Workflow queue setup.
4.5. Concurrent Processing
This section covers key aspects of concurrent processing where performance tuning can be critical.
4.5.1 Concurrent Managers
Concurrent managers are responsible for running requests for running concurrent programs. When an application user submits a request to run a concurrent program, the request is entered into a database table that stores all concurrent requests. Concurrent managers read requests from this table and start concurrent programs to process them. Requests can be scheduled to run on a recurring basis. Check whether such automatic recurrence is appropriate, and if performance is being impacted by the scheduling. Remove any requests that do not need to be run repeatedly.
Tune the concurrent manager parameters such as number of workers, sleep time and cache size depending on the concurrent program running time. Table 2 provides recommendations for the optimal setting of key concurrent manager parameters.
Table 2: Recommendations for Key Concurrent Manager Parameters
| Concurrent Manager | Average Request Duration | Number of Workers | Sleep Time Footnote 1 | Cache Size Footnote 2 |
|---|
| Fast | 10 seconds | Many | 15-20 seconds | Variable |
| Medium | 5 minutes | A few | 60 seconds | 2 x workers |
| Slow | 30 minutes | A few | 60 seconds | 2 x workers |
| Long | > 2 hours | Very few | 60 seconds | 1 x workers |
| Critical | Variable | Variable | Variable | Variable |
Footnote 1 - A manager does not sleep unless there is nothing for it to do, i.e. there are no pending requests available to run. As long as requests are available, the manager will not sleep. Each time a manager fails to lock a request in its cache. it will increment an environment variable, AFSPINMAX. Each time through the main loop it will check this variable, and if the value is more than the threshold defined by the variable the manager will go to sleep. This is to prevent 'spinning', where there are pending requests available but the manager is unable to lock any of them. Such a situation would cause the manager to run its SQL continuously, without any break, and cause high CPU usage. The default value of AFSPINMAX is (Cache Size x 20), but this can be set to a different number if required.
Footnote 2 - The most important factor in choosing a cache size is how long requests will be running. There is little point in caching a lot of requests if each one runs for more than two hours.
The figures provided in Table 2 can be translated into the following practical guidance for concurrent manager setup:
- For a manager that completes program runs in an average of 10 seconds, configure as many workers as possible (depending on CPU availability) and specify a sleep time of 15-20 seconds. Set the cache size as needed.
- For a manager that completes program runs in an average of 5 minutes, configure a modest number of workers and specify a sleep time of 60 sec. Set the cache size as 2 x number of workers.
- For a manager that completes program runs in an average of 30 minutes, do not configure many workers and set sleep time to 60 seconds. Set the cache size as 2 x number of workers.
- For a manager that completes program runs in an average of more than two hours, configure very few workers and set sleep time to 60 seconds. Set the cache size as 1 x number of workers.
- For a manager that runs critical programs with potentially very long duration, it is not feasible to recommend specific values for parameters, and you will need to review each case individually.
4.5.2. Data Purging
- Use the Purge Concurrent Request and Manager Data program (FNDCPPUR) to purge request log files, concurrent manager log files, tables, and report output files. Schedule the purge program as appropriate.
- Double-check the concurrent manager job activities and load on the system. Every concurrent program has a parameter, 'SHELF_LIFE', which can be used to specify how long the logs and output from the program should be kept. Note that when SHELF_LIFE is defined for a program, that parameter comes into play only when the Purge program is run: SHELF_LIFE on its own does not cause log or out files be purged. In addition, the purge program will not purge log and output for requests if the assocated program's SHELF_LIFE has not expired, regardless of the age or count parameters defined for the purge request.
- Optimize log directory access time by reducing the inode file entries. Oracle E-Business Suite Release 12.2 introduced a new environment variable, APPLLDM, to specify whether log and out files are stored in a single directory for all Oracle E-Business Suite products, or in one subdirectory per product. Refer to My Oracle Support Knowledge Document 1616827.1 Managing Concurrent Managers Log and Out Directories.
- The
cpadmin command line tool can be used to set the APPLLDM environment variable, and in addition can be used to move existing log and out files to other directories without purging. Refer to Concurrent Processing Command-Line Utility (cpadmin) in Oracle E-Business Suite Setup Guide.
- Apply Patch 21786827 to define another environment variable, APPLWDIR. You can set the APPLWDIR variable to an empty local directory that the concurrent managers will use as their current working directory. This can be especially useful if NFS-mounted storage devices are being used.
- Ask users to provide selective parameters when submitting concurrent jobs, to avoid the concurrent jobs having a needlessly long run time.
- If you need to cater for critical short duration requests, create a modest number of specialized concurrent managers dedicated to processing them. This will help ensure that the critical short duration requests are not waiting in the queue for an excessive time. However, avoid creating more specialized managers than really needed, as doing so can reduce overall concurrent processing performance because of polling on queue tables.
4.5.3. Job Scheduling
- Taking into account the business criticality and running time of your concurrent programs, schedule them to a particular window so the performance of other processes will not be impacted. It is particularly advisable to avoid scheduling resource intensive requests during peak time OLTP windows.
- Configure concurrent managers to schedule requests out of peak time. To ensure that a concurrent program will not run during peak time, you need to define both the manager's work shift and specialization rules. This will ensure that even if a concurrent program is submitted during peak time, the concurrent manager will run the request outside this period.
- Define a Workload Management Strategy based on average job duration and system usage profile.
- Truncate the
reports.log file in your log directory. Refer to My Oracle Support Knowledge Document 842850.1, R12 E-Business Suite Concurrent Processing Users Report The Following Failure Message 'Error: emsg:was terminated by signal 25' For All Concurrent Programs Which Access $APPLCSF / $APPLLOG (Doc ID 842850.1).
- Set the profile option 'Concurrent: Active Request Limit' to restrict the number of concurrent requests that can be run simultaneously by each user.
4.5.4. Conflict Resolution Manager
- The Conflict Resolution Manager (CRM) checks the incompatibility rules effective for the pending concurrent request against other running requests. The CRM allows the request to run only when all incompatible requests have completed.
- To maximize throughput for jobs which spawn parallel workers (such as jobs for Auto Invoice and Payroll), consider reducing the sleep time of the Conflict Resolution Manager from the default of 60 seconds to 5 or 10 seconds.
- Patch 23021248 is important for CRM performance.
4.5.5. Transaction Managers
- Transaction managers exist to process day to day transactions. Each transaction manager has its own set of tasks and responsibilities to process some set of records. While conventional concurrent managers let you run long-running, data-intensive application programs asynchronously, transaction managers support synchronous processing of particular requests from client machines. A request from a client program to run a server-side program synchronously causes a transaction manager to run it immediately, and then to return a status to the client program. Transaction managers are implemented as immediate concurrent programs. At runtime, concurrent processing starts a number of these managers. Rather than polling the concurrent requests table to determine what to do, a transaction manager waits to be signaled by a client program. The running of the requested transaction program takes place on the server, transparent to the client and with minimal time delay. At the end of the program run, the client program is notified of the outcome by a completion message and a set of return values.
- Communication with a transaction manager is automatic. The transaction manager mechanism does not establish an ongoing connection between the client and the transaction manager processes. The mechanism is designed to enable a small pool of server processes to service a large number of clients with real-time response.
- Set the profile 'Concurrent:Wait for Available TM' to 1 second to minimize TM latency. This profile sets the total time to wait for a transaction manager before switching over to the next available transaction manager.
- If (and only if) you are using dbms_pipes, set the sleep time on transaction managers to 30 minutes. This avoids over-frequent polling to check for possible shutdown requests. Note that if you set sleep time this high you may have to wait up to 30 minutes to do a shutdown or restart of the managers.
4.6. Oracle RAC Node Affinity
- Distribute the workload to specific Oracle RAC nodes where possible. This will maximize scalability by minimizing the need for internode communication and cache synchronization.
- For concurrent requests, run a concurrent program against a specific Oracle RAC instance with PCP/RAC set up. To define node affinity at the program level, refer to My Oracle Support Knowledge Document 1129203.1, How To Run a Concurrent Program Against a Specific RAC Instance with PCP/RAC Setup. This note also describes how you can make use of instance affinity.
- For HTML-based applications, enable node affinity either by setting the profile option 'App%Agent' to application tier hosts configured for specific services, or setting the profile option "Application Database ID" to a node-specific DBC file name. For more information, refer to My Oracle Support Knowledge Document 1375686.1 Using Load Balancers with Oracle E-Business Suite Release 12.2 and Document 380489.1 Using Load-Balancers with Oracle E-Business Suite Release 12.0 and 12.1.
- For Forms-based applications, you can enable node affinity by setting the profile option 'Database Instance' at the application or responsibility level, which can be tied to the value of the $TWO_TASK environment variable or to a database service.
Section 5: Sizing Oracle E-Business Suite for Mobile Applications
Oracle E-Business Suite has many mobile applications from different product groups. The mobile applications share the same system resources such as CPU and Memory along with Oracle E-Business Suite. The user load is co-located with other self-service and forms application users. The same managed instance sizing guidance applies to the mobile user community. Hence, for every additional 150-180 concurrent mobile users, it is recommended configure additional managed instance to scale for the additional workload based on the standard managed instance sizing guidelines. Size the database parameters such as PROCESSES, SESSIONS, SGA and PGA to match the additional load requirements.
Section 6: Online Patching Best Practices
- Consider the sizing and resource requirements needed to support the online patching capability introduced in Oracle E-Business Suite Release 12.2. For initial sizing guidance, refer to Database and Application Tier Sizing Guidelines in Oracle E-Business Suite Installation Guide: Using Rapid Install. Some specific examples are given in the other points of this section.
- Ensure the number of online patching processes is set correctly, based on system activity and number of available CPU cores.
- Before you run the
adop online patching utility, ensure there is the same number of managed servers, with the same configuration, for both the run and patch file systems. This is so AutoConfig can successfully complete in both file systems before the adop prepare phase is run. If it cannot complete, the prepare phase will fail.
- Ensure there is adequate memory to load all the configured managed servers (OA and forms) for both the run and patch file systems, as
adop will start them.
- The database will accumulate old database edition objects as each online patching cycle is completed, and system performance may be reduced when too much space is used. As a best practice, consider dropping all old database editions once the number of editions reaches 20. You can drop old editions by running the
actualize_all online patching phase, and then performing a full cleanup. For more information on online patching operations and management, refer to Oracle E-Business Suite Maintenance Guide.
Section 7. Sizing Your Oracle E-Business Suite Environment
Sizing an Oracle E-Business Suite environment is a multi-dimensional activity that largely involves database and application server sizing. Specifically, sizing for better performance and scalability involves a process of refinement, where you need to define the key performance indicators and perform representative stress testing. By reviewing key performance indicators (KPIs) and verifying the performance metrics carefully, you can choose the correct size for your production system.
When analyzing performance data, focus on the following metrics to determine whether actual usage is lower than instance capacity.
7.1 Considerations for User Load Analysis
Answering the following questions will help you both size your EBS environment correctly and enable you to select the optimum deployment topology.
- Is your hardware adequately sized to handle the peaks in usage?
- When are the periods of peak usage? (For example, period close and batch data load.)
- What is the pattern of load spikes on the system? And is it due to transactional or batch load?
- Does your system have surges in demand at month end or year end?
- Is your system overloaded at certain times of the day? If so, can some of the load be moved to quieter periods in the day and thereby maximize your current resources?
- What is the number of named concurrent users in the source system, and what are their usage profiles?
- What are the response time requirements from the system?
7.2 Sizing Newly Deployed Instance
- For sizing a newly provisioned instance, refer to Database and Application Tier Sizing Guidelines in Oracle E-Business Suite Installation Guide: Using Rapid Install to define the initial state of an instance according to your workload requirements. Once it is provisioned, set the initialization parameters based on the CPU and memory available on the server.
- You then need to perform a pre-production load test on the provisioned instance, measure the performance and deduce values for various database initialization parameters and application server JVM parameters. The key metrics to look for are CPU usage and memory usage. Once you have chosen the right values, you can if desired move the test instance to be a production instance.
The following steps describe the process for various deployment scenarios. You should review them all carefully when migrating your EBS environment to OCI.
7.3 Sizing An Existing Instance
- If you are migrating to OCI from on-premises, your existing environment can provide a wealth of information that can be used to configure the target instance in OCI correctly, while preserving the original performance levels on the migrated environment.
- This information includes, but is not limited to, the current workload characteristics (peak/average active users on the system); the resource utilization levels for CPU, Memory and IO resources; and the acceptable performance figures for crucial business processes.
7.4 When There is No Change in Workload Between Source and Target Environments
- Based on the performance data from your source environment to choose the OCI shape that most closely matches the hardware resource profile of the source environment.
- Set the database initialization parameter values on the target instance to match the values on the source instance. Ensure that parameter settings are in accordance with My Oracle Support Knowledge Document 396009.1 Database Initialization Parameter Settings for Oracle E-Business Suite.
- Define benchmarks that match performance tests run on your on-premises instance, with the goal of achieving similar performance metrics in OCI.
- Carry out pre-production sizing and performance benchmark tests, keeping in mind the expected number of concurrent users and workload on the target environment.
- Review the results to ensure the performance figures are acceptable, and also to make necessary adjustments to sizing instance-related initialization parameter values.
- Once the environment is configured to provide optimal performance, use the validated values to size the production environment.
7.5 Where There is an Increase in the Projected Workload on Target Environment
- Based on the current resource utilization data and workload characteristics on the source environment, extrapolate your hardware requirements and choose an OCI shape that can accommodate the expected increase in workload without impacting the performance.
- Use the scaling factor method based on the projected workload to estimate the target environment sizing, and validate it by pre-production testing.
- Carry out pre-production sizing and performance benchmark tests, keeping in mind the expected number of concurrent users and workload on the target environment.
- Review the results to ensure the performance figures are acceptable, and also to make any necessary adjustments to sizing instance-related initialization parameter values.
- Once the environment is configured to provide optimal performance, use the validated values to size the production environment.
Section 8. Benchmarking Best Practices
8.1 Introduction
The purpose of this section is to provide a set of best practices for Oracle E-Business Suite pre-production validation benchmarks.
Benchmarks are crucial for validating system dynamics in the context of system state, saturation of domain capacity and overall system performance signature, as a function of projected workload, transaction payloads and frequency.
The benchmark process flow comprises the following steps:
- Transaction Workload Definition and Data Distribution.
- Environment Setup and Deployment Validation.
- Workload Simulation and Performance Data Collection.
- Results Analysis and Exit Criteria Validation.
For all the aforementioned process steps, a valid EBS benchmark should have the following characteristics:
- The benchmark should be representative of the target production system including, but not limited to:
- Deployment profile and sizing characteristics.
- Data distribution and high watermark levels.
- Projected workload characteristics, which include: online and batch transaction payloads, batch and concurrent manager run frequencies, online users concurrency levels, and online and batch co-location dynamics.
- The benchmark should be re-entrant, with the benchmark environment always having a consistent re-entrant state at the start of each run cycle. The re-entrant state should be maintained on multiple levels, including data distribution and high watermark levels, transaction bindings and run variables, and workload state and concurrency levels.
- The benchmark should be well-formed in the context of transaction error rate and functional consistency for each benchmark testing cycle.
Proper checks and balances should be in place through the life cycle of the benchmark to ensure the above properties are maintained.
8.2 Transaction Workload Definition and Data Distribution
Transaction workload definition and data distribution is the first and most important process step in the benchmark life cycle. Proper gap analysis is critical while defining EBS benchmark workloads, not only to cover important and active transactions in the system but also to define transaction concurrency levels in the target benchmark workload.
The following are some key guidelines and recommendations for EBS transaction workloads:
- Identify a representative selection of transactions including Self-Service, Forms, and Concurrent Manager workloads. Particular attention should be paid to custom code and transaction extensions impact.
- For Self-Service and Forms flows, define projected workloads as a function of transaction types and concurrency levels.
- For Self-Service and Forms flows, make sure that both query and posting flows/navigation are included, to simulate an active state of the system.
- For Self-Service and Forms flows, define user pools, user concurrency levels, iteration cycles, iteration sleep times and user navigation sleep times. It is crucial to properly set the sleep times at proper levels, to avoid synthetic connection storms and system overload.
- For Concurrent Manager flows, define projected workloads as a function of concurrent manager request types and run frequency.
- Define co-location test cycles for projected workloads. Ideally three benchmark cycles should be planned: one dedicated cycle for Self-Service and Forms workload, another cycle for Concurrent Manager workload, and a combined workload cycle that includes both the Self-Service and Concurrent Manager flows.
- Ideally benchmark times should reflect a warm run state, so a warmup cycle is recommended: the goal is to bring the system to a warm state after restore, before running formal cycles and collecting performance data.
Following are some guidelines and recommendations covering EBS data distribution:
- Define the initial data state of the system as a function of tables row counts, tables high watermarks and correct fixed, dictionary and application statistics.
- Define a backup/restore cycle to the initial data state to be used before each benchmark iteration.
8.3 Environment Setup and Deployment Validation
Pre-production environments should be identical in capacity and system characteristics to the target production environment. It is also important to have the pre-production benchmark system state and production system state synchronized in terms of patching at the operating system, database, and application tier levels.
The primary goal of any pre-production benchmark is to define the target production system sizing as a function of workload and system dynamics, In order to achieve that goal, multiple benchmark cycles are needed to properly size the environment. These should be based on the combined projected workload, system saturation and contention levels.
During the benchmark cycles for proper sizing of the target system, 70-80% of the system resources (CPU, memory and IO) should be the maximum threshold of utilization. For guidance on the initial sizing phase for the pre-production environment, refer to Database and Application Tier Sizing Guidelines in Oracle E-Business Suite Installation Guide: Using Rapid Install.
8.4 Workload Simulation and Performance Data Collection
Simulation runs should cover all workload buckets, including for Self-Service, Forms, and Concurrent Processing, plus co-located or combined workloads with multiple iterations for each of the target buckets.For each iteration, a predefined set of performance Key Performance Indicators (KPIs) should be collected, baselined, and compared in terms of factors including response times, and system vitals covering both saturation and contention states.
Care should be taken to monitor the load generator system state as well, as load generator saturation will disrupt the benchmark system dynamics and will deviate all collected performance KPIs.
To validate the load generator state along with the test scripts, a set of dry runs is required in order to collect all the performance KPIs and transaction success rates.
Proper monitoring of system vitals during EBS benchmark cycles is key to the success of the benchmark project. Monitoring should be done at the database operating system level, application tier operating system level, database tier level, and application tier level.
The following are some key monitoring pointers and associated My Oracle Support knowledge document references:
- Overall System Monitoring: Enterprise Manager
- System Monitoring:
- OS - OSWatcher (My Oracle Support Knowledge Document 301137.1)
- Network Test Utilities Best Practices (My Oracle Support Knowledge Document 556738.1)
- Database Monitoring Diagnostics
- AWR Report (My Oracle Support Knowledge Document 748642.1)
- ADDM report (My Oracle Support Knowledge Document 250655.1)
- Active Session History (ASH)
- Application Monitoring and Diagnostics
- Concurrent Manager timings
- Load Generator Timings
- Forms Tracing (My Oracle Support Knowledge Document 373548.1)
- FRD Log (My Oracle Support Knowledge Document 445166.1)
- Managed instances or JVM Logs
8.5 Results Analysis and Exit Criteria Validation
The exit criteria of Oracle E-Business Suite pre-production benchmarks are defined on the following:- Self-Service and forms acceptable 90% percentile response times.
- Concurrent Manager requests throughput and elapsed times.
- EBS system dynamics as a function of saturation and contention states.
- Final EBS system sizing as a function of co-located workload and projected concurrency levels.
- Patching levels for all EBS artifacts in the deployment topology.
- Final initialization parameters as verified by the pre-production benchmark.
Based on the above exit criteria, pre-production settings are graduated to the production environment with a sizing contingency scaling factor of 10-20%. The purpose of the sizing scaling factor is to compensate for any deviation between the synthetic benchmark workload and the real-life production workload.
Section 9: Oracle E-Business Suite Upgrade Best Practices
To help ensure optimum performance during Oracle E-Business Suite upgrades, refer to My Oracle Support Knowledge Document 1581549.1, Best Practices for Minimizing Oracle E-Business Suite Release 12.2.n Upgrade Downtime. In addition to recommendations for reducing downtime, this document provides information on releases, the technology stack, upgrade paths, upgrade resources, and customer upgrade cases.
Section 10: Other Best Practices
The miscellaneous points mentioned in this section do not fall into any of the categories discussed so far.
- Monitor the database server for any jobs consuming significant resources. Validate the need for such jobs, and eliminate any unnecessary ones to help reduce the server workload.
- Wherever possible, use bind variables rather than literal values in custom code and ad hoc SQL queries. This will reduce shared pool fragmentation that might otherwise eventually result in an ORA-04031 error. To identify SQL statements that use literals, review the database AWR report, 'SQL ordered by executions' section.
- Use standard archive and purge programs and functionality provided with Oracle E-Business Suite:
- Partitioning tables.
- Data compression using advanced compression.
- Information Life Cycle Management (ILM).
- Partitioning selected tables can bring significant benefits in performance and manageability. If a table is partitioned incorrectly, however, it can adversely affect performance. Refer to My Oracle Support Knowledge Document 554539.1 Database Partitioning for Oracle E-Business Suite for details of how partitioning can be used effectively with EBS.
- Refer to My Oracle Support Knowledge Document 752322.1 Reducing the Oracle E-Business Suite Data Footprint for details on different methods for purging and archiving data.
- Oracle Advanced Compression (ACO) will save space when data is written to the database, and also has the benefit of reducing the associated disk I/O for read operations. Refer to My Oracle Support Knowledge Document 2458161.1 Oracle E-Business Suite Release 12.2 with Oracle Database 12c Release 1 Advanced Compression for details on the performance impact of advanced compression.
- Managing the high water mark (HWM) and reclaiming space of Oracle E-Business Suite tables is highly recommended. Purge unwanted data from the tables frequently, to avoid full table scans and their performance issues. This will also improve transaction response times, as measured in transactions per second. Refer to My Oracle Support Knowledge Document 284692.1 How to Reduce the High water Mark for Advanced Queueing Objects Using Truncate in Pre-10g Databases.
Section 11: References
11.1 My Oracle Support Knowledge Documents
11.2 Other Documents
