In This Document

Purpose

The purpose of the E-Business Suite Performance Tuning Guide is to give an overview of :

The typical process flow for resolving performance issues on Oracle E-Business Suite.
How to determine which diagnostic tools and data to use.
The methods available for gathering diagnostics.

The common mistakes to avoid.

This is the first of a series of several documents.
This document gives an overview of how to approach an E-Business Suite performance issue, where to start and how to proceed.
It references other documents that give the detail on each of the main diagnostic tools, the key actions that can be taken to resolve issues and the key areas for performance tuning.
Where there is already comprehensive and up to date content elsewhere (e.g. My Oracle Support, Documentation Library or Blogs) this is referenced rather than repeating the detail of that content in these document.
The aim is to give the user:

The minimum number of documents to read and reference.
Full action plan templates or instructions including any caveats, pre-requisites or important notes.
A flow through each of the diagnostic tools and key actions.

without repeating significant content.

Content Not Included Yet

This is work in progress, there are more links, sections and documents to be subsequently added on the following topics:

Identifying Inefficient SQL
SQL Tuning
Obtaining Explain Plan
CBO Statistics
Initialization and CBO Parameters
Hints
SQL Profiles
SQL Tuning Advisor
Temporary Tables
Dynamic Sampling
ADDM
CBO Trace (10053)
Locking
Bulk PL/SQL and Reducing Executions
RAC
MOAC
Concurrent Manager / Processing
Transaction Managers
AQ / Streams
Parallel SQL and DML
Purging
Partitioning

Solution Flow Diagram

The following diagram is a very simplified view of the process.
There are many other paths and actions that can be taken depending on the information received and the analysts level of experience. There are also other diagnostics that can be obtained. However, this diagram covers the most likely paths.
Solution Flow Diagram

Where to Start

The usual place to start is the SQL Trace and TKPROF. However this is not always the best place.
If the performance issue is not (yet) linked to a specific module or is system wide then an AWR report would be the best starting point.
If the specific SQLs with the performance issues are known, the SQL is still in memory (the cursor is still loaded in the cursor cache) and the "statistics_level" initialization parameter is set to ALL (or the initialization parameter "_rowsource_execution_statistics" is set to TRUE) when the SQLs are executed, then the display cursor or SQL Monitor report can be a good place to start.

Reducing Iterations

Sufficient Information

It is important to get the balance right, between getting all necessary information on the first pass and asking for too much information.
Round trips between parties should be minimized. This can be a significant delay, especially across different time zones, and can create a lot more work.
However, gathering too much data can:

Delay the start of investigation/analysis
Create unnecessary work for the other party (customer) and possibly impact working relationships

So, if the module or program is known, then typically ask for the AWR report and the SQL Trace/TKPROF.
However, many of the other diagnostics should not be requested until later.

Some, like the Forms Trace (Forms Runtime Diagnostics) or Reports Client Process Trace are rarely needed.
Others, like the SQLT, cannot be requested until further information is obtained (like the specific SQL/sql_id).

Correct and Complete Diagnostics

To avoid repeating requests for the same information

Specify exactly what is needed in detail and any common mistakes to avoid. There are some useful “Action Plan” templates in this document.
Review the files as soon as they are received to check that they are correct / complete.

For example :

The SQL Trace should be complete, for the correct program / module and environment.
The SQLT should normally be produced with XTRACT method and run just after the program has completed. It must be for the correct SQL and from the correct environment.
The AWR report / Operating Statistics must be for the correct period of time (snapshots).
The AWR should contain the expected SQL/program and be from the correct environment.

Solution Process Overview

SQL Trace / TKPROF

Around 90% of performance issues on Oracle E-Business Suite are SQL related.
So, if the module/program is known, then start with the SQL Trace (Level 8 – with waits) and TKPROF.
The timed_statistics initialization parameter must be set to TRUE, so that the SQL Trace contains row source statistics (STAT lines) and CPU times.
In most circumstances this will show the actual runtime execution plan with row source statistics, so it should be possible to identify any inefficient execution plan steps from the TKPROF. However, it does not show which predicates are applied on each execution plan line.
Row source statistics are the actual (not estimated) statistics (elapsed time, physical reads/writes, consistent reads) for each execution plan line.
It will also identify any time consuming wait events.
For more information see My Oracle Support Document "Oracle E-Business SQL Trace and TKPROF Guide (Document 1674024.1)".

AWR

The AWR report should normally be obtained on the first pass as well. This should be for the period (between snapshots) that the module / program was running.
This can help identify expensive SQLs, time consuming wait events, system resource issues or DB configuration issues.
It does not report execution plans (runtime or explain plan).
Even if the module/program or SQLs with the performance issue are known:

It could be caused or amplified by general performance issues, configuration or system capacity (I/O, memory, processing etc).
It could be due to interaction or contention with other sessions/processes.
The AWR may have supplementary information that can help diagnose the issue.

In some cases Active Session History (ASH) reports can be used to give more detailed information on which SQLs, Wait Events, Objects and Row Sources the performance issue occurs.
For more information see My Oracle Support Document "Performance Diagnosis with Automatic Workload Repository (AWR) (Document 1674086.1)".

Display Cursor / SQL Monitor Report

If specific SQLs with the potential performance issues are known, the SQL is still in memory (the cursor is still loaded in the cursor cache) and "statistics_level" is set to ALL (or "_rowsource_execution_statistics" is set to TRUE) when the SQL is executed, then the Display Cursor or SQL Monitor report can be a good place to start.
These reports can be produced whilst a SQL is still executing.
If it is not possible to obtain a complete SQL trace (or one that contains row source statistics (STAT) lines) then these reports can also be useful in providing actual runtime execution plan and row source statistics. See "Runtime Execution Plans / Row Source Operation Counts", "Incomplete Trace File" and "11g and Extended SQL Trace" sections in the My Oracle Support document "Oracle E-Business SQL Trace and TKPROF Guide (Document 1674024.1)"
To produce these reports it will normally be necessary to know the sql_id of SQLs with potential performance issues. See here for more information.
The Display Cursor report provides the actual runtime execution plan and row source statistics along with the predicates applied on each execution plan line. Row source statistics are the actual (not estimated) statistics (starts, number of rows, elapsed time, physical reads, buffer gets, memory used (in PGA)) for each execution plan line.
The Display Cursor report 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.
The SQL Monitor report also provides the actual runtime execution plan. It shows row source statistics (executions, rows, memory usage, I/O Requests, CPU Activity, Wait Activity) for each runtime execution plan line, but there are differences with those provided on the Display Cursor report.
It will show executions, rows and memory usage and I/O requests even if the "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.
However, it will only show Active Period, CPU Activity and Wait Activity for execution plan lines 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. So these parameter values are preferred for SQL Monitor reports.
It also gives a good view of how parallel SQL/DML performs across stages of the plan and parallel slaves. However, it is only available in Oracle Database 11g.
Display Cursor report is preferred if the SQL/DML is non-parallel. It is also preferred prior to 11g.
SQL Monitor report is preferred if the SQL/DML is parallel.
The advantage of these diagnostics is that they can be obtained quickly and after the event. There is often no need for another iteration to obtain the SQL Trace / TKPROF.
See here for more information on Display Cursor report.
See here for more information on SQL Monitor report.

Most of the time spent in the application tier

High levels of “SQL*Net message from client” can indicate a large amount of time spent in the client application or on the application tier. This will not just be a high total time, but high average wait time as well.
Normally the “SQL*Net message from client” waits will average around 1ms or less. However, it really depends on the hardware/network configuration and bandwidth between tiers.
Note that:

Very large “SQL*Net message from client” waits are usually due to waiting for user entry on the client, particularly at the start and end of the trace.
A high number of short “SQL*Net message from client” waits usually indicates a high number of SQL executions (which should closely match the execution count of the SQLs) and does not indicate a performance issue on the application tier.
A consistently high level of “SQL*Net message from client” wait times might also indicate network bandwidth or latency issues.

Applications Tier Diagnostics

If, after analyzing the SQL Trace / TKPROF (or even the AWR), the majority of time is not spent in the database then diagnostics from the application tier may be needed.
Even if the application tier is on the same physical host as the database tier, diagnostics will still be needed.
These include:

JVM Logs and Configuration Files. These are in the $INST_TOP directory. The best way of providing these is to zip the $INST_TOP directory.
Forms Trace and Forms Runtime Diagnostics
Reports Client Process Trace
Application Tier Operating System Statistics

Most Spent in SQL Execution

Typically, the majority of time will be spent parsing, executing or fetching in SQL.
However, if

