Tuesday, July 13, 2021

Best Practices for Minimizing Oracle E-Business Suite Release 12.2.n Upgrade Downtime (Doc ID 1581549.1)

 This document also provides links to two presentations on the subject:

  • Oracle OpenWorld presentation "Technical Upgrade Best Practices Oracle E-Business Suite R12.2" which was designed to offer an overview of the key recommendations to reduce downtime. It also includes information on releases, the technology stack, upgrade paths, upgrade resources, and customer upgrade cases.

  • "Best Practices for Minimizing Oracle E-Business Suite Release 12.2 Upgrade Downtime", which summarizes the content of this My Oracle Support document.

The My Oracle Support Document "Oracle EBS R12.2 Upgrade - Outstanding Performance Bugs, Issues and Solutions (Document 2357810.1)" is a companion to this document. It lists recently identified performance bugs/issues and their solutions.

In This Document

Executive Overview

This document offers a strategy for upgrading Oracle E-Business Suite to Release 12.2.n that minimizes downtime, whilst minimizing the implementation and resource required.

In particular it will help to:

  • Minimize downtime during the final iteration.

  • Minimize number of test iterations used to resolve performance issues.

  • Identify issues early. Ideally on the first test iteration.

  • Simplify the final iterations, so that they require the minimum of resource

1. Introduction

 

1.1 Using this document

The summary section contains the high level recommendations and should be reviewed fully.

The detailed "Preparation and Planning" and "Resolving Performance Issues" sections contain detailed explanations for the recommendations or detailed instructions. They may also contain important notes to be aware of. This section will typically be used for reference.

There are two parts to the suggested approach:

  • Preparation and Planning

  • Resolving Performance Issues

Both are these are discussed below.

 

1.2 Preparation and Planning

This includes:

  • Recommended configuration or setup.

  • Tasks that should be run as part of the Release 12.2.n upgrade process.

  • Diagnostics that should be captured for all runs.

  • Advice on monitoring general performance.

  • Advice on determining upgrade parameters (AD Parallel workers, batch size, max_parallel_servers) for each implementation.

This will typically apply to all implementations.

 

1.3 Resolving Performance Issues

This will include advice on how to resolve specific performance issues that are encountered on each implementation.

Although there are some specific areas and jobs which sometimes have performance issues, there are often new undiscovered / unreported performance issues. It is not possible to predict where performance issues will occur for a given customer.

2. Overview of the R12.2.n Upgrade Process

In an upgrade to R12.2.n the main patches are applied in the following sequence:

  • Main Upgrade Driver for R12.2.0 (merged with pre-installed Oracle E-Business Suite Consolidated Upgrade Patch for R12.2.0)

  • Online Patching Enablement

  • R12.AD.C.Delta.n and R12.TXK.C.Delta.n

  • 12.2.n Release Update Pack (RUP)

Autopatch (adpatch) is used to apply all the patches up to and including "Online Patching Enablement".

Online Patching (ADOP) and the underlying Edition-Based Redefinition are only used after "Online Patching Enablement" for the "R12.AD.C.Delta.n", "R12.TXK.C.Delta.n", "12.2.n RUP" and subsequent patches.

Note that although the Online Patching method is used for the latter stages in the upgrade to R12.2.n, these are run during downtime (the application is not running).

Since R12.AD.C.Delta.5 the 12.2.n RUP patches can be applied using "downtime" mode.

So the performance advice and challenges for an upgrade to R12.2.0 are largely the same as for an upgrade to R12.1.n. The same method is used to apply many of the patches (AutoPatch).

However, the Online Patching Enablement patch, Online Patching and the Release Update Packs for 12.2.n are quite different to the main R12.2.0 upgrade, so they may introduce new performance challenges, particularly with internal SQL and ADOP SQL (i.e. SQL run from or on AD_ZD objects).

It is important to stress that although Online Patching (ADOP) provides new logs to track performance, the key diagnostics are still the same ones used for previous Oracle E-Business upgrades (e.g. AWR reports, AD Job Timing Reports, Display Cursor Reports, and SQLT Extracts etc.)

3. Online Patching

The duration of the cutover phase is kept to a minimum in Online Patching. However, this inevitably results in some additional overhead. In particular:

  • Multiple phases (e.g. prepare, apply, finalize, cutover, cleanup).

  • Two editions of the file system are updated.

  • Checking and validation.

  • Tables cannot be editioned in EBR (Edition-Based Redefinition). So they are populated/updated across editions. This is usually done using the AD_ZD_TABLE.apply utility to fire the same forward cross edition trigger for all rows, whereas previously (on AutoPatch) SQL set operations were used. This uses the Oracle supplied package DBMS_PARALLEL_EXECUTE, which splits the work up into chunks and then uses the Oracle Scheduler (DBMS Scheduler) to execute the chunks in parallel. This can add an additional performance overhead. For more information on how tables are upgraded using Forward Cross Edition Triggers and AD_ZD_TABLE_APPLY.sql see:

    My Oracle Support Document "Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2 (Document 1577661.1)"

    Oracle E-Business Suite Concepts, Release 12.2. Section "Editioned and Non-Editioned Objects".

  • It can also share resources with the running application during the prepare, apply, finalize and cleanup phases. Although this will not be the case during the R12.2.n upgrade.

So customers should expect online patching to take longer.

Usually the Apply, Cleanup (If FULL, not QUICK) and FS Clone phases take the longest.

The new diagnostics available for ADOP are logs. These give timestamps for particular steps in the upgrade, or list errors that have occurred. However, they do not identify the SQLs or underlying events that caused the performance issue, so it is essential to use AWR reports, AD Job Timing Reports, Display Cursor Reports and SQLT Extracts etc.

For more information see My Oracle Support document "Oracle E-Business Suite Release 12.2: Online Patching FAQ (Document 1583902.1)"

3.1 Downtime Mode

Since R12.AD.C.Delta.5 a new patching capability called downtime mode can be used.

When applying Oracle E-Business Suite patches in this mode, ADOP will first confirm that the application tier services are down, and will then proceed to apply the patch to the run edition of the Oracle E-Business Suite database and file system.

Downtime mode patching does not use an online patching cycle. The process of applying a patch in downtime mode completes more quickly than in online mode, but at the cost of increased system downtime (although this will not be the case during the R12.2.n upgrade).

Note: Release 12.2 patches are not normally tested in downtime mode. Downtime mode is only supported for production use where explicitly documented, or when directed by Oracle Support or Development (e.g. for 12.2.4 RUP to 12.2.10 RUP).

It is recommended that the 12.2.n Release Update Pack (e.g. Oracle E-Business Suite 12.2.10 Release Update Pack (Patch 30399999)) is applied using "downtime" mode. And this will help reduce the elapsed time.

Note that this is only relevant for upgrades from 11i/12.0.x/12.1.x or new installs (Path A). It does not apply to upgrades from 12.2.2, 12.2.3, 12.2.4, 12.2.5, 12.2.6, 12.2.7, 12.2.8 or 12.2.9 (Path B).

3.2 Online Patching, Custom Code and Performance

Be aware that any custom code may need updating in preparation for an upgrade to R 12.2.n, so that it is compliant with Online Patching standards.

Prior to upgrading to R12.2.n the following must be run on the pre-upgrade environment (e.g. 11i, 12.0 or 12.1). All, except ADZDMVREFRESHNEEDED.sql, should also be run before and after Online Patching Enablement :

  • Online Patching Readiness Report to identify issues in custom database objects that will be fixed automatically or need manual intervention. (ADZDPSUM.sql)

  • Manual Fix Readiness Report. (ADZDPMAN.sql)

  • Online Patching Database Compliance Checker (ADZDDBCC.sql). Fix any violations of development standards listed in this report.

  • Global Standards Compliance Checker script (gscc.pl). Address errors that are reported by this script.

  • Script to list MVs with stale log data (ADZDMVREFRESHNEEDED.sql). This lists the refresh command required (exec dbms_mview.refresh) for each MV (which requires manual refresh). The customer can then refresh out of date MVs to clear extremely large MV logs pre-upgrade. 11i.

    This has been available since R12.AD.C.delta.8 and is in "E-Business Suite: Consolidated Online Patching Enablement Readiness Report" (since Patch 27962989:R12.AD.C).

    The latest versions of this are in Patch 31026891:R12.AD.C for 12.2, Patch 31026891:R12.AD.B for 12.1, Patch 31026891:R12.AD.A for 12.0, and Patch 31026891 for 11i.

These scripts/reports are designed so that they can also be executed on an environment that has not yet been online patching enabled (e.g. 11i, 12.0, 12.1, 12.2.0).

Note that the some of the SQL statements in the "Online Patching Database Compliance Checker (ADZDDBCC.sql)" have a dependency on EBS 12.2. If the report is run prior to the 12.2 upgrade any SQL failures can be ignored.

Important: All violations that are reported by these utilities must be fixed before enabling online patching.

Note : this will not only ensure that the custom code complies with Oracle E-Business Suite online-patching coding standards but also ensure that Online Patching Enablement and other parts of the upgrade will run more quickly (particularly upgrading Materialized Views).

For more information and the latest patch details (for 11i, 12.0, 12.1, or 12.2) see My Oracle Support document:

  • Using the Online Patching Readiness Report in Oracle E-Business Suite Release 12.2 (Document 1531121.1)

Also see:

And My Oracle Support document:

  • Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2 (Document 1577661.1)

Also review any customizations to see if any can be removed because they are no longer needed or relevant. Improved functionality in R12.2 may remove the need for a particular customization.

4. Prediction of Performance Issues

It can be difficult to predict how long a Release 12.2.n upgrade will take (in terms of downtime). In particular, it is difficult to produce reliable or accurate estimates based on database size. The downtime is dependent on the modules and functionality used, and on the hardware specification.

The nature of the cost based optimizer and the mathematics behind contention (e.g. queuing theory) means that jobs that previously had no known performance issues can suddenly have performance issues for particular customers. For example:

  • Very small changes in CBO statistics, database initialization parameters and other configuration could lead to a different execution plan being used.

  • Queue (wait) times can start to increase rapidly (exponentially) once the load reaches a certain level.

The modules, data distributions, and functionality used, not only across different tables and entities, but chronologically too, can all mean that the time to run a particular upgrade job or SQL varies enormously; or a different execution plan is used. The execution plan that was efficient for one customer may not be for another customer. The parameters or configuration of the environment may cause contention when previously they didn't: in this context, small changes can have a large impact.

Similarly, a performance issue encountered on a job may not be the same as a previous issue on the same job.

On the other hand, some jobs may run long for all customers that use certain modules heavily.

5. Summary

This section contains a summary of the recommendations to minimize downtime. More details, explanations and instructions can be found in the detailed sections that follow.

 

5.1 Preparation and Planning - Pre-Upgrade Environment / Activities

This includes steps to prevent performance issues and reduce downtime.

These are all activities to be carried out on the pre-upgrade environment prior to Release 12.2.n upgrade.

 

5.1.1 List of recommended pre-upgrade activities

  • If upgrading from 11i then convert to OATM (Oracle Applications Tablespace Model). If upgrading from 12.0 or 12.1 then migrate any existing objects (not in OATM) using the Tablespace Migration Utility. More

  • Ensure that the large transaction tables populated or modified by the release 12.2 upgrade have a large extent size (1-10MB). Ensure that the tablespaces (for large transaction tables created during release 12.2.0) have large default extent sizes (1-10MB). Small extents will seriously limit the AD Parallel batch size used on (Autopatch (adpatch)). They will also increase the occurrence of high water mark waits. More

  • Convert to the new Multiple Organizations (Multi-Org) architecture (Upgrade from 11i only). More

  • Purge all old data that is no longer needed prior to the upgrade. More

  • Flush all the interfaces, such as Auto Invoice, Journal Entry Import, Order Import etc. More

  • Gather the schema statistics (with GATHER_AUTO option for all schemas) close to the start of the downtime. Use FND_STATS or Gather Statistics concurrent programs. More

  • Gather Fixed Object and Dictionary Statistics. More

  • Drop MRC Schema if it still exists (Upgrade from 11i only). More

  • Prior to upgrade (on pre-upgrade environment) run Online Patching Readiness Report (ADZDPSUM.sql) - including ADZDMVREFRESHNEEDED.sql, Manual Fix Readiness Report (ADZDPMAN.sql), Online Patching Database Compliance Checker (ADZDDBCC.sql) and Global Standards Compliance Checker script (gscc.pl). Fix any issues reported. More

 

5.2 Preparation and Planning – Release 12.2.n Upgrade Activities

Only run the Release 12.2.n upgrade with one Oracle RAC node enabled.

See the detail section for more information.

 

5.2.1 Planning

Always read the relevant upgrade guide.

or

 

Note that you should use the Upgrade Guide in conjunction with the readme document for the Oracle E-Business Suite Release 12.2.n RUP you are applying. For a list of Oracle E-Business Suite Release 12.2.n Readme documents, see "Oracle E-Business Suite Release 12.2: Suite-Wide Rollup and AD/TXK Delta Information, Document 1583092.1"

If migrating Oracle E-Business Suite to OCI or upgrading an instance already on OCI then always read My Oracle Support Document "Performance Best Practices for Oracle E-Business Suite on Oracle Cloud Infrastructure and On-Premises (Document 2528000.1)"

 

It is important to identify the latest Consolidated Upgrade Patch (CUP) for R12.2.0, Online Patching Enablement, R12.AD.C.Delta.n, R12.TXK.C.Delta.n and 12.2.n Release Update Packs (RUP) to apply. These will have the latest improvements for timing and performance of the upgrade.

Note that the CUP for 12.2.0 (which is pre-installed and merged with the main 12.2.0 upgrade driver) will contain performance fixes for the main 12.2.0 driver and R12.AD.C.Delta will contain performance fixes for ADOP (Online Patching).

At time of last update (May 2021), these were:

Note that there are a timing and performance fixes (for the upgrade) in the latest patches/packs. So upgrading to the latest version (12.2.10) of Oracle E-Business Suite is strongly recommended.

Note that:

  • For upgrades from 11i/12.0.x/12.1.x or new installs (Path A) the R12.AD.C.Delta.12 and R12.TXK.C.Delta.12 patches (along with the consolidated patches on top) are applied using adop hotpatch on the run file system.

  • The Oracle E-Business Suite Release 12.2.10 Online Help Patch is always installed using adop hotpatch on the run file system.

See the following My Oracle Support documents for information on the latest patches, documentation etc.

  • Oracle E-Business Suite Release Notes, Release 12.2 (Document 1320300.1)

  • Oracle E-Business Suite Release 12.2: Suite-Wide Rollup and AD/TXK Delta Information (Document 1583092.1)

  • Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2 (Document 1617461.1)

Note that it is very important to:

  • Performance test the upgrade on a system that is identical or comparable to the production system (same Architecture, CPU, IO, Memory and data volumes/ages). It should be a recent clone of the production system.

  • Start performance testing as early as possible. Do not start it late.

  • Allow time for multiple iterations of performance testing (at least 3, usually more). So that performance issues can be resolved, utilization maximized and parameters tuned.

  • Gather all the recommended diagnostics on each test run, even if they initially appear to be unnecessary, this is likely to minimize number of test iterations used to resolve performance issues.

  • Speed the test cycle up.

  • Reduce the gaps between actions/patches during the upgrade (dead time). This could be time taken to read instructions, decide what to do, manual tasks that can be scripted, tasks that can be performed in parallel or delays in observing the completion of actions/patches. It is important to produce clear technical instruction documents / tick lists (with timings) and ensure that progress is monitored at all times.

More

5.2.1.1 Laying Down 12.2.0 File System prior to upgrade

Note that (since StartCD 49) Rapid Install (RI) can be used to lay down 12.2.0 code and tech stack and create the Dual File System prior to the upgrade downtime.

5.2.2 Upgrade Path and Database Certification

Check :

  • Database Preparation Guidelines for an Oracle E-Business Suite Release 12.2 Upgrade (Document 1349240.1)

  • My Oracle Support Certification Tab For database versions certified with EBS and the upgrade paths.

 

5.2.3 Performance and Technology Fixes

Before upgrading to 12.2 look at My Oracle Support document "Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Document 1594274.1)" for technology patches to apply before upgrading to 12.2. This also gives information on the latest "EBS Technology Codelevel Checker (Patch 17537119)".

Ensure that any recommended performance patches are applied. Check the My Oracle Support documents

  • R12.1 and 12.2 Oracle E-Business Suite Pre-install Patches Report [Video] (Document 1448102.2)

  • Oracle E-Business Suite Recommended Performance Patches (Document 244040.1). Note: Only identify the patches that are applicable to your environment, based on the products you have installed and their current patch level.

5.2.4 Removing Unnecessary Workloads / Overheads

  • Disable any custom triggers and business events

  • Disable unnecessary DBMS scheduler (DBMS_SCHEDULER), DBMS Job (DBMS_JOB) and Autotask (DBMS_AUTO_TASK_ADMIN) activities during the upgrade. Note that the DBMS Scheduler itself should not be disabled or stopped. It is needed for some upgrade tasks. More

  • Review and disable custom VPD policies as needed

  • Disable auditing if enabled. More

  • Review and disable all debug or logging; do this at all levels (site, responsibility, user level etc.).

  • If possible run in noarchivelog mode.

  • Disable flashback DB.

  • Remove TDE (Transparent Data Encryption) from high volume tables.

  • Consider running AutoConfig in parallel on a multi-node system.

  • Split any RDBMS Upgrade, Platform Upgrade, Conversion to OATM into a separate downtime period. More

  • Use TUMS (The Upgrade Manual Script) to avoid running tasks that are not relevant to the installation (Upgrade from 11i Only). More

  • Minimize Historical Data To Be Upgraded (Upgrade by Request) (Upgrade from 11i Only). More

  • Parallelize pre and post upgrade technical activities. More

  • Define separate concurrent manager queue for post upgrade jobs. If using RAC then "Parallel Concurrent Processing (PCP)" could be used for post-upgrade concurrent jobs. More

Note: currently it is NOT recommended that customers add nodes to their 12.2 Rapid Install upgrade file system until AFTER the upgrade to the latest 12.2.x RUP is complete, so using Distributed AD and Shared APPL_TOP is not relevant.

Note that using a Staged Application System (APPL_TOP) is not an option for upgrades to Release 12.2.n. There are multiple reasons.

Note that "Defer Upload of Patch Information" cannot normally be used, as subsequent patches may rely on information from previous patches, and the Release 12.2.n upgrade is a series of patches.

See ADOP/Autopatch and phtofile option in :

More

 

5.2.5 Upgrade and Initialization Parameters

5.2.5.1 parallel_max_servers / job_queue_processes / AD Parallel workers

For less than 32 cores set:

  • parallel_max_servers = 2 x number of CPU cores

  • AD Parallel workers – start with 1.5 x number of CPU cores. Possibly increase to 2 x number of CPU cores

  • job_queue_processes = 2 x number of CPU cores

For 32 cores and above, start with:

  • parallel_max_servers = 1.5 x number of CPU cores

  • AD Parallel workers = between 1.0 and and 1.5 x number of CPU cores

  • job_queue_processes = 1.5 x number of CPU cores

Based on the performance diagnostics, these values may need to be decreased. It may also be possible to increase them. The level of contention and resource (CPU) usage (in AWR) will be the key to determining this.

Be careful if the hardware has dynamic or hyper-threading. In such cases the number of logical CPUs could be a multiple of the number of cores. If so ensure that the values above are calculated using the number of CPU cores and not the number of logical CPUs.

Only increase the number of AD Parallel workers if the level of contention on all long running AD Parallel jobs is low. Similarly, only increase parallel_max_servers if the level of contention on all parallel query/DML jobs is low. Typically, this means SQL Tuning (poor execution plans) and Contention issues should be resolved before increasing the AD Parallel workers or parallel_max_servers.

If the script AD_ZD_TABLE_APPLY.sql in the 12.2.n Release Update Pack is long running, CPU utilization is not particularly high and contention is still low, then there may be scope for increasing the value of job_queue_processes.

See here for more on parallel_max_servers / AD Parallel workers.

See here for more on job_queue_processes.

5.2.5.2 AD Parallel batch size

Set AD Parallel batch size to 10,000 on Autopatch (adpatch).

Note: Small extents (e.g. 128k = 16 blocks) will seriously limit the batch size. So batches of much less than 1000 are likely, regardless of what batch size is selected.

More

5.2.5.3 SGA / PGA

If possible SGA and PGA should be maximized. Check feedback from AWR Advisory Statistics, but be aware that:

  • The advisory statistics are reported for the last snapshot interval only.

  • Where there are many workers accessing the same objects at the same time (e.g. AD Parallel jobs), the SGA Target Advisory (and Buffer Pool Advisory) may underestimate the reduction in physical reads obtained from increasing the SGA.

Increasing PGA will improve timings if there I/O waits on temporary space ("direct path read temp" and "direct path write temp"). There may also be physical reads showing on Hash Join, Sort or Group By operations in Display Cursor reports and SQL Monitor reports.

Increasing SGA may improve timings if there are medium to high (>10%) I/O waits on database objects (e.g. db file sequential read, db file scattered read, direct path read, direct path write etc.)

Some starting rules of thumb are:

  • log buffer = 30 to 100 MB

  • shared pool = 1 to 4 GB

  • pga target = 3 to 20 GB

  • SGA/buffer cache = multi GB: be generous without causing excessive paging

However, ensure that there is sufficient physical memory to contain the SGA and PGA; and enough remaining space for Online Patching, database processes and other databases / applications.

More

Note that using HugePages on Linux can improve SGA allocation and hence performance. More

5.2.5.4 db_file_multiblock_read_count

If specified, remove db_file_multiblock_read_count. This is the recommended value for normal running of Oracle E-Business Suite.

More

5.2.5.5 optimizer_dynamic_sampling

optimizer_dynamic_sampling level should normally be 2 (which is the default if not set) or higher.

A value of 4 is recommended during the Release 12.2.n Upgrade, but revert to 2 (or remove) after the upgrade.

More

5.2.5.6 optimizer_adaptive_features

This initialization parameter was introduced in Oracle Database 12c Release 1.

A value of FALSE is recommended during the Release 12.2.n Upgrade. The default value is TRUE.

More

5.2.5.7 parallel_degree_policy

This parameter was introduced in Oracle Database 11g Release 2. The value "ADAPTIVE" was added in Oracle Database 12c Release 1.

This initialization parameter should be removed or set to the default value MANUAL during the Release 12.2.n Upgrade.

More

5.2.5.8 db_writer_processes

This parameter should not be set. It will default to (CPU_COUNT / 8), which should be more than adequate.

If it is set to a value lower than this then the performance of AD_ZD_TABLE_APPLY.sql in the acet phase (12.2.n RUP) could be affected.

5.2.5.9 Other initialization parameters

For other initialization parameters, refer to My Oracle Support document, "Database Initialization Parameters for Oracle E-Business Suite Release 12 (Document 396009.1)".

Note that the values of the initialization parameters above (except db_file_multiblock_read_count) may be different from the values used for normal running. So be sure to revert after the Release 12.2.n upgrade has completed.

More

5.2.6 Dynamic/Hyper Threading

Be aware of Dynamic or Hyper Threading. Configuring dynamic/hyper-threading for maximum throughput per CPU cycle is recommended. Since the T4 processor, SPARC servers use a critical threading mode that does this automatically.

In addition, disable any power management. Power management will either prevent threading for maximum throughput, or throttle the CPU capacity.

More

5.2.7 Resource Manager

If using a Resource Plan to specify how resources are distributed among the different resource consumer groups, please review this to ensure that all sessions running during the upgrade downtime window have access to the full CPU resources.

Note that this does not apply to Post Upgrade Concurrent jobs, which will run alongside the normal online workload.

More

5.2.8 12.2 Middle Tier Sizing Guidelines

Managed instances JVM sizing should consider both memory and CPU domains.

On 64bit environments, allocating huge heap sizes is not recommended, but rather have more managed instances in the cluster to scale up to the target concurrency levels.

For Admin Server sizing, the default size of 512M is not enough for most installations, setting the XMS to at least 1 GB and the XMX to 2GB is recommended.

Allow an extra 3 GB of free memory for Online Patching.

An initial guidance on sizing can be found in