The majority of the reported run time is accounted for in the SQL Trace/TKPROF
There is a high elapsed time on PL/SQL procedures / functions /blocks reported in the TKPROF, but that time is not reported on the SQLs contained in the PL/SQL
Client processing wait events (e.g. "SQL*Net message from client") do not add up to the unaccounted time

then the issue could be in the server side PL/SQL.

PL/SQL Profiler

The PL/SQL Profiler and PL/SQL Hierarchical profiler can be used to analyze which steps in the server side PL/SQL are consuming the time.
More

SQLT with XTRACT Method

Once SQLs with possible sub-optimal execution plans have been identified then obtain the SQLT with XTRACT method for those SQLs.
This provides an html report, which contains most of the data required to identify the reasons why the CBO chose the execution plan.
It contains execution plans, runtime statistics, CBO statistics, DB parameters, CBO parameters, performance statistics and metadata (schema object definitions etc) associated with the SQL.
It saves a huge amount of time by providing all information in one action (and in one zip file).
It is particularly useful if engineers/analysts do not have access to the instance.
The XTRACT method is recommended as it gives actual SQL execution runtime data (if it is available).
Note that the SQLT (with XTRACT and XECUTE methods) 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.
Row source statistics are the actual (not estimated) statistics (starts, number of rows, elapsed time, physical reads, buffer gets, memory used (in PGA)) for each execution plan line.
The XTRACT method will require the SQL ID or SQL Hash Value.
The XPLAIN and XECUTE methods will require a file containing the SQL (and application context / bind variables declared and populated for XECUTE).
Sometimes it is not possible to use SQLT on an environment. 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. So afxplain.sql could be used instead to provide some of the information.
See here for more information on SQLT.

Operating System Statistics

While AWR and trace data give a good initial picture of whether there are issues with I/O, CPU or memory; on linux/unix environments there are a number of individual operating system commands that can be used to monitor overall resource usage, or the resource usage for individual processes. e.g. "sar", "ps", "top", "ifconfig", "mpstat", "iostat", "netstat" "vmstat" etc.
The OS Watcher and LTOM tools provide automated ways of running some of these commands and then collating and presenting the results.
These diagnostics will not be needed if the performance issue is due to sub-optimal SQL execution plans, DB Configuration, Initialization Parameters, Incorrect/Missing CBO Statistics, high numbers of executions etc.
More

Display Cursor Report

This displays the runtime execution plan of any cursor loaded in the cursor cache.
At a 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.
It can be produced by running the dbms_xplan.display_cursor function with the ALL +ALLSTATS option.
The +ALLSTATS option will include actual row source statistics for each execution plan step. ALLSTATS includes IOSTATS and MEMSTATS, e.g. :

Elapsed time
Physical reads
Buffer gets
Memory used (in PGA) for memory intensive operations (such as hash joins, sorts, and bitmap operators).

However, this additional information is only provided if STATISTICS_LEVEL=ALL when the SQL is executed.
Setting the initialization parameter "statistics_level" to ALL can be an overhead, so may not be advisable on a production system.
Alternatively the initialization parameter "_rowsource_execution_statistics" can be set to TRUE, which gives a lower overhead.
These parameters can be set for a specific session (e.g. using the profile option “Initialization SQL Statement - Custom”).
If testing from SQL*Plus then the hint /*+ gather_plan_statistics*/ can be used to collect row source statistics for a specific SQL (even if "statistics_level" is not ALL and "_rowsource_execution_statistics" is FALSE).
Note that SQLT with XTRACT method will also report actual row source statistics in the same circumstances.
The Display Cursor report should be run as soon as possible. If it is delayed, the cursor may have been flushed from memory or invalidated, and no data will be available.
The SQL ID will be required to be able to run the Display Cursor report. See here for information on how to obtain it.
See here for instructions on how to run the Display Cursor report.
By default, the plan statistics are shown for all executions of the cursor, however the keyword LAST can be added to the options to see the statistics for the last execution only.
Here is an example of the output from a display cursor report.

SQL_ID  8d9ny4cf9qmvm, child number 1 
     ------------------------------------- 
     INSERT INTO HZ_CUST_SITE_USES_ALL ( SITE_USE_ID , CUST_ACCT_SITE_ID, 
     SITE_USE_CODE , PRIMARY_FLAG , STATUS , LOCATION , ORG_ID , 
     OBJECT_VERSION_NUMBER, CREATED_BY_MODULE, LAST_UPDATE_DATE , 
     CREATION_DATE , LAST_UPDATED_BY , CREATED_BY , LAST_UPDATE_LOGIN) 
     SELECT HZ_CUST_SITE_USES_S.NEXTVAL, L.CUST_ACCT_SITE_ID, 'LATE_CHARGE', 
     'Y', 'A', TO_CHAR(HZ_CUST_SITE_USES_S.CURRVAL), L.ORG_ID, 1, 
     'AR_LATE_CHARGE_UPG', :B3 , :B3 , -1551, -1551, -1551 FROM (SELECT 
     CAS.CUST_ACCT_SITE_ID, CAS.ORG_ID FROM HZ_CUST_ACCT_SITES_ALL CAS, 
     HZ_CUST_SITE_USES_ALL CSU WHERE CAS.ROWID >= :B2 AND CAS.ROWID <= :B1 
     AND CAS.STATUS = 'A' AND CAS.CUST_ACCT_SITE_ID = CSU.CUST_ACCT_SITE_ID 
     AND CSU.STATUS = 'A' AND (( CSU.SITE_USE_CODE = 'STMTS' AND NOT EXISTS 
     (SELECT NULL FROM HZ_CUST_SITE_USES_ALL B WHERE B.CUST_ACCT_SITE_ID = 
     CAS.CUST_ACCT_SITE_ID AND B.SITE_USE_CODE = 'DUN' AND B.STATUS = 'A')) 
     OR CSU.SITE_USE_CODE = 'DUN')) L 
     
     Plan hash value: 1449143904 
     
     ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
     | Id  | Operation                        | Name                   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   | 
     ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
     |   0 | INSERT STATEMENT                 |                        |    655 |        |       | 52008 (100)|          |      0 |15:49:30.79 |     147M|    101K|       |       |          | 
     |   1 |  LOAD TABLE CONVENTIONAL         |                        |    655 |        |       |            |          |      0 |15:49:30.79 |     147M|    101K|       |       |          | 
     |   2 |   SEQUENCE                       | HZ_CUST_SITE_USES_S    |    655 |        |       |            |          |    109K|15:20:52.78 |     145M|  98825 |       |       |          | 
     |*  3 |    FILTER                        |                        |    655 |        |       |            |          |    109K|15:19:30.01 |     145M|  98825 |       |       |          | 
     |*  4 |     FILTER                       |                        |    655 |        |       |            |          |   5920K|15:02:20.32 |     144M|  80273 |       |       |          | 
     |*  5 |      HASH JOIN                   |                        |    655 |  42541 |  1744K| 52008   (1)| 00:18:18 |   5920K|15:01:52.29 |     144M|  80273 |   963K|   963K|   655/0/0| 
     |*  6 |       TABLE ACCESS BY ROWID RANGE| HZ_CUST_ACCT_SITES_ALL |    655 |  30763 |   751K| 14546   (1)| 00:05:07 |   3507K|00:02:21.51 |   93535 |  80273 |       |       |          | 
     |*  7 |       TABLE ACCESS FULL          | HZ_CUST_SITE_USES_ALL  |    655 |   3356K|    54M| 37454   (1)| 00:13:11 |   4050M|06:58:51.26 |     144M|      0 |       |       |          | 
     |*  8 |     TABLE ACCESS BY INDEX ROWID  | HZ_CUST_SITE_USES_ALL  |    109K|      1 |    17 |     4   (0)| 00:00:01 |      0 |00:20:29.26 |     328K|  18552 |       |       |          | 
     |*  9 |      INDEX RANGE SCAN            | HZ_CUST_SITE_USES_N1   |    109K|      1 |       |     3   (0)| 00:00:01 |      0 |00:20:28.15 |     328K|  18552 |       |       |          | 
     ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
     
     Query Block Name / Object Alias (identified by operation id): 
     ------------------------------------------------------------- 
     
     1 - SEL$F5BB74E1 
     6 - SEL$F5BB74E1 / CAS@SEL$2 
     7 - SEL$F5BB74E1 / CSU@SEL$2 
     8 - SEL$3        / B@SEL$3 
     9 - SEL$3        / B@SEL$3 
   
     Predicate Information (identified by operation id): 
     --------------------------------------------------- 
   
     3 - filter((("CSU"."SITE_USE_CODE"='STMTS' AND  IS NULL) OR "CSU"."SITE_USE_CODE"='DUN')) 
     4 - filter(CHARTOROWID(:B2)<=CHARTOROWID(:B1)) 
     5 - access("CAS"."CUST_ACCT_SITE_ID"="CSU"."CUST_ACCT_SITE_ID") 
     6 - access("CAS".ROWID>=CHARTOROWID(:B2) AND "CAS".ROWID<=CHARTOROWID(:B1)) 
     filter("CAS"."STATUS"='A') 
     7 - filter("CSU"."STATUS"='A') 
     8 - filter("B"."STATUS"='A') 
     9 - access("B"."CUST_ACCT_SITE_ID"=:B1 AND "B"."SITE_USE_CODE"='DUN') 
   
     Column Projection Information (identified by operation id): 
     ----------------------------------------------------------- 
   
     2 - "CAS"."CUST_ACCT_SITE_ID"[NUMBER,22], "CSU"."CUST_ACCT_SITE_ID"[NUMBER,22], "CAS".ROWID[ROWID,10], "CAS"."ORG_ID"[NUMBER,22], "CAS"."STATUS"[VARCHAR2,1], 
   "CSU"."STATUS"[VARCHAR2,1], "CSU"."SITE_USE_CODE"[VARCHAR2,30] 
     3 - "CAS"."CUST_ACCT_SITE_ID"[NUMBER,22], "CSU"."CUST_ACCT_SITE_ID"[NUMBER,22], "CAS".ROWID[ROWID,10], "CAS"."ORG_ID"[NUMBER,22], "CAS"."STATUS"[VARCHAR2,1], 
   "CSU"."STATUS"[VARCHAR2,1], "CSU"."SITE_USE_CODE"[VARCHAR2,30] 
     4 - "CAS"."CUST_ACCT_SITE_ID"[NUMBER,22], "CSU"."CUST_ACCT_SITE_ID"[NUMBER,22], "CAS".ROWID[ROWID,10], "CAS"."ORG_ID"[NUMBER,22], "CAS"."STATUS"[VARCHAR2,1], 
   "CSU"."STATUS"[VARCHAR2,1], "CSU"."SITE_USE_CODE"[VARCHAR2,30] 
     5 - (#keys=1) "CAS"."CUST_ACCT_SITE_ID"[NUMBER,22], "CSU"."CUST_ACCT_SITE_ID"[NUMBER,22], "CAS".ROWID[ROWID,10], "CAS"."ORG_ID"[NUMBER,22], "CAS"."STATUS"[VARCHAR2,1], 
   "CSU"."STATUS"[VARCHAR2,1], "CSU"."SITE_USE_CODE"[VARCHAR2,30] 
     6 - "CAS".ROWID[ROWID,10], "CAS"."CUST_ACCT_SITE_ID"[NUMBER,22], "CAS"."STATUS"[VARCHAR2,1], "CAS"."ORG_ID"[NUMBER,22] 
     7 - "CSU"."CUST_ACCT_SITE_ID"[NUMBER,22], "CSU"."SITE_USE_CODE"[VARCHAR2,30], "CSU"."STATUS"[VARCHAR2,1] 
     8 - "B".ROWID[ROWID,10], "B"."CUST_ACCT_SITE_ID"[NUMBER,22], "B"."SITE_USE_CODE"[VARCHAR2,30], "B"."STATUS"[VARCHAR2,1] 
     9 - "B".ROWID[ROWID,10], "B"."CUST_ACCT_SITE_ID"[NUMBER,22], "B"."SITE_USE_CODE"[VARCHAR2,30] 
   
     Note 
     ----- 
     - dynamic sampling used for this statement (level=7)
     
     150 rows selected.

In the above example, it can clearly be seen that the performance issue is related to a full table scan of HZ_CUST_SITE_USES_ALL (alias CSU) and the subsequent hash join.
The columns Starts, A-Rows, A-Time, Buffers, Reads, OMem, 1Mem and O/1/M are actual statistics. The columns E-Rows, E-Bytes, Cost (%CPU) and E-Time are estimates (calculated by the Cost Based Optimizer).
Most of the information is also included in SQLT with XTRACT method. However, display_cursor provides a simpler view of the information and can be obtained very quickly with little overhead.
The runtime execution plan and row source statistics are also available in views V$SQL_PLAN and V$SQL_PLAN_STATISTICS.
Note : serveroutput must be set to "off" for dbms_xplan to work.

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 slaves.
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.
However, it is only available in Oracle Database 11g.
For more information see Oracle Database Performance Tuning Guide and My Oracle Support Document "Monitoring SQL statements with Real-Time SQL Monitoring (Document 1380492.1)".
The SQL Monitor report will only show the Active Period, CPU Activity and Wait Activity for execution plan lines 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.
This report uses data from several fixed views (e.g. GV$SQL_MONITOR, GV$SQL_PLAN_MONITOR, GV$SQL_PLAN, GV$ACTIVE_SESSION_HISTORY, GV$SESSION_LONGOPS, GV$SQL), so the database user must have the privilege to select from these (e.g. SELECT_CATALOG role).
It can be produced by running the dbms_sqltune.report_sql_monitor function with report_level = ALL.
The SQL ID will be required to be able to run the SQL Monitor report. See here for information on how to obtain it.
See here for instructions on how to run the SQL Monitor report.
By default the SQL Monitor report shows the last (monitored) execution of the SQL. The sql_exec_start and sql_exec_id parameters can be used to display other executions. See the Oracle Database PL/SQL Packages and Types Reference for more information.
SQL Monitor reports are also available from Oracle Enterprise Manager.
Here is an example of the output from a SQL Monitor report:
SQL Monitor Example Part 1

The Estimated Rows and Cost columns in the Monitoring Details section contain estimates, the remaining columns contain actual statistics.
Note that pointing to any of the colored bars on the HTML report will display more information about that statistic. e.g. For Wait Activity it displays the name of the wait events.
Real-Time SQL Monitoring requires the Oracle Tuning Pack. The Oracle Diagnostics Pack is a prerequisite for the Oracle Tuning Pack.
So Oracle Tuning Pack and Oracle Diagnostics must be licensed in order to use Real-Time SQL Monitoring. They can only be purchased with the Enterprise Edition of the Oracle Database.
See My Oracle Support document "Monitoring SQL statements with Real-Time SQL Monitoring (Document 1380492.1)" or Oracle Database Licensing Information in the documentation library.
SQL monitoring is enabled when the initialization parameter "statistics_level" is set to "ALL" or "TYPICAL" (default). The "control_management_pack_access" parameter should also be set to "DIAGNOSTIC+TUNING" (SQL monitoring is part of the Oracle Database Tuning Pack).
SQLs are monitored if they consume more than 5 seconds of CPU or I/O time in a single execution or if they use parallel execution (SQL, DML or DDL).

Forms Trace and Forms Runtime Diagnostics

Forms Trace was introduced with Forms 9i. It an be used with Oracle E-Business Suite R12 to provide information on events in Forms and when they occurred (relative to each other). It can be used to diagnose and investigate Forms performance issues, when it is clear that the time is being consumed in the Form itself and not in the database.
The output an be provided in HTML or XML format.
It can record events such as:

Errors
Navigation
Trigger, LOV, Program Unit, Built-in and User Exit (Start & End)
SQL and Database Events
Communication (Network) Events

Forms Runtime Diagnostics (FRD) is the primary diagnostic tool used in Forms 6i (and hence Oracle E-Business Suite 11i). It also reports on events in Forms and when they occur. The events include

Navigation
Triggers
Built-ins
Messages
Opening of forms executables (.fmx) and menu executables (.mmx)
Unhandled exceptions (including error message/number)
External user events

For R12.0 see My Oracle Support Document "How To Collect And Use Forms Trace (FRD) in Oracle Applications Release 12 (Document 373548.1)". Also see "R12: Forms Runtime Diagnostics (FRD), Tracing And Logging For Forms In Oracle Applications (Document 438652.1)" for more information.
For Release 11i, see My Oracle Support Document "11i/R12: Obtaining Forms Runtime Diagnostics (FRD) In Oracle Applications 11i and Release 12 (Document 150168.1)"
Note that the location in the FORMS_TRACE_DIR variable can also be obtained through Help in Oracle Forms (Go to Help->About Oracle Applications, section "Forms Server Environment Variables"). If the value is not visible then it may be necessary to set profile option "FND: Diagnostics" to "Yes" and bounce the middle tier.

Using Oracle Applications Manager

From R12 onwards Forms Runtime Diagnostics (FRD) can be obtained for another Forms Session using the Monitoring feature within Oracle Applications Manager (Monitoring > Forms Sessions (under Current Activity)).
Click on the Session Details button or the AUDSID to view database information for the selected Forms session.
The Diagnostics button can then be clicked to enable Forms Runtime Diagnostics (FRD) for that Forms Session.
The View Diagnostics button can be used to view the Forms Runtime Diagnostics (FRD) log file.
Once the relevant actions are completed, remember to switch off diagnostics using the Diagnostics button.
Note that the profile option "Sign-On:Audit Level" must be be set to "Form" to be able to use this functionality.
If this button is disabled then check that the Forms patchset level is 12 or higher (i.e. 6.0.8.20 or higher) and set the environment variable FORMS60_OAM_FRD for the Forms Listener process.
See the Oracle E-Business Suite Maintenance Guide (R12.2 and onwards) or System Administrator's Guide – Maintenance (prior to R12.2) for more information.

Reports Client Process Trace

Reports can be traced in three different ways:

Add Options to the Concurrent Program Definition (in 11i and R12).
Specify Debug Options when submitting a Concurrent Request (in R12 and 11.5.10 (with latest ATG patch levels)).
Using Debug Workbench in Oracle Applications Manager (in R12 and 11.5.10 (with latest ATG patch levels)).

These produce identical output.
See here for instructions on how to run.
There are a number of Trace Options. None of them are ideal. For detail reports TRACE_ALL can give significantly distorted timings for the formatting part of the report (Reports Time), as well as produce a very large Reports Trace log file. TRACE_PRF gives an overview of where time is spent; either in formatting the report or retrieving data from the database (UPI or SQL), but no detail.
So, for detail reports it is advisable to use the "Log report performance statistics" (TRACE_PRF) option. If this indicates that the issue is in "Reports Time" or "UPI" time then run the report again with the "Log all possible trace information" (TRACE_ALL) option (which includes TRACE_APP, TRACE_PLS, TRACE_TMS etc).
For summary reports use the "Log all possible trace information" (TRACE_ALL) option.

Choosing Reports Trace Options (Debug Option Values)

The following trace options (TRACEOPTS) and Debug Option Values are available:

Debug Option Values	TRACEOPTS	Comments
Log all possible trace information	TRACE_ALL	This contains all information sources (including the most verbose - TRACE_APP) All sources have timestamps. This is the default. If an incorrect trace option is entered (in Concurrent Program options field) then the this is what will be traced.
Log report object trace information	TRACE_APP	This includes all objects in the report including boilerplate, fields, database columns, frames and groups presented in the report. If the report outputs a high number of lines then this can result in a large number of lines and a very large file. It can also add a very significant overhead to the report timings. There is no timestamp unless combined with TRACE_TMS option.
Log report distribution information	TRACE_DST	This lists report output destination information. There is no timestamp unless combined with TRACE_TMS option.
Log report error and warning messages	TRACE_ERR	There is no timestamp unless combined with TRACE_TMS option.
Log report plsql object trace information	TRACE_PLS	There is no timestamp unless combined with TRACE_TMS option.
Log report performance statistics	TRACE_PRF	This only outputs overall performance statistics: Total Elapsed Time : This is the total execution time of the report. Reports Time : This is the time that was spent on formatting the data and outputting it. Oracle Time : This is the time spent retrieving the data. This is split into UPI and SQL Time. UPI: This is the time spent connecting to the database, parsing and executing the SQL. SQL: This is the time spent in the database (time spent executing SRW.DO_SQL() statements, EXEC_SQL statements, PL/SQL Cursors, etc.).
Log report SQL trace information	TRACE_SQL	This reports the SQL executed by the Report (including SQL Text and Bind values). Note that this is only SQL sent from the Report to the database to be executed. It does not include any additional SQL executed by server side objects (e.g. triggers, functions, procedures). There is no timestamp unless combined with TRACE_TMS option.
Log report time stamp information	TRACE_TMS	This traces nothing on it's own. However, if combined with other options it will add timestamps to each entry in the log.
	TRACE_BRK	Log report breakpoint information All Debug, Info, State and Exception sources with timestamp This is only available from the Concurrent Program definition. It cannot be selected in Debug Options.

Combining options e.g. TRACEOPTS=(TRACE_APP,TRACE_PLS,TRACE_TMS) will include multiple source of information. Note that there are no spaces before or after the comma delimiters.
The most useful options are TRACE_APP, TRACE_PLS and TRACE_PRF.
However, for reports where the output is large compared to the database activity (e.g. detail reports) then TRACE_APP will give significantly distorted timings for the formatting part of the report (Reports Time), as well as produce a very large Reports Trace log file.
So, it is advisable to start with TRACE_PRF. If TRACE_PRF indicates that the issue is in "Reports Time" or "UPI" time then run the report again with TRACE_ALL option (which includes TRACE_APP, TRACE_PLS, TRACE_TMS etc).
Note that the times on the TRACE_ALL for report objects (e.g. boilerplate, fields, columns - TRACE_APP) will contain an overhead for each line, however an analysis of any large time gaps within this section (or elsewhere in the trace) should reveal where time is being consumed (when the report is not being traced).
The difference in performance statistics ("Reports Time") between the TRACE_ALL and TRACE_PRF traces should give an indication of the overhead of the TRACE_ALL option.

Application Tier Operating System Statistics

If a significant amount of time has been spent in the application server then Operating System Statistics should be provided for the application tier.
See Operating System Statistics for more information on how to gather and present these using Oracle's OS Watcher and Lite Onboard Monitor (LTOM) tools.
For Forms the "Forms Process ID" can be obtained through Help in Oracle Forms (Go to Help->About Oracle Applications, section "Forms Server"). If the value is not visible then it may be necessary to set profile option "FND: Diagnostics" to "Yes" and bounce the middle tier.
For Self Service (OAF) the process can be obtained through the "About this Page" link at the bottom left hand side of the page. Go to the "Java System Properties" tab and get the value for the CLIENT_PROCESSID. Note that the "About this Page" link is only available when profile FND_DIAGNOSTICS set to YES.

PL/SQL Profiler

The PL/SQL Profiler can be used to analyze which steps in the server side PL/SQL are consuming the time.
The output is an html report with summary and detail sections, listing the program units and lines with the time consumed.
There are three ways of producing a PL/SQL Profiler report in Oracle E-Business Suite, depending on the module being profiled.

From Oracle Forms
From Self Service (Oracle Applications Framework)
From a Concurrent Request

See here for more information on how to use these methods.

Enable PL/SQL Profiler manually in PL/SQL

PL/SQL Profiler data can be collected manually by enabling and disabling directly in PL/SQL.
This could be useful if testing directly from SQL*Plus / SQL Developer (i.e. outside of E-Business Suite).
This requires updating the PL/SQL code and re-compiling.
Care is also needed to ensure that the commands to enable and disable are placed at the correct points (where they will definitely be executed and capture all the necessary actions).
Enable PL/SQL Profiler using:

dbms_profiler.start_profiler(run_comment => ‘<comment>’, run_comment1 => ‘<comment 1>’);

Disable PL/SQL Profiler using:

dbms_profiler.stop_profiler;

Note that profiler data is stored in memory and is flushed into profiler tables when the profiler is stopped. It can be advisable to run dbms_profiler.flush_data at intervals to flush data to tables and free up memory.
See the following for more information:

My Oracle Support Document "Using DBMS_PROFILER (Document 97270.1)"
Oracle Database PL/SQL Packages and Types Reference

Other reporting options

Additionally, the script profiler.sql can be run as follows to list the top resource consuming PL/SQL lines and the code lines around them.
START profiler.sql <run id>;
This can be downloaded from My Oracle Support Document “Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data (Document 243755.1)”. Sample output can also be downloaded.
There are also reporting scripts profsum.sql and profrep.sql available on older database versions.

Interpretation

The standard E-Business Suite “PL/SQL Profiler Report” contents are self-explanatory.
It contains the total elapsed time for the run (run ID).
If the run is part of a related run then the grand total for the related run is also reported.
The “Execution Summary By Program Units” section reports on the total elapsed time for each program unit (e.g. Package Body) where the total time is greater than 1% of the total.
The “Execution Details For Program Units” sections give a breakdown of the total elapsed time and executions of each source code line within the program units. This is sorted by line number and not by total time.
There is an example in My Oracle Support Document “How to generate pl/sql profiler output for the concurrent program under EBS? (Document 808005.1)”.

Technical detail

The PL/SQL Profiler implemented in E-Business Suite comprises Oracle Database functionality, augmented by E-Business Suite functionality.
There is also a reporting script provided by Oracle Server Technologies Center of Expertise.
Here is a description of the components:
and

Object	Source	Type	Comments
plsql_profiler_runs	Database	Table	Contains information for the PL/SQL Profiler Runs
plsql_profiler_units	Database	Table	Contains information for each program unit (e.g. Package)
plsql_profiler_data	Database	Table	Contains information for each line of code in program units
$ORACLE_HOME/rdbms/admin/proftab.sql	Database	SQL Scipt	Installs the tables plsql_profiler_runs, plsql_profiler_units and plsql_profiler_data
dbms_profiler	Database	Package	Used to start, stop, pause, resume and flush the profiler
$ORACLE_HOME/rdbms/admin/profload.sql	Database	SQL Script	Installs the package dbms_profiler. Calls dbmspbp.sql and prvtpbp.plb
dbmspbp.sql	Database	SQL Script	dbms_profiler package specification
prvtpbp.plb	Database	SQL Script	dbms_profiler package body specification
PL/SQL Profiler Report	E-Business Suite	Concurrent Program	The executable is FNDPMPRPT. Calls fnd_trace_utils.plsql_prof_rpt
fnd_trace_utils	E-Business Suite	Package	Used to produce the PL/SQL Profiler report. Uses dbms_profiler hidden procedure (rollup_run) and accesses tables plsql_profiler_runs, plsql_profiler_units and plsql_profiler_data.
AFPMUTLS.pls	E-Business Suite	SQL Script	fnd_trace_utils package specification
AFPMUTLB.pls		SQL Script	fnd_trace_utils package body specification
profiler.sql	Oracle Server Technologies Center of Expertise	SQL Script	Provides an alternative report on PL/SQL Profiler data.

E-Business Suite Forms, Self Service (OAF) and the Debug Rules/Options functionality available from Oracle Applications Manager and Concurrent Request submission all utilise the database functionality (package dbms_profiler and plsql_profiler tables to collect PL/SQL profiler data).
See Oracle Database PL/SQL Packages and Types Reference for more information on the database tables and package.

Installing PL/SQL Profiler package

To install the PL/SQL Profiler tables, run the following as the APPS user:

$ORACLE_HOME/rdbms/admin/proftab.sql

Note that if the tables and sequence (in this file) are created in a different schema then synonyms will be needed in the APPS schema.
To install the dbms_profiler package, run the following (as the user running the application e.g. APPS):

$ORACLE_HOME/rdbms/admin/profload.sql

See My Oracle Support Document "Using DBMS_PROFILER (Document 97270.1)"

Common issues

If the PL/SQL Profiler report(s) do not return any detailed information then it could be because the PL/SQL is compiled in NATIVE mode. See My Oracle Support document "DBMS_PROFILER does not Return Detailed Information (Document 602711.1)" and Oracle Database PL/SQL Packages and Types Reference for more information.
Note that if the PL/SQL profiler objects (installed with PROFTAB.sql and PROFLOAD.sql) are created in a different user than APPS, but are accessed by other database users then it will be necessary to add privileges (Grant SELECT/INSERT/UPDATE) and synonyms for the tables and sequences. See Oracle Database PL/SQL Packages and Types Reference for more information.

PL/SQL Hierarchical profiler (HPROF)

The PL/SQL hierarchical profiler is available from 11G Release 1 onwards.
It has a better hierarchical breakdown of the time spent in each program unit, showing how much time was consumed in the actual program unit (Function), the number of calls and how much time was consumed in its sub-programs (Descendents).
Time spent in SQL and PL/SQL is accounted separately.
However, it does not show the time spent at line level and it is not integrated into Oracle E-Business Suite functionality like PL/SQL Profiler.
It does not need to be inserted into source code, but it can only be enabled for the current session.
So for Oracle E-Business Suite the only way it can be enabled (without modifying code) is to use the profile option “Initialization SQL Statement - Custom”(internal name FND_INIT_SQL), adding the following:

BEGIN Dbms_hprof.start_profiling('<profiling directory>','<trace_filename>'); END;

The results are stored in a trace file, but can be reported in html output or written to database tables for further analysis.
See here for instructions on how to obtain and report PL/SQL Hierarchical Profiler information.
For more information see:

My Oracle Support document “How to Tune PL/SQL Applications and Identify Hot Spots using DBMS_HPROF (Document 763944.1)”
Oracle Database Advanced Application Developer's Guide
Oracle Database PL/SQL Packages and Types Reference

SQLT with XTRACT Method

This provides an html report, which contains most of the data required to identify the reasons why the CBO chose the execution plan.

It contains execution plans, runtime statistics, CBO statistics, DB parameters, CBO parameters, performance statistics and metadata (schema object definitions etc) 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 saves a huge amount of time by providing all information in one action (and in one zip file).
It is particularly useful if engineers/analysts do not have access to the instance.
It is developed and supported by the Oracle Server Technologies Center of Expertise (ST CoE).

Note that SQLT requires its own source code and objects on the database, so it needs to be installed.
See My Oracle Support document “SQLT Diagnostic Tool (Document 215187.1)” for more information.
It has 3 main methods (XPLAIN, XTRACT and XCUTE).

XTRACT. This obtains the runtime execution plan from memory or AWR. The SQL ID or SQL Hash Value will be required.
XPLAIN. This requires a file containing the SQL and uses the EXPLAIN PLAN FOR statement to obtain the explain plan.
XECUTE. This requires a SQL script containing the SQL to be executed, with the application context set and bind variables declared and populated.

The XTRACT method is recommended because it gives the runtime execution plan (provided the runtime execution plan is available in memory or AWR).
If the runtime execution plan has been obtained from another source (e.g. SQL Trace, Display Cursor or SQL Monitor) then XPLAIN will suffice.

Methods

There are 3 main methods:

XTRACT

This does not actually execute the SQL, so a script is not needed and time is saved.
It requires the SQL ID or SQL Hash Value.
It obtains the runtime execution plan from memory or AWR and row source statistics (if available) from memory.
It is RAC aware.
If the SQL was executed when “statistics_level” was set to ALL (or "_rowsource_execution_statistics" is set to TRUE) then it will contain actual row source statistics on the execution plan.
It also uses SQL Tuning Advisor and Test Case Builder. If the SQL is no longer in memory or AWR then this method errors.
This is the preferred option.
However, for maximum benefit it should be run soon after the SQL has executed.

XPLAIN

This requires a file containing the SQL.
This may report a different execution plan than the one used at runtime.
It is also based on the EXPLAIN PLAN FOR statement, so it does not do bind peeking.
It has less information than XTRACT or XECUTE.
However, if the runtime execution plan has already been obtained from another source (e.g. SQL Trace, Display Cursor or SQL Monitor) and the SQL/execution plan is no longer in memory or AWR then this is the preferred option.

XECUTE

This requires a SQL script containing the SQL to be executed, with the application context set and bind variables declared and populated.
Although this provides all the information, bear in mind that it may use a different execution plan than the one used at runtime.
It may be difficult to provide a SQL script that reproduces the conditions (data state, sequence of actions etc) of the program. So, it is preferable to run the program again and use the XTRACT method.
The majority of SQLs in E-Business Suite use either Multi-org views (11.5.10) or Synonyms (R12). These have database policies and contexts applied (virtual private database). In addition there are multi-language views/tables and HR uses security groups. These views and policies rely on the application context (e.g. the user, responsibility, application and security group) to determine the data returned by views or synonyms. Without the application context being set these views/synonyms will return no rows.

Preference

So the preference is:

XTRACT, if the SQL/runtime execution plan is still in memory or AWR.
XPLAIN, if the runtime execution plan has been obtained from another source (e.g. SQL Trace, Display Cursor or SQL Monitor). This will provide the remaining information (e.g. CBO statistics, DB parameters, CBO parameters, performance statistics and metadata) associated with the SQL.
Otherwise Run the program again (with “statistics_level” set to ALL / "_rowsource_execution_statistics" set to TRUE) and use the XTRACT method.

Row Source Statistics / Statistics_Level = ALL

The SQLT (with XTRACT or XECUTE method) will also report the row source statistics if they are available.
Row source statistics are the actual (not estimated) statistics (starts, number of rows, elapsed time, physical reads, buffer gets, memory used (in PGA)) for each execution plan line.
They are available 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.
If testing from SQL*Plus then the hint /*+ gather_plan_statistics*/ can be used to collect row source statistics for a specific SQL (even if "statistics_level" is not ALL and "_rowsource_execution_statistics" is FALSE). This could also be used in the SQL script if using the XECUTE method.

Use the XTRACT method as soon as possible after a SQL statement executed

There are three reasons for this:

If the row source statistics have been gathered (e.g. “statistics_level” set to ALL / "_rowsource_execution_statistics" is set to TRUE) then there is a higher probability that they will still be in memory.
If the AWR retention period is short then there is a higher probability that the runtime execution plan will still be in memory or AWR.
There is a higher probability that the CBO statistics and other metadata will be consistent with the state when the SQL was executed. The SQLT reports the CBO statistics at the time the SQLT is run, not the point at which the SQL was executed. There will be cases where the CBO statistics have been subsequently gathered for some tables (either as part of Oracle E-Business Suite business processes or upgrades or manually by DBAs).

Latest Version

The SQLT tool will be updated from time to time.
Always make sure that latest version (for the database version) is installed.
The latest versions can be downloaded from the My Oracle Support document “SQLT Diagnostic Tool (Document 215187.1)”.
There are different versions of SQLT for each database version (from 9.2 through to 12c). Always download and install the version specific to the database version being used.
See My Oracle Support document “SQLT Changes (Document 1614201.1)” for information on the changes in each version.

More Information

See My Oracle Support document “SQLT Diagnostic Tool (Document 215187.1)”.
This document contains:

A number of attachments including sample output and a powerpoint presentation that provide more information about SQLT.
Links to other My Oracle Support Documents, which include usage instructions, frequently asked questions and changes. E.g.
- SQLT Changes (Document 1614201.1)
- SQLT Usage Instructions (Document 1614107.1)
- FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions (Document 1454160.1)
- Links to some My Oracle Support Community (MOSC) discussions.

The installation zip files contain useful information in the following files:

sqlt_instructions.html / sqlt_instructions.txt – Instructions for installation and usage.
sqlt_changes.html – Changes for each version.
The version for Database Release 9i and 10.1 also contains some presentations (SQLTXPLAIN1.pdf / SQLTXPLAIN2.pdf / SQLTXPLAIN3.pdf) on how to use SQLT and interpret the output.

Installing and Running

See the sqlt_instructions.html file that is delivered with the install zip for instructions on installing and running SQLT. These are also in My Oracle Support Document “SQLT Usage Instructions (Document 1614107.1)”
For the XTRACT method the SQL ID or Hash Value are required.
For the XECUTE method a text file containing the SQL, application context and declared / initialized bind variables (if required) will be needed.
For the XPLAIN method a text file containing the SQL text is required. If the SQL contains bind variables then they can be left in the SQL or replaced with literals of the same datatype. However, remember to use TO_DATE conversions on literals for date columns.
Be aware that XPLAIN is blind to bind variable values used in the SQL statement.

Installation or Running Issues

General

Many of the errors that occur are usually due to incorrect installation.
Please obtain the latest version from My Oracle Support document “SQLT Diagnostic Tool (Document 215187.1)”.
Refer to the instructions sqlt_instructions.html (also on My Oracle Support Document “SQLT Usage Instructions (Document 1614107.1)”).
Check the install log files (*.log).
And re-install exactly as indicated in the instructions, using default names for the directories.
See My Oracle Support Document “Troubleshooting SQLT Issues (Document 1521607.1)” for links to some useful My Oracle Support Community (MOSC) discussions on SQLT issues.

Issues writing to files

By default, SQLT will pick up the stage directories location to use from the database parameter "user_dump_dest".
In Database version 11.2 the install script can fail with errors (ORA-29283: invalid file operation, ORA-20102: SQLT$STAGE: not capable of reading or writing), because the directory location in initialization parameter “user_dump_dest” is a symbolic linked directory.
See my Oracle Support document “Installing SQLT fails with ORA-29283: invalid file operation (Document 1362270.1)” for more information and a solution.

Obtaining SQL ID and Hash Value

The XTRACT method requires the SQL ID or the SQL hash value (not to be confused with the plan hash value).
See here for more information on obtaining the SQL ID.
For 10g and before, if only the SQL Trace/TKPROF is available then the hash value can be obtained from the SQL Trace (in the “hv=” token in the PARSING IN CURSOR line above the SQL Text).
On 11G the TKPROF and SQL Trace also contain the plan_hash (identified by plh= token in the SQL Trace). This is not to be confused with the SQL hash value.

Alternative to SQL (afxplain.sql)

Sometimes it is not possible to use SQLT on an environment. 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.
$FND_TOP/sql/afxplain.sql can be used to produce some of the same output as SQLT.
It does not require any installation on the environment (other than the script file itself) and has a negligible performance impact.
It provides the following information:-

Database/Apps version, O/S version and instance/database name
Explain Plan with cost and predicate information
Amount of shared memory
Object information including Package, View and Triggers
Statistics for all objects referenced by SQL (including columns and indexes)
Non-default Database parameters (init.ora)

A .sql file containing the problematic SQL is required, but without ; or / appended.
afxplain.sql has 3 arguments:

The name of the file containing the SQL
CBO Trace required (Y or N)
CBO Stats Export (Y or N)

It generates output with the name <sqlfile>.out.
Where <sqlfile> is the name of the file containing the SQL.

Misleading CBO Statistics on SQLT

Note that the SQLT will report CBO Statistics and other metadata at the time it is run, not the point at which the program /SQL was run.
It is possible that these could have changed since the program (with the performance issue) was run (especially the CBO Statistics).

CBO statistics could have been gathered for some tables since the program was run. For example, in the main R12 upgrade, the script adsstats.sql is run towards the end of the upgrade. This gathers CBO stats for all tables that are new or have grown/shrunk significantly.
Similarly, data may have been deleted from temporary, transitory or interface tables.
Or there may have been manual actions.
Global Temporary tables will usually contain data when they are accessed by the program (for that session), but will have no rows when accessed by SQLT (and a different session), even if the original session (and the rows in the Global Temporary table) persist.

Improving Performance of SQLT

Note that SQLT runs AWR and ASH reports. Some dictionary objects (particularly WRH$_LATCH_CHILDREN) will have grown significantly during the upgrade. So, fixed object and dictionary statistics should be gathered before running SQLT, especially if statistics_level is set to ALL (and is normally set to TYPICAL or BASIC).
SQLT can take quite a while to run.
To reduce the workload, it is recommended that the following is 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 the table SQLTXPLAIN.SQLI$_PARAMETER.
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.

Operating System Statistics

On linux/unix environments there are a number of individual operating system commands that can be used to monitor overall resource usage, or the resource usage for individual processes. (e.g. "sar", "ps", "top", "ifconfig", "mpstat", "iostat", "netstat" "vmstat" etc.).
However, the OS Watcher and LTOM tools provide automated ways of running some of these commands and then collating and presenting the results.

OS Watcher

Oracle’s OS Watcher tool (oswbb) runs shell scripts that run the operating system tools (ps, top, ifconfig, mpstat, iostat, netstat, traceroute, vmstat, meminfo (Linux Only), slabinfo (Linux Only)) to collect CPU, Memory, I/O and Network usage and collate the results. OSWatcher Analyzer (oswbba) presents these results graphically.
oswbb and oswbba require installation, however this is relatively simple and there is little overhead.
By default the diagnostics are only retained for 48 hours, but this can be configured.
Note that it is a unix / linux based utility.
For more information see My Oracle Support Documents:

OSWatcher (Includes: [Video]) (Document 301137.1)
OS Watcher User's Guide (Document 1531223.1)
OSWatcher Analyzer User Guide (Document 461053.1)

LTOM

The Lite Onboard Monitor (LTOM) is a java program that runs on unix / linux (e.g. Solaris, Linux, HP-UX, AIX, Tru64)
The LTOM System Profiler uses information from operating system commands (such as "top", "vmstat" and "iostat") and Oracle database views (v$session, v$process, v$sesson_wait, v$system_event and v$system_statistics) to profile the system usage.
LTOM also includes:

Automatic Session Tracing, which determines which sessions to SQL trace (Extended SQL Trace) using a set of rules, which can be user defined.
Automatic Hang Detection, which uses configurable rules (based on database wait events) to identify and trace sessions that may have hung. It can also notify by email.
LTOMg graphing utility, which can be used to present the information produced by the LTOM System Profiler in graphs and an html report.

See the My Oracle Support documents

LTOM - The On-Board Monitor User Guide (Document 352363.1)
The LTOM Graph (LTOMg) User Guide (Document 461050.1)
LTOM System Profiler - Sample Output (Document 461052.1)
The LTOM Graph FAQ (Document 461228.1)

Diagnosing High CPU Usage in Modules

The My Oracle Support Document "How to Diagnose High CPU Usage Problems to the Module Level (Document 352648.1)" gives some tools and techniques for investigating processes that consume a large amount of CPU where there is no specific activity (e.g. very high number of logical reads) or wait event that explains this.

Action Plan Templates

Display Cursor Report

Please produce a Display Cursor Report for SQL ID <sql_id>.
For the Display Cursor Report to be useful (i.e. contain actual row source statistics as well as the runtime execution plan).

The initialization parameter "statistics_level" must be set to ALL (or "_rowsource_execution_statistics" set to TRUE) at the time the SQL is executed.
The SQL must still be loaded in the cursor cache (memory).

The report can be produced by running:

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;

See here for how to obtain the SQL ID.

SQL Monitor Report

Please produce a SQL Monitor report for SQL ID <sql_id>.
The SQL Monitor report can only be used if:

On Oracle Database 11g and above
Oracle Tuning Pack and Oracle Diagnostics are licensed
The "control_management_pack_access" parameter is set to "DIAGNOSTIC+TUNING"
The initialization parameter "statistics_level" is set to "ALL" or "TYPICAL" (default) when the SQL is executed.
The SQL consumes more than 5 seconds of CPU or I/O time in a single execution or it uses parallel execution (SQL, DML or DDL).

Note that the SQL Monitor report will only show the Active Period, CPU Activity and Wait Activity for execution plan lines 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.
The report can be produced by running the following. The HTML format is preferred:

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

SQL Monitor reports are also available from Oracle Enterprise Manager.
See here for how to obtain the SQL ID.

Reports Client Process Trace

Please provide a Reports Client Process Trace.
For summary reports use the "Log all possible trace information" (TRACE_ALL) option.
For detail reports use the "Log report performance statistics" (TRACE_PRF) option. If this indicates that the issue is in "Reports Time" or "UPI" time then run the report again with the "Log all possible trace information" (TRACE_ALL) option.

Add Options to the Concurrent Program Definition

This method is available in both 11i and R12.
To produce a Reports Client Process Trace at Concurrent Program Level:
1. Log in to the environment that will be traced.
2. Choose the relevant responsibility for defining concurrent programs.
3. Navigate to the Concurrent > Program > Define window.
4. Query for the concurrent program to be traced.
5. Turn on Reports Trace for the concurrent program by entering TRACEOPTS=TRACE_ALL and TRACEFILE=<full path and filename> in the Options field. The two keywords are delimited by a space. TRACEOPTS can also be set to TRACE_PRF. See here.
Options must contain TRACEFILE along with TRACEOPTS, otherwise the request will error with "REP-0715: Trace file must be specified when tracing."
6. Navigate to the Submit Request window (e.g. Request > Run).
7. Submit a concurrent request for the concurrent program.
8. Make a note of the request_id of the concurrent request.
9. Check the progress of the concurrent request in the requests window.
10. When the request has completed navigate to the Concurrent > Program > Define window and disable the Reports Trace by clearing the Options field.
11. Obtain the trace file from the location specified in TRACEFILE above.
Other values for TRACEOPTS are available. See here for a full list.
The TRACEMODE parameter can also be used to specify if trace information is appended to the file (TRACE_APPEND) or overwrites it (TRACE_REPLACE).

Specify Debug Options when submitting a Concurrent Request

This method is available in R12 and 11.5.10 (with latest ATG patch levels).
To produce a Reports Client Process Trace at Concurrent Request Level:
1. Log in to the environment that will be traced.
2. Set the profile option “Concurrent: Allow Debugging“ (internal name FND_CONC_ALLOW_DEBUG) user-level value to “Yes”.
3. Navigate to the Submit Request window (e.g. Request > Run).
4. Select the Concurrent Request to be run.
5. Click on the “Debug Options” button.
6. Enable Reports Trace by checking the “Reports Trace” Debug Option and then select Debug Option Value "Log all possible trace information". Debug Option Value "Log report performance statistics" can also be used. See here.
7. Click the “OK” button to confirm the selection. A “Create Debug Rule : Debug Option” page is displayed. Click on the “OK” button again.
8. Submit the concurrent request.
9. Make a note of the request_id of the concurrent request.
10. Check the progress of the concurrent request in the requests window.
11. When the request has completed, the trace file name and location (full path) can be obtained from the concurrent program log (file) .
Other levels are available in the Debug Option Value. See here for a full list.

Using Debug Workbench in Oracle Applications Manager

In both 11.5.10 (with latest ATG patch levels) and R12 it is also possible to specify Debug Options in the Debug Workbench in Oracle Applications Manager by creating a Debug Rule.
Site Map > Diagnostics and Repair (tab) > Diagnostics (heading) > Debug Workbench (link).
Create a Debug Rule from the Debug Workbench, select “Reports Trace” Debug Option and then select Debug Option Value "Log all possible trace information". Debug Option Value "Log report performance statistics" can also be used. See here.
The Debug Rule also specifies the context (either the specific User, Responsibility or Any (All)) and the number of repetitions or time span for which the debug/trace will be gathered.
By default the Debug Options are disabled. They can be enabled by setting the profile option “Concurrent: Allow Debugging“ (internal name FND_CONC_ALLOW_DEBUG) user-level value to “Yes”.
Once the relevant actions are completed, remember to delete or end date the Debug Rule.
See the Oracle E-Business Suite Maintenance Guide (R12.2 and onwards) or System Administrator's Guide – Maintenance (prior to R12.2) for more information.

PL/SQL Profiler Report

Please provide a PL/SQL Profiler Report.

Obtain PL/SQL Profiler Report from Forms

To obtain a PL/SQL Profiler report for Oracle Forms:
1. Log in to the environment that will be traced.
2. Set the profile option “Utilities:Diagnostics“ (internal name DIAGNOSTICS) user-level value to “Yes”.
3. Choose the relevant responsibility for the form and navigate to the form that will be traced.
4. Enable SQL Trace using the menu option : Help > Diagnostics > Trace. Check the “PL/SQL Profiling” box.
5. Execute the required actions on the Form.
6. When complete, disable SQL Trace using the menu option : Help > Diagnostics > Trace > Uncheck the “PL/SQL Profiling” box.
7. If the "PL/SQL Profiler Report" concurrent request is not started automatically then do the following:

Identify the run id for the actions traced above using the SQL:
SELECT runid, related_run, TO_CHAR(run_Date, 'DD-MON-YYYY HH24:MI:SS'), run_comment, run_comment1 FROM plsql_profiler_runs; 
Submit a request for concurrent program “PL/SQL Profiler Report” from the “System Administrator” responsibility.
Enter the parameters Run ID and Purge Profiler Data = “No” (default is “Yes“).

8. When the "PL/SQL Profiler Report" concurrent request is complete, click on the “View Output” button to see the PL/SQL Profiler report.

Obtain PL/SQL Profiler Report from Self Service

This is also sometimes referred to as OAF (Oracle Applications Framework) after the technology framework used on these applications.
To obtain a PL/SQL Profiler report for Self Service (OAF) pages:
1. Log in to the environment that will be traced.
2. Set the profile option “FND: Diagnostics“ (internal name FND_DIAGNOSTICS) user-level value to “Yes”.
3. Choose the relevant responsibility for the HTML-based application.
4. To enable SQL Trace click the “Diagnostics” icon at the top right of the page > Set Trace Level > Go > Enable PL/SQL Profiler > Save.
5. Select “Home” and then execute the required actions.
6. When complete, disable the trace by clicking on the “Diagnostics” icon > Set Trace Level > Go > Disable PL/SQL Profiler > Save
7. The “Trace Ids” will be provided on the left hand side of the screen. This is relevant for SQL Traces (not PL/SQL Profiler)
8. Exit the HTML Application and log out.
9. If the "PL/SQL Profiler Report" concurrent request is not started automatically then do the following:

Identify the run id for the actions traced above using the SQL:
SELECT runid, related_run, TO_CHAR(run_date, 'DD-MON-YYYY HH24:MI:SS'), run_comment, run_comment1 FROM plsql_profiler_runs; 
Submit a request for concurrent program “PL/SQL Profiler Report” from the “System Administrator” responsibility.
Enter the parameters Run ID and Purge Profiler Data = “No” (default is “Yes“).

10. When the "PL/SQL Profiler Report" concurrent request is complete, click on the “View Output” button to see the PL/SQL Profiler report.

Obtain PL/SQL Profiler Report for a Concurrent Request

Note that this method is only available in R12 and 11.5.10 (with latest ATG patch levels).
To obtain a PL/SQL Profiler report for a Concurrent Request :
1. Log in to the environment that will be traced.
2. Set the profile option “Concurrent: Allow Debugging“ (internal name FND_CONC_ALLOW_DEBUG) user-level value to “Yes”.
3. Navigate to the Submit Request window (e.g. Request > Run).
4. Select the Concurrent Request to be run.
5. Click on the “Debug Options” button.
6. Enable SQL Trace by checking the “PL/SQL Profiler” Debug Option. There is only one Debug Option Value "PL/SQL Profiler", which is already selected.
7. Click the “OK” button to confirm the selection. A “Create Debug Rule : Debug Option” page is displayed. Click on the “OK” button again.
8. Submit the concurrent request.
9. Make a note of the request_id of the concurrent request.
10. Check the progress of the concurrent request in the requests window.
11. When the request has completed, the “PL/SQL Profiler Report” concurrent program will be submitted.
12. When the “PL/SQL Profiler Report” request has completed, select the “View Output” button (of the “PL/SQL Profiler Report”) to view the output.
See My Oracle Support document “How to generate pl/sql profiler output for the concurrent program under EBS? (Document 808005.1)” for more information.

PL/SQL Hierarchical Profiler Report

Please provide a PL/SQL Hierarchical Profiler report.
To enable the PL/SQL Hierarchical profiler for an E-Business Suite session using the Profile Option method:
1. Log in to the environment that will be traced.
2. If the PL/SQL Hierarchical profiler is being enabled for the current (logged in) user account then navigate to the Profile > Personal window, otherwise navigate to the Profile > System window.
3. Query the profile “Initialization SQL Statement - Custom”(internal name FND_INIT_SQL).
4. Enter the following as the profile value and save.

BEGIN dbms_hprof.start_profiling('<profiling directory>','<raw_output_filename>'); END;

Note: If there is existing SQL in the profile then the above should be merged with the existing SQL.
5. Log in (again) to the application using the user account being traced. This is necessary because the Initialization SQL Statement is only executed at login.
6. Execute the actions to be profiled.
7. Exit the application and log out.
8. Navigate to the profile form again, remove the SQL above (ensuring any original SQL remains) from the “Initialization SQL Statement - Custom” profile and save.
9. Obtain the raw output file from the file location specified in dbms_hprof.start_profiling above.
10. Generate PL/SQL hierarchical profiler HTML reports from the raw output file using:

plshprof -output <html_root_filename> <profiler_output_filename>

Where
<html_root_filename> is the name of the root HTML file to be created.
<profiler_output_filename> is the name of a raw profiler output file.
11. This will create a set of HTML reports, which can be browsed starting from the root HTML file. Please provide all these files.

SQLT

Please provide SQLT output using the XTRACT/XPLAIN/XECUTE method for <SQL/sql_id>. See My Oracle Support Document 215187.1.
[XTRACT Method only, and runtime execution plan already known] If the SQL is no longer in memory (or AWR) then provide a SQLT with XPLAIN method instead.
For installation and running instructions read the file sqlt_intructions.html (included in the SQLT installation zip file).
SQLT should be provided on same environment that performance issue has been observed and should be run as soon after the observed program/process as possible.
[XTRACT Method only] The XTRACT method requires the SQL ID or the SQL hash value (not to be confused with the plan hash value).
[XTRACT Method only] See here for how to obtain the SQL ID.
[For 10g and before, if only the SQL Trace/TKPROF is available] The hash value can be obtained from the SQL Trace (in the “hv=” token in the PARSING IN CURSOR line above the SQL Text).
[XTRACT Method and program rerun only] Set "statistics_level" initialization parameter to ALL ( or "_rowsource_execution_statistics" to TRUE) before re-running the program (which runs the SQL in question) and running SQLT with XTRACT.
[XECUTE Method only] Please use the attached file. This file contains the application context, bind variable declaration / initialization and the SQL text.
[XECUTE Method only] If the SQL contains context sensitive views/synonyms/policies (e.g. Multi-Language or Multi-Organization in Oracle E-Business Suite) then the application context (language, user, responsibility, security group) will also need to be set in the SQL script. The "Obtaining Traces (TKPROF) in E-Business Suite - From SQL*Plus" section in the My Oracle Support document "Oracle E-Business SQL Trace and TKPROF Guide (Document 1674024.1)" has useful instructions on doing this.
[XPLAIN Method only] Please use the attached file containing the SQL text.

AFXPLAIN.SQL

Please provide the output from the $FND_TOP/sql/afxplain.sql script for the <SQL/sql_id>.
Copy this script to a working directory (e.g. user or temporary).
Create a .sql file containing the problematic SQL in the same directory. Do not append the SQL with ; or /
From the working directory login into sqlplus and run the script.

@afxplain.sql <sqlfile> N N

Where <sqlfile> is the name of the file containing the SQL
The second argument (N or Y) specifies whether a CBO Trace (event 10053) will be produced as well.
The third argument (N or Y) specifies if the CBO statistics will be exported.
This generates output with the name <sqlfile>.out in the working directory.

Obtaining SQL ID

The sql_id can be obtained from:

AWR reports (SQL Statistics section)
ASH reports
TKPROF (if 11g and above)
SQL Trace (if 11g and above) (sqlid= token in the PARSING IN CURSOR line above the SQL Text)
View V$SQL
Oracle Enterprise Manager.

If querying v$SQL, use the following query:

SELECT sql_id, hash_value, SUBSTR(sql_text,1, 80)
FROM v$sql  
WHERE sql_fulltext LIKE '%<part of SQL text>%';

Alternatively, the sql_text column can be used to search on the first 1000 characters.
The UPPER function could also be used on column sql_fulltext or sql_text if the exact case of some of the SQL is unknown.
See My Oracle Support Document "How to Determine the SQL_ID for a SQL Statement (Document 1627387.1)".

Oracle Database Documentation Library Links

The documentation libraries are available here.
They are also available here.

Change Log

Date	Description
01 May 2014	Initial creation.

Attachments

SQL Monitor Example 1(396.71 KB)
SQL Monitor Example 2(237.12 KB)
Flow Diagram showing the steps in obtaining diagnostics for EBS Performance Issues(63.01 KB)

Pages

Friday, 6 June 2014

Oracle E-Business Suite Performance Guide (Doc ID 1672174.1)

In This Document

Purpose

Introduction

Content Not Included Yet

Solution Flow Diagram

Where to Start

Reducing Iterations

Sufficient Information

Correct and Complete Diagnostics

Solution Process Overview

SQL Trace / TKPROF

AWR

Display Cursor / SQL Monitor Report

Most of the time spent in the application tier

Applications Tier Diagnostics

Most Spent in SQL Execution

PL/SQL Profiler

SQLT with XTRACT Method

Operating System Statistics

Display Cursor Report

SQL Monitor Report

Forms Trace and Forms Runtime Diagnostics

Using Oracle Applications Manager

Reports Client Process Trace

Choosing Reports Trace Options (Debug Option Values)

Application Tier Operating System Statistics

PL/SQL Profiler

Enable PL/SQL Profiler manually in PL/SQL

Other reporting options

Interpretation

Technical detail

Installing PL/SQL Profiler package

Common issues

PL/SQL Hierarchical profiler (HPROF)

SQLT with XTRACT Method

Methods

XTRACT

XPLAIN

XECUTE

Preference

Row Source Statistics / Statistics_Level = ALL

Use the XTRACT method as soon as possible after a SQL statement executed

Latest Version

More Information

Installing and Running

Installation or Running Issues

General

Issues writing to files

Obtaining SQL ID and Hash Value

Alternative to SQL (afxplain.sql)

Misleading CBO Statistics on SQLT

Improving Performance of SQLT

Operating System Statistics

OS Watcher

LTOM

Diagnosing High CPU Usage in Modules

Action Plan Templates

Display Cursor Report

SQL Monitor Report

Reports Client Process Trace

Add Options to the Concurrent Program Definition

Specify Debug Options when submitting a Concurrent Request

Using Debug Workbench in Oracle Applications Manager

PL/SQL Profiler Report

Obtain PL/SQL Profiler Report from Forms

Obtain PL/SQL Profiler Report from Self Service

Obtain PL/SQL Profiler Report for a Concurrent Request

PL/SQL Hierarchical Profiler Report

SQLT

AFXPLAIN.SQL

Obtaining SQL ID

Oracle Database Documentation Library Links

Performance Tuning Guide

PL/SQL Packages and Types Reference

Advanced Application Developer's Guide

Licensing Information

Change Log