And in My Oracle Support Documents:

  • Managing Configuration of Oracle HTTP Server and Web Application Services in Oracle E-Business Suite Release 12.2 (Document 1905593.1)

  • Achieving Optimal Performance with Oracle E-Business Suite (Doc ID 2125596.1) – sections "Size The Middle Tier For Concurrency", "Size The Middle Tier For Concurrency (JVM Heaps)" and "Size The Middle Tier For Concurrency (Forms)"

More

Note: Upgrading to Oracle E-Business Suite Release 12.2 is performed with a single application tier node.

If possible, make sure that the one application tier node used during the upgrade process is configured with a larger RAM, more CPU and faster I/O (e.g. local SSD storage). More

5.2.9 Using "downtime mode" for Online Patching

It is recommended that the 12.2.n Release Update Pack (e.g. Oracle E-Business Suite 12.2.10 Release Update Pack (Patch 30399999)) is applied using "downtime" mode. And this will help reduce the elapsed time.

Note that this is only relevant for upgrades from 11i/12.0.x/12.1.x or new installs (Path A). It does not apply to upgrades from 12.2.2, 12.2.3, 12.2.4, 12.2.5, 12.2.6, 12.2.7, or 12.2.8(Path B).

5.2.10 Resolve any SQL Tuning Issues on Long Running Jobs Early

SQLs with poor execution plans often exhibit disproportionate use of a particular resource (e.g. I/O, CPU or memory) or a high level of contention (e.g. on blocks in buffer cache or on I/O). This can be misleading if trying to resolve general performance issues or investigating the benefit of changes in architecture or configuration.

Sometimes these architectural or configuration changes can cause regressions on long running jobs with poor execution plans and high resource usage or contention. And these regressions outweigh the benefits elsewhere.

Once any SQL tuning issues on long running jobs are resolved, it is much easier to investigate and assess the benefits of changes in architecture, configuration and numbers of AD Parallel Workers/Parallel Max Servers.

5.2.11 Gathering CBO Statistics

 
5.2.11.1 Gathering Schema Statistics

In normal circumstances it should not be necessary to manually gather schema statistics during the Release 12.2.n upgrade. In many circumstances (if necessary) the script adsstats.sql automatically gathers them towards the end of the R12.2.0 upgrade (e.g. phase: last+63).

However, CBO statistics should be gathered for all Oracle E-Business Suite schemas with GATHER_AUTO option using FND_STATS (or gather statistics concurrent program) again, after the entire Release 12.2.n upgrade, but prior to the system being online and available to users.

If the adsstats.sql script is taking a significant amount of time, the upgrade time can be reduced by:

  • Exporting schema statistics gathered during test runs (by adsstats.sql - at same point: phase: last+63).

  • Importing these statistics instead of running adsstats.sql.

Only do this if:

  • The estimated time saving will be significant, and it has been checked that parallel execution is being used effectively (with parallel_max_servers set to a suitable value, such as 2 x number of cores).

  • The test environment has the same data volumes as the production environment (i.e. is a clone of production).

Use the recommended scripts in the detailed section "Importing Oracle E-Business Suite Schema Statistics".

If the adsstats.sql job is taking a long time during the R12.2.0 upgrade then gathering CBO statistics for specific long running tables (with more than 100 million rows) with a lower sample size (percentage) may help.

After go live, watch out for statistics that adsstats.sql has unlocked and which may need to be locked again.

Note that adsstats.sql is often confused with adstats.sql, which disables automatic statistics gathering and gathers fixed object and dictionary statistics.

More

 
5.2.11.2 Fixed Object and Dictionary Statistics

Fixed Object Statistics should be gathered:

  • After any associated platform or database upgrade that is part of the overall Oracle E-Business Suite upgrade.

  • After any SGA/PGA parameters have changed.

  • After Release 12.2.n upgrade, when there is representative activity on the system.

Dictionary Statistics should be gathered:

  • After any associated platform or DB upgrade that is part of the overall Oracle E-Business Suite upgrade.

  • After the Release 12.2.n upgrade.

  • After move to OATM.

There are also changes to fixed and dictionary objects due to Online Patching Enablement (and the underlying Edition-Based Redefinition). There are new dictionary objects and changes to the number of rows on other objects. As a result internal SQL in Online Patching Enablement can sometimes be long running. Gathering statistics for specific fixed or dictionary objects can help in these circumstances, particularly on editioning objects.

Fixed object and dictionary statistics are gathered at the end of Online Patching Enablement (in script ADZDSTATS.sql). However, they are not gathered automatically at any other points during the Release 12.2 upgrade.

There may be additional specific circumstances during the upgrade where fixed object or dictionary statistics need to be gathered (such as before importing schema statistics or running SQLT or AWR reports when AWR has grown significantly).

If there are only a handful of internal SQLs with inefficient execution plans and only a few objects then statistics could be gathered for specific objects rather than gathering all dictionary or fixed object statistics.

More

Note : Do not use adstats.sql to gather fixed object and dictionary statistics (unless as part of a database upgrade). Use the APIs directly, as instructed in Fixed Object and Dictionary Statistics detailed section.

5.3 Resolving Performance Issues - Using Diagnostics

See here for detailed section on diagnostics.

Clarification : In all cases it is only necessary to produce diagnostics during test runs (and not during the final production run).

5.3.1 General Diagnostics to be gathered during the upgrade

The following diagnostics are still required for all patches/phases of the upgrade. This includes Online Patching Enablement, the online patching (ADOP) phases (e.g. prepare, apply, finalize, cutover, cleanup) of post 12.2 patches, especially the 12.2.n RUPs.

When analyzing Release 12.2.n Upgrade performance issues, the goal is to:

  • Prevent wasted test iterations. Aim to provide solutions that solve the issue first time.

  • Maximize the number of performance issues investigated on each iteration.

  • Maximize the number of performance issues resolved.

Upgrade jobs cannot be tested in isolation. They can only be tested on the next iteration. If a fix does not work, it is a potential wasted test iteration. To do this the following are needed:

  • Actual statistics: So it is possible to see exactly which execution plan steps are inefficient, rather than those that might be inefficient. The likely impact of a performance fix can also be estimated. There will then be a higher probability of providing a fix that can solve the performance issue first time. Also, it will be possible to identify marginal fixes (i.e. fixes that reduce elapsed times by 10-50%, for example by having more specific index access). These fixes often reduce contention between workers.

  • Diagnostics that are quick and easy to run.

  • Diagnostics that have very little impact on the performance of the Release 12.2.n upgrade. If they can safely be run during the upgrade then the results are be obtained sooner and the issue resolved more quickly.

To do this it is recommend that the steps outlined in "Express Diagnosis of Oracle E-Business Suite Release 12 Upgrade Performance Issues (Document 1583752.1)" are used. The key steps are:

Before the Oracle E-Business Suite Upgrade:

  • Set the statistics_level to ALL or _rowsource_execution_statistics = TRUE for the duration of the Release 12.2.n upgrade test runs (there are no actual statistics without this). More

  • Automatic Workload Repository (AWR) should be enabled with a snapshot of 30 minutes (the default is 60 minutes). For short upgrades, a shorter snapshot may be more suitable. More

  • The AWR retention period should be long enough to cover the duration of the upgrade run and a significant period afterwards (to gather diagnostics and analyze). The suggestion is N+7 days, where N is the estimated upgrade time, but a longer period will provide more time to gather subsequent diagnostics and statistics. More

During the Oracle E-Business Suite Upgrade:

  • Monitor top SQL in AWR or Cursor Cache (memory). This could be internal or application SQL. More. Enterprise Manager can also be used to identify expensive SQL as it occurs.

  • Obtain Display Cursor Reports (ALL +ALLSTATS) for the long running SQLs. More. Use this useful script to automatically produce Display Cursor reports for the top SQLs during the upgrade.

  • Obtain SQL Monitor Reports for SQL that uses Parallel Query or DML. More

  • Identify when a piece of SQL ran. More

  • Match long running SQL with a job. More

  • Report on CBO Statistics for all Oracle E-Business Suite tables. More

After the Oracle E-Business Suite Upgrade (or after each main patch):

  • Obtain AD Job Timing Reports. More

  • Identify long running upgrade jobs. More

  • Obtain the file versions for long running jobs. More

  • Obtain AWR Reports. More

  • Obtain SQLHC reports to obtain detailed information for specific SQLs. More

For more detailed analysis, the following may be required:

  • AD utility and worker logs. More

  • SQLT with XTRACT for long-running SQL. More.

    Note that SQLT should be used rather than SQLHC. See here.

  • SQL Trace (10046 event) for specific scripts/jobs on the next test run. More

In addition, Oracle Support and Development may require:

  • AWR Export. More

  • AD Parallel tables export. More

5.3.2 Online Patching Enablement - Specific Diagnostics to be gathered during the upgrade

In addition the following diagnostics are available during the Online Patching Enablement patch. More

  • ADZDSHOWDDLS.sql

5.3.3 Online Patching - Specific Diagnostics to be gathered during the upgrade

In addition the following diagnostics are available for patches applied using online patching.

  • ADOP log directories. More

  • The Online Patching Log Analyzer Utility adopscanlog (delivered in R12.AD.C.Delta.n since R12.AD.C.Delta.4). This analyzes the adop log directories. More

  • ADZDSHOWLOG.sql / adzdshowlog.out. More

  • ADOP cycle status. More

  • SQL to identify status of the ADOP phases. More

  • SQL to identify the AD and TXK C Patch levels. More

For more guidance see:

And My Oracle Support Document:

  • 12.2 E-Business Suite - Collecting Online Patching and fs_clone Log Files (Document 1542162.1)

5.3.4 Long Running SQL, Contention and Tuning

For long running jobs or SQL it is best to start by investigating if good execution plans are being used. A poor execution plan (or even just one that is moderately sub-optimal) can be the root cause of contention, especially if that contention only occurs during a particular job. More

Once any unnecessary contention caused by sub-optimal execution plans has been removed, a small amount of contention (e.g. 5 to -15% on particular waits) between AD Parallel or Parallel Execution sessions can be a useful indicator that the most is being obtained from the available resources. More

Be aware that although the entire wait time of some wait events can be classed as contention, this is not the case for all waits, and in particular not for I/O waits such as db file sequential read/db file scattered read and direct path reads/writes.

Be aware of shifting bottlenecks. Sometimes an action is taken that reduces a particular wait significantly, but another wait significantly increases. The obvious assumption is that the action has caused the new wait. However, this may not be the case: it may just have revealed the wait. More

5.4 Resolving Performance Issues – Common Solutions

See here for more information on the common solutions mentioned below.

5.4.1 Known Issues

Once the long running jobs and SQL have been identified, check My Oracle Support for known issues and potential solutions or workarounds.

The My Oracle Support Document "Oracle EBS R12.2 Upgrade - Outstanding Performance Bugs, Issues and Solutions (Document 2357810.1)" is also a useful document to check for recently identified performance bugs/issues and their solutions.

However, bear in mind that the fix or workaround may not necessarily fix the particular problem that has been observed.

If it cannot be confirmed (from the diagnostics) that the issue is exactly the same then the fix may still be applied, but continue to gather diagnostics and search for solutions until the issue is fully resolved.

More

5.4.2 Custom Indexes

Create custom indexes for long running jobs where a new index could significantly improve the execution plan and performance.

More

5.4.3 SQL Profiles for Inefficient Execution Plans

If it has been identified that a long running job has an inefficient execution plan, a SQL Profile could be used to apply hints that will help the CBO choose a better execution plan. SQL tuning expertise will be needed to do this.

Note that this only applies to SQL Profiles that are created to apply hints. Not to SQL Profiles, often created from SQL Tuning Advisor (STA), that use OPT_ESTIMATE hint (and usually SCALE_ROWS) to correct the cardinality over/underestimates. These latter SQL Profiles can cause significant performance issues if the tables concerned (such as new tables or temporary/transient table) have incorrect CBO stats or misleading number of rows when the SQL Profile is created.

More

5.4.4 SQL Tuning Advisor limitations

Using SQL Tuning Advisor (STA) to resolve performance issues during the R12.2.n upgrade has some limitations.

More

5.4.5 Pragmatic Stable Execution Plans

A pragmatic stable execution plan may not be the very best execution plan possible, but it will not be inefficient and the job time will be predictable and stable.

In most cases, there is one join order that will give a good execution plan and minimize throwaway.

For AD Parallel jobs, a pragmatic execution plan will lead with the driving table, accessing using a range of rowids and then use nested loop joins and index access on subsequent tables. The tables which are likely to have the lowest number of rows joined to (either due to selective filters, or total number of rows on the table) should be joined to first.

More

5.4.6 Long running SQL in Online Patching Enablement / Online Patching

There can sometimes be long running internal SQL with inefficient execution plans in Online Patching Enablement, R12.2.n RUPs and other online patches.

Gathering statistics for specific fixed or dictionary objects can help in these circumstances, particularly on editioning objects.

More

5.4.7 Long running Cleanup in Online Patching

If cleanup (after applying AD/TXK and 12.2.n RUP patches) is taking too long (particularly "drop covered objects") then consider running quick cleanup rather than standard cleanup.

Note that:

Cleanup and FS_CLONE stages are still required even if patches are being applied using ADOP in downtime mode. So this advice still applies.

If cleanup has not be run from the previous patching cycle the cleanup will run at the start of FS_CLONE.

More

5.4.8 Long Running Index Creation

This will typically apply to xdf and odf jobs.

Obtain an AWR Report for the snapshots where the index is being created, then investigate further.

Although pre-creation can help in a few circumstances, there is more likely to be another solution. If pre-creation is necessary then take care to do it correctly.

More

5.4.9 Long Running Index Re-creation

If the definition of a seeded E-Business Suite seeded index has been customized to change the type, columns, order (ASC or DESC), uniqueness, compression or partitioning, then it will be re-created during the upgrade to match the standard definition.

For large tables this is likely to consume significant additional time.

5.4.10 High level of contention on indexes on long running DML jobs

If a long running job runs heavy DML, has a high level of contention and that contention is on particular indexes then consider dropping the indexes before the job and recreating them afterwards (provided the index is not used in the meantime).

Ensure that indexes are recreated in parallel and with exactly the same definition. And remember to ALTER INDEX to revert the degree of parallel (NOPARALLEL).

More

5.4.11 High Level of "enq: HW – contention" or "enq: HV – contention"

If a long running job inserting into a table and indexes has a high level of waits "enq: HW – contention" or "enq: HV – contention", the following could be done:

  • If the wait is occurring largely on particular indexes, drop the indexes before the job and recreate them afterwards (provided the index is not used in the meantime).

  • Increase the extent size on the table and indexes.

  • Pre-allocate extents for the table and indexes.

  • Partition the table and any indexes to spread the load across multiple partitions. Note that the tables and indexes would still be partitioned after go live. So only do this if it will also give definite benefits after going live on the production environment.

More

5.4.12 High Level of redo log waits "log buffer space", "log file sync", "log_file_switch" etc.

If there are a high level of waits associated with redo log, especially "log buffer space" and "log file sync" then consider:

  • changing the configuration of redo logs

  • move to faster filer

  • increase the size; increase the number or increase the log parallelism (hidden parameter _log_parallelism_max). Consider running with NOLOGGING, but this is not advised.

If getting high "log file switch (checkpoint incomplete)" waits then consider removing (resetting) the initialization parameter log_checkpoint_interval.

More

5.4.13 Long Running Statistics Gathering (adsstats.sql)

If the upgrade is taking a long time to gather CBO statistics (adsstats.sql), then consider the following strategies:

  • Increasing parallel_max_servers and pga_aggregate_target. (adsstats.sql should run with few or no concurrent tasks).

  • Using all nodes on an Oracle RAC system.

  • Importing statistics gathered during test runs.

More

5.4.14 Gathering or Deleting Statistics to resolve specific performance issues

Performance issues may indicate the need to gather or delete statistics for specific objects.

The specifics of doing so will depend on the exact circumstances, and the solutions may not be simple.

More

5.4.15 Long-Running Compilation / Re-compilation

If scripts (e.g. adobjcmp.sql/adutlrcmp.sql) which utilizes the job queue (DBMS Scheduler) to compile/re-compile objects (e.g. UTL_RECOMP.RECOMP_PARALLEL) are taking a long time then check that the value of initialization parameter job_queue_processes is high enough (a value equal to the number of CPU cores is normally recommended).

More

5.4.16 Long Running Materialized View xdf/odfs

If there are

  • long running xdf or odf jobs creating materialized views (MV)

  • long running scripts executing MV operations, such as drop or refresh

then consider refreshing MVs prior to the upgrade or truncating any large MV logs.

Note that this requires complete refresh of all MVs that are dependent on the MV log. Check that the number of rows in the MV log (mlog$) is zero to confirm that all dependent MVs have been refreshed.

There is now a facility in the Online Patching Enablement Readiness Report to list MVs with stale log data (ADZDMVREFRESHNEEDED.sql). This script needs to be run before starting the 12.2.0 upgrade driver.

5.4.17 Long Running Jobs that might not be needed

Check if any long running jobs are actually needed (e.g. for a module or localization that is not used or not licensed). Oracle Support may advise that a job can be skipped, or offer a fix or workaround.

More

5.4.18 High I/O waits on temporary space

If there I/O waits on temporary space ("direct path read temp" and "direct path write temp") and there is no scope to increase PGA further (or it has not resolved the issue) then consider moving the temp space to local disk (or faster disk).

5.4.19 Long running class, xdf, xml and other files

If during the 12.2.0 CUP and 12.2.n RUP patches there are long running class (especially XDOLoader.class, WFXLoad.class, akload.class), xdf or xml files, with groups of files taking similar lengths of time or completing at around the same time, then there could be an issue with random number entropy.

This can be resolved by using /dev/urandom rather than /dev/random.

More

5.4.20 Maximum AD Parallel Workers

The Auto Patch utility specifies a maximum number of workers. The number depends on several factors, and under certain circumstances may be less than the number of workers required.

See here for how this maximum is calculated, and how this issue might be resolved (increase processes parameter, or reduce other sessions/processes).

5.4.21 Applications Tier Bottlenecks

Sometimes the applications tier can be the bottleneck.

This will usually be on phases which contain multiple jobs, running in parallel with each other, with a high applications tier workload.

If possible, make sure that the one application tier node used during the upgrade process is configured with a larger RAM, more CPU and faster I/O (e.g. local SSD storage).

However, the total time taken on the activities, where the application tier is the bottleneck, may only a maximum of an hour or two and, for larger customers, may be insignificant compared to the whole upgrade time. So, this advice is only likely to be applicable to customers who have smaller databases, but who are heavily reliant on 24x7 operations, with a minimum upgrade window.

Note: Upgrading to Oracle E-Business Suite Release 12.2 is performed with a single application tier node and a single database node (even if you are using RAC). Scaling the environment is a post-upgrade task. So using Distributed AD to allocate worker processes to multiple application tier nodes is not possible.

More

5.4.22 Long running instances of AD_ZD_TABLE_APPLY.sql

In the acet phase of the 12.2.n Release Update Pack.

If CPU utilization is not particularly high and contention is still low, then there may be scope for increasing the value of job_queue_processes. If the issue is due to chunk sizes (too large or small) in table DBMS_PARALLEL_EXECUTE_CHUNKS$, this could mean that CBO statistics need gathering for specific table(s).

More

5.5 Key Resources and Documents

Documentation:

My Oracle Support documents:

  • Oracle E-Business Suite Release Notes, Release 12.2 (Document 1320300.1)

  • Oracle E-Business Suite Release 12.2: Suite-Wide Rollup and AD/TXK Delta Information (Document 1583092.1)

  • Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2 (Document 1617461.1)

  • Database Preparation Guidelines for an E-Business Suite Release 12.2 Upgrade (Document 1349240.1)

  • Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Document 1594274.1)

  • Best Practices for Gathering Statistics with Oracle E-Business Suite (Document 1586374.1)

  • Fixed Objects Statistics (GATHER_FIXED_OBJECTS_STATS) Considerations (Document 798257.1)

  • Oracle E-Business Suite Release 12.2: Upgrade Sizing and Best Practices (Document 1597531.1)

  • R12.1 and 12.2 Oracle E-Business Suite Pre-install Patches Report [Video] (Document 1448102.2)

  • Use of Multiple Organizations In Oracle Applications Release 11i (Document 210193.1)

  • Reducing Your Oracle E-Business Suite Data Footprint using Archiving, Purging, and Information Lifecycle Management (Document 752322.1)

  • Express Diagnosis of Oracle E-Business Suite Release 12 Upgrade Performance Issues (Document 1583752.1)

  • Oracle E-Business Suite Performance Guide (Document 1672174.1)

  • Oracle E-Business Suite SQL Trace and TKPROF Guide (Document 1674024.1)

  • Performance Diagnosis with Automatic Workload Repository (Document 1674086.1)

  • All About the SQLT Diagnostic Tool (Document 215187.1)

  • How Does Adpatch Determine The Number Of Workers To Recommend? (Document 800024.1)

  • Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 (Document 387859.1) – section "5.1. Running AutoConfig in Parallel Across Multiple Nodes".

  • Database Initialization Parameters for Oracle E-Business Suite Release 12 (Document 396009.1)

  • R12 FAQ for the SLA Upgrade: SLA Pre-Upgrade, Post-Upgrade, and Hot Patch (Document 604893.1)

  • Managing Configuration of Oracle HTTP Server and Web Application Services in Oracle E-Business Suite Release 12.2 (Document 1905593.1)

  • Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2 (Document 1577661.1)

  • Using the Online Patching Readiness Report in Oracle E-Business Suite Release 12.2 (Document 1531121.1)

  • HugePages on Oracle Linux 64-bit (Document 361468.1)

  • Oracle Linux: Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Document 401749.1)

  • 12.2 E-Business Suite - Collecting Online Patching and fs_clone Log Files (Document 1542162.1)

  • EBS - Technology Area - Webcast Recording 'E-Business Suite - RAC & Parallel Concurrent Processing (PCP)' [video] (Document 1359612.1)

  • Patching Best Practices And Reducing Downtime (Document 225165.1)

  • R11i / R12: Oracle E-Business Suite Upgrades and Platform Migration (Document 1377213.1)

  • Oracle E-Business Suite Recommended Performance Patches (Document 244040.1)

  • SQL Tuning Health-Check Script (SQLHC) (Document 1366133.1)

  • Oracle E-Business Suite Release 12.2: Online Patching FAQ (Document 1583902.1)

  • Oracle EBS R12.2 Upgrade - Outstanding Performance Bugs, Issues and Solutions (Document 2357810.1)

  • Can Distributed AD Be Used For Running The 12.2 Upgrade Driver? (Document 1935007.1)

  • OSWatcher (Includes: [Video]) (Document 301137.1)

  • OS Watcher User's Guide (Document 1531223.1)

  • OSWatcher Analyzer User Guide (Document 461053.1)

  • Using Database Partitioning with Oracle E-Business Suite (Document 554539.1) - section "What Access Paths Are Used - Analysis of AWR views"

  • Upgrade From 11.5.10.2 Is Very Slow as XDOLOADER Task Hangs (Document 1065393.1)

  • USE_LARGE_PAGES To Enable HugePages (Document 1392497.1)

  • Performance Best Practices for Oracle E-Business Suite on Oracle Cloud Infrastructure and On-Premises (Document 2528000.1)

6. Preparation and Planning - Pre-Upgrade Environment / Activities

This includes steps to prevent performance issues and reduce downtime.

These are all activities to be carried out on the pre-upgrade environment prior to Release 12.2.n upgrade.

 

6.1 OATM (Oracle Applications Tablespace Model)

Migrating existing objects to OATM on the pre-upgrade environment is recommended.

If upgrading from 11i then convert to OATM (Oracle Applications Tablespace Model), whilst still on 11i. If the environment was previously upgraded from Release 11i to Release 12.0 or 12.1, then the upgrade process created tablespaces for all new products, configured the database for the new tablespace model, and created new objects. However, it did not automatically migrate existing objects. So, migrate any existing objects (not in OATM) using the Tablespace Migration Utility whilst still on 12.0 or 12.1.

If not already done, Oracle strongly recommends that the Tablespace Migration Utility is used to perform this migration now.

Note that this utility is not supported for use after Online Patching has been enabled, so the migration cannot be performed after the environment is upgraded to Release 12.2.n.

If the environment is not migrated to OATM now, then tablespaces must continue to be managed separately.

See the following for more information:

6.2 Extent Size for Large Transaction Tables

Ensure that the large transaction tables populated or modified by the release 12.2.0 upgrade have a large extent size (1-10MB).

Ensure that the tablespaces (for large transaction tables created during release 12.2.0) have large default extent sizes (1-10MB).

For production environments and large tablespaces like transaction tables, transaction indexes, interfaces, summaries, archives, and media, a uniform extent size of 1MB or 10MB (with caution) should be considered. For large multi-terabyte system, an extent size of 4-10 MB has been tested successfully.

Small extents will seriously limit the AD Parallel batch size used on Autopatch (adpatch) e.g. an extent size of 128k = 16 blocks is highly likely to give a batch size of less than 1000. They will also increase the occurrence of high water mark waits ("enq: HW – contention" or "enq: HV – contention").

AD Parallel jobs, driven from tables with a high row length, could be particularly affected by this (e.g. jobs ar120trxl.sql, ar120trx.sql, ar120pay.sql, zxartrxmigupg.sql, arleupgtrx.sql which are driven from RA_CUSTOMER_TRX_ALL).

See the following for more information:

6.3 Multi-Org Architecture (Upgrade from 11i Only)

If migrating from 11i then it is possible to convert to the new Multiple Organizations (Multi-Org) architecture while still on Oracle E-Business Suite 11i. See Chapter 1 (Planning for an Upgrade) of the "Oracle E-Business Suite Upgrade Guide Release 11i to 12.2" for more information.

Also see:

6.4 Purge old data

Purge all old data that is no longer needed prior to the upgrade.

This can be quite a lengthy process, and issues around performance, execution frequency and the periods to be purged in each execution may need to be resolved. So this should be started as soon as possible.

There are over 130 standard purge programs in 11i and over 240 in R12.

A list of purge programs is listed in My Oracle Support document "Reducing Your Oracle E-Business Suite Data Footprint using Archiving, Purging, and Information Lifecycle Management (Document 752322.1)".

6.5 Purge interface data

Flush all the interfaces, such as Auto invoice, Journal entry import, and order import.

 

6.6 Gather CBO Statistics

Regularly gather schema statistics with GATHER_AUTO option for all schemas while still on Release 11i, 12.0 or 12.1.

Then gather schema statistics (with GATHER_AUTO option for all schemas) close to the start of the Release 12.2.n upgrade.

Always use the FND_STATS package or the Gather Statistics concurrent programs. Do not use the DBMS_STATS package or Analyze.

6.6.1 Zero or Incorrect Statistics on transient/temporary tables

Be aware that the statistics from the pre-upgrade environment may contain some issues, even if they have been gathered with the Auto option.

Check the statistics for transient, temporary or Global Temporary (GT) tables. These are tables which can alternately contain rows for processing (e.g. import/interface/staging), or contain no (or very few) rows.

For GT tables, rows are only visible to the session that inserted them, and those rows only persist for the duration of the session. If statistics on these tables are gathered then only statistics for the rows present (in that table) in that session will be collected: that is likely to result in statistics for 0 rows.

In addition, the statistics for GT tables are not session specific, so there is only one set of statistics and these are visible to all other sessions. Even if the statistics are gathered by a session when the tables are populated, they could still result in suboptimal execution plans if the volume or distribution of data is different for other sessions.

Similarly, for other transient, temporary or staging tables, statistics may have been gathered when the table is empty.

Having zero statistics (or statistics with low row count) could result in poor execution plans for temporary/transient tables that contain a large number of rows.

Ideally, all these temporary, transient or global temporary tables should have no statistics, and dynamic sampling should be used.

However, in specific cases, a strategy of populating with indicative statistics (when the table is populated) or gathering statistics each time a process is run may have been taken. In some cases the seeded Oracle E-Business Suite code will gather statistics.

See section "Temporary Tables (Statistics)" for instructions on either deleting, locking and excluding the statistics or gathering or populating representative statistics.

6.7 Fixed Object and Dictionary Statistics

These should have been gathered and be correct/up to date on the pre-upgrade environment. More.

6.8 Drop MRC Schema (Upgrade from 11i Only)

All programs and reports now use the APPS schema. The MRC_APPS schema is no longer needed, so dropping it frees space and reduces processing overhead during the upgrade.

Drop this schema prior to the Release 12.2.n upgrade if it still exists. It should have already been removed on upgrade to 11.5.10.

See "Oracle E-Business Suite Upgrade Guide Release 11i to 12.2".

6.9 Comply with Online Patching standards

Prior to upgrading to R12.2.n the following must be run on the pre-upgrade environment (e.g. 11i, 12.0 or 12.1). All, except ADZDMVREFRESHNEEDED.sql, should also be run before and after Online Patching Enablement :

  • Online Patching Readiness Report to identify issues in custom database objects that will be fixed automatically or need manual intervention. (ADZDPSUM.sql). Excluding ADZDMVREFRESHNEEDED.sql

  • Manual Fix Readiness Report. (ADZDPMAN.sql)

  • Online Patching Database Compliance Checker (ADZDDBCC.sql). Fix any violations of development standards listed in this report.

  • Global Standards Compliance Checker script (gscc.pl). Address errors that are reported by this script.

Important: All violations that are reported by these utilities must be fixed before enabling online patching.

For more information and the latest patch details (for 11i, 12.0, 12.1, or 12.2) see My Oracle Support document:

  • Using the Online Patching Readiness Report in Oracle E-Business Suite Release 12.2 (Document 1531121.1)

More

7. Preparation and Planning – Release 12.2.n Upgrade Activities

Only run the Release 12.2.n upgrade with one Oracle RAC node enabled. The majority of the elapsed time of the Release 12.2.n upgrade is taken by jobs running DML (INSERT, UPDATE, DELETE).

These jobs use multiple workers/parallel severs, which all access the same objects and blocks at the same time. So the additional communication between nodes on the cluster (and cluster waits) will significantly outweigh the gains from using the additional CPUs to increase throughput. In some cases it can lead to severe contention and deadlocks.

7.1 Planning

Always read the relevant upgrade guide.

or

Note that you should use the Upgrade Guide in conjunction with the readme document for the Oracle E-Business Suite Release 12.2.n RUP you are applying. For a list of Oracle E-Business Suite Release 12.2.n Readme documents, see "Oracle E-Business Suite Release 12.2: Suite-Wide Rollup and AD/TXK Delta Information, Document 1583092.1"

If migrating Oracle E-Business Suite to OCI or upgrading an instance already on OCI then always read My Oracle Support Document "Performance Best Practices for Oracle E-Business Suite on Oracle Cloud Infrastructure and On-Premises (Document 2528000.1)"

 

It is important to always apply the latest CUP for 12.2.0, R12.AD.C.Delta, R12.TXK.C.Delta and 12.2.n RUP, as these will have the latest improvements for timing and performance of the upgrade.

Note that the CUP for 12.2.0 (which is pre-installed and merged with the main 12.2.0 upgrade driver) will contain performance fixes for the main 12.2.0 driver and R12.AD.C.Delta will contain performance fixes for ADOP (Online Patching).

Identify the latest Consolidated Upgrade Patch (CUP) for R12.2.0, Online Patching Enablement, R12.AD.C.Delta.n, R12.TXK.C.Delta.n and 12.2.n Release Update Packs (RUP) to apply.

At time of last update (May 2021), these were:

Note that there are a timing and performance fixes (for the upgrade) in the latest patches/packs. So upgrading to the latest version (12.2.10) of Oracle E-Business Suite is strongly recommended.

Note that:

  • For upgrades from 11i/12.0.x/12.1.x or new installs (Path A) the R12.AD.C.Delta.12 and R12.TXK.C.Delta.12 patches (along with the consolidated patches on top) are applied using adop hotpatch on the run file system.

  • The Oracle E-Business Suite Release 12.2.10 Online Help Patch is always installed using adop hotpatch on the run file system.

See the following My Oracle Support documents for information on the latest patches, documentation etc.

  • Oracle E-Business Suite Release Notes, Release 12.2 (Document 1320300.1)

  • Oracle E-Business Suite Release 12.2: Suite-Wide Rollup and AD/TXK Delta Information (Document 1583092.1)

  • Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2 (Document 1617461.1)

 

7.1.1 Laying Down 12.2.0 File System

Note that (since StartCD 49) Rapid Install (RI) can be used to lay down 12.2.0 code and tech stack and create the Dual File System prior to the upgrade downtime.

7.2 Upgrade Path and Database Certification

Check :

  • Database Preparation Guidelines for an Oracle E-Business Suite Release 12.2 Upgrade (Document 1349240.1)

  • My Oracle Support Certification Tab for database versions certified with EBS and the upgrade paths.

7.3 Performance and Technology Fixes

Before upgrading to 12.2 look at My Oracle Support document "Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Document 1594274.1)" for technology patches to apply before upgrading to 12.2. This also gives information on the latest "EBS Technology Codelevel Checker (Patch 17537119)".

Ensure that any recommended performance patches are applied. Check the My Oracle Support documents

  • R12.1 and 12.2 Oracle E-Business Suite Pre-install Patches Report [Video] (Document 1448102.2)

  • Oracle E-Business Suite Recommended Performance Patches (Document 244040.1. Note: Only identify the patches that are applicable to your environment, based on the products you have installed and their current patch level.

 

7.4 Removing Unnecessary Workloads / Overheads

To reduce the workload do the following:

  • Disable any custom triggers and business events.

  • Review and disable custom VPD policies as needed.

  • Disable auditing if enabled. The "Oracle E-Business Suite Upgrade Guide, Release 11i to 12.2" and "Oracle E-Business Suite Upgrade Guide, Release 12.0 and 12.1 to 12.2" state that the Oracle AOL Audit Trail should be disabled before upgrade, anyway.

  • Review and disable all debug and logging that has been enabled using profiles. Do this at all levels (e.g. site, responsibility, user level.).

  • If possible, run in noarchivelog mode.

  • Disable flashback DB.

  • Remove TDE (Transparent Data Encryption) from high volume tables.

  • Consider running AutoConfig in parallel on a multi-node system. See :

Note: currently it is NOT recommended that customers add nodes to their 12.2 Rapid Install upgrade file system until AFTER the upgrade to the latest 12.2.x RUP is complete, so using Distributed AD and Shared APPL_TOP is not relevant.

Note that using a Staged Application System (APPL_TOP) is not an option for upgrades to Release 12.2.n. There are multiple reasons.

Note that "Defer Upload of Patch Information" (See My Oracle Support document "Patching Best Practices And Reducing Downtime (Document 225165.1)") cannot normally be used, as subsequent patches may rely on information from previous patches, and the Release 12.2.n upgrade is a series of patches (e.g. Online Patching Enablement, R12.AD.C.Delta.n, R12.TXK.C.Delta.n and 12.2.n RUP patches all follow R12.2.0).

7.4.1 Disable all DBMS scheduler, DBMS Job and Autotask activities

Disable unnecessary DBMS scheduler (DBMS_SCHEDULER), DBMS Job (DBMS_JOB) and Autotask (DBMS_AUTO_TASK_ADMIN) activities during the upgrade.

Note that the DBMS Scheduler itself should not be disabled or stopped. It is needed for some upgrade tasks, such as compilation/recompilation and the job AD_ZD_TABLE_APPLY.sql (applying 4CETs). More. Just the jobs/tasks that are scheduled should be cancelled, especially custom jobs.

The predefined automated maintenance tasks run by Autotask (DBMS_AUTO_TASK_ADMIN) are:

  • Automatic Optimizer Statistics Collection. Note that this should already be turned off for Oracle E-Business Suite by setting _optimizer_autostats_job=FALSE. See My Oracle Support document "Database Initialization Parameters for Oracle E-Business Suite Release 12 (Document 396009.1)"

  • Optimizer Statistics Advisor

  • Automatic Segment Advisor

  • Automatic SQL Tuning Advisor

  • SQL Plan Management (SPM) Evolve Advisor

These should all be disabled.

7.4.2 Split Into Separate Steps

Identify tasks that could be completed in a separate downtime period, prior to the production upgrade.

The following could all be candidates:

7.4.3 Use TUMS (The Upgrade Manual Script) to avoid running tasks not relevant to the installation (Upgrade from 11i Only)

The TUMS report lists tasks that can be omitted from the upgrade because they do not apply to the installation (for example, a task required for a product that is not used or a patch that has previously been applied). TUMS is delivered in a patch "TUMS UTILITY FOR RELEASE 11I TO RELEASE 12.2 UPGRADE (Patch 21900924)", which supplies the scripts needed to examine the system and create the report. It is strongly recommend the TUMS report is created and reviewed before beginning the upgrade.

Download and apply Patch 21900924, then run the script adtums.sql to generate the report tumsr12.html.

The tumsr12.htm report lists the steps (identified by the TUMS step key in this book) that do not apply to the installation. Any steps listed in this report may safely be ignored.

See "Oracle E-Business Suite Upgrade Guide, Release 11i to 12.2" Preparing for the Upgrade (Chapter 2) for more information.

7.4.4 Minimize Historical Data To Be Upgraded (Upgrade from 11i Only)

See "Oracle E-Business Suite Upgrade Guide Release 11i to 12.2" appendix Upgrade By request (H).

Also see My Oracle Support documents:

  • R12 FAQ for the SLA Upgrade: SLA Pre-Upgrade, Post-Upgrade, and Hot Patch (Document 604893.1)

  • Oracle E-Business Suite Release 12.2: Upgrade Sizing and Best Practices (Document 1597531.1)

Note that there are some SLA upgrade jobs in the upgrade from 11i to Release 12.2.0, especially for Oracle Payables, which still process all the historical data, even if only the most current data is being upgraded.

The Release 12.2.0 upgrade migrates all the transactions for the following regardless - Payables (Transaction Entities, Events, Journal Headers, Journal Lines), Receivables (Transaction Entities).

7.4.5 Parallelize pre and post upgrade technical activities

Where practical, perform technical upgrade tasks in parallel. For example, while the upgrade driver is running on the database, Release 12.2 setups or re-register single sign-on could be performed on the application tiers.

7.4.5 Define separate concurrent manager queue for post-upgrade job

There are many concurrent programs automatically submitted in the downtime portion of the upgrade to Release 12.2.n. Many of these programs will run in multiple threads, so the total number of concurrent requests that form part of the post-upgrade step is much higher.

These programs will be picked up and executed by the concurrent manager once the system is up. Consequently, their execution will be mixed with the execution of ongoing concurrent jobs in the system.

It can be a good idea to define a separate concurrent manager queue for these requests, to enable post-upgrade testing of standard run-time requests to proceed without interference from upgrade-related processes.

Such a definition can be achieved by using inclusion and exclusion rules to prevent other manager queues such as standard from picking up these requests, and allow this new manager queue to process these requests only.

Doing this now allows the number of target processes allocated to these post-upgrade concurrent programs to be controlled, including doing this dynamically with the use of work shifts. For additional details on configuring new manager queues, target processes, inclusion/exclusion rules and work shifts, refer to the Applications System Administrator's Guide from Oracle E-Business Documentation Library.

See the following for more information:

If using RAC then "Parallel Concurrent Processing (PCP)" could be used for post-upgrade concurrent jobs to assign programs to different nodes, but care should be taken over node affinity (there is little point having two different requests accessing the same blocks/rows from different nodes at the same time).

Note that since Oracle E-Business Suite 12.1, Concurrent requests can be directed to a specific database instance or node on a per-program basis.

For more information see MOS document "EBS - Technology Area - Webcast Recording 'E-Business Suite - RAC & Parallel Concurrent Processing (PCP)' [video] (Document 1359612.1)".

7.5 Upgrade and Initialization Parameters

Note that the values of the initialization parameters below (except db_file_multiblock_read_count) may be different from the values used for normal running. So be sure to revert after the Release 12.2.n upgrade is complete.

For other initialization parameters, refer to My Oracle Support document "Database Initialization Parameters for Oracle E-Business Suite Release 12 (Document 396009.1)".

7.5.1 AD Parallel Workers and parallel_max_servers

The number of parallel execution threads is determined by the initialization parameter parallel_max_servers.

This will impact the elapsed time of jobs that use parallel SQL and DML. Such jobs include apintbal.sql, apxlaupg.sql, and adsstats.sql.

The number of workers used in the AD Parallel Utility (in Autopatch and ADOP) is also configurable. It is important to get the number of AD Parallel workers and the parallel_max_servers right.

The typical recommendation is to set:

  • parallel_max_servers = 2 x number of CPU cores.

  • AD Parallel workers – start with 1.5 x number of CPU cores. Possibly increase to 2.0 x number of CPU cores.

However, note that lower values may turn out to be more optimal, for the following reasons:

  • The above values are recommended to maximize CPU utilization. However, the constraints may lie in I/O, memory, or elsewhere. So check memory utilization (no swapping/excessive paging) and I/O response times (histogram shows a fat tail for the longer wait times). Also check for buffer busy waits spread across all objects (high levels on specific objects can often indicate a sub-optimal execution plan).

  • The number of CPUs/cores on systems is increasing, and consequently the number of CPU cores presented to the operating system is also increasing. This is particularly true if the hardware has dynamic/hyper-threading capabilities and they are not disabled for the duration of the Release 12.2.n upgrade.

The recommendations above are usually valid if there are less than 32 CPU cores. However, above 32, the levels of contention can increase significantly, outweighing any gains in CPU and resource utilization obtained from increasing the workers/threads.

So, if the number of CPU cores is 32 and above, then start with parallel_max_servers and AD Parallel workers below the above levels. For example:

  • parallel_max_servers = 1.5 x number of CPU cores.

  • AD Parallel workers to between 1.0 and 1.5 x number of CPU cores.

Based on the performance diagnostics, these values may need to be decreased. It may also be possible to increase them. The level of contention and resource (CPU) usage (in AWR) will be the key to determining this.

Be careful if the hardware has dynamic or hyper-threading. In such cases, the number of logical CPUs could be a multiple of the number of cores. If so, calculate the values above using the number of CPU cores and not the number of logical CPUs.

If hyper-threading or dynamic threading is enabled and CPU utilization is low, the hyper-threading configuration may need to be changed. See the Dynamic / Hyper threading section.

Only increase the number of AD Parallel workers if the level of contention on all long running AD Parallel jobs is low. Similarly, only increase parallel_max_servers if the level of contention on all long running parallel query/DML jobs is low. Typically, this means SQL Tuning (poor execution plans) and contention issues should be resolved before increasing the AD Parallel workers or parallel_max_servers.

Note that parallel_servers_target should have no value (default). In the upgrade process there is normally only one parallel DML/SQL job running at any one time, so it should never have a value less than parallel_max_servers.

7.5.2 Job_queue_processes

Similarly, job_queue_processes = 2 x number of CPU cores is normally recommended.

If there are a large number of CPU cores (or dynamic/hyper-threading) then a reduced value of job_queue_processes may be better (e.g. 1.5 x number of CPU cores).

The two major areas where job_queue_processes is used are:

  • Scripts adobjcmp.sql/adutlrcmp.sql (in "12.2.n Release Update Pack" and "Consolidated Upgrade Patch (CUP) for R12.2.0"), which utilize Oracle Scheduler (DBMS Scheduler) to compile objects (e.g. UTL_RECOMP.RECOMP_PARALLEL).

  • Instances of the script AD_ZD_TABLE_APPLY.sql in the acet phase of the 12.2.n Release Update Pack, which applies Forward Cross Edition Triggers (4CET) to upgrade tables. This uses the Oracle supplied package DBMS_PARALLEL_EXECUTE, which splits the work up into chunks and then uses the Oracle Scheduler (DBMS Scheduler) to execute the chunks in parallel.

If the latter is long running, CPU utilization is not particularly high and contention is still low, then there may be scope for increasing the value of job_queue_processes.

For more information on how tables are upgraded using Forward Cross Edition Triggers and AD_ZD_TABLE_APPLY.sql see

7.5.3 SGA, Shared Pool, PGA etc.

If possible, SGA and PGA should be maximized.

Check the feedback from AWR Advisory Statistics and the SQLT / Display Cursor report, which (if statistics_level is set to ALL or _rowsource_execution_statistics=TRUE), should show the temporary space used by sorts, hash joins, etc.

Note that the AWR Advisory Statistics may underestimate the SGA required.

  • The advisory statistics are all reported for the last snapshot interval only. If the AWR report covers more than one snapshot interval and the last snapshot interval has a lower workload (e.g. the process being observed completes a long time before the end snapshot) then the advisory statistics could underestimate the advised values.

  • Where there are many workers accessing the same objects at the same time (e.g. AD Parallel jobs), the SGA Target Advisory (and Buffer Pool Advisory) may underestimate the reduction in physical reads obtained from increasing the SGA.

For the Release 12 Upgrades, an improvement in performance (due to reduction in physical I/O such as db file sequential read) has been noticed when SGA size is increased, even though the SGA Target Advisory on AWR indicates that the SGA is adequately sized. Customers have used values that are 2x the recommended value, and still seen significant benefit.

The reason why the SGA Target Advisory does not recommend a larger value is not known, but it is suspected that it is due to the nature of the Release 12 Upgrade process (almost all rows on each table are repeatedly read; however, each AD Parallel or Parallel Execution Thread only accesses a small percentage of rows).

Increasing PGA will improve timings if there I/O waits on temporary space ("direct path read temp" and "direct path write temp"). There may also be physical reads showing on Hash Join, Sort or Group By operations in Display Cursor reports and SQL Monitor reports.

Increasing SGA may improve timings if there are medium to high (>10%) I/O waits on database objects (e.g. db file sequential read, db file scattered read, direct path read, direct path write etc.)

Note that a very large SGA/buffer cache can cause excessive paging or swapping. It can also cause excessive latch waits as blocks in the buffer share the same latches.

Some starting rules of thumb are:

  • log buffer = 30 to 100 MB

  • shared pool = 1 to 4 GB

  • pga target = 3 to 20 GB

  • SGA / buffer cache = multi GB. Be generous, but avoid causing excessive paging.

However, ensure that there is sufficient physical memory to contain the SGA and PGA. Allow enough remaining space for :

  • 2 GB of Free Memory for Online Patching

  • Memory used by database processes. e.g. (maximum PROCESSES) * 4MB

  • Other databases or applications resident on the same server.

7.5.4 SGA and HugePages (Linux)

Using HugePages on Linux can improve SGA allocation and hence performance.

See My Oracle Support document "HugePages on Oracle Linux 64-bit (Document 361468.1)" for information on how to configure HugePages. Use the script in My Oracle Support document "Oracle Linux: Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Document 401749.1)" to calculate the recommend hugepages configuration.

Note that Only SGA allocation benefits from HugePages. PGA allocation does not.

Set the parameter USE_LARGE_PAGES='only' for each instance so that the instance will only start if sufficient Huge Pages are available. See My Oracle Support document "USE_LARGE_PAGES To Enable HugePages (Document 1392497.1)"

7.5.5 db_file_multiblock_read_count

If specified, remove db_file_multiblock_read_count. This is the recommended value for normal running of Oracle E-Business Suite.

7.5.6 Dynamic Sampling

A number of key high volume tables (particularly in the Sub Ledger Accounting (XLA) schema) are created and populated during the Release 12 upgrade. These often have no CBO statistics gathered (prior to adsstats.sql running), and dynamic sampling could be of benefit.

The Release 12.2.n upgrade is a batch process, and most of the SQL is executed less frequently and for a larger number of rows. So increasing the dynamic sampling level should not be much of an overhead, provided the sample size (blocks) is not increased too much.

For Oracle E-Business Suite, it is normally recommended not to set the database parameter optimizer_dynamic_sampling, which means that the default value (2) will be used. However, a value of 4 is recommended for the upgrade, provided it is reverted to default (2) afterwards.

Note that, at level 4, the sample size is 64 blocks. Level 5, 6, 7, 8, 9 and 10 increase the sample size to 128, 256, 512, 1024, 4086 and All blocks.

7.5.7 optimizer_adaptive_features

This parameter was introduced in Oracle Database 12c Release 1.

A value of FALSE is recommended during the Release 12.2.n Upgrade. The default value is TRUE.

This enables two sets of related functionality :

  • Selection of alternate execution plans based on statistics collected as a query executes.

  • Augmentation of CBO statistics with adaptive statistics gathered at SQL statement parse time to improve the quality of SQL execution plans.

Many E-Business Suite upgrade SQLs make use of embedded hints to improve performance. This is because the CBO statistics are often missing or inaccurate for new tables during the upgrade process. These hints will have been added to resolve known issues that would impact most customers. Heavy or comprehensive hints have been used to ensure plan stability (using a pragmatic execution plan). A pragmatic execution plan is one that will be good in the highest percentage of cases, but not necessarily the very best. Heavy hints give a much higher level of plan stability and a much lower risk of variation in running time.

Using adaptive plans and statistics can often result in hints being ignored, plan instability and inefficient execution plans being chosen (initially). There is a risk of variation in running time (upgrade time).

See My Oracle Support document "Database Initialization Parameters for Oracle E-Business Suite Release 12 (Document 396009.1)" for recommendations on this parameter for normal running when the upgrade is complete.

7.5.8 parallel_degree_policy

This parameter was introduced in Oracle Database 11g Release 2. The value "ADAPTIVE" was added in Oracle Database 12c Release 1.

This initialization parameter should be removed or set to the default value MANUAL during the Release 12.2.n Upgrade.

Values of LIMITED, ADAPTIVE or AUTO could result in SQL statements (that are designed to run in serial) running in parallel, which the optimizer calculates will be best for the individual instance of the SQL, but which may not be the best option for the whole job or upgrade.

The value of ADAPTIVE could result in plan instability and inefficient execution plans being chosen. There is a risk of variation in running time (upgrade time).

7.5.9 db_writer_processes

This parameter should not be set. It will default to (CPU_COUNT / 8), which should be more than adequate.

If it is set to a value lower than this then the performance of AD_ZD_TABLE_APPLY.sql in the acet phase (12.2.n RUP) could be affected.

7.5.10 AD Parallel Batch Size on Autopatch (adpatch)

10K is suitable for most installs: test other values if time allows. Changing the batch size is not normally recommended. As this is limited by the extent size on the driving table, the ability to change the actual batch size used will be limited anyway.

Only change the batch size if there are serious performance issues that can be solved by increasing or decreasing the batch size. And only once the SQL concerned has been confirmed as having optimal execution plans.

Having larger extent sizes for the larger transaction tables populated or modified by the Release 12.2.0 upgrade is suggested. In any case, it is best practice to have larger extent sizes for tables with high growth.

Note there are some limitations on batch size and how it is calculated. These are described here.

Small extents (e.g. 128k = 16 blocks) will significantly limit the batch size. The batches are likely to much less than 1000, regardless of what batch size is selected.

Even if there are large extents, the actual batch sizes may vary considerably from the ones chosen because of storage overheads (percent free, block/row headers) and compression.

There are advantages and disadvantages with large and small batch sizes. Larger batch sizes have less overhead, and can reduce the overhead of sub-optimal execution plans. However, there is more risk of the workers trying to perform the same operations at the same time, and thus causing contention (especially if there are multiple pieces of SQL in the job). There is also more risk of remainder batches delaying the end time of a job.

Note that rows_processed column on AD_PARALLEL_UPDATE_UNITS can often contain values that do not correspond to the actual batch size used (or entered). The value of rows processed is usually SQL%ROWCOUNT following a DML statement, so it is typically the number of rows inserted or updated as a result of the SQL, not the number of rows used to drive the SQL. Sometimes there is more than one SQL/DML in the job and it is the count from the latest SQL.

7.5.11 Changing the AD Parallel Autopatch (adpatch) Batch Size

Only change the batch size if there are experience serious performance issues that can be resolved by increasing or decreasing the batch size. And before doing so, check that the execution plans are optimal.

  • High overhead of repeated SQL repeatedly performing the same operation => Increase batch size.

  • High overhead of repeated full scans/range scans that cannot be solved by tuning => Increase batch size

  • High contention between workers, occurring in highly defined peaks, at start of job (workers are doing same activity at same time/synchronized) => Decrease batch size so that workers become unsynchronized sooner.

  • Low worker utilization at end of a job due to large remainder batch => Decrease batch size.

Be aware that changing the overall batch size is a very imprecise action. Although it may improve the performance of one job, the performance of another job may regress.

The batch size for individual AD Parallel jobs can be changed by hard coding the batch size in the SQL script. However, (because of the possibility of extreme values being chosen or editing errors) this is not strictly supported.

e.g.

--l_batch_size                VARCHAR2(30) := &&2;
 l_batch_size                VARCHAR2(30) := 100000;

Or in the unified driver itself.

e.g.

#sql bom patch/115/sql cstmtaupg.sql none none none sqlplus_repeat &phase=upg+72 checkfile:bom:patch/115/sql:cstmtaupg.sql &un_inv &batchsize 
sql bom patch/115/sql cstmtaupg.sql none none none sqlplus_repeat &phase=upg+72 checkfile:bom:patch/115/sql:cstmtaupg.sql &un_inv 100000

7.5.12 Batch Size Calculation and its limitations on Autopatch (adpatch)

Note that (with the current version of AD_PARALLEL_UPDATES_PKG - adprupdb.pls 120.1 2006/05/08) there are some limitations on batch size and how it is calculated.

This means that the actual batch sizes can often be quite different from those entered.

The extent size can also cause some limitations with batch size.

The code in AD_PARALLEL_UPDATES_PKG calculates the number of blocks to be used for each batch as:

  1. B = (batch_size * Average_row_length)/8192

  2. It rounds this to the nearest 10.

  3. If the average row length is 0 or the result is 0 then a default of 200 blocks is used.

Each extent on the driving table is then split into "Update Units" containing B blocks each, with the remainder being placed into its own update unit. Each update unit is then used as a batch.

There are several limitations:

  • If B is greater than the extent size, the whole extent is used as an update unit. Consequently, the actual batch size cannot be greater than the rows that are stored in an extent.

  • When calculating the number of blocks (B) required for a batch, AD_PARALLEL_UPDATES_PKG does not take into account the storage overhead for the block header, percent free or row header, and so can underestimate the number of blocks required. This effect is magnified if the average row length is small.

  • If advanced compression or compression is used, this is not reflected in the average row length. The average row length will consequently be overstated, and a larger number of blocks used for each batch.

  • Rounding the number of blocks required to the nearest 10 adds an additional inaccuracy.

  • Splitting each extent into update units of B blocks also adds inaccuracy. If the number of remainder blocks is much less than B, this remainder batch will be much smaller. If there is a low number of update units per extent, this will have more influence on the batch size.

  • If the CBO Statistics are not populated, the smaller of either 200 blocks or the extent size will be used as a batch size.

This all means that the actual batch is usually smaller than that entered, and can vary in size significantly. It can be therefore be difficult to manage.

Looking at the following three examples will help explain all this.

Example 1
Average Row Length 200, batch size 10000, extent size 256 blocks, percent free and storage overheads result in only 70% of block being occupied by row data.

B = 244.1 blocks = 240 blocks (rounded to the nearest 10).

So that will result in two update units for each extent: one of 240 blocks, and one of 16 blocks.

This will in turn result in two batch sizes:

  • 1 x 240 blocks * (8192 bytes / average row length 200) * 70% = Average of 6881 rows

  • 1 x 16 blocks * (8192 bytes / average row length 200) * 70% = Average of 459 rows.

Example 2
Average Row Length 200, batch size 10000, extent size 16 blocks, percent free and storage overheads result in only 70% of block being occupied by row data.

B = 244.1 blocks = 240 blocks (rounded to the nearest 10).

However, the extent size is 16. So the batch is limited to 16 blocks.

This will result in a batch size of 16 blocks * (8192 / actual average row length 200) * 70% = Average of 459

Example 3
Average Row Length 200, batch size 10000, extent size 256 blocks, percent free and storage overheads result in only 70% of block being occupied by row data, compression ratio is 2.

B = 244.1 blocks = 240 blocks (rounded to the nearest 10). So that will result in two update units for each extent. One of 240 blocks and one of 16 blocks.

This will in turn result in two batches:

  • 1 x 240 blocks * (8192 bytes / average row length 200) * 70% * compression ratio 2 = Average of 13,762 rows

  • 1 x 16 blocks * (8192 bytes / average row length 200) * 70% * compression ratio 2 = Average of 918 rows.

7.5.13 Remainder Batch Issue if Batch Size is too large

There is a risk that a very large (actual) batch size could result in a job being unduly delayed by a small number of batches that process after all other batches.

Let’s refer to these as remainder batches.

If T = number of threads (AD workers), R = total rows to be processed, B = Batch size

And B is slightly less than R/T (e.g. R/(T+1))

Then this will result in T+1 batches assigned across T threads.

So one thread will have twice as much work as the others.

If 192,000 rows (of the driving table) were processed on 32 workers then R/T would be around 6,000.

So (assuming a large extent size) choosing a batch size of 10,000 might result in remainder batches. Therefore, in this case a much smaller batch size (such as 1,000) might be preferable.

However, do not reduce the batch size excessively, as small batch sizes can result in larger overheads on each batch.

7.6 Dynamic / Hyper Threading

Many hardware vendors offer a version of Dynamic Threading or Hyper Threading. In these cases the hardware can be configured to present virtual CPUs (also known as Logical CPUs) to the operating system (and ultimately database). This number of virtual CPUs will be much larger than the number of cores.

For example, the Oracle SPARC M8 processor has 32 cores, each of which handles up to 8 threads using unique dynamic threading technology, making a total of 256 threads (or virtual CPUs).

This is ideal for applications with a large number of online users, where the number of active sessions is many times the number of CPUs. Traditionally these active sessions would have shared the CPUs and there would have been a large amount of context switching. Dynamic threading can reduce context switching and thereby improve performance.

However, this is not needed for cases such as the Release 12.2.n upgrade, where there are a smaller number of active sessions, each with a high workload. In this case, the CPU capability available to each AD Parallel (or Parallel Execution) worker/thread, could be limited.

Clearly a large number of workers/threads (e.g. 256) all performing the same operation will result in massive contention that will far outweigh the benefits of greater CPU utilization.

So the recommendation is to configure the dynamic threading/hyper threading for maximum throughput per CPU cycle (i.e. 1 thread for each core).

Note that, since the T4 processor, SPARC servers use a critical threading mode that does this automatically.

It is also recommended to calculate the values for AD Parallel Workers, parallel_max_servers and job_queue_processes using the number of CPU cores and not the number of logical CPUs.

In addition, disable any power management as this will either prevent configuration of threading for maximum throughput, or throttle the CPU capacity.

7.7 Resource Manager

If using a Resource Plan to specify how resources are distributed among the different resource consumer groups, please review this to ensure that all sessions running during the upgrade downtime window have access to the full CPU resources.

Note that this does not apply to Post Upgrade Concurrent jobs, which will run alongside the normal online workload.

7.8 12.2 Middle Tier Sizing Guidelines

Managed instances JVM sizing should consider both memory and CPU domains.

On 64bit environments, allocating huge heap sizes is not recommended, but rather have more managed instances in the cluster to scale up to the target concurrency levels. For example, two managed instances with a 4 GB heap size for each will provide much better response times than one JVM with a total heap size of 8 GB

For Admin Server sizing, the default size of 512M is not enough for most installations, setting the XMS to at least 1 GB and the XMX to 2GB is recommended.

Allow an extra 3 GB of free memory for Online Patching.

An initial guidance on sizing can be found in

And in My Oracle Support Documents:

  • Managing Configuration of Oracle HTTP Server and Web Application Services in Oracle E-Business Suite Release 12.2 (Document 1905593.1)

  • Achieving Optimal Performance with Oracle E-Business Suite (Doc ID 2125596.1) – sections "Size The Middle Tier For Concurrency", "Size The Middle Tier For Concurrency (JVM Heaps)" and "Size The Middle Tier For Concurrency (Forms)"

Note: Upgrading to Oracle E-Business Suite Release 12.2 is performed with a single application tier node.

If possible, make sure that the one application tier node used during the upgrade process is configured with a larger RAM, more CPU and faster I/O (e.g. local SSD storage). More

7.9 Using "downtime mode" for Online Patching

It is recommended that the 12.2.n Release Update Pack (e.g. Oracle E-Business Suite 12.2.10 Release Update Pack (Patch 30399999)) is applied using "downtime" mode. And this will help reduce the elapsed time.

Note that this is only relevant for upgrades from 11i/12.0.x/12.1.x or new installs (Path A). It does not apply to upgrades from 12.2.2, 12.2.3, 12.2.4, 12.2.5, 12.2.6, 12.2.7, 12.2.8 or 12.2.9 (Path B).

7.10 Gathering CBO Statistics

For more information see My Oracle Support document "Best Practices for Gathering Statistics with Oracle E-Business Suite (Document 1586374.1)".

7.10.1 Gathering Oracle E-Business Suite Schema Statistics

In the pre-upgrade environment, schema statistics for all Oracle E-Business Suite schemas should have been gathered using FND_STATS (or the Gather Statistics concurrent program) with the GATHER_AUTO option.

The Release 12.2.n upgrade will gather all Oracle E-Business Suite schema statistics again (using adsstats.sql) towards the end of the R12.2.0 upgrade (last+63).

Note that adsstats.sql is not run in the R12.2.n RUPs.

It is important to be aware of the difference between adsstats.sql and adstats.sql. They are two distinct scripts:

After the Release 12.2.n upgrade, but prior to the system being available to users, gather CBO statistics again for all Oracle E-Business Suite schemas using FND_STATS (or the Gather Statistics concurrent program) with the GATHER_AUTO option. Do not use DBMS_STATS or Analyze.

In principle, CBO statistics for Oracle E-Business Suite schemas should not need to be gathered at any other time during the Release 12.2.n upgrade. However, in practice, as a result of performance issues, the statistics for specific objects may need to be gathered or deleted, especially on tables created by the Release 12.2.n upgrade or on temporary/transient tables.

7.10.2 Importing Oracle E-Business Suite Schema Statistics

If the adsstats.sql script is taking a significant amount of time to run, the Release 12.2.n upgrade time can be reduced by:

  • Exporting statistics gathered during test runs (by adsstats.sql - at same point: phase: last+63).

  • Importing these statistics instead of running adsstats.sql.

Only do this if the test environment has the same data volumes as the production environment (i.e. is a clone of production).

However, this does complicate the upgrade. So only do this if the estimated time savings will be significant, and there is confidence in adopting this approach.

If parallel_max_servers is set to a value much less than 2 x number of cores, consider increasing parallel_max_servers before resorting to export/import.

If importing statistics is also long running, it may have a performance issue that could be resolved by gathering fixed object and dictionary statistics prior to importing. Fixed object and dictionary statistics would normally only be gathered after the Release 12.2.n upgrade is complete (i.e. after R12.2.n RUP). So these are additional activities and their elapsed time should be deducted from any gains made by importing statistics.

The statistics can be exported using:

FND_STATS.backup_schema_stats(schema_name => ‘ALL’, statid => '<your statid>')

And imported using:

FND_STATS.restore_schema_stats(schema_name => ‘ALL’, statid => '<your statid>')

There are two reasons that FND_STATS.backup_schema_stats and restore_schema_stats should be used:

  • Only FND_STATS is supported with Oracle E-Business Suite.

  • FND_STATS.backup_schema_stats and restore_schema_stats will only backup and restore Oracle E-Business Suite schemas (around 85% of objects on a Release 12.2 database). In contrast, DBMS_STATS.export_database_stats and import_database_stats will export/import all schema statistics and also gather fixed object, dictionary and system statistics. So the workload will be considerably less if FND_STATS is used.

The following SQL scripts are suggested for exporting and importing statistics.

 
7.10.2.1 Exporting Statistics
set verify off 
whenever sqlerror exit failure rollback; 
whenever oserror exit failure rollback; 

DECLARE 
BEGIN 
   FND_STATS.BACKUP_SCHEMA_STATS(schemaname => 'ALL', statid => '<your identifier>'); 
EXCEPTION 
WHEN OTHERS THEN 
   RAISE_APPLICATION_ERROR(-20000, sqlerrm ||' Error while executing FND_STATS.BACKUP_SCHEMA_STATS package.'); 
END;
/ 

exit;
 
7.10.2.2 Importing Statistics
set verify off 
whenever sqlerror exit failure rollback; 
whenever oserror exit failure rollback; 

DECLARE 
BEGIN 
   FND_STATS.RESTORE_SCHEMA_STATS(schemaname => 'ALL', statid => '<your identifier>'); 
EXCEPTION 
WHEN OTHERS THEN 
   RAISE_APPLICATION_ERROR(-20000, sqlerrm ||' Error while executing FND_STATS.BACKUP_SCHEMA_STATS package.'); 
END;
/

exit;

However, at the time of writing there is an issue with DBMS_STATS.IMPORT_SCHEMA_STATS: if a schema has no tables, an exception will be raised that causes FND_STATS.RESTORE_SCHEMA_STATS to fail before completion. There are several Oracle E-Business Suite schemas with no tables. A workaround is to use the following script:

set verify off 
whenever sqlerror exit failure rollback; 
whenever oserror exit failure rollback; 

DECLARE

CURSOR schema_cur IS 
WITH schema_tabcount AS 
   (SELECT c5, COUNT(*) num_obj 
    FROM fnd_stattab 
    WHERE statid = <your identifier>' 
    GROUP BY c5 ) 
SELECT upper(oracle_username) sname 
FROM fnd_oracle_userid 
WHERE oracle_id BETWEEN 900 AND 999 
AND read_only_flag = 'U' 
AND EXISTS (SELECT 'exists' from schema_tabcount WHERE c5 = upper(oracle_username) AND num_obj >0) 
UNION ALL 
SELECT DISTINCT upper(oracle_username) sname 
FROM fnd_oracle_userid a, 
     fnd_product_installations b 
WHERE a.oracle_id = b.oracle_id 
AND EXISTS (SELECT 'exists' from schema_tabcount WHERE c5 = upper(oracle_username) AND num_obj >0) 
ORDER BY sname; 

BEGIN 

   FOR c_schema IN schema_cur LOOP 
      BEGIN 
         DBMS_STATS.IMPORT_SCHEMA_STATS(c_schema.sname, 'FND_STATTAB', <your identifier>', 'APPLSYS'); 
      EXCEPTION 
         WHEN OTHERS THEN 
            dbms_output.put_line('Error on '||c_schema.sname); 
            dbms_output.put_line(SUBSTR(SQLERRM,1,80)); 
      END; 
   END LOOP; 
EXCEPTION
   WHEN OTHERS THEN 
      raise_application_error(-20000, sqlerrm ||' Error while executing adsstats_restore');
END;
/ 

exit;

 

7.10.3 Further strategies for reducing adsstats.sql elapsed time

If the adsstats.sql job is taking a long time during the R12.2.0 upgrade there are two further approaches that may help for large environments, where there are tables with more than 100 million rows.

7.10.3.1 Sample specific long running tables at a lower percentage

Check the table FND_STATS_HIST to see which tables are taking the longest.

Use the following SQL to show the latest FND_STATS runs (request_id) with the number of tables analyzed. The <request_id> for the adsstats.sql run in the R12.2.0 upgrade can then be identified.

SELECT  request_id, 
count(*) tables_analyzed, 
TO_CHAR(MAX(last_gather_end_time),'DD-MON-YYYY HH24:MI:SS') max_end_time, 
MAX(last_gather_end_time) max_end_time_int 
FROM fnd_stats_hist 
WHERE object_type = 'CASCADE' 
AND last_gather_end_time IS NOT NULL 
GROUP BY request_id 
ORDER BY MAX(last_gather_end_time) DESC

Once the request_id has been identified, the following SQL will give the tables where gathering statistics took the longest along with information on the number of rows, blocks and sample size.

SELECT fsh.schema_name, fsh.object_name, fsh.object_type, 
ROUND((fsh.last_gather_end_time - fsh.last_gather_start_time)*24*3600,0) time_secs, 
to_char(fsh.last_gather_start_time,'DD-MON-YYYY HH24:MI:SS') start_time, 
to_char(fsh.last_gather_end_time,'DD-MON-YYYY HH24:MI:SS') end_time, 
dt.num_rows, 
dt.blocks, 
dt.avg_row_len, 
ROUND(DECODE(NVL(num_rows,0),0,NULL,(sample_size*100)/num_rows),0) est_pct, 
sample_size 
FROM fnd_stats_hist fsh ,
     dba_tables dt 
WHERE dt.owner (+) = fsh.schema_name 
AND dt.table_name (+) = fsh.object_name 
-- AND fsh.request_id = <request_id> 
-- AND fsh.object_type = 'CASCADE' 
AND fsh.last_gather_end_time IS NOT NULL 
ORDER BY (fsh.last_gather_end_time - fsh.last_gather_start_time)*24*3600 desc

The longest running ones are candidates for running at a lower estimate percentage (sample size) on the next run. By sampling at a lower percentage the accuracy of the CBO statistics will be reduced, so take care to monitor the performance of post R12.2.0 activities (e.g. R12.2.n RUP and post upgrade patches/jobs).

These tables can then be gathered at a lower percentage directly before running adsstats.sql. Or adsstats.sql can be edited directly to gather these tables at a lower percentage prior to gathering schema statistics (FND_STATS.GATHER_SCHEMA_STATISTICS).

The command is:

	fnd_stats.gather_table_stats('<owner>', <table_name>', <estimate_percent>, <parallel_degree>);

Where <parallel-degree> is the setting of parallel_max_servers (e.g. v_parallel in adsstats.sql itself).

It is advised that the following are used:

  • 3 percent for tables between 100 million and 1 billion rows

  • 1 percent for tables with more than 1 billion rows.

7.10.4 Incremental Statistics for Partitioned Tables

Incremental statistics gathering was a new Oracle 11gR2 DBMS_STATS feature and is fully supported by FND_STATS. Oracle derives global statistics by scanning only new or modified partitions rather than the whole table, which is highly beneficial with high volume Oracle E-Business Suite tables.

This will have no impact for partitioned tables during the upgrade itself, but it will speed up statistics gathering post go live.

It should be set for the following new partitioned tables: XLA_AE_HEADERS, XLA_AE_LINES, XLA_DISTRIBUTION_LINKS, XLA_EVENTS, XLA_TRANSACTION_ENTITIES, XLA_TRIAL_BALANCES, AP_DBI_LOG, AP_LIABILITY_BALANCE.

Run the following command to invoke it:

dbms_stats.set_table_prefs('<schema_name>', '<table_name>',     'INCREMENTAL', 'TRUE');

7.10.5 Locked Statistics

There may be a reason to lock the statistics on some tables. For example, the table could be a temporary or transient table (or Global Temporary table), where a representative set of statistics has been gathered, which should always be used and never overwritten.

In such cases, be aware that the adsstats.sql script will unlock the statistics and re-gather. And it is likely that these tables will be empty (or not have representative data volumes) during the Release 12.2.n upgrade.

It would be useful to obtain a list of these prior to the upgrade, so that they can be locked and excluded.

For Oracle E-Business Suite lock statistics using the DBMS_STATS.LOCK_TABLE_STATS procedure, and then exclude them from subsequent FND_STATS gathering jobs by using FND_STATS.LOAD_XCLUD_TAB procedure.

Once the statistics are locked using the DBMS_STATS.LOCK_TABLE_STATS procedure, FND_STATS will skip the tables on which statistics are locked in the subsequent statistics gathering jobs. Gather Schema / Table Statistics Concurrent Program will display a message in the request log file saying that statistics are locked on the table.

FND_STATS.LOAD_XCLUD_TAB will tell FND_STATS to skip the table from the Gather Schema Statistics Concurrent Program. It will seed an entry in the FND_EXCLUDE_TABLE_STATS table.

7.10.6 Fixed Object and Dictionary Statistics

These should have been gathered on the pre-upgrade environment.

Oracle Database uses the CBO for most of the dictionary SQL, so statistics on the dictionary objects and fixed objects must be gathered.

A fixed object (X$ tables) resides in memory only, and typically records the information about the instance or memory structures. The v$ dynamic performance views are defined on top of X$ tables e.g. V$SQL and V$SQL_PLAN.

Data dictionary tables (e.g. SYS.USER$, SYS.TS$, SYS.SEG$, SYS.OBJ$, SYS.TAB$, SYS.FILE) are stored on data files like normal application tables.

If there is internal SQL (on V$ views or on SYS/SYSTEM objects) appearing high in AWR and TKPROF reports, it is likely that dictionary and fixed object statistics need to be gathered.

Note that the FND_STATS API does not gather statistics for dictionary or fixed objects. The DBMS_STATS APIs need to be used.

7.10.6.1 Fixed Object Statistics

Fixed object statistics are gathered at the end of Online Patching Enablement (in script ADZDSTATS.sql). However, they are not gathered automatically at any other points during the Release 12.2 upgrade.

Typically, fixed object statistics will need to be gathered when there have been significant changes to fixed objects. For example, after:

  • A major database upgrade.

  • Platform upgrades, especially Exadata.

  • Application upgrades, or addition of a new application module.

  • Changes to the SGA/PGA configuration of the database.

  • Significant changes in the workload or number of sessions.

For optimal performance, statistics on fixed objects should be gathered when there is representative activity (typical load) on the system.

The command to run is:

	execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(no_invalidate=>FALSE);

Usually the "no_invalidate=>FALSE" argument will not be needed. However, the procedures DBMS_STATS.set_database_prefs, set_global_pefs, set_schema_prefs or set_table_prefs could have been used to set the default value for NO_INVALIDATE to TRUE

For the Release 12.2.n upgrade, the fixed object statistics should be gathered:

  • After any associated platform or database upgrade that is part of the overall Oracle E-Business Suite upgrade.

  • After any SGA/PGA parameters have changed.

  • After the Release 12.2.n upgrade, when there is representative activity on the system.

There are also changes to fixed objects due to due to Online Patching Enablement (and the underlying Edition-Based Redefinition). As a result internal SQL in Online Patching Enablement can sometimes be long running. Gathering statistics for specific fixed objects can help in these circumstances.

See the following My Oracle Support documents for more information:

  • Best Practices for Gathering Statistics with Oracle E-Business Suite (Document 1586374.1)

  • Fixed Objects Statistics (GATHER_FIXED_OBJECTS_STATS) Considerations (Document 798257.1)
7.10.6.2 Dictionary Statistics

Dictionary statistics are gathered at the end of Online Patching Enablement (in script ADZDSTATS.sql). However, they are not gathered automatically at any other points during the Release 12.2 upgrade.

Normally, the Dictionary Statistics need to be analyzed after a sufficient number of DDL operations have occurred in the database (for example, if there is a significant change in the database when a lot of new objects are created or rebuilt).

Use the following command to gather statistics for all system schemas, including SYS and SYSTEM.

execute DBMS_STATS.GATHER_DICTIONARY_STATS( estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, options => 'GATHER AUTO', no_invalidate=>FALSE);

Usually the "no_invalidate=>FALSE" argument will not be needed. However, the procedures DBMS_STATS.set_database_prefs, set_global_pefs, set_schema_prefs or set_table_prefs could have been used to set the default value for NO_INVALIDATE to TRUE.

It is not normally feasible to parallelize the gathering of dictionary statistics.

Dictionary statistics are gathered in the degree of the table (which will normally be 1). So they will normally be gathered serially. There is the option of setting degree=AUTO_DEGREE. But this parameter is ignored on options GATHER_AUTO, so GATHER would have to be used instead. So, parallel processing can be used at the expense of gathering for all tables (whether they need to be gathered or not). This is a choice for each customer.

For the Release 12.2.n upgrade, Dictionary Statistics should be gathered:

  • After any associated platform or DB upgrade that is part of the overall Oracle E-Business Suite upgrade.

  • After the Release 12.2.n upgrade.

  • After move to OATM.

There are also changes to dictionary objects due to due to Online Patching Enablement (and the underlying Edition-Based Redefinition). There are new dictionary objects and changes to the number of rows on other objects. As a result internal SQL in Online Patching Enablement, R12.2.n RUPs and other online patches can sometimes be long running. Gathering statistics for specific dictionary objects can help in these circumstances, particularly on editioning objects.

For more information see My Oracle Support document "Best Practices for Gathering Statistics with Oracle E-Business Suite (Document 1586374.1)".

7.10.6.3 Gathering Statistics for Specific Dictionary or Fixed Objects

If there are only a handful of internal SQLs with inefficient execution plans and only a few objects then specific objects could be targeted rather than gathering all dictionary or fixed object statistics.

e.g.

exec dbms_stats.gather_table_stats(ownname=>'SYS', tabname=>'OBJ$', no_invalidate=>false); 
exec dbms_stats.gather_table_stats(ownname=>'SYS', tabname=>'X$KQFP', no_invalidate=>false);
7.10.6.4 adstats.sql

This script:

  • Disables the Oracle Automatic Statistics gather jobs.

  • Gathers Dictionary and Fixed Object Statistics.

However, it can only be run with the database in restricted mode.

Do not use this script to gather fixed object or dictionary statistics as part of the Oracle E-Business Suite Release 12.2.n upgrade itself (although it may be run as part of an associated database upgrade). Use the APIs directly, to gather "Fixed Object Statistics" and "Dictionary Statistics".

It is recommended that this script is run as part of the database upgrades of Oracle E-Business Suite instances. Details are included in the upgrade instructions.

Note:

  • The adstats.sql script is not run automatically as part of the Release 12.2.n upgrade, and will not be in the R12.2.0 or other drivers.

  • For optimal performance, statistics on fixed objects should be gathered when there is a typical load in the system. This script is designed to be run only when the database is in restricted mode.

  • The script does not gather system statistics.

  • As mentioned earlier, adstats.sql is sometimes confused with adsstats.sql, which gathers Oracle E-Business Suite schema statistics.

8. Resolving Performance Issues

This section includes guidance on:

  • Diagnostics to gather during the upgrade, particularly diagnostics to gather when long running jobs and SQL are encountered.

  • Common (generic) issues and their solutions. Specific issues will be covered in bugs on My Oracle Support.

 

8.1 Diagnostics to be gathered during the upgrade

Note that the new diagnostics available for online patching (ADOP) are logs. These give timestamps for particular steps in the upgrade, or list errors that have occurred. However, they do not identify the SQLs or underlying events that caused the performance issue, so it is essential to use the general diagnostics listed below.

Clarification : In all cases it is only necessary to produce diagnostics during test runs (and not during the final production run).

8.1.1 Statistics_Level = ALL (or _rowsource_execution_statistics = TRUE)

This can be set using the command:

SQL>alter system set statistics_level='ALL'

This is the simplest way to see actual row source statistics (including elapsed time, physical reads, buffer gets etc) for each execution plan line (on SQLT and Display Cursor report). The alternative of SQL Trace and TKPROF requires editing standard code.

Note that the internal views v$sql_plan_statistics and v$sql_plan_statistics_all will not contain any actual row source statistics for execution plan lines if statistics_level = TYPICAL, even if timed_statistics = TRUE.

When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics, which include row source statistics.

Using this strategy will typically speed up the resolution of issues significantly and may also allow the correct solution to be identified first time.

Alternatively, the same actual execution statistics can be collected by setting the initialization parameter _rowsource_execution_statistics=TRUE (with statistics_level = 'TYPICAL'). This gives a lower overhead than statistics_level=ALL.

Many technical architects and DBAs at customers (or implementing partners) can be resistant to setting statistics_level = ALL (or _rowsource_execution_statistics = TRUE), believing that this can slow down performance significantly.

Two points are relevant here:

  • Although setting statistics_level = ALL / _rowsource_execution_statistics = TRUE will have some performance impact, it is likely to be small and not significant. The Release 12 upgrade is made up of batch processes, and so the statistics workload is a much lower proportion of the total.

  • Even if the performance impact is significant, the goal is to reduce the elapsed times for the latter dry runs and go live (when it will be feasible to revert statistics_level / _rowsource_execution_statistics to its previous value). So suffering an increase in elapsed time during an early stage of testing is not an issue.

So there may be a small impact on elapsed time and the work that needs to be done initially, but it will help to subsequently reduce the elapsed time and amount of re-work that needs to be done.

Note that setting statistics_level to ALL while AWR is enabled could significantly increase the number of rows inserted to the WRH$_LATCH_CHILDREN table. So monitor the SYSAUX tablespace to ensure that it does not run out of space.

8.1.2 SQL Specific

Once AWR or TKPROF have been used to identify the long-running SQL (in the long-running jobs), the output below should be provided:

Note that for these to be useful, statistics_level should be set to ALL (or _rowsource_execution_statistics = TRUE). The actual statistics are needed to be able to identify the expensive execution plan steps or execution plans that are moderately sub-optimal. (The execution plan itself may look OK without this information).

8.1.2.1 Display Cursor Report

This displays the actual execution plan of any cursor loaded in the cursor cache. At the basic level it shows the runtime execution plan. However, the format ALL also includes extra information such as pruning, parallel execution, predicate, projection, alias and remote SQL information.

This should be run (as soon as possible) whilst the jobs are running or very shortly afterwards. If running of the Display Cursor Report is delayed, the cursor may have been flushed from memory or invalidated and no actual statistics will be available and the report will display no data.

This useful script can be used to automatically produce Display Cursor reports for the top SQLs during the upgrade.

The +ALLSTATS option (which includes IOSTATS and MEMSTATS) will include actual statistics for each execution plan step. These include:

  • Elapsed time

  • Physical reads

  • Buffer gets

  • Memory used (in PGA) for memory intensive operations (such as hash-joins, sorts, bitmap operators etc).

However, this additional information is only provided if statistics_level=ALL / _rowsource_execution_statistics = TRUE

Note that SQLT with XTRACT will also report actual row source statistics in the same circumstances. However, display_cursor provides a simpler view of the information. It can also be run during the upgrade, while the long running SQL is in progress, without much of an overhead.

The report can be produced by running the following SQL script:

SET pages 0
SET lines 300
SET LONG 10000
SET LONGCHUNKSIZE 10000
SPOOL <report_name>.txt
SELECT * FROM TABLE(dbms_xplan.display_cursor('<sql_id>', NULL, 'ALL +ALLSTATS'));
SPOOL OFF;

For more information see the "Display Cursor" section in My Oracle Support document "Oracle E-Business Suite Performance Guide (Document 1672174.1)"

If the SQL is no longer in memory, but is in the AWR, use the Display AWR report (DBMS_XPLAN.DISPLAY_AWR) instead. However, this does not report on actuals: it does not have a +ALLSTATS option, and there are no actual statistics for execution plan steps stored in AWR.

Note that SQLT with XTRACT method will not report on actual statistics in this case either.

Note that the display cursor and AWR reports only show the sql_text (first 1000 characters) and not the full_text.

So, if necessary, run the following SQL script to obtain the full SQL text.

SET pages 0
SET lines 300
SET LONG 10000
SET LONGCHUNKSIZE 10000
SPOOL <report_name>.txt 
SELECT sql_id, sql_text, sql_fulltext FROM v$SQL WHERE sql_id = '<sql_id>';
SPOOL OFF;
8.1.2.2 SQL Monitor Report

The main advantage of this is that it gives a good view of how parallel SQL/DML performs across stages of the plan and parallel child (secondary) processes.

It can also give a good idea of the actual executions and row counts for each execution plan line even if "statistics_level" initialization parameter is not set to ALL ( or "_rowsource_execution_statistics" is not set to TRUE) at the time the SQL is executed.

It can be run during the upgrade, while the long running SQL is in progress (or shortly afterwards), without much of an overhead.

It can be produced by running the following SQL script:

set trimspool on
set trim on
set pages 0
set long 10000000
set longchunksize 10000000
set linesize 200
set termout off
spool sql_monitor_for_<sql_id>.htm
variable my_rept CLOB;
BEGIN 
   :my_rept := dbms_sqltune.report_sql_monitor(sql_id => '<sql_id>', report_level => 'ALL', type => 'HTML');
END;
/

print :my_rept

spool off;

set termout on

For more information see the "SQL Monitor Report" section in My Oracle Support document "Oracle E-Business Suite Performance Guide (Document 1672174.1)"

8.1.2.3 SQLHC or SQLT

There are occasions when SQLHC should be used rather than SQLT:

  • For example a customer may object to installing SQLT on their production environment or they may be concerned about the performance impact on a production system.

  • It may be necessary to obtain more information on a SQL during an upgrade patch/pack.

  • Or the customer may not have installed SQLT yet.

  • SQLT is more complicated and may require more explanation, so it may be quicker and simpler to ask for SQLHC.

There are situations where SQLT is preferred over SQLHC:

  • The SQL ID (and runtime execution plan) may not be in memory or AWR. So SQLHC will not provide any output (and SQLT with XPLAIN or XECUTE method is required).

  • More information is required.

8.1.2.4 SQLHC

When SQLs with possible sub-optimal execution plans have been identified then use SQLHC (sqlhc.sql).

This provides html reports, which contain most of the data required to identify the reasons why the CBO chose a sub-optimal execution plan. e.g. Health Check (Observations), CBO Statistics, Metadata for tables and indexes, Execution plans, Runtime Statistics, System/Instance (Initialization) Parameters, SQL Profiles/Baselines associated with the SQL and SQL Activity Report.

It saves a huge amount of time by providing all information in one action (and in one zip file sqlhc_YYYYMMDD_HHMISS_SQLID.zip).

It does not require any installation on the environment (other than the script file itself) and has a negligible performance impact. If necessary it can be run whilst the upgrade is in progress.

It is particularly useful if engineers/analysts do not have access to the instance.

Note that SQLHC will only provide actual row source statistics if the "statistics_level" initialization parameter is set to ALL ( or "_rowsource_execution_statistics" is set to TRUE) at the time the SQL is executed.

See the following My Oracle Support documents for more information:

8.1.2.5 SQLT Output using the XTRACT method

This uses a significant amount of system resources, so should not be run during the upgrade. Instead, Display Cursor report and SQLHC can be used to obtain much of the information that is included in the SQLT output.

See the following My Oracle Support documents for more information:

The SQLT should be provided on the same environment where the performance issue was observed, and should be run as soon after the relevant program/process as possible.

Be aware of any actions that may alter the data that SQLT is capturing (that is, actions that take place after the observed performance issue, but before SQLT is run). For example, statistics being gathered, removed, locked, imported, or data being deleted from temporary or interface tables.

To run SQL with XTRACT the sql_id or hash_value will be needed.

The TKPROF will give the sql id and hash value.

The raw trace gives the sql_id (sqlid=).

Other methods of obtaining the sql_id include using AWR, Oracle Enterprise Manager, and directly querying V$SQL.

The SQLT provides execution plans, CBO statistics, database parameters, CBO parameters, performance statistics, and metadata (schema object definitions and so on) associated with the SQL.

Depending on the SQLT parameters it can also contain supporting information such as AWR reports, ASH Reports, ADDM Reports, CBO (10053) trace, SQL Monitor report, EBS Initialization Parameters healthcheck report (bde_chk_cbo), Test Case Builder and SQL Tuning Advisor.

It is particularly useful if access to the instance is not possible. Even if access is possible, it gives all the information in one place (one zip file).

The XTRACT method is needed because:

Note that SQLT runs AWR and ASH reports. Some dictionary objects (particularly WRH$_LATCH_CHILDREN, especially if statistics_level is set to ALL) will have grown significantly during the upgrade. So, it may be necessary to gather fixed object and dictionary statistics before running SQLT.

SQLT can take quite a while to run.

To reduce the workload, it is recommended that the following are run (from SQL*Plus) before running sqltxtract.sql:

To disable Test Case Builder TCB and/or SQL Tuning Advisor

EXEC sqltxplain.sqlt$a.set_param('test_case_builder', 'N'); 
EXEC sqltxplain.sqlt$a.set_param('sta_time_limit_secs', '30');

To disable the automatic export of a test case repository

EXEC sqltxplain.sqlt$a.set_param('export_repository', 'N');

If SQLT still takes a long time, and the schema objects used by the SQL contain a large number of sub-partitions, the granularity of the data collected can be reduced as follows:

EXEC sqltxplain.sqlt$a.set_param('c_gran_segm', 'PARTITION'); 
EXEC sqltxplain.sqlt$a.set_param('c_gran_cols', 'PARTITION'); 
EXEC sqltxplain.sqlt$a.set_param('c_gran_hgrm', 'PARTITION');

Note that these commands can all be run as APPS. They do not need to be run as user SQLTXPLAIN.

These values are stored in a table called SQLTXPLAIN.SQLI$_PARAMETER. Once they are set, they do not need to be re-set for each execution of SQLT. The current values can be checked by querying this table.

To reduce the time further the counting of rows on tables can be disabled, by running the following. However, information on the actual number of rows in each table will be lost.

 EXEC sqltxplain.sqlt$a.set_param('count_star_threshold', '0');

All of this assumes that a SQLT version greater than 1.4.4.4 (April 2, 2012) is being used.

8.1.3 SQL Trace – SQL Script Specific

If it is not possible to resolve a performance issue from the Display Cursor report, SQL Monitor report and AWR diagnostics, then obtain a 10046 SQL Trace (level 16 – ALL_EXECUTIONS (with waits)) for the script/job on the next test run.

Level 16 (ALL_EXECUTIONS) is required because sometimes (especially for AD Parallel jobs) performance issues do not occur until later executions and the first execution (default on level 8) does not give enough information.

SQL Trace can be enabled at system level. However, this will produce a large number of trace files, and it may be difficult to locate the correct traces. (Tip: on UNIX, use grep to search files for key SQL strings or script/module names).

Although it is possible to SQL Trace other sessions using DBMS_MONITOR.

See My Oracle Support document "Oracle E-Business Suite SQL Trace and TKPROF Guide (Document 1674024.1)" section "Obtaining Traces (TKPROF) in E-Business Suite" > "For another database session using DBMS_MONITOR"

There are some limitations in this case.

  • To obtain the diagnostics needed (parse, execute and fetch statistics; row source statistics) then the trace should be enabled before the process or SQL has started.

  • There must be a way of identifying the sessions to be traced. E.g. client identifier (which is not set during the R12.2.n Upgrade scripts), service, module (which is sometimes set), action, session ID and Serial Number, OS Process ID, Oracle Process ID or Process Name. These may not be known before the job/script is run.

  • Also, there could be multiple parallel children (secondary) processes or AD workers.

For this reason it is advisable to enable SQL Trace for specific scripts by temporarily editing the SQL script as follows:

Add the following before the main body of the script.

	alter session set tracefile_identifier='<identifier string>'; 
	alter session set events '10046 trace name context forever, level 16'; 
	alter session set statistics_level='ALL' ; 
	alter session set max_dump_file_size='UNLIMITED';

Add the following after the main body of the script.

	alter session set events '10046 trace name context off';

Another advantage of enabling the trace at script level is that each script can be given different trace identifiers, so finding the traces becomes easy.

For more information see My Oracle Support document "Oracle E-Business Suite SQL Trace and TKPROF Guide (Document 1674024.1)", especially section "Obtaining Traces (TKPROF) in E-Business Suite" > "From SQL*Plus".

8.1.4 AWR Snapshot

Typically, the snapshot interval should be 30 minutes (the default is 1 hour). This can be altered to suit the overall elapsed time of the upgrade/window. If the upgrade/window is shorter then smaller delays are significant, and a shorter snapshot may be needed. For upgrades taking 8-15 hours, a snapshot of 15 minutes would be more suitable. When running AWR reports for specific jobs the report should include the period that the job was running, without containing too much before or afterwards.

8.1.5 AWR Retention

The AWR retention period should be long enough to cover the duration of the upgrade run and a significant period afterwards (to gather diagnostics and analyze). The suggestion is N+7 days, where N is the estimated upgrade time, but a longer period will provide more time to gather subsequent diagnostics and statistics.

8.1.6 AWR Reports

Obtain AWR reports for:

  • The whole period that the upgrade is running.

  • For the duration of long-running jobs (i.e. between the snapshots taken just before the job starts and just after it finishes).

  • Each individual snapshot.

Initially, the following can be identified:

  • Long running SQL (SQL Statistics/SQL Ordered By)

  • Contention and bottlenecks (Top 5 Timed Foreground Events/Foreground Wait Events)

  • CPU utilization

However, many other useful statistics can be obtained from AWR reports, depending on the situation e.g. :

  • Advisory Statistics on SGA/PGA sizing etc.

  • Segment Statistics to identify the objects with the highest physical/logical reads or waits (Row Lock, ITL, Buffer Busy).

  • Wait Event Histogram to identify the distribution of wait times and how serious contention is.

If there are high levels of a particular wait(s), first check to see if it only occurs with particular long-running SQL and jobs, before assuming that it is a system configuration or resource issue.

awrrpt.sql will typically be used to generate the AWR reports. Always choose HTML report type. On an Oracle RAC instance, awrrpti.sql will usually suffice, as the upgrade will be run on one Oracle RAC node only.

AWR reports can be automated. This is useful if producing a large number of AWR reports, particularly for successive snapshots. See the "Automating AWR Reports" section in My Oracle Support document "Performance Diagnosis with Automatic Workload Repository (Document 1674086.1)".

The following can also be run:

  • awrsqrpt.sql (or awrsqrpi.sql) to report on the execution plans for particular long-running SQL.

  • ashrpt.sql (or ashrpti.sql) to report on the Active Session History for particular SQL. This gives useful information on the waits and events for each row source or object.

Note that some fixed objects and dictionary objects (particularly WRH$_LATCH_CHILDREN) will have grown significantly during the upgrade. This is especially the case if statistics_level = ALL, or there is a high retention period or a short snapshot interval.

So fixed object and dictionary statistics may need to be gathered before running AWRs.

See My Oracle Support document "Performance Diagnosis with Automatic Workload Repository (Document 1674086.1)" for more information.

8.1.7 AWR Export

The AWR can be exported to a dump file (data pump export file) using $ORACLE_HOME/rdbms/admin/awrextr.sql.

The $ORACLE_HOME/rdbms/admin/awrload.sql script can be used to load the export dump into a local database, where they can be analyzed (using SQL) to discover where waits occur (job, SQL, object etc), and any patterns.

The key tables are DBA_HIST_ACTIVE_SESS_HISTORY, DBA_HIST_SEG_STAT_OBJ, DBA_HIST_SYSTEM_EVENT , DBA_HIST_SEG_STAT, DBA_HIST_WAITSTAT, DBA_HIST_SQLSTAT, DBA_HIST_SQLTEXT.

Note that from 12c Release 2, in a multi-tenant environment, the DBA_HIST views can also be interchanged with the AWR_ROOT views and AWR_PDB views at the CDB level and the PDB level respectively.

Alternatively, SQL can be run directly on the source environment (i.e. without the need to run export/import).

One advantage of the export /import strategy is that diagnostics can be kept after the original environment has been refreshed.

See Oracle Database Performance Tuning Guide, Transporting Automatic Workload Repository Data.

8.1.8 AD Job Timing Report

This reports:

  • Number of successful, failed, deferred, re-started or skipped jobs.

  • The top 100 time consuming jobs.

  • The failed, deferred, re-started and skipped jobs.

  • The timing of each upgrade phase, with the total number of jobs, and the number deferred, re-started and skipped.

However, it only reports the Top 100 Time Consuming Jobs, and for AD Parallel jobs it considers each worker to be a different job. This means it may only report a handful of jobs. An alternative is to query the AD_TASK_TIMING table for long running jobs directly.

When ADOP, AutoPatch or AD Administration is run, it automatically generates an AD Job Timing report (adt<session_id>.lst). The contents of this report can be accessed from Oracle Application Manager, or reports can be obtained for completed upgrade sessions from the APPL_TOP/admin/<SID>/out directory. The report is called adt<session_id>.lst.

The AD Job Timing Report can also be run for AD Administration jobs from the command line.

$ cd $APPL_TOP/admin/<SID>/out 
$ sqlplus <APPS username>/<APPS password> @$AD_TOP/admin/sql/adtimrpt.sql \ <session id> <output file>

Where <session_id> is the session of the timing statistics required, and <output file> is the name of the file where the statistics will be written.

$AD_TOP/admin/sql/adtimdet.sql can also be run in a similar way. This gives details on all jobs ordered by phase or elapsed time. This is useful for finding out how long any job took to run, and also where the "Top 100 Time Consuming Jobs" is dominated by multiple workers of a few jobs.

Note that the SQL scripts may be in $AD_TOP/sql (not admin/sql).

See "Oracle E-Business Suite Maintenance Guide Release 12.2" for more information.

8.1.9 AD Parallel tables export

The key tables to export (either using Data Pump Export or Original Export) are:

  • AD_PARALLEL_UPDATES

  • AD_PARALLEL_UPDATE_UNITS

  • AD_TASK_TIMING

These can then be imported onto a local database using either Data Pump Import or Original Import, and then be analyzed.

The AD_PARALLEL_UPDATES, AD_PARALLEL_UPDATE_UNITS tables can give information on the actual rows processed, the number of batches, progress over time (to spot jobs that slow down) and any significant irregularity in rows processed/time taken for each batch that might suggest locks/sleeps or data distribution issues.

AD_TASK_TIMING gives start and end times of jobs and workers, which can help identify all long running jobs, and match long running SQL and performance issues (on AWR) with specific jobs. It also helps identify low worker utilization (and potentially resource utilization) due to phasing waits.

The AD Job Timing Report only reports the Top 100 Time Consuming Jobs. However, for AD Parallel jobs each worker is reported as a separate job, so if there are a high number of AD workers (for example, 16, 24 or 32), it may only report a handful of jobs. In this case, the AD_TASK_TIMING table can be queried directly to identify all the long running jobs.

See the "Useful Scripts" part of this document for reports that will show all long running jobs and phasing waits.

8.1.10 AD Utility and Worker Logs

The AD utility and worker logs can also be useful for diagnostics, giving more detail about what happened and when. The AD workers logs (adwork001.log, adwork002.log ...) will give the activities carried out by each worker and the timings.

All AD utilities record their processing actions and any errors that they encounter in log files. Many utilities prompt for the name of the log file, but default to <utility_name>.log. For example, for AD Administration the default log file is adadmin.log. For AutoPatch, it is adpatch.log.

AD utilities that process jobs in parallel also write details to worker log files. The adwork<number>.log files (adwork001.log, adwork002.log...) reside in the $APPL_TOP/admin/<SID>/log directory, where <SID> is the value of the ORACLE_SID or TWO_TASK variable (UNIX).

For Online Patching (ADOP) the AD Utility and Worker logs are located in the non-editioned file system (fs_ne) in the <INSTALL BASE>/fs_ne/EBSapps/log/adop directory e.g.

/u01/PROD/fs_ne/EBSapps/log/adop

See ADOP Logs and Diagnostics.

8.1.11 File Versions

When an SR or Bug is raised, Oracle Support and Development will ask for the version of the job (file) that has the issue, or the version of code (file) used by the job.

It may be necessary to check what is done in a particular job, or the exact metadata/object definition in a file. When doing this, be sure to identify the version of the files used in the upgrade.

For example, for an R12.2.n upgrade, the version present in the file system or database after all the upgrade steps – including R12.2.n RUP and post upgrade steps – may be different from the one used during the R12.2.0 upgrade.

To find out the version of a file used during the R12.2.0 upgrade, check the unified driver used for that part of the upgrade: the same principle applies to the 12.2.n RUP. For example:

$ cat u_merged.drv|grep –A5 cstpostimportaad.sql

Note : Do not rely on the version of the file or object in the file system or database, as it could be a later version applied by a subsequent patch.

8.1.12 Online Patching Enablement - Specific Diagnostics

The Online Patching Enablement patch is applied using AutoPatch (adpatch). In addition to the general diagnostics above the output from the following script will be useful during Online Patching Enablement:

$ sqlplus apps @$AD_TOP/sql/ADZDSHOWDDLS.sql

8.1.13 ADOP Logs and Diagnostics

All the ADOP logs are located on the non-editioned file system (fs_ne) in the <INSTALL BASE>/fs_ne/EBSapps/log/adop directory e.g.

/u01/PROD/fs_ne/EBSapps/log/adop

Each cycle of ADOP creates a subdirectory corresponding to the patch session ID, e.g.

/u01/PROD/fs_ne/EBSapps/log/adop/n

Where n is the session ID.

It is easiest and quickest to produce a zip of the entire directory.

The main files of interest are the ADOP logs (e.g. adop_YYYYMMDD_HHMISS.log).

But the adzdshowlog.out, adworker*.log, u*.log, u*.lgi, admrgpch*.log files are all useful and under the same path.

When running ADOP the on screen terminal output will mention which ADOP session ID is in use. e.g.

/u01/PROD/fs_ne/EBSapps/log/adop/9/apply_20121011_024437

The session ID directory will contain a trace file for each phase (e.g. adop_20130316_091340.log) and a corresponding log directory for each phase containing other logs (e.g. apply_20130316_091340).

The timestamp of the trace file and the corresponding log directory will match.

8.1.13.1 Non ADOP Logs

The same log directory for each phase (e.g. apply_20130316_091340) also contains some AD Utility and worker logs.

These include adrelink.log, adlibin.log, adlibout.log, adworknnn.log. The most useful are the adworknnn.log files that show the jobs run on each AD Parallel worker along with timestamps.

8.1.14 Online Patching Log Analyzer Utility

This is delivered in R12.AD.C.Delta.n (since R12.AD.C.Delta.4).

This utility analyzes adop log directories for errors and warnings, and displays messages to help the user quickly identify any problems that may have occurred during an adop run. It thereby offers an alternative to reviewing log files manually.

The Log Analyzer utility can be run without options:

To scan all log directories of the latest adop session for errors:

$ adopscanlog

The utility can also be run with various options. Examples include:

  • To scan log directories relating to the latest run of adop in the latest session:
    $ adopscanlog -latest=yes
  • To scan log directories relating to the latest run of the specified phase, in the latest session:
    $ adopscanlog -latest=yes -phase=<phase_name>
  • To scan all log directories of a given session (represented by a session_id) for errors:
    $ adopscanlog -session_id=<number>
  • To see a complete list of supported parameters:
    $ adopscanlog -help

8.1.15 adzdshowlog.out

This reports the contents of the AD_ZD_LOGS table. This contains messages on the progress of online patching with timestamps. The contents of this table will be truncated every time cleanup/prepare phase is run.

This can also be obtained for previous phases by running the following script:

$ sqlplus apps @$AD_TOP/sql/ADZDSHOWLOG.sql

Or running the SQL

SELECT * FROM ad_zd_logs ORDER BY log_sequence desc;

 

8.1.16 Check the current status of the adop cycle

To check the status of the current ADOP cycle, source the run filesystem environment file and run command

adop -status

Usage:

adop -status generates a summary report

adop -status <sessionID> generates a summary report for that session ID

adop -status -detail generates a detailed report

8.1.17 SQL to determine status of ADOP phases

The following SQL statement will show the status for each adop phase along with its corresponding session id.

SELECT adop_session_id, prepare_status, apply_status, finalize_status, 
       cutover_status, cleanup_status, abort_status, status, abandon_flag, node_name 
FROM ad_adop_sessions 
ORDER BY adop_session_id;

This is effectively a history of online patching in an environment.

The following statuses apply to all phases:

Y : the phase is done

N : the phase has not been completed

X : the phase is not applicable

R : the phase is running (in progress)

F : the phase has failed

P : (is applicable only to APPLY phase) at least one patch is already applied for the session id

C : the status of this ADOP session has completed

Note: Numerical statuses are only relevant for the cutover phase. These status values are updated when a step has completed, and are as follows:

0 : cutover/force_shutdown has started

1 : "force_shutdown" step has successfully executed

3 : "db_cutover" step has successfully executed

4 : "fs_cutover" step has successfully executed

6 : "force_startup" step has successfully executed

Cutover statuses

cutover_status='Y' 'COMPLETED'

cutover_status not in ('N','Y','X') and status='F' 'FAILED'

cutover_status='0' 'CUTOVER STARTED'

cutover_status='1' 'SERVICES SHUTDOWN COMPLETED'

cutover_status='3' 'DB CUTOVER COMPLETED'

cutover_status='D' 'FLIP SNAPSHOTS COMPLETED'

cutover_status='4' 'FS CUTOVER COMPLETED'

cutover_status='5' 'ADMIN STARTUP COMPLETED'

cutover_status='6' 'SERVICES STARTUP COMPLETED'

cutover_status='N' 'NOT STARTED'

cutover_status='X' 'NOT APPLICABLE'

8.1.18 Check AD and TXK C Patch levels

SQL can also be directly used to check the status of ADOP phases or AD/TXK Patch Levels.

Run :

SELECT codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');

8.1.19 Long Running SQL, Contention and Tuning

For long running jobs or SQL, it is best to start by first investigating if good execution plans are being used. A poor execution plan (or even just one that is moderately sub-optimal) can be the root cause of contention, especially if that contention only occurs during a particular job.

Often a sub-optimal execution plan can cause contention and/or additional waits because it is accessing unnecessary database blocks. This might be because of a full table scan, or an index that is not particularly selective. This increases the probability of different AD workers accessing the same blocks at the same time. Once the execution plan no longer accesses those unnecessary blocks, the contention/waits may reduce or disappear.

However, to identify sub-optimal execution plans, check the actual row counts, buffer gets, and elapsed time of each execution plan step. Quite often execution plans that look good at first glance (e.g. use selective indexes) may not be close to the best. Also note that an execution plan that is quite good may still have one step where some unnecessary blocks are accessed, increasing the risk of contention as workers or threads are added. This is why setting statistics_level = ALL / _rowsource_execution_statistics = TRUE is advised.

8.1.19.1 A Little Contention is good

Once any unnecessary contention caused by sub-optimal execution plans has been removed, a small amount of contention (e.g. 5 to 15% on particular waits) between AD Parallel or Parallel Execution sessions can be a useful indicator that the most is being obtained from the available resources.

However, measuring the level of contention for some events is difficult. For typical contention waits due to sessions accessing the same block (in buffer), latch or row, the whole of the wait can be considered to be contention. But for waits on resources such as disk (e.g. db file sequential read), there is a minimum average wait time even when the resource is not busy. In such a case, look at how long the wait has increased, and the length/fatness of the tail on the histogram.

8.1.19.2 Shifting Bottlenecks

Be aware of shifting bottlenecks. Sometimes an action is taken that reduces a particular wait significantly, but another wait significantly increases. The obvious assumption is that the action has caused the new wait. However, this may not be the case: it may just have revealed the wait.

This does not mean that action should not be taken to remove the new wait. It just means that reversing the original action may not be the solution.

Here is an illustration of this effect.

Bottleneck Before

Sessions arrive at first event in random pattern and they have to wait. So the first event is a bottleneck. Because the first event/bottleneck is in effect regulating the flow, there are few waits at the second event.

Bottleneck After

When the first bottleneck is removed, the sessions arrive at the second event in a random pattern and have to wait. The second event then becomes a bottleneck.

8.2 Useful Scripts

Note that from 12c Release 2, in a multi-tenant environment, the DBA_HIST views can also be interchanged with the AWR_ROOT views and AWR_PDB views at the CDB level and the PDB level respectively.

8.2.1 Top SQL between two snapshots in Cursor Cache or AWR

If SQL is still in memory (cursor cache) the following can be used to identify long running SQLs that may not have been written to the AWR yet (at last snapshot).

SELECT * FROM 
   (SELECT 
      ss.sql_id, 
      ROUND(SUM(ss.elapsed_time/1000000),0) elapsed_time_secs, 
      ROUND(SUM(ss.cpu_time/1000000),0) cpu_time_secs, 
      SUM(ss.disk_reads) disk_reads, 
      SUM(ss.direct_writes) direct_writes, 
      SUM(ss.buffer_gets) buffer_gets, 
      SUM(ss.px_servers_executions) px_server_execs, 
      SUM(ss.rows_processed) rows_processed, 
      SUM(ss.executions) executions, 
      SUM(ss.application_wait_time) apwait_secs, 
      SUM(ss.sharable_mem) sharable_mem, 
      SUM(ss.total_sharable_mem) total_sharable_mem
   FROM v$database d -- From DB 12.1. onwards
        v$sqlstats ss 
   WHERE ss.con_dbid = d.con_dbid -- From DB 12.1. onwards
   GROUP BY ss.sql_id 
   ORDER BY 2 DESC) 
WHERE ROWNUM <= 100;

The following SQL script will report the longest running SQLs between two AWR snapshots.

SELECT * FROM 
   (SELECT 
      dhs.sql_id, 
      ROUND(SUM(dhs.elapsed_time_delta/1000000),0) elapsed_time_secs, 
      ROUND(SUM(dhs.cpu_time_delta/1000000),0) cpu_time_secs, 
      SUM(dhs.disk_reads_delta) disk_reads, 
      SUM(dhs.buffer_gets_delta) buffer_gets, 
      SUM(dhs.px_servers_execs_delta) px_server_execs, 
      SUM(dhs.rows_processed_delta) rows_processed, 
      SUM(dhs.executions_delta) executions, 
      ROUND(SUM(dhs.iowait_delta/1000000),0) iowait_secs, 
      ROUND(SUM(dhs.clwait_delta/1000000),0) clwait_secs, 
      ROUND(SUM(dhs.ccwait_delta/1000000),0) ccwait_secs, 
      ROUND(SUM(dhs.apwait_delta/1000000),0) apwait_secs 
   FROM dba_hist_sqlstat dhs ,
        v$database d 
   WHERE dhs.dbid = d.dbid 
   AND dhs.con_dbid = d.con_dbid -- From DB 12.1 onwards
   AND snap_id > <begin snap> 
   AND snap_id <= <end snap> 
   GROUP BY dhs.sql_id 
   ORDER BY 2 DESC) 
WHERE ROWNUM <= 100;

Where <begin snap> and <end snap> are the start and end snapshot IDs.

Note that from 12c Release 2, in a multi-tenant environment, the DBA_HIST views can also be interchanged with the AWR_ROOT views and AWR_PDB views at the CDB level and the PDB level respectively.

8.2.2 Identify when a piece of SQL ran

The following SQL will show when a particular piece of SQL ran (i.e. between which snapshots). This is useful in matching SQLs to jobs.

SELECT  
   dhs.sql_id, 
   dsn.snap_id, 
   dsn.begin_interval_time, 
   dsn.end_interval_time, 
   ROUND(SUM(dhs.elapsed_time_delta/1000000),0) elapsed_time_secs 
FROM dba_hist_sqlstat dhs ,
     v$database d ,
     dba_hist_snapshot dsn 
WHERE dhs.dbid = d.dbid
AND dhs.con_dbid = d.con_dbid -- From DB 12.1 onwards 
AND dsn.snap_id = dhs.snap_id 
AND dsn.dbid = dhs.dbid 
AND dsn.instance_number = dhs.instance_number 
AND dhs.sql_id = '<sql_id>' 
AND dsn.snap_id > <begin_snap> 
AND dsn.snap_id <= <end_snap>  
GROUP BY dhs.sql_id, dsn.snap_id, dsn.begin_interval_time, dsn.end_interval_time 
ORDER BY dsn.snap_id;

Where <begin snap> and <end snap> are the start and end snapshot IDs.

Note that from 12c Release 2, in a multi-tenant environment, the DBA_HIST views can also be interchanged with the AWR_ROOT views and AWR_PDB views at the CDB level and the PDB level respective

8.2.3 Long Running Upgrade Jobs

Note that the "Top 100 Time Consuming Jobs" section of the standard adtimrpt.sql report lists all workers for AD Parallel jobs separately. So the top 100 can be dominated by a handful of jobs.

The following SQL can be used to list all jobs in order of maximum elapsed time (descending), but reporting all workers of an AD Parallel job in one line. It only reports completed jobs.

Note that <session_id> is the ID for the upgrade "session" and not a user session.

Be aware of jobs that are called multiple times in the same phase (e.g. akload.class, LoadMap.class, XDOLoader.class).

SELECT 
   phase, 
   phase_name, 
   product, 
   job_name, 
   max_elapsed_time, 
   min_start_time, 
   max_end_time, 
   workers 
FROM 
   (SELECT 
       phase, 
       phase_name, 
       product, 
       job_name, 
       MAX(elapsed_time) elapsed_time_unconv, 
       LPAD(FLOOR(MAX(elapsed_time)*24), 4)||':'|| 
          LPAD(FLOOR((MAX(elapsed_time)*24-floor(MAX(elapsed_time)*24))*60), 2, '0')||':'|| 
          LPAD(MOD(ROUND(MAX(elapsed_time)*86400), 60), 2, '0') max_elapsed_time, 
       INITCAP(TO_CHAR(MIN(start_time),'MON DD HH24:MI', 'NLS_DATE_LANGUAGE = american')) min_start_time, 
       INITCAP(TO_CHAR(MAX(end_time),'MON DD HH24:MI', 'NLS_DATE_LANGUAGE = american')) max_end_time, 
       count(worker_id) workers 
    FROM ad_task_timing 
    WHERE session_id = <session_id> 
    GROUP BY phase, phase_name, product, job_name)
ORDER BY elapsed_time_unconv DESC;

8.2.4 Matching long-running SQL with a job

The following variant will give jobs running at any point between two time intervals, with the longest running jobs first. This is useful in matching SQLs to jobs:

<start_time> = start of period to report in format YYYYMMDDHH24MISS

<end_time> = end of period to report in format YYYYMMDDHH24MISS

Note that the job must have completed for it to be reported by this script.

SELECT 
   phase, 
   phase_name, 
   product, 
   job_name, 
   max_elapsed_time, 
   min_start_time, 
   max_end_time, 
   workers 
FROM 
   (SELECT 
       phase,
       phase_name, 
       product, 
       job_name, 
       MAX(elapsed_time) elapsed_time_unconv, 
       LPAD(FLOOR(MAX(elapsed_time)*24), 4)||':'|| 
          LPAD(FLOOR((MAX(elapsed_time)*24-floor(MAX(elapsed_time)*24))*60), 2, '0')||':'|| 
          LPAD(MOD(ROUND(MAX(elapsed_time)*86400), 60), 2, '0') max_elapsed_time,
       INITCAP(TO_CHAR(MIN(start_time),'MON DD HH24:MI', 'NLS_DATE_LANGUAGE = american')) min_start_time,
       INITCAP(TO_CHAR(MAX(end_time),'MON DD HH24:MI', 'NLS_DATE_LANGUAGE = american')) max_end_time, 
       count(worker_id) workers 
    FROM ad_task_timing
    WHERE session_id = <session_id> 
    AND ( start_time BETWEEN TO_DATE('<start_time>','YYYYMMDDHH24MISS') AND TO_DATE('<end_time>','YYYYMMDDHH24MISS') 
        OR 
          NVL(end_time, start_time+elapsed_time) BETWEEN TO_DATE('<start_time>','YYYYMMDDHH24MISS') AND TO_DATE('<end_time>','YYYYMMDDHH24MISS') )
    GROUP BY phase, phase_name, product, job_name)
ORDER BY elapsed_time_unconv DESC;

To find upgrade AD jobs that are in progress, use adctrl option 1 (Show worker status).

When they started can be determined by looking at the patch log file. e.g.

$ cat u_merged.log|grep -A2 cstpostimportaad.sql

Assigned: file cstpostimportaad.sql on worker 48 for product bom username BOM. Time is: Fri Mar 22 2013 22:48:54

8.2.5 Report of Jobs Running at Different Times During The Upgrade

It can be useful to know:

  • Which jobs were running at a particular time: this helps identify the job that a long running SQL (in AWR) belongs to.

  • The particular jobs causing events (such as contention/waits) on AWR.

  • Which jobs were running at the same time as another job.

  • Which jobs were running during a long period of low worker utilization. However, be aware that this may be legitimate: for example, a job that runs parallel SQL/DML, or creates objects in parallel.

The following script reports on the jobs that were running at specified intervals during the upgrade.

< session_id> = Id for the upgrade session

<interval> = The interval in minutes

<start_time> = start of period to report in format YYYYMMDDHH24MISS

<end_time> = end of period to report in format YYYYMMDDHH24MISS

This date/time format is used because it is same in all regions and has no delimiters.

-1 for <start time> and <end time> reports the whole period of upgrade session

-1 for <end time> just reports the jobs running at the start time

Note that this script does not show all jobs run in any interval - just those that were running at the sample times.

FND_TABLES is used as a dummy table to generate interval rows - this will give a maximum of around 20,000 intervals, so choose an interval size that does not result in more intervals than this.

BREAK ON time_slot 
WITH 
ad_start_end AS 
   (SELECT MIN(start_time) start_time, 
           MAX(end_time) end_time  
    FROM ad_task_timing  
    WHERE session_id = <session_id> ) 
,intervals AS 
   (SELECT (NVL(TO_DATE(DECODE(<start_time>,'-1',NULL,<start_time>),'YYYYMMDDHH24MISS'),st.start_time) 
       + (((rownum-1)* TO_NUMBER(<interval>))/(24*60))) interval_time  
    FROM  fnd_tables ft, 
          ad_start_end st 
    WHERE rownum <= DECODE(<start_time>
                          ,'-1'
                          ,CEIL(((st.end_time-st.start_time)*24*60)/TO_NUMBER(<interval>))+1
                          ,DECODE(<end_time>
                                  ,'-1'
                                  ,1
                                  ,CEIL(((TO_DATE(DECODE(<end_time>,'-1',NULL,<end_time>),'YYYYMMDDHH24MISS')
                                          -TO_DATE(DECODE(<start_time>,'-1',NULL,<start_time>),'YYYYMMDDHH24MISS')
                                          )
                                        *24*60
                                        )
                                       /TO_NUMBER(<interval>)
                                       )+1
                                 )
                          )
   )
SELECT 
   TO_CHAR(di.interval_time,'DD-MON-YYYY HH24:MI:SS') time_slot, 
   adt.job_name job_running, 
   COUNT(adt.worker_id) workers 
FROM intervals di, 
     ad_task_timing adt 
WHERE di.interval_time BETWEEN adt.start_time
AND NVL(adt.end_time,
        DECODE(adt.elapsed_time
              ,NULL
              ,di.interval_time
              ,adt.start_time + adt.elapsed_time)
       )
AND adt.session_id = <session_id>
GROUP BY di.interval_time, job_name 
ORDER BY di.interval_time, job_name;

8.2.6 Report of worker utilization

The following script can be used to report worker utilization at specific intervals during the upgrade:

<session_id> = ID for the upgrade session

<interval> = The interval in minutes

As with the previous script, FND_TABLES is used as a dummy table to generate interval rows - this will give a maximum of around 20,000 intervals, so choose an interval size that does not result in more intervals than this.

Look for long periods of time where worker utilization is much lower than the number of AD workers. However, be aware that it may be legitimate: for example, a job that runs parallel SQL/DML, or creates objects in parallel.

WITH 
ad_start_end AS 
   (SELECT MIN(start_time) start_time, 
           MAX(end_time) end_time 
    FROM ad_task_timing 
    WHERE session_id = <session_id> )
,intervals AS
   (SELECT (st.start_time + (((rownum-1)* TO_NUMBER(<interval>))/(24*60))) interval_time 
    FROM fnd_tables ft, 
         ad_start_end st 
    WHERE rownum <= CEIL(((st.end_time-st.start_time)*24*60)/TO_NUMBER(<interval>))+1 ) 
SELECT 
   TO_CHAR(di.interval_time,'DD-MON-YYYY HH24:MI:SS') time_slot, 
   COUNT(adt.worker_id) workers, 
   DECODE(MIN(adt.job_name), MAX(adt.job_name),MIN(adt.job_name),'Multiple') job_running 
FROM intervals di, 
     ad_task_timing adt 
WHERE di.interval_time BETWEEN adt.start_time 
                           AND NVL(adt.end_time, DECODE(adt.elapsed_time ,NULL ,di.interval_time ,adt.start_time+adt.elapsed_time) ) 
AND adt.session_id = <session_id> 
GROUP BY di.interval_time 
ORDER BY di.interval_time;

8.2.7 Report of batch/worker utilization for a specific job

The following script can be used to report the number of batches in progress for intervals during a specific job. It also shows if the size of batches or speed of processing is increasing or decreasing.

<script_name> = script/job to be analyzed

<interval> = the interval in minutes

Again, FND_TABLES is used as a dummy table to generate interval rows - this will give a maximum of around 20,000 intervals, so choose an interval size that does not result in more intervals than this.

Note that there could be previous upgrades with the same script name. So this script may need to be edited to ensure that only data for the most recent run of the script is picked up.

Also be aware that the script_name may not be the same as the job_name from the task timing reports. For example, it may contain an appended version number. The script name is usually assigned to the variable l_script_name in the actual script.

WITH 
ad_start_end AS
   (SELECT MIN(start_date) start_time, 
           MAX(end_date) end_time 
    FROM ad_parallel_update_units aduu, 
         ad_parallel_updates adu 
    WHERE aduu.update_id = adu.update_id 
    AND adu.script_name = '<script_name>' )
,intervals AS 
   (SELECT (st.start_time + (((rownum-1)* TO_NUMBER(<interval>))/(24*60))) interval_time 
    FROM fnd_tables ft, 
         ad_start_end st 
    WHERE rownum <= CEIL(((st.end_time-st.start_time)*24*60)/TO_NUMBER(<interval>))+1 ) 
SELECT 
   adu.script_name, TO_CHAR(di.interval_time,'DD-MON-YYYY HH24:MI:SS') time_slot, 
   COUNT(*) batches, 
   ROUND(AVG(aduu.rows_processed),0) avg_row_process, 
   ROUND(AVG((aduu.end_date - aduu.start_date)*(24*60*60)),0) avg_bat_tim_secs, 
   DECODE(SUM(aduu.rows_processed)
          ,0
          ,0
          ,ROUND(((SUM(aduu.end_date-aduu.start_date))*(24*60*60)*10000)/(SUM(aduu.rows_processed)),0)) sec_per_10k_rows, 
   ROUND(AVG(aduu.end_block + 1 - aduu.start_block),1) avg_blocks_per_bat 
FROM intervals di, 
     ad_parallel_update_units aduu, 
     ad_parallel_updates adu 
WHERE aduu.update_id = adu.update_id 
AND adu.script_name = '<script_name>' 
AND di.interval_time BETWEEN aduu.start_date AND NVL(aduu.end_date,di.interval_time) 
GROUP BY adu.script_name, di.interval_time 
ORDER BY adu.script_name, di.interval_time;

8.2.8 Report on CBO Statistics for all Oracle E-Business Suite tables

It may be necessary to report on the CBO statistics for Oracle E-Business Suite tables during the upgrade, particularly before adsstats.sql is run.

The script adsstats.sql will populate the statistics correctly before the end of the upgrade. The fact that tables may have incorrect statistics during the upgrade will not be visible. So it may not be possible to see that a table had null, zero or inaccurate CBO statistics, and that this is the reason for an expensive execution plan.

SELECT 
   owner, 
   table_name, 
   num_rows, 
   TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS') last_analyzed 
FROM all_tables 
WHERE owner IN (SELECT upper(oracle_username) sname 
                FROM fnd_oracle_userid 
                WHERE oracle_id BETWEEN 900 
                AND 999 AND read_only_flag = 'U' 
                UNION ALL 
                SELECT DISTINCT upper(oracle_username) sname 
                FROM fnd_oracle_userid a,
                     fnd_product_installations b 
                WHERE a.oracle_id = b.oracle_id )
ORDER BY owner, table_name;

8.2.9 Analyze contention (waits) and where they occur

The following shows the sql_ids on which a particular wait occurs between two snapshots in AWR.

<db_id> is the database ID, <inst_num> is the instance number, <wait name> is the name of the wait, and <begin snap> and <end snap> are the start and end snapshot IDs.

SELECT 
   ss.sql_id, 
   ss.time_waited, 
   ss.counts_waited, 
   tt.total_time, 
   ROUND((ss.time_waited*100/tt.total_time),1) percent 
FROM
  (SELECT 
      s.sql_id ,
      COUNT(*) counts_waited,
      SUM(time_waited) time_waited
   FROM
      dba_hist_active_sess_history s
   WHERE s.dbid = <db_id> 
   AND s.instance_number = <inst_num>
   AND s.con_dbid = <con_dbid> -- From DB 12.1 onwards
   AND s.event = '<wait name>' 
   AND snap_id > <begin_snap> 
   AND snap_id <= <end_snap> 
   GROUP BY s.sql_id) ss,
  (SELECT
      SUM(time_waited) total_time 
   FROM dba_hist_active_sess_history t 
   WHERE t.dbid = <db_id>
   AND t.instance_number = <inst_num>
   AND t.con_dbid = <con_dbid> -- From DB 12.1 onwards
   AND t.event = '<wait name>'
   AND t.snap_id BETWEEN <begin_snap> AND <end_snap>) tt 
ORDER BY ss.counts_waited desc;

Note that from 12c Release 2, in a multi-tenant environment, the DBA_HIST views can also be interchanged with the AWR_ROOT views and AWR_PDB views at the CDB level and the PDB level respective

The following SQL shows the objects on which a particular wait occurs for a given SQL ID (between two snapshots in AWR).

Where <db_id> is the database ID, <inst_num> is the instance number, <wait name> is the name of the wait, <sql_id> is the SQL ID and <begin snap>, and <end snap> are the start and end snapshot IDs.

-- Note the column dba_hist_seg_stat_obj.con_id only exists from DB version 12.2 - omit this before 12.1
SELECT
   ss.sql_id,
   ss.event,
   ss.current_obj#,
   ss.owner,
   ss.object_name,
   ss.object_type,
   ss.time_waited,
   ss.counts_waited,
   tt.total_time,
   ROUND((ss.time_waited*100/tt.total_time),1) percent
FROM
   (SELECT
       s.sql_id,
       s.event,
       s.current_obj#,
       o.owner,
       o.object_name,
       o.object_type,
       COUNT(*) counts_waited,
       SUM(time_waited) time_waited
    FROM dba_hist_active_sess_history s,
        (SELECT MAX(owner) owner,
                obj#,
                dbid,
                con_dbid, -- From DB 12.1 onwards                
                MAX(object_name) object_name,
                MAX(subobject_name) subobject_name,
                MAX(object_type) object_type,
                MAX(tablespace_name) tablespace_name
         FROM dba_hist_seg_stat_obj
         GROUP BY obj#, dbid, con_dbid) o -- con_dbid added from DB 12.1 onwards
    WHERE s.dbid = <db_id> 
    AND s.instance_number = <inst_num>
    AND s.con_dbid = <con_dbid> –- from DB 12.1 onwards
    AND s.sql_id = '<sql_id>'
    AND s.event = '<wait name>'
    AND o.dbid (+) = s.dbid
    AND o.con_dbid (+) = s.con_dbid –- from DB 12.1 onwards
    AND o.obj# (+) = s.current_obj#
    AND snap_id BETWEEN <begin_snap> AND <end_snap>
    GROUP BY s.sql_id, s.event, s.current_obj#, o.owner, o.object_name, o.object_type) ss,
   (SELECT
       SUM(time_waited) total_time
    FROM dba_hist_active_sess_history t
    WHERE t.dbid = <db_id>
    AND t.instance_number = <inst_num>
    AND t.con_dbid = <con_dbid> –- from DB 12.1 onwards
    AND t.sql_id = '<sql_id>'
    AND t.event = '<wait name>'
    AND t.snap_id > <begin_snap> AND t.snap_id <= <end_snap>) tt
ORDER BY ss.counts_waited desc;

Note that from 12c Release 2, in a multi-tenant environment, the DBA_HIST views can also be interchanged with the AWR_ROOT views and AWR_PDB views at the CDB level and the PDB level respective

 

8.2.10 Automate execution of Display Cursor reports for top SQLs during upgrade patches

Note that it is absolutely essential that either statistics_level = ALL or _rowsource_execution_statistics = TRUE, otherwise the Display Cursor report will not contain the actual rowsource statistics (for each plan line) that are essential in quickly identifying if and why a SQL has an inefficient execution plan.

The following script can be used (on linux) to automatically produce Display Cursor reports for the top SQL (by elapsed time) during each upgrade patch. It also produces Display AWR reports, which only provide the execution plan and no actual statistics, but which may be useful as a fall back if statistics_level or _rowsource_execution_statistics have not been set, or a particular SQL was no longer in cursor cache.

Although this automated process will produce many reports that are never looked at. It will mean that if a long running SQL (as part of a long running job) is identified then the Display Cursor report will already be available for analysis. It will not be necessary to wait until the next test run to obtain one.

There are 4 parameters that should be passed to the script:

  • Apps password

  • Number of iterations. This should be large enough so that the shell script keeps on producing Display Cursor reports throughout the upgrade patch. It is recommended that this is much larger than needed, the shell script can always be killed when the patch has completed.

  • Iteration gap in minutes. This should be short enough so that all long running sqls of interest are reported, but not cause much overhead. 10 minutes is recommended.

  • Number of Top SQLs to monitor (by elapsed time). This should be large enough so that all long running sqls of interest are reported, but not cause much overhead. 20 is recommended.

So, if the apps password is represented by <password>, the number of iterations is 72, the gap is 10 minutes and the number of top SQLs is 20, the script will be called as follows:

xxauto_dc.sh <password> 72 10 20

The shell script loops for the specified number of iterations.

In each iteration of the loop it :

  • Calls the SQL script xxautodc.sql, passing the number of top SQLs parameter. This builds a temporary SQL script dispcurbatch.sql to spool and run the Display Cursor reports for the top SQL and then executes that script.

  • Removes the temporary SQL script dispcurbatch.sql (after it has been run).

  • Sleeps for the specified number of minutes (before starting the next iteration of the loop).

There are 3 sets of reports :

  • Disp_Curs_<sql_id>_<YYMMDDHH24MI>.txt. This is the Display Cursor report for a top SQL in the AWR

  • Disp_AWR_<sql_id>_<YYMMDDHH24MI>.txt. This is the Display AWR report for a top SQL in the AWR

  • CC_Disp_Curs_<sql_id>_<YYMMDDHH24MI>.txt. This is the Display Cursor report for a top SQL in the Cursor Cache

Where

  • <sql_id> is the SQL ID for the particular SQL statement.

  • <YYMMDDHH24MI> indicates the approximate time that the report was produced (i.e. from which iteration) in format YYMMDDHH24MI

Ensure that there is plenty of space on the file system (volume) available to accommodate the output files.

# Script xxauto_dc.sh

# Get arguments
# 1. apps password
# 2. number of iterations
# 3. iteration gap - minutes
# 4. No of Top SQLs 

export appspass="$1"
export numit="$2"
export gap="$3"
export topsql="$4"

iteration=1

until [ ${iteration} -gt ${numit} ]
do
   echo "Iteration ${iteration}"

   sqlplus apps/${appspass} @xxautodc.sql $topsql

   # remove batch file (if it exists) 

   rm -f dispcurbatch.sql

   iteration=`expr $iteration + 1` 

   # Now sleep for requested number of minutes 

   if [ ${iteration} -gt ${numit} ] 
   then
      echo "Do not sleep"
   else
      sleep ${gap}m
   fi

done

The SQL script xxautodc.sql:

  • Reads the "Number of Top SQLs" parameter.

  • Runs SQL on the AWR and cursor cache to spool output to temporary SQL script file dispcurbatch.sql. The SQL script dispcurbatch.sql will contain commands to spool and run Display Cursor reports for the top SQLs (in AWR and cursor cache).

  • Executes the SQL script dispcurbatch.sql.

-- Script xxautodc.sql
-- example script for building script to run Display Cursor for top sql ids
-- It is absolutely essential that either statistics_level = ALL or _rowsource_execution_statistics = TRUE
-- otherwise the Display Cursor report will not contain the actual rowsource statistics (for each plan line)
-- that are essential in quickly identifying if and why a SQL has an inefficient execution plan.

WHENEVER SQLERROR CONTINUE
WHENEVER OSERROR CONTINUE

VARIABLE l_topsql NUMBER

-- pick up l_topsql and set bind var
DEFINE topsql = '&&1'

exec :l_topsql := '&topsql'

set echo off heading off feedback off verify off

set pages 0 termout off
set linesize 100

spool dispcurbatch.sql

SELECT 'WHENEVER SQLERROR CONTINUE' FROM DUAL;
SELECT 'WHENEVER OSERROR CONTINUE' FROM DUAL;
SELECT 'SET pages 0' FROM DUAL;
SELECT 'SET lines 300' FROM DUAL;
SELECT 'SET LONG 10000' FROM DUAL;
SELECT 'SET LONGCHUNKSIZE 10000' FROM DUAL;

SELECT
   '                                                                                                 ',
   'SPOOL '
   ||'Disp_Curs_'
   ||sql_id
   ||'_'
   ||TO_CHAR(SYSDATE,'YYMMDDHH24MI')
   ||'.txt                                                         ',
   'SELECT * FROM TABLE(dbms_xplan.display_cursor('''
   ||sql_id
   ||''', NULL, ''ALL +ALLSTATS''));    ',
   'SPOOL OFF',
   '                                                                                                 ',
   'SPOOL '
   ||'Disp_AWR_'
   ||sql_id
   ||'_'
   ||TO_CHAR(SYSDATE,'YYMMDDHH24MI') ||'.txt                                                          ',
   'SELECT * FROM TABLE(dbms_xplan.display_awr('''
   ||sql_id
   ||''', format => ''ALL''));                 ',
   'SPOOL OFF'
FROM
   (SELECT  /*+ leading(d,i,dhss,dhst) */
       dhss.sql_id,
       ROUND(SUM(dhss.elapsed_time_delta/1000000),0) elapsed_time_secs -- needs sum because of different plan (hash)s for same SQL
    FROM v$database d,
         v$instance i,
         dba_hist_sqlstat dhss,
         dba_hist_sqltext dhst
    WHERE dhss.dbid = d.dbid
    AND dhss.instance_number = i.instance_number
    AND dhst.dbid = dhss.dbid
    AND dhst.con_dbid = dhss.con_dbid –- from DB 12.1 onwards
    AND dhss.con_dbid = d.con_dbid –- from DB 12.1 onwards 
    AND dhst.sql_id = dhss.sql_id
    AND dhst.command_type != 47 -- exclude anonymous blocks
    AND dhss.snap_id = (SELECT /*+ push_subq */ MAX(dhsn.snap_id)
                        FROM dba_hist_snapshot dhsn,
                             v$database d,
                             v$instance i
                        WHERE dhsn.dbid = d.dbid
                        AND dhsn.instance_number = i.instance_number) -- Just report on latest completed snapshot
    GROUP BY dhss.sql_id
    ORDER BY 2 DESC)
WHERE rownum <= :l_topsql -- limit to the top SQLs (ordered by elapsed time).
; 

-- Also get top SQls from cursor cache - to fill any gaps

SELECT
   '                                                                                                 ',
   'SPOOL '
   ||'CC_Disp_Curs_'
   ||sql_id
   ||'_'
   ||TO_CHAR(SYSDATE,'YYMMDDHH24MI')
   ||'.txt                                                      ',
   'SELECT * FROM TABLE(dbms_xplan.display_cursor('''
   ||sql_id
   ||''', NULL, ''ALL +ALLSTATS''));    ',
   'SPOOL OFF'
FROM
   (SELECT
       ss.sql_id,
       ROUND(SUM(ss.delta_elapsed_time/1000000),0) elapsed_time_secs
    FROM v$sqlstats ss,
         v$database d, –- from DB 12.1 onwards
         v$sql s
    WHERE s.sql_id = ss.sql_id
    AND s.child_address = ss.last_active_child_address
    AND s.command_type != 47 -- exclude anonymous blocks
    AND ss.con_dbid = d.con_dbid –- from DB 12.1 onwards
    GROUP BY ss.sql_id
    ORDER BY 2 DESC)
WHERE rownum <= :l_topsql -- limit to the top SQLs (ordered by elapsed time).
; 

spool off

set termout on echo on verify on heading on feedback on

set termout off

-- Now run the script that has been built above
@@dispcurbatch

set termout on

exit

Note that from 12c Release 2, in a multi-tenant environment, the DBA_HIST views can also be interchanged with the AWR_ROOT views and AWR_PDB views at the CDB level and the PDB level respective

8.3 Common Solutions

8.3.1 Known Issues

Once the long running jobs and SQL have been identified, check My Oracle Support for known issues and potential solutions or workarounds.

The My Oracle Support Document "Oracle EBS R12.2 Upgrade - Outstanding Performance Bugs, Issues and Solutions (Document 2357810.1)" is also a useful document to check for recently identified performance bugs/issues and their solutions.

However, bear in mind that a fix or workaround may not necessarily fix the particular problem that is observed. Some bugs and SRs do not show the symptoms of the performance problem, but only the solution.

If possible, evidence (diagnostics) should be obtained to justify the suggested fix.

If it cannot be confirmed (from the diagnostics) that the issue is exactly the same then the fix may still be applied, but continue to gather diagnostics and search for solutions until the issue is fully resolved.

Many situations have been encountered where a customer has focused on a particular known issue and fix, only to find out that their performance issue was different from the known one and therefore the required fix was different.

This is because each customer uses the Oracle E-Business Suite modules and functionality in a different way, and has different volumes and distributions of data. They may also have a different database version or configuration, hardware configuration, and CBO and database initialization parameters.

8.3.2 Custom Indexes

There will be cases where a long-running job has an inefficient execution plan, and so a new index will have an enormous impact.

Typically, the execution plan will use a full table scan, an unselective index range scan, or an index skip scan, all of which can result in a large number of rows being filtered out when the table is accessed.

In such cases, a custom index could be created to filter out the rows, reducing the number of rows accessed on the table or the need for an index skip scan.

Ensure that an SR is still raised for the performance issue (supplying diagnostic evidence). The performance issue may still need to be resolved in the standard code.

8.3.3 SQL Profiles for Inefficient Execution Plans

If, when using analyzing the diagnostics (especially Display Cursor report or SQL Trace), it can be identified that a long running job has an inefficient execution plan, a SQL Profile could be used to apply hints that will help the CBO choose a better execution plan. SQL tuning expertise will be needed to do this.

Note that this only applies to SQL Profiles that are created to apply hints. Not to SQL Profiles, often created from SQL Tuning Advisor (STA), that use OPT_ESTIMATE hint (and usually SCALE_ROWS) to correct the cardinality over/underestimates. These latter SQL Profiles can cause significant performance issues if the tables concerned (such as new tables or temporary/transient table) have incorrect CBO stats or misleading number of rows when the SQL Profile is created.

Ensure that an SR is still raised for the performance issue (with diagnostic evidence). The performance issue may still need to be resolved in the standard code.

The syntax for hints in SQL Profiles is stricter than for hints applied directly in the SQL. If the syntax is wrong, they are just ignored. Lower and upper case can still be used, quotes (") omitted, and the leading query block names left out.

However, the best approach is to:

  1. Run EXPLAIN PLAN on the SQL (with hints added).

  2. Get the fully formed hints from the outline by running: SELECT * FROM TABLE(dbms_xplan.display(FORMAT => 'ALL +OUTLINE')). This helps get the correct syntax.

  3. Create the SQL profile script and execute it.

  4. Run EXPLAIN PLAN on the SQL (without hints added). This shows if the SQL Profile has been applied (the Notes section at the end of the output will contain ‘SQL profile "<profile_name>" used for this statement’) and the explain plan should show that the hints have been applied.

Here is an example of a script to create a SQL Profile, applying hints.

DECLARE 

   l_sql_fulltext clob := NULL;
   lv_hint SYS.SQLPROF_ATTR := SYS.SQLPROF_ATTR (); 
BEGIN 

-- Note the SQL below has been formatted so that it fitsd in the document. Please use the SQL string without carriage returns/line feed 

   l_sql_fulltext := 'SELECT AAGC.APPLICATION_ID, AAGC.APPROVAL_GROUP_ID FROM AME_APPROVAL_GROUP_CONFIG AAGC 
                      WHERE SYSDATE BETWEEN AAGC.START_DATE AND NVL(AAGC.END_DATE - (1/86400), SYSDATE) AND 
                      NOT EXISTS (SELECT NULL FROM AME_ACTION_USAGES AAU, AME_RULE_USAGES ARU, AME_ACTIONS AA,
                      AME_ACTION_TYPES AAT WHERE AA.ACTION_ID = AAU.ACTION_ID AND AAU.RULE_ID = ARU.RULE_ID 
                      AND ARU.ITEM_ID = AAGC.APPLICATION_ID AND SYSDATE BETWEEN AA.START_DATE AND 
                      NVL(AA.END_DATE - (1/86400), SYSDATE) AND SYSDATE BETWEEN AAT.START_DATE AND 
                      NVL(AAT.END_DATE - (1/86400), SYSDATE) AND AA.PARAMETER = TO_CHAR(AAGC.APPROVAL_GROUP_ID)
                      AND AAT.ACTION_TYPE_ID = AA.ACTION_TYPE_ID AND AAT.NAME IN 
                      (''pre-chain-of-authority approvals'' , ''post-chain-of-authority approvals'' , 
                      ''approval-group chain of authority'') AND ROWNUM < 2) ORDER BY AAGC.APPLICATION_ID';

   lv_hint.EXTEND;
   lv_hint(1) := 'BEGIN_OUTLINE_DATA';
   lv_hint.EXTEND;
   lv_hint(2) := 'USE_NL(@"SEL$2" "AAT"@"SEL$2")';
   lv_hint.EXTEND;
   lv_hint(3) := 'USE_NL(@"SEL$2" "ARU"@"SEL$2")';
   lv_hint.EXTEND;
   lv_hint(4) := 'USE_NL(@"SEL$2" "AAU"@"SEL$2")';
   lv_hint.EXTEND;
   lv_hint(5) := 'LEADING(@"SEL$2" "AA"@"SEL$2" "AAU"@"SEL$2" "ARU"@"SEL$2" "AAT"@"SEL$2")';
   lv_hint.EXTEND;
   lv_hint(6) := 'INDEX(@"SEL$2" "AAT"@"SEL$2" ("AME_ACTION_TYPES"."ACTION_TYPE_ID" "AME_ACTION_TYPES"."START_DATE" "AME_ACTION_TYPES"."END_DATE"))';
   lv_hint.EXTEND;
   lv_hint(7) := 'INDEX(@"SEL$2" "ARU"@"SEL$2" ("AME_RULE_USAGES"."RULE_ID" "AME_RULE_USAGES"."START_DATE" "AME_RULE_USAGES"."END_DATE"))';
   lv_hint.EXTEND;
   lv_hint(8) := 'INDEX(@"SEL$2" "AAU"@"SEL$2" ("AME_ACTION_USAGES"."ACTION_ID"))';
   lv_hint.EXTEND;
   lv_hint(9) := 'INDEX(@"SEL$2" "AA"@"SEL$2" ("AME_ACTIONS"."PARAMETER"))';
   lv_hint.EXTEND;
   lv_hint(10) := 'FULL(@"SEL$1" "AAGC"@"SEL$1")';
   lv_hint.EXTEND;
   lv_hint(11) := 'LEADING(@"SEL$1" "AAGC"@"SEL$1")';
   lv_hint.EXTEND;
   lv_hint(12) := 'IGNORE_OPTIM_EMBEDDED_HINTS';
   lv_hint.EXTEND;
   lv_hint(13) := 'END_OUTLINE_DATA';

   dbms_sqltune.drop_sql_profile (name => 'R1220_AMEMIGCFG_1'
                                 ,ignore => TRUE );

   dbms_sqltune.import_sql_profile(sql_text => l_sql_fulltext
                                  ,category => 'DEFAULT'
                                  ,name => 'R1220_AMEMIGCFG_1'
                                  ,profile => lv_hint
                                  ,description => 'R1220 amemigcfg.sql <sql_id>'
                                  ,force_match => TRUE );

END;
/

SQL Baselines can also be used to import an execution plan from cursor cache, AWR or SQL Tuning set. However, this does not have the ability to import hints.

In conclusion, SQL Profiles:

  • Are easy to apply.

  • Can be used to directly specify the join order, access and join methods.

  • Are very stable.

8.3.4 SQL Tuning Advisor limitations

Using SQL Tuning Advisor (STA) to resolve performance issues during the R12.2.n upgrade has the following drawbacks.

  • It could delay provision of diagnostics and a subsequent solution.

  • The fact that STA has to be used to get an acceptable execution plan still indicates that there is an underlying issue that needs to be resolved.

  • It can only identify more efficient execution plans if the tables are populated (with representative data) at the time STA is run, or in the session in which STA is run. So it cannot be used for Global Temporary tables or Temporary/ Interim/Transitional tables. It can also produce the wrong optimizer estimate adjustments for new tables.

  • It can only identify more efficient execution plans if the CBO statistics are correct.

  • The SQL Profiles that STA produces do not use hints to specify the actual join order, access and join methods. Instead they use the OPT_ESTIMATE hint (and usually SCALE_ROWS) to correct the cardinality over/underestimates that resulted in the "poor" execution plans. These are not necessarily stable, particularly if CBO statistics change later. SQL profiles that use hints are preferred.

8.3.5 Pragmatic Stable Execution Plans

In most cases, there is one join order that will give a good execution plan and minimize throwaway (that is, unnecessary access to rows that are filtered out at a later stage). Often, that join order is the natural one, following the flow of the application.

8.3.5.1 AD Parallel Jobs

AD Parallel jobs will access batches of rowids from a driving table (specified when calling AD_PARALLEL_UPDATES_PKG.INITIALIZE_ROWID_RANGE to initialize AD Parallel).

In almost all cases these batches will contain a very small percentage of the total rows. So the execution plan should lead with the driving table (accessing it by rowid), and then use nested loop join method and index access. This will give a pragmatic plan for AD Parallel jobs. It may not be the very best execution plan, but it will not be particularly inefficient.

For small tables (particularly lookup or reference), a full table scan and hash join may be better, but (compared to the overall workload) the difference could be negligible. rowid, leading, use_nl and index hints will typically be used. The undocumented cardinality hint could also be used to specify a low cardinality for the driving table (e.g. cardinality(ai 1).

8.3.5.2 Parallel SQL

For jobs that use Parallel SQL, full table scans and hash joins will usually, but not always, be better. They will also benefit from using a join order that minimizes throwaway.

8.3.6 Long running SQL in Online Patching Enablement / Online Patching

There can sometimes be long running internal SQL with inefficient execution plans in Online Patching Enablement, R12.2.n RUPs and other online patches. This will show up as internal SQL (on V$ views or on SYS/SYSTEM objects) appearing high in AWR and TKPROF reports.

Gathering statistics for specific fixed or dictionary objects can help in these circumstances, particularly on editioning objects.

8.3.7 Long running Cleanup in Online Patching

If cleanup (after applying AD/TXK and 12.2.n RUP patches) is taking too long (particularly "drop covered objects") then consider running quick cleanup rather than standard cleanup.

adop phase=cleanup cleanup_mode=quick

Later, when there is more time, cleanup can be run again in standard mode (which is the default):

adop phase=cleanup

Note that:

Cleanup and FS_CLONE stages are still required even if patches are being applied using ADOP in downtime mode. So this advice still applies.

If cleanup has not be run from the previous patching cycle the cleanup will run at the start of FS_CLONE. So if FS_CLONE (after applying AD/TXK and 12.2.n RUP patches) is taking too long to cleanup (particularly "drop covered objects") then quick cleanup should be run prior to FS_CLONE.

Drop covered objects is unfortunately a serial operation. The number of objects to drop depends on how many objects are affected by the patch (which will be a large number in the case of the E-Business Suite 12.2.n RUP).

8.3.8 Long Running Index Creation

This will typically apply to xdf and odfs.

Obtain an AWR report for the snapshots where the index is being created.

8.3.8.1 Not running with parallel DML

First of all check that the index is being created in parallel (e.g. CREATE INDEX …….. PARALLEL) when the odf file is processed by AutoPatch. The DDL command will show up in the AWR (SQL Ordered By).

If the index is being created in serial then the AutoPatch parameter parallel_index_threshold may need to be decreased.

This parameter specifies the number of blocks in a table. If a table contains fewer blocks than the threshold setting, indexes are created with parallel AD workers and serial DML (each worker creating a different index). If the table contains more blocks than the threshold setting, indexes are created with one worker and parallel DML. The valid values are 0 to 2147483647. If set to 0, indexes are created with parallel workers and serial DML. Default value: 20000; meaning a threshold of 20,000 blocks.

Otherwise the statistics for the table may need to be gathered. If the statistics are missing or incorrect (e.g. too low) then AutoPatch (adodfcmp) may get the wrong number of blocks and choose to create with serial DML.

If the index cannot be created in parallel, consider pre-creating the index.

8.3.8.2 parallel_max_servers / parallel degree

Check if parallel_max_servers needs increasing.

If the SQL is not using as many parallel children (secondary processes) as expected, then it is possible that the number of parallel children is being limited by other simultaneous tasks.

The AWR can be checked for any simultaneous tasks taking place at the same time as the create index.

Another limitation could be the CPU_COUNT * PARALLEL_THREADS_PER_CPU parameters.

CPU_COUNT, if set, should either be the number of CPU cores or zero. Note that if CPU_COUNT is not set, or set to zero, it will default to the number of CPUs reported by the operating system. This is therefore acceptable.

PARALLEL_THREADS_PER_CPU if present should be 2. If it is not set, the default is platform-dependent (normally 2), and adequate in most cases.

Note that Automatic Parallel Degree Policy (PARALLEL_DEGREE_POLICY) should not be enabled - the default is MANUAL.

See "Oracle Database VLDB and Partitioning Guide" "How Parallel Execution Works" and "Tuning General Parameters for Parallel Execution" sections.

8.3.8.3 pga_aggregate_target

Create index will use space in the PGA for sorting. If (and only if) there is significant usage of temporary space should increasing pga_aggregate_target be considered.

8.3.8.4 Contention between parallel child processes

If the index is being created in parallel (and with enough child processes), the performance issue may be caused either by contention between the parallel child processes creating the index or contention with another simultaneous process.

Note that if the parallel degree is too high this will also cause contention.

Obtain an AWR report for the period when the index is being created and check for the contention (waits, CPU usage).

If there is no contention on AWR, it is unlikely that pre-creating the index will resolve the issue. The same command would be used to pre-create, with the same parallel degree.

8.3.8.5 Pre-Creating

If the index is being created (by odf) in serial, and either it cannot be created in parallel or there is significant contention when the index is created (by odf) in parallel, then pre-creating the index is likely to help. However, there are some points to bear in mind:

  • Do not do it too soon. If the table subsequently has heavy DML (insert, delete or update of a column used in the index) prior to where the normal creation would have occurred (with odf), then that DML is likely to take much longer. So if possible, pre-create the index after any job that executes heavy DML on the table.

  • Also, be careful to create the index with exactly the same definition as in the odf file. adodfcmp will compare the index that has already been pre-created with the definition in the odf file. If they do not match, adodfcmp may need to alter or re-create the index anyway.

  • Remember to ALTER INDEX to revert the degree of parallel (e.g. NOPARALLEL) afterwards.

So if pre-creating indexes manually, do it as late as possible.

8.3.9 Long Running Index Re-creation

If the definition of a seeded E-Business Suite seeded index has been customized to change the type, columns, order (ASC or DESC), uniqueness, compression or partitioning, then it will be re-created during the upgrade to match the standard definition.

For large tables this is likely to consume significant additional time.

8.3.10 High level of contention on indexes on long running DML jobs

If a long running job running heavy DML (typically INSERT, but it could be UPDATE or DELETE), has a high level of contention, and that contention is on particular indexes, consider dropping the indexes before the job and re-creating them afterwards (provided that the index is not used by any execution plan in the meantime).

Alternatively, consider dropping the indexes prior to the upgrade and re-creating them afterwards. However, be sure that the indexes are not being used during the upgrade.

Check if indexes have been used during any particular period by using the command 'ALTER INDEX <index> MONITORING USAGE', and then querying the view V$OBJECT_USAGE/DBA_OBJECT_USAGE.

In addition, the following AWR tables will contain useful information:

  • DBA_HIST_SEG_STAT will show the usage of indexes.

  • DBA_HIST_SQL_PLAN can be used to identify if (and how) an index is used on actual execution plans.

  • DBA_HIST_ACTIVE_SESS_HISTORY can give some idea of the level of activity for each access path (and index).

Note that from 12c Release 2, in a multi-tenant environment, the DBA_HIST views can also be interchanged with the AWR_ROOT views and AWR_PDB views at the CDB level and the PDB level respective

For more information on how to query these views see My Oracle Support document "Using Database Partitioning with Oracle E-Business Suite (Document 554539.1)" section "What Access Paths Are Used - Analysis of AWR views".

The same views will also show the indicate the time/resource used in maintaining the indexes for each piece of DML.

Ensure that indexes are re-created in parallel and with exactly the same definition. And remember to ALTER INDEX to revert the degree of parallel (e.g. NOPARALLEL) afterwards.

When deciding this, take into account the percentage of rows being inserted/deleted/updated during the DML, as re-creating the index will be for all rows. This will be particularly useful on custom indexes, where dropping the indexes prior to the upgrade and re-creating them afterwards is advised (provided that the custom index does not make any of the upgrade jobs significantly quicker).

8.3.11 High Level of "enq: HW – contention" or "enq: HV – contention"

This is related to the case "High level of contention on indexes on long running DML jobs".

If a long-running job inserting into a table and indexes has a high level of waits ("enq: HW – contention" or "enq: HV – contention") then the following could be done:

  • If the wait is occurring largely on particular indexes, drop the indexes before the job and re-create them afterwards (as in case "High level of contention on indexes on long running DML jobs"), provided the index is not used in the meantime.

  • Increase the extent size on the table and indexes.

  • Pre-allocate extents for the table and indexes.

  • Partition the table and any indexes to spread the load across multiple partitions. Note that the tables and indexes will still be partitioned after go live. So only do this if the partitioning method will also give benefits after going -live on the production environment).

These "enq: HW – contention" or "enq: HV – contention" are often accompanied by higher levels of other waits such as "enq: TX – contention" or "buffer busy waits".

The HW enqueue is used to manage the allocation of space beyond the high water mark of a segment. So the wait "enq: HW – contention" typically means that sessions are waiting for the allocation of extents.

The wait "enq: HV – contention" is the same, except it occurs when parallel child processes are waiting for an extent to be allocated.

The AWR tables can be queried directly to get more detail on where waits are occurring (e.g. on which object). More

8.3.12 High Level of redo log waits "log buffer space", "log file sync", "log_file_switch" etc.

If there are a high level of waits associated with redo log, especially "log buffer space" and "log file sync", consider changing the configuration of redo logs; moving to a faster filer; increasing the size or number of logs; or increasing the log parallelism (hidden initialization parameter _log_parallelism_max).

Consider running with NOLOGGING, but this is not advised. It would require turning logging off for all tables and indexes and then switching it back on post-upgrade for all the tables and indexes where it had been switched off.

If getting high "log file switch (checkpoint incomplete)" waits then consider removing (resetting) the initialization parameter log_checkpoint_interval. The default is 0, which has the same effect as setting the parameter to infinity and causes the parameter to be ignored. This reduces the number of checkpoints.

8.3.13 Long Running Statistics Gathering (adsstats.sql)

If the upgrade is taking a long time to gather CBO statistics (adsstats.sql), consider the following strategies:

  • Increasing parallel_max_servers and pga_aggregate_target (adsstats.sql should run with few or no concurrent tasks).

  • Using all nodes on Oracle RAC system.

  • Importing statistics gathered during test runs.

Note that the last suggestion is a strategic action that may be taken for all Release 12.2.n upgrades. So it is considered and described in more detail in the Preparation and Planning section.

8.3.14 Gathering or Deleting Statistics to resolve specific performance issues

Performance issues may indicate the need for statistics for specific objects to be gathered or deleted.

8.3.14.1 New Tables

The Release 12 upgrade creates and populates many tables. For most tables the statistics are not gathered until late in the upgrade (i.e. by adsstats.sql in the last+63 phase of the R12.2.0 upgrade).

So the statistics may be empty (for new Release 12 tables), and dynamic sampling may not have resulted in a good execution plan. Alternatively, the statistics may be incorrect.

In such a case, it may be a good idea to gather statistics (using FND_STATS.GATHER_TABLE_STATS) for a specific table at a particular stage of the upgrade.

However, only gather statistics for specific tables when the following is known:

  • Incorrect or missing statistics on that table is definitely causing the performance issue.

  • There are no significant inserts to the table afterwards (or significant changes in the number of distinct values (NDV) on key join/filter columns or the distribution of values on columns with a histogram). In such cases, the CBO statistics will then be incorrect and could result in subsequent poor execution plans.

Remember that having no statistics can be better than incorrect statistics (as dynamic sampling will be used) and they are certainly better than zero statistics (gathered when table was not populated).

Guidance about gathering statistics can also be sought from Oracle Support.

8.3.14.2 Temporary Tables

If performance issues are due to transient or temporary tables (including Global Temporary tables), and the statistics for these tables contain unrepresentative statistics, it may be a good idea to do one of two things:

  • Before the upgrade, delete the statistics using DBMS_STATS.DELETE_TABLE_STATS, lock using DBMS_STATS.LOCK_TABLE_STATS, and then exclude using FND_STATS.LOAD_XCLUD_TAB. Dynamic sampling will then be used.

  • Gather representative statistics using FND_STATS.GATHER_TABLE_STATS at an appropriate point or import representative statistics using FND_STATS.RESTORE_TABLE_STATS (with CASCADE = TRUE/default). Then lock using DBMS_STATS.LOCK_TABLE_STATS and exclude using FND_STATS.LOAD_XCLUD_TAB. The representative statistics will have been previously exported using FND_STATS.BACKUP_TABLE_STATS (with CASCADE = TRUE/default).

More

8.3.15 Long-Running Compilation / Re-compilation

If scripts (e.g. adobjcmp.sql/adutlrcmp.sql) which utilizes the job queue (DBMS Scheduler) to compile/re-compile objects (e.g. UTL_RECOMP.RECOMP_PARALLEL) are taking a long time then do the following :

  • Check that the value of initialization parameter job_queue_processes is high enough (a value equal to the number of CPU cores is normally recommended).

8.3.16 Long-Running Materialized View xdf/odfs

If there are

  • long-running xdf or odf jobs creating materialized views (MV)

  • long running scripts executing MV operations, such as drop or refresh

then consider refreshing MVs prior to the upgrade or truncating any large MV logs.

Note that this requires complete refresh of all MVs that are dependent on the MV log. Check that the number of rows in the MV log (mlog$) is zero to confirm that all dependent MVs have been refreshed.

There is now a facility in the Online Patching Enablement Readiness Report to list MVs with stale log data (ADZDMVREFRESHNEEDED.sql).

This script needs to be run before starting the 12.2.0 upgrade driver.

It lists the refresh command required (exec dbms_mview.refresh) for each MV (which requires manual refresh).

8.3.17 Long-Running Jobs that might not be needed

Check if any long-running jobs are actually needed, especially if they do not alter any data (insert, update or delete any rows).It could be that some jobs are for a module or localization that is not used or not licensed.

Oracle Support may advise that a job can be skipped, or offer a fix or workaround.

8.3.17.1 Skipping Jobs

If Oracle Support has identified that a particular job is not required for the instance, there are two options for skipping it:

  • Comment the job out of the unified driver (u driver) for the install.

  • Run the adctrl utility and choose hidden option 8.

8.3.18 High I/O waits on temporary space

If there I/O waits on temporary space ("direct path read temp" and "direct path write temp") and there is no scope to increase PGA further (or it has not resolved the issue) then consider moving the temp space to local disk (or faster disk).

8.3.19 Long running class, xdf, xml and other files

8.3.18.1 Symptoms

Typically during the 12.2.0 CUP and 12.2.n RUP patches most of these following (types of) of jobs should complete almost immediately, within a few seconds :

  • XDOLoader.class

  • WFXLoad.class

  • akload.class

  • Application of xdf files using FndXdfCmp (except for creation of large indexes)

  • Application of xml files

  • Other class files such as CustMigrationTool.class, EDRXDOMigration.class

  • .jar files (e.g. perkigfclient.jar, perkpiclient.jar)

However, if a large number of them are long running, especially if groups of jobs that run together either take a similar length of time or complete at around the same time, then there is likely to be an issue with the speed/volume of random numbers being provided by /dev/random (blocking).

8.3.18.2 Cause

This happens when JDBC connections delay due too lack of random number entropy.

Entropy: In computing, entropy is the randomness collected by an operating system or application for use in cryptography or other uses that require random data. This randomness is often collected from hardware sources, either pre-existing ones such as mouse movements or specially provided randomness generators.

JDBC connection establishment with 11.2.x databases onwards uses a new connection mechanism (o5logon) that requires the use of random numbers.

These numbers are typically generated by a special device (/dev/random). However, this random number generator relies on entropy in order to generate sufficiently random numbers.

This entropy comes from things like mouse pointer movement and keyboard entry.

When there is insufficient entropy, the random number generator will not return any numbers.

When this happens, the o5logon used by the JDBC library stalls, and has to wait until sufficient entropy is available.

8.2.18.3 Solution

Use /dev/urandom, which is an unlimited/non-blocking random source, instead of /dev/random. It reuses the internal pool to produce more pseudo-random bits.

Temporarily replace /dev/random with /dev/urandom by using a symbolic link.

This is outlined in My Oracle Support document "Upgrade From 11.5.10.2 Is Very Slow as XDOLOADER Task Hangs (Document 1065393.1)".

e.g.

mv /dev/random /dev/random_orig 
ln –sf /dev/urandom /dev/random

There are other ways of providing a good supply of random numbers, but this is the simplest and the easiest to apply and reverse after the upgrade.

Note : The use of dev/urandom should be checked with the relevant security team as the keys produced by /dev/urandom are pseudo-random. However, provided the entropy pool has started using genuine randomness (from entropy) it is not predictable or less secure, even though it uses a reduced number of bits. Only the most sensitive uses of random numbers need the entropy guarantee that /dev/random provides.

8.3.20 Maximum AD Parallel Workers

Be aware that AutoPatch specifies a maximum number of workers. The number depending on several factors, and under certain circumstances may be less than the number of workers required. So the issue will need to be resolved.

There will be an error message similar to the following:

AD utilities can support a maximum of 999 workers. Your current database configuration supports a maximum of Z workers. Oracle recommends that you use between X and Y workers.

In the above message, X, Y and Z are calculated as follows:

X = 2 * cpu_count

Y = 4 * cpu_count

Z = (processes - ((total rows of v$process) + parallel_max_servers)) / 2

Where:

processes = processes db initialization parameter.

parallel_max_servers = parallel_max_servers db initialization parameter.

total rows of v$process = select count(1) from v$process

cpu_count = cpu_count db initialization parameter (or default value).

So, the maximum is basically 50% of the available "processes" (from the database initialization file), once the current number of processes (rows in v$processes) and the maximum that could be used by Parallel Query/DML have been deducted.

See My Oracle Support document "How Does Adpatch Determine The Number Of Workers To Recommend? (Document 800024.1)"

8.3.21 Applications Tier Bottlenecks

Sometimes the applications tier can be the bottleneck.

Note: Upgrading to Oracle E-Business Suite Release 12.2 is performed with a single application tier node and a single database node (even if you are using RAC). Scaling the environment is a post-upgrade task. So using Distributed AD to allocate worker processes to multiple application tier nodes is not possible.

8.3.21.1 Symptoms

Typically in this case all AD worker threads will have jobs assigned, but the DB tier utilisation will be low (e.g. DB Time(s) per second in the AWR Load Profile section will be much lower than the number of AD workers).

This will usually be in the phases:

  • daa+ (especially daa+51/daa+52)
  • dat
  • dat+ (dat+2, dat+24)
  • tab
  • seq
  • administration

With a high number of upgrade files (jobs) being processed, each taking a very short amount of time.

And on the following types of jobs:

  • Generating forms (fmx), reports (rdf), PL/SQL units (pll)
  • Loading ldt files (FNDLOAD)
  • Object definition files (odf/xdf) without a heavy DB load. So this does not include activities such as creating large indexes
  • Applying other middle tier metadata or code such as xml and class files.

During these times. There will be a heavy load on the applications tier node being used for the upgrade.

The Operating System Statistics (particularly for CPU, Memory and I/O) for the application tier node can be checked using OS Watcher or underlying Operating System tools (e.g. mpstat, vmstat and iostat).

See My Oracle Support Documents:

8.3.21.2 Solution

If possible, make sure that the one application tier node used during the upgrade process is configured with a larger RAM, more CPU and faster I/O (e.g. local SSD storage).

See My Oracle Support Document "Can Distributed AD Be Used For Running The 12.2 Upgrade Driver? (Document 1935007.1)".

However, the total time taken on this type of activity may only a maximum of an hour or two and, for larger customers, may be insignificant compared to the whole upgrade time. So, this advice is only likely to be applicable to customers who have smaller databases, but who are heavily reliant on 24x7 operations, with a minimum upgrade window.

8.3.22 Long running instances of AD_ZD_TABLE_APPLY.sql

In the acet phase of the 12.2.n Release Update Pack.

These apply Forward Cross Edition Triggers (4CET) to upgrade tables. They use the Oracle supplied package DBMS_PARALLEL_EXECUTE, which splits the work up into chunks and then uses the Oracle Scheduler (DBMS Scheduler) to execute the chunks in parallel.

If CPU utilization is not particularly high and contention is still low, then there may be scope for increasing the value of job_queue_processes.

If the issue is due to chunk sizes (too large or small) in table DBMS_PARALLEL_EXECUTE_CHUNKS$. This could be because CBO statistics are out of date or have not been gathered on specific table(s) where forward cross edition triggers (4CET) are applied. Gather statistics for the table(s) (using FND_STATS.GATHER_TABLE_STATS) prior to AD_ZD_TABLE_APPLY.sql in the acet phase.

9. Oracle Database Documentation Library Links

The documentation libraries are available here.

VLDB and Partitioning Guide

19c

12c Release 1

11g Release 2

Performance Tuning Guide

19c

12c Release 1

11g Release 2

Change Log

DateDescription
17 Oct 2017

Creation of completely re-written HTML Document to replace a previous document (pdf).

Update for 12.2.7, CUP8, AD/TXK Delta 10.

Includes new sections on optimizer_adaptive_features, parallel_degree_policy, SQLHC, Long running class, xdf, xml and other files (dev/random, dev/urandom), Automation of display cursor.

Changes to Downtime Mode, Planning, Diagnostic, Disabling DBMS Scheduler sections. Change recommendations for AD Parallel workers, parallel_max_servers and job_queue_processes.

05 Apr 2018Added references (and links) to new MOS document "Oracle EBS R12.2 Upgrade - Outstanding Performance Bugs, Issues and Solutions (Document 2357810.1)"
5 Oct 2018

Update for release of 12.2.8, CUP9.

Added and amended new best practices content:

  • Middle Tier Sizing / Applications Tier Bottlenecks

  • Additional Fixed Object and Dictionary statistics information and advice.

  • Additional Materialized View advice, including ADZDMVREFRESHNEEDED.sql

  • Explicit mention of Online Patching Readiness reports and compliance checker in pre upgrade activities.

  • Improved explanation of how AD_ZD_TABLE_APPLY.sql uses DBMS_PARALLEL_EXECUTE to split work into chunks and call DBMS scheduler.

  • Addition of solutions for long running AD_ZD_TABLE_APPLY.sql.

  • Specific instances where extent size and high row length could impact batch size and performance adversely.

  • Advice for initialization parameter db_writer_processes

  • Add references to documents 554539.1 1065393.1 and 2125596.1. Remove references to document 1585857.1

  • Amend SGA and Huge Pages advice

13 Mar 2019Removed references to note 464666.1
18 Jul 2019Replaced deleted note 2230444.1 with 2547319.1
16 Aug 2019

Updated for release of 12.2.9, CUP10

Add container DBID to a SQL (from DB 12.1 onwards)

Removed references to Purge Portal as this only contains a small subset of purge programs.

28 Oct 2019Minor corrections to HTML. Updated for 19c
06 Oct 2020Updated for release of 12.2.10, CUP11 etc. Terminology updates
27 May 2021

Added references to "Performance Best Practices for Oracle E-Business Suite on Oracle Cloud Infrastructure and On-Premises (Document 2528000.1)"


at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Database Options/Management Packs Usage Reporting for Oracle Databases 11.2 and later (Doc ID 1317265.1)

  Database Options/Management Packs Usage Report You can determine whether an option is currently in use in a database by running options_pa...