Firstly, if you are a member of Digital Marketer Lab, please bring questions about this Execution Plan (or any other marketing question) to the weekly Office Hours call. This call takes place every Thursday at 3 PM Eastern Time.

Or post questions in the Digital Marketer Engage Facebook Group.

Here's how to get the most out of the content in this Execution Plan,

Read the entire EP once - Read through this entire EP, watch any videos and download the resources. Don't execute on the steps until you have finished reading the entire EP. This will help you understand the progression of the steps and put them into context.

Complete the steps - This Execution Plan is a checklist. Each step builds upon the next. Complete each step in order.

Lastly, here's how to use the Execution Plan interface.

View the example Execution Plan below...

Next

Here’s how this Execution Plan will work. It is divided into five parts. Each part helps you answer one of the Big 5 questions laid out above.

Section 1: Introduction - Start Here

Section 2: How To Identify Your Identify Your Database Requirements By Working With Your Users.

Section 3: How To Identify The Storage Requirements For Your Database

Section 4: How To Identify The Server On Which The Database Will be Hosted

Section 5: How To Create You Database Using DBCA

Section 6: How To Identify The Post Database Creation Steps

This Execution Plan will walk you through each of these five major sections and provide you with worksheets, tools and exercises to get you the answers you need.

Next

With regards to pro-active avoidance, there are 2 different types of 'baselines' you should collect.

1. Systemwide 'performance' AWR Baselines recording the resource usage of the system running normallyIf your system is performing well, then this is a good time to record that information to form a baseline for comparison against in the event of any issue in the future. The automatic workload repository (AWR) allows you to capture baseline data and enables you to specify and preserve a pair or a range of snapshots as a baseline. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely. Reports can then be created from these baselines allowing you to compare them with current situations to identify differences. The details for creating such a baseline can be found here:
   Oracle Database Online Documentation 12c Release 1 (12.1)
   Database Administration
   Database 2 Day + Performance Tuning Guide
   Chapter 9 Resolving Performance Degradation Over Time
   http://docs.oracle.com/database/121/TDPPT/tdppt_degrade.htm#TDPPT089

   In addition to providing you with a comparison to a previous situation, baselines can provide a number of other benefits outlined in the Monitor for Potential Problems section below. There are more extensive suggestions to potential baselines you may wish to collect in the following article:

   Document 1477599.1 Best Practices: Proactive Data Collection for Performance Issues

   Use of the Automatic Workload Repository requires the Diagnostics Pack License. See:

   Document 1490798.1 AWR Reporting - Licensing Requirements Clarification

Next

With any change to a system, you want to create a situation where you can quickly and easily restore the previous performance in the event of change. The best way to ensure that stability is to use SQL Plan Management (SPM) to ensure that critical operations use known good plans and perform the same before and after the change. For systems with relatively static data and usage profiles, support would recommend that you record SQL Plan Baselines for key application queries so as to fix their access paths avoiding unexpected change. If data volumes and usage profiles are variable, then such baselines can be useful to provide a stable fallback position, but you may need the optimizer to be able to change plans to reflect changing data distributions. For details see:

Note that for statistical changes, the database automatically retains the last 30 days worth of statistics, which can be restored in the event of a dramatic change. See:

Also Note that SPM is available as part of Oracle Database Enterprise Edition 11g, so no additional licenses are needed to use SQL Plan Management (SPM) or any of the procedures in the DBMS_SPM package. See:

Next

Upgrades can introduce significant changes in terms of software and hardware usage. Prior to any upgrade it makes good sense to ensure that provision for performance baselines are in place so that the system performs at least as well as the previous iteration. Additionally we recommend using Real Application Testing RAT to enable 'trial' testing on real workloads to ensure that the system will work as expected on transfer. Key articles in these areas are:

There is an example of the capabilities of this suite of tools in the following Oracle Open World Presentation entitled "Load Testing" which provides a Case Study of Capture / Replay. There is further explanation in the manuals:

Next

Problem Description

The first step in troubleshooting any performance issue is to clearly describe the issue.  Proceed to create an electronic file using any text editor, and document in detail the symptoms of the performance issue.  Keep in mind that you may want to use your electronic document if you later need to report the performance issue to Oracle for further analysis.  Document and differentiate factual information and user perceptions.  You may want to use the templates provided in the related documents section at the bottom of this note, to start gathering your information in a well organized manner.  You will first need to categorize your issue however.

Your detailed description should include, at least, the following information:

1. What is the application group or module affected by your performance issue?  Examples: 'AP', or 'ONT and QP', or 'all manufacturing modules', 'just this Form', 'this list of transactions', etc.
2. Instance or instances in which it can be observed.  Does it happen in Production?  Development?  Test?  All of them?  If it does not happen in all your environments, document what is different among them.  Try to determine if the difference among them can be isolated.  This may narrow the problem to a very specific area.
3. Determine if the poor performance is equally observed when the system load is either low or high.  What if there is only one active user?  Is it equally slow?  Document if the poor performance keeps any relation to the system load, or not at all.
4. Is the performance issue affecting one user only?  If no, document how many users are affected.
5. If your application is used on multiple locations:  Does the location seem to affect the performance?  Do all users in all locations experience the same performance?  Can you state that poor performance has nothing to do with the user location?
6. Can you reproduce the issue systematically?  Document how can the issue be systematically reproduced.  If you can't, document why not.  In any case, try to reproduce it systematically and document your findings.
7. Do you have a workaround available?  What is it?
8. When was the last time you could complete the process or transaction without experiencing poor performance?  Document what has changed since then.
9. Determine if any software has been installed or upgraded in your server or instance since the problem started.  Include any RDBMS, Applications or 3rd party software in your analysis.
10. Document any patches applied recently which could have affected your performance.  Include Operating System patches as well as patches from RDBMS or Apps.

Next

ategories of common Tuning Issues

The detailed description of the performance issue should be sufficient to categorize it into one of the following 3 high-level areas.  Determine which category best describe your issue.  Review and document the Certify and Statistics Gathering sections below.  Then proceed to the tuning section corresponding to the high-level category.

1. Transaction Tuning:  One specific transaction denotes poor performance.  It may be a Form, a Concurrent Program, or a specific operation within a Form (i.e. the click of a button, saving one row, displaying a list of values, etc.).  If your issue affects more than one transaction but just a few of them, it can still be classified as transaction tuning.  You may want to diagnose and document each issue separately.  Transaction Tuning usually translates into SQL Tuning.
2. Back-End Tuning:  Overall poor performance is observed, or several transactions perform poorly; usually from more than one application group.  This category typically includes issues where system load has an impact in the overall performance, or it degraded after a major software upgrade.  Back-End Tuning usually translates into DB Server Tuning and/or OS Tuning.
3. Tech-Stack Tuning:  Navigation between Forms or within Forms performs poorly affecting several Forms.  Opening a Form takes an unusual long time.  Different performance is observed from one location to another.  Some users are affected but no others.  Back-End has been reviewed and ruled out.  Tech-Stack Tuning is also known as Middle-Tier Tuning.

ertify

Use Certify within My Oracle Support to verify that your specific combination of Platform/Operating System, RDBMS Release and Oracle Applications Release, is fully certified.  On My Oracle Support main 'Certify - Platform Selection page' select your back-end server platform.  On the 'Product Group selection page', select 'E-Business Suite'.  Review the General Notes.  Select your combination of Apps Release (Oracle E-Business Suite Version), RDBMS Release (Oracle Server Version), and  'Certified combinations only'.  Locate your OS Release in the 'Database Tiers Certifications page' and review the hotlinks under 'Additional Info' and 'Issues' columns.  If there are 'Interoperability Notes' for your implementation, please review them.  Under the 'Issues' hot link you may encounter a list of known Bugs and Issues affecting your particular combination of OS/RDBMS/Apps Releases.  Please review the list of known Bugs and Issues carefully and document in your electronic file:

1. List of known Bugs and Issues according to your OS/RDBMS/Apps Releases that may relate to your performance issue.
2. Which of the related Bugs have been applied?
3. Which ones have not yet been applied?  Explain why.

Note: Be aware that the list of known Bugs and Issues changes periodically.  Therefore, it is a good practice to review this list of known Bugs and Issues affecting your very specific Product combination every time you need to troubleshoot a new performance issue.

Next

Statistics Gathering

• Statistics Gathering in 11i/R12
• Statistics Gathering in 11.0 and 10.7

Oracle Applications 11i and later uses the Cost Based Optimizer to generate execution plans for SQL statements.  In order to generate optimal execution plans, the CBO requires updated statistics regarding your data volume and distribution.  Statistics used by the CBO are stored in the data dictionary and can be queried using several available views.  Oracle Applications 10.7 and 11.0 mostly use the Rule Base Optimizer.  Still a handful of SQL statements from these earlier releases of Oracle Apps invoke the CBO by using embedded CBO Hints.  Therefore, all Oracle Applications Releases must have current CBO statistics available for pertinent schema objects belonging to the installed Apps modules.

There are several tools available to gather CBO statistics.  All of them allow to gather stats either by reading all the data on the table or index (compute method), or just a sample of the data (estimate method).  For Oracle Applications there is no need to gather exact stats using the 'compute statistics' method.  When gathering CBO stats you should generally use the 'estimate' method with a sample size of 10%.

Regarding the frequency of the CBO statistics gathering, the right schedule really depends on your data, based on how often it changes.  Typically, gathering CBO statistics at least once per month, with an estimated sample of 10%, on all pertinent schema objects belonging to Apps installed modules, has proved to be sufficient for most clients.  Some clients prefer to gather CBO statistics weekly, during a quiet system load interval.  Other clients prefer to optimize the gathering of CBO stats, by scheduling the frequency and estimate sample percentage according to the current size of their tables.

In any case, if you suspect that your CBO stats may be affecting your process performing poorly, a more aggressive CBO stats gathering is usually worth attempting.  Measure the performance again if you refreshed your CBO statistics for one or more schema objects accessed by your process performing poorly.  Be aware that prior measurements, Traces, Trace Analyzer and TKPROF files, may become invalid.

Also keep in mind that when an object has its CBO stats refreshed, all references to that object in the shared pool or library cache become invalid, requiring a new hard parse for each SQL statement referencing the object.  Therefore, in order to avoid temporarily affecting the performance of all active users in a production environment, you must refrain from gathering CBO stats during times of intensive system usage.  Always schedule the CBO stats gathering during periods of time with very low system load, and when the number of active users is at its minimum.  In other words: DO NOT GATHER CBO STATS WHEN NUMBER OF ACTIVE USERS IS MEDIUM TO HIGH.

Note: Try gathering stats at least once per month and with an estimate sample size of 10% for all your schema objects belonging to Apps.  If you can afford gathering stats every week, do so (it is preferred, but not required).  Always gather stats with no active users, or when system load is very low.

Regarding your CBO statistics gathering, document in your electronic file:

1. Frequency of the CBO stats gathering on the pertinent schema objects accessed by your process performing poorly (monthly, weekly, daily, etc.)
2. Method used (per table, per schema, all schemas, etc.)
3. Tool used (coe_stats.sql, FND_STATS, DBMS_STATS, ANALYZE command, DBMS_UTILITY) NOTE: While ANALYZE and the DBMS_UTILITY which can call the ANALYZE command was used in past RDBMS releases before DBMS_STATs was first created, the actual statistics collected are different. For this reason we strongly recommend not using the older ANALYZE command or DBMS_UTIL which calls the ANALYZE for collecting statistics. The ANALYZE is a subset and would not provide the statistics that the optimizer needs for optimal functionality. ANALYZE is still used for collecting information on CHAINED ROWS so should not be considered completely defunct, just not a good choice for statistics collection.
4. Estimate sample size percentage used (or if compute method, specify so)
5. When was the last time the CBO stats where gathered for the affected schema objects?

Note: Using 8i, never gather CBO statistics for data dictionary objects owned by user 'SYS'.  The only exception is the table DUAL owned by SYS, for which FND_STATS gathers stats when executed for one or all schemas.

Statistics Gathering in 11i/R12

From all the tools available, Oracle Apps 11i mandates the exclusive use of the FND_STATS package to gather the CBO stats.  This package can be invoked either from a seeded Concurrent Program, or directly from SQL*Plus.  When invoked using the seeded Concurrent Programs, use only the 'Gather Schema Statistics' or the 'Gather Table Statistics'.

When using the 'Gather Schema Statistics' concurrent program, pass only the schema name parameter.  Let the other parameters default automatically.  The schema name passed can be a specific schema or 'ALL' if you prefer to gather CBO stats for the pertinent objects belonging to all Apps installed modules, using the estimate method with a sample size of 10% (default).  Be aware this process may take several hours to complete. Stats collection really needs to be estimated at 25% - 30%, particulary for large tables, to give CBO enough information to derive good execution plan.

When using the 'Gather Table Statistics' concurrent program, pass only the owner of the table (schema name) and the table name.  Let all other parameters default automatically, except when the table has been partitioned.  When gathering CBO stats for a partitioned table, pass 'PARTITION' in the Granularity parameter, otherwise FND_STATS will calculate global stats (plus partition stats) instead of rolling up the global stats from the partitions.  If this happens, you may have to delete the global stats (with cascade equals 'false') and gather the stats for one partition to once again enable the automatic rolling up into the global stats.

To execute the corresponding FND_STATS procedures from SQL*Plus to gather CBO stats for one or all schemas, or for a particular table, use the following examples:

# sqlplus apps/<apps_pwd>
SQL> exec fnd_stats.gather_schema_statistics('MRP');		    <- One schema
SQL> exec fnd_stats.gather_schema_statistics('ALL');		    <- All schemas
SQL> exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES'); <- One table

SQL> begin 							    <- Partitioned table
  2      fnd_stats.gather_table_stats(ownname     => 'APPLSYS',
  3                                   tabname     => 'WF_ITEM_ACTIVITY_STATUSES',
  4                                   granularity => 'PARTITION');
  5  end;
  6  /

There is also a public script available that may help you automate the CBO statistics gathering.  This is the coe_stats.sql script, included and documented in Note:156968.1.  Thecoe_stats.sql script uses the FND_STATS package and schedules the CBO stats gathering according to table sizes.  It uses the estimate method with a variable sample size based also on table size.  It gathers stats more accurately for small tables (larger sample percent) and uses a smaller sample size percent for larger tables.  The main benefit of this script is that it has the potential to reduce the overall CBO stats gathering execution time without sacrificing stats accuracy.  If your total CBO stats gathering time for all pertinent schema objects is acceptable using plain FND_STATS procedures, then there is no need to explore using the coe_stats.sql script.  In the other hand, if yours is a 24x7 operation and you need to minimize the window to gather CBO stats, consider using the coe_stats.sql script.

If you are not confident of the current status of your overall CBO stats in your Apps 11i instance, download and execute the bde_last_analyzed.sql script from Note:163208.1.  This script reports the current status of the CBO stats for all schema objects owned by Apps installed modules.  It summarizes by schema name (application group) and by date, where at least one schema object got its stats refreshed.  It warns you of possible erroneous stats gathering on schema objects owned by SYS.  It also reports and warns on partitioned tables which global and partition level stats are out of sync.  You can execute this bde_last_analyzed.sql script at any time.  By reviewing the summary page at the beginning of the report produced, you can quickly get the status of the CBO stats on your instance.

Statistics Gathering in 11.0 and 10.7

For earlier Releases of Oracle Applications you can use any tool available to gather CBO statistics.  The recommended tool is the DBMS_STATS package, documented in the corresponding 'Supplied PL/SQL Packages Reference' manual for your RDBMS Release.  The FND_STATS package did not exist on 11.0 or 10.7, but it is available through back-port patch for Bug 1268797.  If you don't have to have FND_STATS installed on your 11.0 or 10.7 instance, use DBMS_STATS instead.

Besides the DBMS_STATS package, the ANALYZE command docume

Next

The TNSPING utility determines whether the listener for a service on an Oracle Net
network can be reached successfully.

If you can connect successfully from a client to a server (or a server to another server)
using the TNSPING utility, then it displays an estimate of the round trip time (in
milliseconds) it takes to reach the Oracle Net service.

If it fails, then it displays a message describing the error that occurred. This enables
you to see the network error that is occurring without the overhead of a database
connection.

syntax of command:

tnsping {net service alias}

use the Oracle Net utility tnsping to validate the Net Service Alias is properly defined in your client's TNSNAMES.ORA (or other Naming Service):

The output will is displayed below

cfdbsnd01:/home/oracle>tnsping db1tst

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 22-FEB-2016 14:16:45

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/apps/oracle/product/12.1.1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname.domain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db1tst.domain)))
OK (10 msec)

If this Step fails, make sure you are using the correct TNSNAMES.ORA file.  If you are not using the correct TNSNAMES.ORA file, validate that the TNS_ADMIN variable is properly set (or not set). Also, make sure you have used the same hostname / IP address  as the ADDRESS for the Net Service Alias in the TNSNAMES.ORA file.

Note: net_service_name must exist in tnsnames.ora file or the name service in use, such as
db1.domain.

If the net service name specified is a database name, then TNSPING attempts to
contact the corresponding listener. It does not actually determine whether the database
is running.

Check the output for an Invalid Net Service Name with tnsping below :

hostname:/home/oracle>tnsping db2tst

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 22-FEB-2016 15:00:04

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/apps/oracle/product/12.1.1/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

Checking the output for a valid Net Service Name but No Listener with TNSPING:

hostname:/home/oracle>tnsping db2tst

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 22-FEB-2016 15:00:04

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname.domain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db1tst.domain)))

TNS-12541: TNS:no listener

Next

Transaction Tuning

• Data Gathering
• Researching
• Initial Troubleshooting
• Apps 11i init.ora parameters
• Advanced Troubleshooting

Once you have validated Certify and Statistics Gathering, proceed with this next section if your performance issue qualifies as this high-level tuning category.

Data Gathering (files and information)

Gather the indicated files and document in your electronic file the following information:

1. How long does it take for the process to complete (specify hours/mins/secs)?
2. How long did it previously take (before having the performance issue)?
3. What is the expected performance for this process (specify hours/mins/secs)?
4. If you have any customization related to this process, document it in your electronic file, in full detail.
5. Determine module information requested below, for specific Form, Report, Package, Main Pro*C, etc.
   1. Module internal name (short name):  For a Form, use the menu to find the short name and version.  For a concurrent program, use the 'Concurrent Program Define' Form from the SYSADMIN responsibility to find short and long name.
   2. Module title (descriptive or long name):  For a Form, use the navigation path or the title displayed in the Form.
   3. Module version:  For a concurrent program in UNIX, navigate to directory (i.e. $AP_TOP/reports or $PO_TOP/bin) and use strings command:
   4. # strings -a <module> | grep -i '$Header'
   5. Latest version available according to My Oracle Support:  Use 'Patches' option on My Oracle Support main menu.
   6. Current patchset level for application group owning the affected module:  Ask your System Administrator or DBA.
   7. Latest patchset available according to My Oracle Support for the application group:  Use 'Patches' option on My Oracle Support main menu.

   Note:  If you find that you have an old version of the module, be aware that quite often a newer version of it may fix a known performance issue.  This may be true even if you don't find a direct hit in the list of issues fixed between your version and the latest version.  If possible, upgrade the affected module to the latest version available.  Also be aware that in many cases, due to intricate file dependencies, Oracle Development requires you have the latest version of the module applied, in order to troubleshoot a new issue.

6. When the affected module is a Pro*C program, generate a text file with the list of modules (components) linked to the Pro*C program.  Use this or similar command:

# strings -a <Pro*C module> | grep -i '$Header' > MODULES.TXT

1. On a multi-org environment, determine the ORG_ID used while monitoring the performance of the affected module.  This ORG_ID or Operating Unit is usually required to later execute an isolated SQL statement performing poorly, using SQL*Plus.  If you don't know the ORG_ID, use this command to produce a list of Operating Units, and determine the ORG_ID from the list:

SQL> select organization_id org_id, name from hr_operating_units;

1. If your transaction performing poorly is a Concurrent Program, determine if your application group provides a Profile Option to turn ON and OFF some sort of debugging information, if it does, execute your Concurrent Process with debugging information turned ON and recover the Log file generated (this is in addition to the Trace file requested below).  Review your Log file.
2. If yours is a Concurrent Program, document in your electronic file all the parameters requested by the execution of your process, indicating which values you passed, including which values were left NULL, or automatically defaulted.  Include all parameter names and values.  Keep in mind that the number, name, or order of the parameters may change from one version of the module to the next.
3. For online transactions, document in your electronic file, in full detail, the complete navigation path, as well as step-by-step instructions to reproduce your issue.  If the transaction performing poorly is a query, document which parameters are passed to the query, as well as, which parameters are NULL, or automatically defaulted.  Be as specific as possible.
4. If your transaction has been running for several hours or days, and you did not turn SQL Trace at the beginning of it, you can still capture the expensive SQL statements for further analysis.  Download, familiarize yourself, and execute the bde_session.sql script (Note:169630.1) and/or the SQLAREAT.SQL script (Note:238684.1 for 8.1 and Note:163209.1 for 8.0).  These scripts can be executed even a few minutes after the process has been killed.  The former requires the session_id, the latter has no parameters.
5. All Apps Transaction Tuning issues require, at the very least, one raw SQL Trace.  If tracing a Form, turn Trace ON using the menu (Help → Diagnostics → Trace → Trace with Binds and Waits), and set the trace size to unlimited (Help → Diagnostics → Trace → Unlimited Trace File Size).  If tracing a Concurrent Program, use whatever method is available for your Apps Release (usually a check box at the Concurrent Program Define Form).  Under some specific conditions, Oracle Development requires a raw SQL Trace generated with Event 10046 (Trace with Binds and Waits).  If you can provide that special Trace up front, it helps to expedite the whole process.  Once you generate the raw SQL Trace (standard for Concurrent Programs, or with Event 10046 for any Form), compress it and have it available in case you have to provide it to Oracle Support.  This file is usually large.  Traces are found in the 'user_dump_dest' directory.

Note: It is common to iterate in this step because the raw SQL Trace generated does not have statistics turned ON, or because it was truncated due to its size.  To avoid this unnecessary loop in the process, verify these two init.ora parameters settings: 'timed_statistics' set to TRUE, and 'max_dump_file_size' set to UNLIMITED.  On the other hand, if your process 'hangs' and you killed it, be aware that even an incomplete raw SQL Trace may be useful to pin-point the SQL Statement(s) performing poorly.

1. If you created a Trace with Binds and Waits, use the Trace Analyzer tool (Note:224270.1) to analyze your Trace.  This tool, when used on the same db instance where the Trace was generated, produces a comprehensive report that can be used to identify expensive SQL in terms of service and/or wait times.
2.      # sqlplus apps/<apps_pwd> SQL> START TRCANLZR.sql UDUMP prod_ora_9105.trc;
3. For all Apps Transaction Tuning issues, generate and be ready to provide to Oracle Support, one TKPROF file generated with the Explain Plan option.  TKPROF reports must be generated on the same db instance where the raw SQL Trace was created.  If you had to kill the process because it never completed, don't destroy the raw SQL Trace generated, proceed to create a TKPROF from it as well.  To generate one TKPROF, unsorted, and with Explain Plan, use this syntax: # tkprof 12345.trc 12345.prf explain=apps/<apps_pwd>  Note: Read the TKPROF and determine if it corresponds to the transaction performing poorly.  Providing to Oracle a TKPROF which does not correspond to the problematic transaction is very common, and it causes unnecessary delays in the resolution of performance issues.  In the TKPROF, you should recognize table names and possibly the transaction.  The total TKPROF Elapsed Time should be close to the 'user' Elapsed Time.

Next

At this point, you can pursue your performance issue with Oracle Support.  Provide as many of the requested files as possible.  Or, you may opt to participate more pro-actively in the research phase by performing the following steps before contacting Oracle Support:

1. Use the Trace Analyzer and/or TKPROF file(s) to find the expensive SQL statements.  In most cases you want to focus your attention in those SQL statements causing more than 5 to 20% of the overall logical reads or elapsed time totals (summarized in the first page of the Trace Analyzer report, or the last page of the TKPROF).  If you are using the Trace Analyzer, you may also want to review SQL statements with large non-idle wait times.
2. Document in your electronic file the most expensive SQL statement(s) found.  Include from the Trace Analyzer or TKPROF the block showing the SQL statement; the block showing the parse, execute and fetch statistics for the SQL statement; and the block that shows the Explain Plan for it.
3. For each expensive SQL statement (in most cases it is just one), search on My Oracle Support for known issues.  Review the SQL statement and grab from it significant and not-so-common columns to make your search criteria as selective as possible.  Take pieces from the FROM clause and the WHERE clause.  Be aware that you may be experiencing a performance issue already reported by another customer and for which either a fix or a workaround exists.  While doing your search on My Oracle Support, you may want to use also the short name of the module (Form or Concurrent Program).
4. Document in your electronic file all My Oracle Support hits regarding your poorly performing SQL statement(s).  Even if they seem to be unrelated or never fixed.

Initial Troubleshooting

If you decide to be pro-active in the initial troubleshooting phase, perform the following steps for each SQL statement identified from the Trace Analyzer or TKPROF as expensive (according to the number of logical reads performed, or according to the elapsed time for its complete execution, or the non-idle wait time).  Start with the very most expensive SQL statement that is not a BEGIN procedure or data dictionary recursive SQL.  In other words, focus only on DML commands (SELECT, UPDATE, INSERT or DELETE) accessing Apps tables and indexes.

Note: If the most expensive statement in your Trace Analyzer or TKPROF is NOT a DML command (SELECT, UPDATE, INSERT or DELETE), in other words, it is a PL/SQL library call, use the profiler.sql script from Note:243755.1.

1. Create a flat file (text type) pasting the DML command (SQL statement) directly from the Trace Analyzer or TKPROF report.  Leave one and only one blank line at the end of the SQL statement.  Do not leave the statement without a single blank line at the end, and do not include more than one blank line at the end.  This is required by the SQLTXPLAIN.SQL or coe_xplain_xx.sql scripts.  Create your first flat file with the name of sql1.txt, the second with sql2.txt and so on.  The SQLTXPLAIN.SQL and coe_xplain_xx.sql scripts can handle any file name, but the standard is sql<n>.txt.  Bind variables (those with colons, i.e. ':b1',':2') should be left intact.  And you should not have a semicolon ';' at the end of your SQL statement.
2. For RDBMS Release 8.1 or later, download script SQLTXPLAIN.SQL from Note:215187.1.  For 8.0 download coe_xplain_80.sql from Note:156959.1.  And for 7.3 downloadcoe_xplain_73.sql from Note:156960.1.  Read the downloaded version of the script and familiarize yourself with it.
3. Place your SQLTXPLAIN.SQL set of files or your coe_xplain_xx.sql script and your sql<n>.txt file(s) into a dedicated directory.
4. Execute SQLTXPLAIN.SQL or coe_xplain_xx.sql script from SQL*Plus connecting as apps/apps_pwd, passing as the inline parameter the name of the file containing your SQL statement sql<n>.txt as indicated by the instructions on the corresponding script (see commands below).  The SQLTXPLAIN.SQL or coe_xplain_xx.sql script will not execute your SQL statement included into sql<n>.txt.  They just parse your SQL statement and proceed to explode it into pieces for a detailed analysis.SQL> start coe_xplain_xx.sql sql<n>.txt
5. SQL> start SQLTXPLAIN.SQL sql<n>.txt
6. If you need to provide to Oracle Support the output of the SQLTXPLAIN.SQL or coe_xplain_xx.sql scripts, compress and send the whole directory with all spool files within it.
7. From the Trace Analyzer or TKPROF, and from the spool file created by the SQLTXPLAIN.SQL or coe_xplain_xx.sql script, determine and compare the Optimizer used.  It should be consistent.  Document in your electronic file which Optimizer is being used: Rule Based - RBO or Cost Based - CBO.
8. If using CBO, good statistics of the schema objects accessed are crucial to generate an optimal execution plan.  Locate in your spool file the column 'Last Analyzed'.  This column tells you when you last gathered stats for each table accessed by your SQL statement.  (You can do the same to review all indexes).  Look also at the column that reads 'Delta Percent' to determine gap between actual number of rows in your tables (dynamically calculated with COUNT function) and the number of rows recorded in your data dictionary as part of the CBO stats for your tables accessed by your SQL statement.  If your stats for the schema objects accessed by your SQL statement are more than one month old, or the gap reported under the 'delta percent' column is more then 10%, you need to gather fresh stats for your affected schema objects.
9. Only for CBO: If you determine that some or all the schema objects accessed by your SQL statement require refreshed stats, you must use the appropriate tool to gather stats according to your Apps Release.  If your Apps Release is 11i, you may want to use the SQLTGSTATS.SQL script included in the SQLT.zip file.
10. Only for CBO: If you had to refresh stats, execute the SQLTXPLAIN.SQL or coe_xplain_xx.sql script again to produce a new Explain Plan.  If the Explain Plan changed, measure again the performance of your original transaction, as it may have changed as well.
11. If you are using CBO, are on 8.1 or later, and have only identified one expensive SQL statement, there are two files that you want to recover and have available for Oracle Support.  Be aware that second file is Binary, therefore you must copy across servers as such.
    1. Find in 'user_dump_dest' directory a raw SQL Trace generated by the SQLTXPLAIN.SQL script automatically.  This raw SQL Trace file is completely unrelated to the first raw SQL Trace file you generated when executing your original transaction.  If SQLTXPLAIN.SQL was executed using SQL*Plus on the db server, the raw SQL Trace generated by it may had been already copied into the same dedicated directory from which the script was executed.
    2. Generate Binary file SQLT.dmp with your CBO statistics for affected objects, by executing Export command (exp) as per INSTRUCTIONS.TXT included in SQLT.zip.  Use command below.  Execute export using ORACLE_HOME 8.0.6.# exp apps/<apps_pwd> file=SQLT tables='SQLT$STATTAB'
12. If you have a vanilla instance, or one in which the same transaction performs fine, follow the same steps to reproduce the Explain Plan, and compare them to verify that you can produce the same Explain Plan in both instances.  If not, focus first on indexes.  If you find index discrepancies among instances, solve such discrepancies.
13. If you are using RBO and see in your Explain Plan that a custom index is being accessed, drop the custom index and measure the performance again.  Be aware that once you drop an index being accessed according to the Explain Plan, all prior measurements and files become invalid.  The Explain Plan will change, as well as the performance.
14. If on an 11i instance you notice that your SQL statement makes references to Apps Views, find corresponding view definitions in the spool file created by the SQLTXPLAIN.SQL script.  You may optionally execute the coe_view.sql to generate scripts which can be used later to clone your views from one instance into another.  Be aware there may exist several versions of the same view depending on the version of the corresponding ODF file used to create the view.  You can use the syntax of the command below to search from the corresponding APPL_TOP for the specific ODF file that creates a view.  Once you know the file and find its version, you can search on My Oracle Support for newer versions: # find . -type f -exec grep -i "CZ_COMPONENT_SELECTIONS_V" {} \; -print
15. If on an 11i instance, you may want to ensure all required and recommended init.ora parameters for 11i are set correctly.  Since you already have at least one SQLTXPLAIN.SQL orcoe_xplain_xx.sql report, near the end of them you will find a list of init.ora parameters affecting the behavior of the CBO.  The init.ora parameters required for your Apps Release are clearly identified.  If necessary, fix any parameter showing an incorrect setting, and repeat your test.

Apps 11i init.ora parameters

Development has provided an official list under Note:216205.1.

You may optionally download the bde_chk_cbo.sql script from Note:174605.1.  This script allows to quickly validate all database initialization parameters according to Note:216205.1.  This script includes which EVENTs must be set or unset for Oracle Apps 11i as well.

Next

This phase requires special skills, and at this point most performance issues are raised to Oracle Support.  If you are proficient in SQL*Plus and you understand the Explain Plan, you may want to review this section; otherwise collect the requested files and provide them to Oracle Support.

Fully read the output of the SQLTXPLAIN.SQL or coe_xplain_xx.sql script and make sense of the Explain Plan and all related information.  Familiarize yourself with this report and make a sanity check on its contents.

To proceed with this phase, you need to prepare a tuning environment.  This tuning environment can be located on the same instance on which the performance issue is observed, or it can be on a different instance.  If the issue is observed in a Production instance, try to set the tuning environment on the Test or Development instances.  Tune on the Production instance only when you have failed to reproduce the issue on a non-production environment.  Reproducing an issue does not necessarily mean reproducing its performance.  In most cases it is sufficient to reproduce the same 'bad' Explain Plan, rather than the slow access itself.

In general, a good tuning environment is one in which you can perform the following two independent activities at will.  You may end up with two tuning environments, one for each activity.

1. Reproduce the exact same Explain Plan from original version of SQL statement.
2. Execute the expensive SQL statement in its original version and on several different 'modified' versions (hopefully improved).

Reproducing the exact same Explain Plan on a different instance usually represents a significant challenge, as the CBO decides the plan based on: CBO statistics, init.ora parameters, schema object attributes, RDBMS Release, and in a very few cases, in the Platform used.  The latter is particularly true when hitting a platform specific OS or RDBMS Bug.  With RBO, reproducing the same explain plan is much easier, since it depends mostly on the schema objects defined and accessed.  Therefore, this advanced troubleshooting phase relates mostly to issues regarding the CBO.

The focus of the tuning effort is usually in or around the Explain Plan Operation with the largest number of rows, according to the Explain Plan from the Trace Analyzer or TKPROF report.  If the column 'Rows' on the Explain Plan from the Trace Analyzer or TKPROF report shows all zeroes (meaning zeroes for all Operations), then it is almost indispensable to isolate the SQL statement, identify, define and assign the values of the bind variables, and execute from SQL*Plus with SQL Trace enabled.  Then create a new Trace Analyzer or TKPROF from new raw SQL Trace.  The new Trace Analyzer or TKPROF will have the Rows column of the Explain Plan populated.  This relates to how SQL*Plus handles cursors compared to other tools.  The cursor must be closed in order to get the number of rows column in the Explain Plan, and SQL*Plus keeps open one cursor at a time, forcing the closure of the prior cursor.

To reproduce the exact same Explain Plan from original version of SQL statement, try the following:

1. Copy the CBO statistics from the source instance (where the SQL performs poorly)  into the destination instance (tuning environment):
   1. When you ran the SQLTXPLAIN.SQL on the source instance, it updated a staging table SQLT$STATTAB with the CBO stats for all schema objects related to the SQL statement (tables, indexes, columns and histograms).
   2. Use the Export command on the source instance, according to the INSTRUCTIONS.TXT file provided inside the SQLT.zip file.  Generate an external binary file SQLT.dmp with the following command:
      

      # exp apps/<apps_pwd> file=SQLT tables='SQLT$STATTAB'

   3. Copy this binary file from source to destination instance.  Do not use ASCII.  Treat always as BINARY.
   4. Use the Import command on destination instance, as documented on the SQLTSTATTAB.SQL script, also included inside the SQLT.zip file.
      

      #imp apps/<apps_pwd> file=SQLT tables='SQLT$STATTAB' ignore=y

   5. Execute script SQLTSTATTAB.SQL on the destination instance to update the data dictionary CBO stats from imported SQLT$STATTAB table.
      

      Note: To restore the stats in the destination instance, simply gather new stats for affected schema objects.  You may also want to use the SQLTGSTATS.SQL script included in the SQLT.zip file.  This means you don't need to backup CBO stats on destination instance prior to overriding them with stats from source instance.

2. Review and compare the init.ora parameters files from source and destination instances.  If these instances are Production and Test, you may have the exact same parameters.  If not, annotate the differences in case you have to adjust the CBO relevant parameters in the destination instance.  You don't have to take action right away, as you may produce the same Explain Plan without changing any init.ora parameter.  Even if you adjust an init.ora parameter, you may be able to do so with an ALTER SESSION command for most of them, instead of modifying the init.ora file and bouncing the database altogether.
3. Review and compare schema object attributes such as 'Partitioned' and Parallel 'Degree'.  If you find discrepancies, sync them up.
4. Annotate the RDBMS Release version (up to the fifth digit), as well as the Platform.  You must be at least on the same RDBMS Release up to the 3rd digit before trying to reproduce the same Explain Plan.  It would be better, if you are on sync up to the 4th digit.
5. Now try, using the SQLTXPLAIN.SQL script, to generate the same Explain Plan in source and destination.  If you don't get the same Explain Plan, adjust CBO related init.ora parameters in destination and try again (use ALTER SESSION if parameter allows).  If you still cannot obtain the same Explain Plan using common CBO stats, init.ora parameters, and schema object attributes, try then ruling out differences among the source and destination instances (one at a time).  Determine what is different between source and destination instances.   Focus on the discrepancies that may be causing the 'bad' Explain Plan (assuming the plan on destination instance performs better, of course), and rule them out (one by one) until you find which of the discrepancies causes the Explain Plan to switch from 'good' to 'bad'.
   

   Note: In most cases, finding the root cause of a ' bad' Explain Plan, is the same than finding the solution to the performance issue.

6. If you find yourself in a situation in which CBO stats, schema object attributes, and init.ora, are all the same, but Platform and RDBMS Release are different, and instance in which RDBMS is newer performs better, you can be almost certain that upgrading the RDBMS engine will solve your performance issue.
7. If you have the same CBO stats, schema object attributes, init.ora and Platform, and only RDBMS Release is different, and the instance with the newer Release performs better, try to upgrade your RDBMS Release on the older instance.
8. If you, or Oracle Support, can reproduce the same 'bad' Explain Plan on the same or different Platform (common up to the 3rd digit RDBMS Release), using your CBO stats, most likely your issue is an Application Bug.  In such case, the issue needs to be reported to Oracle Development and you will be given a new Bug number.
9. Once your performance issue has been cataloged as a new Application Bug, the next steps in the troubleshooting process require to explain and execute the SQL statement in its original form and on several different modified versions, trying to create one execution plan delivering a better performance.
   

   Note: If you have two similar instances.  One performing well and the other poorly, and they have similar data but producing different Explain Plans, as a workaround you can force the 'good' plan into the slow instance by migrating the CBO stats using SQLTXPLAIN.SQL together with SQLTSTATTAB.SQL.

To execute the expensive SQL statement in its original version and on several different modified versions, try the following:

1. You need to be able to isolate the expensive SQL statement for execution from SQL*Plus, at will.  To do this, you need first to find the values of the bind variables referenced.  If you can deduce the values by reviewing the SQL statement, do so.  Otherwise, you might have to use one of the methods available for tracing with bind variables according to Note:171647.1, and then use the Trace Analyzer (Note:224270.1) to process the raw SQL Trace and identify the bind variables from your raw SQL Trace created with Event 10046 on your transaction.
2. Assign the values of the bind variables referenced in the SQL statement.  Be careful conserving the same data types.  You may need to define and assign values to bind variables.  Avoid replacing bind variables with literals.
3. If your Explain Plan shows any table with suffix '_ALL', this means you need to set up the multi-org environment.  Using the corresponding Operating Unit (ORG_ID), set multi-org with this command:
   

   SQL> exec fnd_client_info.set_org_context('&org_id');

4. Use ALTER SESSION commands to turn SQL_TRACE ON and OFF, and execute your SQL statement.  To facilitate the whole process, you may want to create a script with the setting of multi-org, the 'ALTER SESSION SET SQL_TRACE = TRUE;' command, the assignment of the bind variable values, your SQL statement, and the command to terminate the SQL Trace.  This way you can execute the SQL statement systematically.
5. For INSERT, UPDATE or DELETE SQL statements, you need to focus on the subquery performing poorly (usually the main query feeding the insert/update/delete).  Transform the original SQL into an isolated stand-alone query.  If this is not possible, add the ROLLBACK command to the script with your INSERT, UPDATE or DELETE SQL statement.
6. Tuning the SQL statement requires a lot of practice and patience.  All methods converge to the same principle: apply your knowledge to create a modified and improved version of the original SQL statement and determine if it performs better for every situation.  Some analysts mostly use the Explain Plan leaving the benchmark of a candidate to the end.  Others prefer to execute and measure the performance of each candidate (modified SQL statement).  It becomes a matter of style at this level.
7. While iterating in this process, generating explain plans for several versions of the same SQL statement, you can use the SQLTX.SQL script (included in SQLT.zip), or the bde_x.sqlscript from Note:174603.1.  The latter script generates a very simple Explain Plan and their execution is faster than complex SQLTXPLAIN.SQL.
8. Things to try in order to improve the Explain Plan and ultimately the performance of the SQL statement:
   1. Verify all tables referenced in the FROM clause are actually joined in the WHERE clause.
   2. Improve CBO stats, either by using a higher sample size or with histograms for columns that are filtered with literals in the WHERE clause.  You can also temporarily modify the CBO stats for an index or column (only as a short term workaround).
   3. Indexes with better selectivity for specific filter or join conditions.  These indexes may ultimately be custom indexes.
   4. Switch the optimizer from RBO to CBO, or from CBO to RBO.  The latter as a temp solution only.
   5. Use one or more CBO Hints.
   6. Rewording the SQL statement.
   7. Avoid overuse of UNION, ORDER BY, DISTINCT and other clauses that may not be necessary.
   8. Using dynamic SQL to avoid poorly written code with functions like NVL or DECODE in the WHERE clause, which may affect the selectivity of the predicates.
   9. If the SQL statement has been fully tuned, and the reason of the poor performance is purely the volume of data necessarily retrieved, then you may want to consider data partitioning or parallel query.  Keep in mind that data partitioning or parallel processing are not the right solution to fix a poorly tuned SQL.

Next

Once you have validated Certify and Statistics Gathering, proceed with this section if your performance issue qualifies as this high-level tuning category.

• Data Gathering
• Initial Troubleshooting
• Advanced Troubleshooting

Data Gathering (files and information)

Gather the indicated files and document in your electronic file the following information:

1. Architecture information: Basically how are the database server and the middle-tier configured?  Are they in the same box?  If not, how many boxes you have?  Which type? etc.  Try answering: Where do you have what?
2. Hardware Profile: CPU(s) number, type and speed.  Amount of memory.  How is the I/O subsystem configured?  What is the network type and speed? etc.  Provide high-level information of your hardware and components.
3. Server workload profile:  What do you have running on same box?  Databases in use on same server.  Number of concurrent active users.  Number of concurrent active processes at a given time (usually peak times).  Basically, how busy is your box.
4. Do other applications or databases you may have running in the same box also perform poorly?  If not, explain.
5. What is the current and projected system load?  What is the current vs. projected number of active concurrent users.  Same for active concurrent processes.
6. What are the concurrent processing policies?  Have you moved as much of the concurrent processing to lower online user activity periods?  Do you have any restrictions to concurrent processing at all?  Do you use concurrent managers to restrict this intensive processing activities during the day?
7. Have you validated any Operating System resource contention?  This includes CPU, memory and disk.  Your comments should take into consideration all your boxes (back-end and middle-tier).  Validate and document if you are experiencing CPU spikes, memory paging, hot disks, etc.  If you have noticed memory swapping, document in detail, as well as any action being taken, including short-term actions like borrowing hardware from Development or Test servers.
8. Make a list of all application modules (application groups), installed and in use on this Apps instance.  Example: AP, GL, FND, ONT, etc.
9. For the application modules (groups) affected by poor performance, document currently installed patchset level.
10. For same affected application modules, research on My Oracle Support and document in your electronic file what is the latest patchset available.  Use the 'Patches' option on My Oracle Support main menu.  Document if you can upgrade to latest patchset levels shortly.
11. What is the Operating System patching level in your server box?  Try to rule out any known performance issues on your hardware platform.  In general, try to keep your OS patching level as current as possible.  It is known that several overall performance issues are caused by inadequate OS patching level.  Consult your hardware vendor in this regard.
12. If you have any prior Performance related Report with specific recommendations regarding hardware, configuration sizing, benchmark, stress test or similar, include the report  in your electronic documentation of your performance issue and provide it to Oracle Support.  This includes any formal or informal recommendations made by any Oracle or 3rd party entity.  Especially important if you are considering or in the process of implementing such recommendations.
13. Review init.ora parameters file doing a sanity check on it, and have it ready to make it available to Oracle Support.  If you find that some Events are set in this file, determine if there is a strong valid reason why.  If none, delete them.  If your init.ora file references another file by using the command IFILE, review the referenced file and have it available as well.
14. Review ALERT.LOG for possible server errors or any other evident abnormalities.  If the ALERT.LOG is large, have the last 48 hours of it available for Oracle Support.  Be sure theALERT.LOG includes the monitored interval where the performance was poor.
15. Make file APPLPTCH.TXT available to Oracle Support.  This file keeps track of patches applied to Apps.
16. Identify up to the top 10 user transactions performing poorly.  This transactions are usually what is causing your users to complain of the poor performance in the first place.  Examples: entering an order, saving a line, performing a query, etc.  Document for each of this up to 10 'critical' user transactions:
    1. Transaction name or short description.  Example: booking an order
    2. Performance during normal system load (secs/mins)
    3. Expected performance (secs/mins)
    4. Performance when system load is almost zero (when only one or very few users are active on instance)

Initial Troubleshooting

At this point, you can pursue your performance issue with Oracle Support.  Provide as many of the requested files as possible.  Or, you may opt to participate more pro-actively in the initial troubleshooting phase by performing the steps in this section before contacting Oracle Support.

1. For 11i instances: Verify required and recommended init.ora parameters are set correctly.  Use the provided script AFCHKCBO.sql and Interoperability Notes as well.  Generate theAFCHKCBO.TXT file containing the Apps 11i required init.ora parameters names and values.  Make this file available to Oracle Support.  Fix any discrepancy, and document possible reason why 'current' and 'required' columns don't have the same value.  If the AFCHKCBO.sql script and the Interoperability Notes require different values, set the values of the Note.  To execute the script use the syntax below:

# sqlplus apps/<apps_pwd>
SQL> SPOOL AFCHKCBO.TXT;
SQL> start $FND_TOP/sql/AFCHKCBO.sql
SQL> SPOOL OFF;
SQL> QUIT;

2. For 11i instances: There is another script that validates all required and recommended init.ora parameters according to RDBMS Release.  Use script bde_chk_cbo.sql from Note:174605.1 as an extension to AFCHKCBO.sql.  Be aware that script bde_chk_cbo.sql provides more detail than AFCHKCBO.sql.
3. For 11i instances: Verify CBO statistics by executing the bde_last_analyzed.sql script from Note:163208.1.  This script reports CBO stats per application group (schema name) and per date.  It also creates a summary at the beginning indicating how old the CBO stats are, if data dictionary objects are free of CBO stats, and if you have partitioned tables requiring you fix their global stats.  Review BDE_LAST_ANALYZED.TXT reports and make them available to Oracle Support.
4. For 10.7 and 11.0 instances: Verify you are gathering your CBO stats at least one per month, all schema objects, with an estimate of 10%
5. For 8.0 and later: when the system is performing poorly, take one snapshot of the SQL area, to easily identify if there is any evident expensive SQL being executed, or recently executed.  For 8.1 and later, use SQLTAREAT.SQL from Note:238684.1.  For 8.0 use coe_sqlarea_80.sql from Note:163209.1.  Read and get yourself familiarized with the specific version ofSQLTAREAT.SQL script and its output.  Compress the whole directory which contains the SQLAREAT.SQL generated spool file(s).

Advanced Troubleshooting

This phase is not complex, but it requires you to invest more time initially, monitoring the performance of your instance for up to one wor

Next

Advanced Troubleshooting

This phase is not complex, but it requires you to invest more time initially, monitoring the performance of your instance for up to one working day.  Therefore, you may want to proceed toreporting to Oracle Support your overall bad performance issue at this time.  Once your issue has been reported, proceed  immediately with this advanced troubleshooting phase, in order to quickly generate the necessary files to pursue a final resolution.

1. For 7.3 and 8.0: Generate and provide at least one REPORT.TXT file from UTL scripts 'ULTBSTAT/UTLESTAT'.  Your DBA should be familiar with these common performance monitoring scripts.
2. For 8.1 and later: Install the latest version available of the StatsPack package.  This package is automatically available on 8.1, but it requires it be installed from its location at $ORACLE_HOME/rdbms/admin into the database (as any other package).  Look for detailed instructions in the document spdoc.txt within the same directory.  Your DBA should already either be familiar with this package, or get familiar with it since it replaces former UTL scripts 'ULTBSTAT/UTLESTAT'.  Read Note:153507.1 for more information on StatsPack for Oracle Apps.  Once installed, monitor poor performance during 4-8 hours, taking snapshots every 30 mins during the whole monitored interval (4-8 hours).  Then generate one or moreStatsPack Report based on the snapshots while performance was worse (usually between 1 and 2 hours within the 4-8 hours monitored interval).  Provide these reports to Oracle Support.
3. For all RDBMS Releases, monitor OS performance at the same time database performance is monitored.  For 8.1 and later, every 30 mins during the whole monitored interval (4-8 hours).  For 7.3 and 8.0, every 30 mins during the same interval of 'ULTBSTAT/UTLESTAT' execution.  Use standard OS performance monitoring tools like: vmstat, iostat, mpstat and top.
4. If there is an obvious resource contention in your OS, stop here and try to alleviate it with permanent or temporary actions at the OS level.
5. Have your DBA read and analyze your ULTBSTAT/UTLESTAT or StatsPack Report.  Search on My Oracle Support for possible known issues with the same characteristics.
6. If you opt to be pro-active reducing the number of performance issues due to known problems, keep your code as recent as possible, starting by the OS and moving towards RDBMS, leaving Apps code last.  Upgrading the OS software is usually painless, the RDBMS requires minor testing, and the Apps layer requires significant user testing.
7. Provide all your gathered files, performance template, and dial-in to Oracle Support.  Familiarize yourself with Oracle Direct Connect ODC

Tech-Stack Tuning

Once you have validated Certify and Statistics Gathering, proceed with this section if your performance issue qualifies as this high-level tuning category.

Data Gathering (files and information)

Gather the indicated files and document in your electronic file the following information:

1. How long does it take for the process to complete (specify hours/mins/secs)?
2. How long did it use to take (before having the performance issue)?
3. What is the expected performance for this process (specify hours/mins/secs)?
4. Architecture information: Basically how are the database server and the middle-tier configured?  Are they in the same box?  If not, how many boxes you have?  Which type? etc.  Try answering: Where do you have what?
5. Hardware Profile: CPU(s) number, type and speed.  Amount of memory.  How is the I/O subsystem configured?  What is the network type and speed? etc.  Provide high-level information of your hardware and components.
6. Server workload profile:  What do you have running on same box?  Databases in use on same server.  Number of concurrent active users.  Number of concurrent active processes at a given time (usually peak times).  Basically, how busy is your box.
7. Do other applications or databases you have running on the same box also perform poorly?  If no, explain.
8. Do other applications running on the same client also perform poorly?  If no, explain.
9. If the issue relates to slowness in loading a Form or navigating, does it reproduce when the client is connected directly to the network hub closest to the server?
10. What is the current and projected system load?  What is the current vs. projected number of active concurrent users.  Same for active concurrent processes.
11. What are the concurrent processing policies?  Have you moved as much of the concurrent processing to lower online user activity periods?  Do you have any restrictions to concurrent processing at all?  Do you use concurrent managers to restrict this intensive processing activities during the day?
12. Have you validated any Operating System resource contention?  This includes CPU, memory and disk.  Your comments should take into consideration all your boxes (back-end and middle-tier).  Validate and document if you are experiencing CPU spikes, memory paging, hot disks, etc.  If you have noticed memory swapping, document in detail as well as any action being taken, including short-term actions like borrowing hardware from Development or Test servers.
13. Make a list of all application modules (application groups), installed and in use on this Apps instance.  Example: AP, GL, FND, ONT, etc.
14. For the application modules (groups) affected by poor performance, document the currently installed patchset level.
15. For the same affected application modules, research on My Oracle Support and document in your electronic file what is the latest patchset available.  Use 'Patches' option on My Oracle Support main menu.  Document if you can upgrade to latest patchset levels shortly.
16. What is the Oracle Forms Release version?
17. Which is the JINITIATOR version?
18. Browser name and version?
19. Document in your electronic file, in full detail, the complete navigation path, as well as step-by-step instructions to reproduce your issue.  If the transaction performing poorly is a query, document which parameters are passed to the query as well as which parameters are NULL, or automatically defaulted.  Be as specific as possible.

Proceed to report your Tech-Stack performance issue to Oracle Support.

Reporting to Oracle

Providing to Oracle well organized, accurate, and comprehensive information and files, helps to expedite the total resolution time.  Try to determine the right balance between the initial time invested in gathering as much information and files early in the process, with the benefit they provide to the overall process reducing the number of iterations between the end-user, the technical user, Oracle Support, and Oracle Development.  Reports like the one produced by the SQLTXPLAIN.SQL script consolidate pieces of information like CBO stats, indexes, init.ora parameters, etc.  Having all this information gathered systematically and into one single place, certainly reduces the number of iterations and therefore the total resolution time.

Requested Files for Transaction Tuning Issues

At the very minimum, provide files 1 to 5 below.  If you pro-actively participated on the initial troubleshooting phase, or Oracle Support has guided you through it, provide files 6 to 9 accordingly.

1. Apps Performance Bug Template - Transaction Tuning
2. For Pro*C Concurrent Programs, MODULES.TXT with versions of modules
3. For Concurrent Programs, Log file with debugging information
4. Raw SQL Trace from the transaction, with or without Event 10046 details (compressed), complete or incomplete
5. Trace Analyzer and/or TKPROF file(s) with Explain Plans
6. SQL<n>.TXT file(s) with identified expensive SQL
7. Compressed file with spool files generated by SQLTXPLAIN.SQL
8. Only for CBO and SQL1.TXT: Raw SQL Trace produced by SQLTXPLAIN.SQL
9. Only for CBO and SQL1.TXT: Binary file SQLT.dmp with CBO stats

Requested Files for Back-End Tuning Issues

At the very minimum, provide files 1 to 5 below.  If you pro-actively participated on the initial troubleshooting phase, or Oracle Support has guided you through it, provide files 6 to 8 accordingly.  If you opted to, or Oracle Support instructed you to perform the advanced troubleshooting steps, provide files 9-11 accordingly.

1. Apps Performance Bug Template - Back-End Tuning
2. Any prior Performance related Report available
3. File init.ora, including any ifile(s) referenced on the init.ora
4. ALERT.LOG file, including last 48 hours
5. APPLPTCH.TXT file
6. For 11i, AFCHKCBO.TXT and/or BDE_CHK_CBO.TXT with required init.ora parameters
7. For 11i, BDE_LAST_ANALYZED.TXT with summary of CBO stats
8. For 8.1 or later, SQLAREAT.SQL spool files with expensive SQL statements
9. For 7.3 or 8.0, REPORT.TXT produced by bstat/estat tool
10. For 8.1 or later, StatsPack Report
11. Set of stats from OS performance monitoring tools, like: vmstat, iostat, mpstat and top

Requested Files for Tech-Stack Tuning Issues

1. Apps Performance Bug Template - Tech-Stack Tuning

Next

Master Note For Privileges And Roles (Doc ID 1347470.1)

Security Checklist: 10 Basic Steps to Make Your Database Secure from Attacks (Doc ID 1545816.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=483409173288344&id=398838.1&displayIndex=9&_afrWindowMode=0&_adf.ctrl-state=814x5iaj_469#aref_section210

FAQ: SQL Query Performance - Frequently Asked Questions (Doc ID 398838.1)

Tuning Queries: 'Quick and Dirty' Solutions (Doc ID 207434.1)

How to Investigate Slow or Hanging Database Performance Issues (Doc ID 1362329.1)

Master Note: Troubleshooting Structured Query Language (SQL) (Doc ID 1517773.1)

Master Note: SQL Query Performance Overview (Doc ID 199083.1)

TROUBLESHOOTING: Possible Causes of Poor SQL Performance (Doc ID 33089.1)

TROUBLESHOOTING: Possible Causes of Poor SQL Performance (Doc ID 33089.1)

How to Diagnose High CPU Usage Problems to the Module Level (Doc ID 352648.1)

TROUBLESHOOTING: Tuning Slow Running Queries (Doc ID 179668.1)

Script to Show Objects That are Missing Statistics (Doc ID 957993.1)

Master Note For Privileges And Roles (Doc ID 1347470.1)

SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)

Master Note: SQL Query Performance Overview (Doc ID 199083.1)

Archive of Database Performance Related Webcasts and Videos (Doc ID 1597373.1)

Master Note For Privileges And Roles (Doc ID 1347470.1)

Security Checklist: 10 Basic Steps to Make Your Database Secure from Attacks (Doc ID 1545816.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=483409173288344&id=398838.1&displayIndex=9&_afrWindowMode=0&_adf.ctrl-state=814x5iaj_469#aref_section210

FAQ: SQL Query Performance - Frequently Asked Questions (Doc ID 398838.1)

Tuning Queries: 'Quick and Dirty' Solutions (Doc ID 207434.1)

How to Investigate Slow or Hanging Database Performance Issues (Doc ID 1362329.1)

Master Note: Troubleshooting Structured Query Language (SQL) (Doc ID 1517773.1)

Master Note: SQL Query Performance Overview (Doc ID 199083.1)

TROUBLESHOOTING: Possible Causes of Poor SQL Performance (Doc ID 33089.1)

TROUBLESHOOTING: Possible Causes of Poor SQL Performance (Doc ID 33089.1)

How to Diagnose High CPU Usage Problems to the Module Level (Doc ID 352648.1)

TROUBLESHOOTING: Tuning Slow Running Queries (Doc ID 179668.1)

Script to Show Objects That are Missing Statistics (Doc ID 957993.1)

Master Note For Privileges And Roles (Doc ID 1347470.1)

SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)

Master Note: SQL Query Performance Overview (Doc ID 199083.1)

Archive of Database Performance Related Webcasts and Videos (Doc ID 1597373.1)

Next

The primary tool to manage the database is Enterprise Manager Cloud Control
(Cloud Control), a web-based interface. After you install the Oracle software, create
or upgrade a database, and configure the network, you can use Cloud Control to
manage the database. In addition, Cloud Control provides an interface for
performance advisors and for database utilities, such as SQL*Loader and Recovery
Manager (RMAN).

Next

Oracle Diagnostics Pack offers a complete, cost-effective, and easy-to-use solution
to manage the performance of Oracle Database environments by providing unique
features, such as automatic identification of performance bottlenecks, guided
problem resolution, and comprehensive system monitoring. Key features of Oracle
Diagnostics Pack used in this guide include Automatic Workload Repository
(AWR), Automatic Database Diagnostic Monitor (ADDM), and Active Session
History (ASH).

Next

Oracle Database Tuning Pack automates the database application tuning process,
thereby significantly lowering database management costs while enhancing. performance and reliability. Key features of Oracle Database Tuning Pack that are
used in this guide include the following:
– SQL Tuning Advisor
This feature enables you to submit one or more SQL statements as input and
receive output in the form of specific advice or recommendations for how to
tune statements, along with a rationale for each recommendation and its
expected benefit. A recommendation relates to collection of statistics on objects,
creation of new indexes, restructuring of the SQL statements, or creation of SQL
profiles.
– SQL Access Advisor
This feature enables you to optimize data access paths of SQL queries by
recommending the proper set of materialized views and view logs, indexes, and
partitions for a given SQL workload.

Next

Oracle Real Application Testing consists of the following key features:
– Database Replay
This feature enables you to capture the database workload on a production
system, and replay it on a test system with the exact same timing and
concurrency as the production system on the same or later release of Oracle
Database.
– SQL Performance Analyzer
This feature enables you to assess the effect of system changes on SQL
performance by identifying SQL statements that have regressed, improved, or
remained unchanged.

Next

ADDM is diagnostic software built into Oracle Database. ADDM examines and analyzes data captured in Automatic Workload Repository (AWR) to determine possible database performance problems. ADDM then does the following:

• Locates the root causes of the performance problems
• Provides recommendations for correcting them
• Quantifies the expected benefits
• Identifies areas where no action is necessary.

This section contains the following topics:

• ADDM Analysis
• ADDM Recommendations
• ADDM for Oracle Real Application Clusters

Next

Database statistics provide information about the type of load on the database and the
internal and external resources used by the database. To accurately diagnose
performance problems with the database using ADDM, statistics must be available.
A cumulative statistic is a count such as the number of block reads. Oracle Database
generates many types of cumulative statistics for the system, sessions, and individual
SQL statements. Oracle Database also tracks cumulative statistics about segments and
services. Automatic Workload Repository (AWR) automates database statistics
gathering by collecting, processing, and maintaining performance statistics for
database problem detection and self-tuning purposes.

By default, the database gathers statistics every hour and creates an AWR snapshot,
which is a set of data for a specific time that is used for performance comparisons. The
delta values captured by the snapshot represent the changes for each statistic over the
time period. Statistics gathered by AWR are queried from memory. The gathered data
can be displayed in both reports and views.
The following initialization parameters are relevant for AWR:
• STATISTICS_LEVEL
Set this parameter to TYPICAL (default) or ALL to enable statistics gathering by
AWR. Setting STATISTICS_LEVEL to BASIC disables many database features,
including AWR, and is not recommended. To learn more about this initialization
parameter, see Oracle Database Reference.
• CONTROL_MANAGEMENT_PACK_ACCESS
Set to DIAGNOSTIC+TUNING (default) or DIAGNOSTIC to enable automatic
database diagnostic monitoring. Setting CONTROL_MANAGEMENT_PACK_ACCESS to
NONE disables many database features, including ADDM, and is strongly
discouraged. To learn more about this initialization parameter, see Oracle Database

SQL> sho parameter TATISTICS_LEVEL

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL> sho parameter CONTROL_MANAGEMENT_PACK_ACCESS

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING

Next

Time model statistics measure the time spent in the database by operation type. The most important time model statistic is database time (DB time). DB time represents the total time spent in database calls by foreground sessions, and is an indicator of the total instance workload. As shown in Figure 2-1, database time makes up a portion of an application's overall user response time.

A session is a logical entity in the database instance memory that represents the state of a current user login to a database. Database time is calculated by aggregating the CPU time and wait time of all active sessions (sessions that are not idle). For any database request, the CPU time is the sum of the time spent working on the request, while the wait time is the sum of all the waits for various database instance resources. DB time includes only time spent on client processes and does not include time spent on background processes such as PMON.

For example, a user session may involve an online transaction made at an online bookseller consisting of the actions shown in Figure 2-2.

1. Query for novels by authorThe user performs a search for novels by a particular author. This action causes the application to perform a database query for novels by the author.
2. Browse results of queryThe user browses the returned list of novels by the author and accesses additional details, such as user reviews and inventory status. This action causes the application to perform additional database queries.
3. Add item to cartAfter browsing details about the novels, the user decides to add one novel to the shopping cart. This action causes the application to make a database call to update the shopping cart.
4. CheckoutThe user completes the transaction by checking out, using the address and payment information previously saved at the bookseller's website from a previous purchase. This action causes the application to perform various database operations to retrieve the user's information, add a new order, update the inventory, and generate an email confirmation.

For each of the preceding actions, the user makes a request to the database, as represented by the down arrow in Figure 2-2. The CPU time spent by the database processing the request and the wait time spent waiting for the database are considered DB time, as represented by the shaded areas. After the request is completed, the results are returned to the user, as represented by the up arrow. The space between the up and down arrows represents the total user response time for processing the request, which contains other components besides DB time, as illustrated in Figure 2-1.

The objective of database tuning is to reduce DB time. In this way, you can improve the overall response time of user transactions in the application.

Next

Wait events are incremented by a session to indicate that the session had to wait for an event to complete before being able to continue processing. When a session has to wait while processing a user request, the database records the wait by using one of a set of predefined wait events. The events are then grouped into wait classes, such as User I/O and Network. Wait event data reveals symptoms of problems that might be affecting performance, such as latch, buffer, or I/O contention.

Next

A large number of cumulative database statistics are available on a system and session level. Some of these statistics are collected by AWR.

Next

The Active Session History (ASH) statistics are samples of session activity in the database. The database samples active sessions every second and stores them in a circular buffer in the System Global Area (SGA). Any session that is connected to the database and using CPU, or is waiting for an event that does not belong to the idle wait class, is considered an active session. By capturing only active sessions, a manageable set of data is represented. The size of the data is directly related to the work being performed, rather than the number of sessions allowed on the database.

Using the DB time example described in "Time Model Statistics", samples of session activity are collected from the online transaction made at the bookseller's website, represented as vertical lines below the horizontal arrow in Figure 2-3.

The light vertical lines represent samples of inactive session activity that are not captured in the ASH statistics. The bold vertical lines represent samples of active sessions that are captured at:

• 7:38, while novels by the author are being queried
• 7:42, while the user is browsing the query results
• 7:50, when one novel is added to the shopping cart
• 7:52, during the checkout process

Table 2-1 lists ASH statistics collected for the active sessions, along with examples of the session ID (SID), module, SQL ID, session state, and wait events that are sampled.

Next

SQL statements that are consuming the most resources produce the highest load on the system, based on criteria such as elapsed time and CPU time.

Next

Check the following:

• Is it an isoalted system? meaning are other users able to connect to thesame database or is it just this one user who is unable to connect.
• Has the server, database, or listener configuration remained the same for some
  time? Or Have there been a recent maintenance or changes to the configuration?

Next

To logon to the Linux/Unix oracle server, you will need the following information:

1. your Linux/Unix username/password.
2. The ability to "su" as the oracle user.
3. The Oracle password.

To check that the database is up, log in to the database and connect with a valid
username and password. For example:
SQLPLUS system
Enter password: password

A message appears, confirming that you are connected with the database. If you
receive the following errors, then ask your Database Administrator to assist you:
■ ORA-1017: invalid U/P
■ ORA-1034: Oracle not available

Next

Verify at least one of the following statements. This will help you decide if it is a client
problem.
■ The database server passed a loopback test, showing that the connection worked.
■ Other computers connect also using Oracle Net Services to this same database.
■ Connections from this workstation worked before making changes on this
computer, such as the installation of a new product or modification to the network
configuration.

The following procedure describes how to perform diagnostics on the client:
1. Check that you have installed the same protocol support that was installed on the
database server.
On Linux and UNIX platforms you can use the ADAPTERS utility to verify protocol
support. On the database server, run the following command from the ORACLE_HOME/bin directory to display the protocol support, naming methods, and security
options linked with the oracle executable:

run the command as shown below

hostname:/apps/oracle/product/12.1.1/bin>adapters ./oracle

Oracle Net transport protocols linked with ./oracle are:

IPC
BEQ
TCP/IP
SSL
SDP/IB
RAW

Oracle Net naming methods linked with ./oracle are:

Local Naming (tnsnames.ora)
Oracle Directory Naming
Oracle Host Naming

Oracle Advanced Security options linked with ./oracle are:

RC4 40-bit encryption
RC4 56-bit encryption
RC4 128-bit encryption
RC4 256-bit encryption
DES40 40-bit encryption
DES 56-bit encryption
3DES 112-bit encryption
3DES 168-bit encryption
AES 128-bit encryption
AES 192-bit encryption
AES 256-bit encryption
MD5 crypto-checksumming
SHA-1 crypto-checksumming
Kerberos v5 authentication
RADIUS authentication

On the client, run the adapters command from the ORACLE_HOME/bin directory to
display the configured Oracle protocol support, naming methods, and security
options. The ADAPTERS utility displays output similar to the following:

Ensure that the Oracle Net foundation layer and the appropriate Oracle protocol
support are present by verifying that all Oracle Net Services software has been
installed for the client.
4. Ensure that the client computer has the tnsnames.ora and the sqlnet.ora files in
the correct locations.

If you have any other working client computers connecting to the selected Oracle
Database, then back up your existing files and copy both the working
tnsnames.ora and sqlnet.ora files from the working client computer to the
non-working clients. This eliminates the possibility of errors in the files.
5. Test the Oracle Net foundation layer. You can test using the following command to
connect to SQL*Plus:
sqlplus user/password@connect_string

Note: Do not use the TNSPING utility. The TNSPING utility
works like the TCP/IP PING utility and does not create and open a
socket, nor does it connect with the listener. It ensures that the
listener is present on the database server.

Next

Cause: An error has occurred on the database server.
Action: Check the alert_sid.log file on the server. An unexpected end of file was processed on the communication channel. This may be an indication that the communications link may have gone down at least temporarily, or it may indicate that the server has gone down.You may need to modify your retransmission count.

Next

ORA-12154: TNS:could not resolve the connect identifier specified
Cause: A connection to a database or other service was requested using a connect
identifier, and the connect identifier specified could not be resolved into a connect
descriptor using one of the naming methods configured. For example, if the type
of connect identifier used was a net service name then the net service name could
not be found in a naming method repository, or the repository could not be located
or reached.
Action: Perform the following steps:
1. Check the type of naming adapters listed in the names.directory_path
parameter in the sqlnet.ora file. If none are configured, then use the
adapters command to determine which adapters are in use. The following
example shows the adapters:
$ adapters
...
Installed Oracle Net naming methods are:
Local Naming (tnsnames.ora)
Oracle Directory Naming
Oracle Host Naming
NIS Naming
The net service name given in the connect string should be defined for at least
one of the naming methods.
2. Check the resolution path for each adapter for possible problems. For
example, ensure that the name given in the connect string is correct and
complete, using the full name of the net service if necessary.
When using the local naming method, do the following:
1. Verify that the tnsnames.ora file exists and is in the correct location. The
location is either the ORACLE_HOME/network/admin directory or the directory
specified by the TNS_ADMIN environment variable.
2. Verify there is an entry in the tnsnames.ora file for the name given in the
connect string. This net service name should match the name in the
tnsnames.ora file exactly if the name is simple and there is not NAMES_
DEFAULT_DOMAIN in the sqlnet.ora file, or the net service name is a
fully-qualified name. If the net service name in the connect string is simple,
then check the NAMES_DEFAULT_DIRECTORY parameter in the sqlnet.ora
file. Its value is appended to the net service name given in the connect string.
This fully-qualified name should be the entry in the tnsnames.ora file.
3. If you are connecting from a login dialog box, then verify that you are not
placing an at sign (@) before your connect net service name.

Activate client tracing and repeat the operation.
When using the directory naming method, do the following:
1. Verify the ldap.ora file exists and is in the correct location. The following
directories are searched for ldap.ora file in the order given. The ldap.ora file
found will be used.
– The directory specified by the TNS_ADMIN environment variable.
– The ORACLE_HOME/network/admin directory.
– The directory specified by the LDAP_ADMIN environment variable.
– The ORACLE_HOME/ldap/admin directory.
2. Verify that the parameters defined in the ldap.ora file are correct, as follows:
– The DIRECTORY_SERVERS parameter defines the correct host and port
for one or more valid LDAP servers.
– The DEFAULT_ADMIN_CONTEXT parameter defines the location of the
Oracle Context in this directory which should include the net service
entry.
If the ldap.ora file does not exist, then these parameters will be resolved
using automatic discovery.
3. Verify that the LDAP server host and port are defined in DNS.
4. Verify that the directory has the default Oracle Context defined.
5. Use the ldapsearch utility or a directory administration tool to verify that the
net service object exists in the Oracle Context at the location given by the value
of the DEFAULT_ADMIN_CONTEXT parameter.
When using the Easy Connect naming method, do the following:
1. Verify that the host name give is correct, and is defined in the local host name
resolution service, such as local hosts file, DNS, and so on.
When using the external naming method, do the following:
1. Verify that the NIS file for tnsnames is properly set up.
2. Check that the net service name matches the tnsnames entry as described in
the preceding local naming section.

■ "Example of Troubleshooting a TNS-12154 Error" on page 16-19
for additional information about troubleshooting the error
■ "Using Localized Management" on page 3-1 for configuration
file location information
■ Chapter 8, "Configuring Naming Methods" for naming
information

Next

Cause: The client failed to establish a connection and complete authentication in
the time specified by the SQLNET.INBOUND_CONNECT_TIMEOUT parameter in the
sqlnet.ora file. This error may be a result of network or system delays, or it may
indicate that a malicious client is trying to cause a denial-of-service attack on the
database server.

Action: If the error occurred due to system or network delays that are normal for
the particular environment, then perform the following steps:
1. Turn on tracing to determine which clients are timing out.
2. Reconfigure the SQLNET.INBOUND_CONNECT_TIMEOUT, SQLNET.SEND_TIMEOUT, or
SQLNET.RECV_TIMEOUT parameters in sqlnet.ora to a larger value.
If you suspect a malicious client, then perform the following steps:
1. Restrict access to the client. For example, you can configure parameters for
access rights in the sqlnet.ora file.
2. Locate the IP address of the client in the sqlnet.log file on the database server
to identify the source. Remember that an IP address can be forged.
For example, the following sqlnet.log excerpt shows a client IP address of
192.168.2.35.
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.0.0
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.0.0
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.0.0
Time: 03-MAY-2009 13:51:12
Tracing to file: /ora/trace/svr_13279.trc
Tns error struct:
nr err code: 0
ns main err code: 12637
TNS-12637: Packet receive failed
ns secondary err code: 12604

nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.35)(PORT=52996))
If the time out occurs before the IP address can be retrieved by the database
server, then enable listener tracing to determine the client that made the
request.

Next

Cause: The listener failed to start the Oracle program. Possible reasons include:
■ The maximum number of processes allowed for a single user was exceeded

The listener does not have execute permission on the Oracle program
■ The associated Microsoft Windows service is not started
In some cases, these errors can be caused by the same conditions which cause the
following errors:
■ TNS-12549/ORA-12549
■ TNS-00519
■ TNS-12540/ORA-12540
■ TNS-00510
■ TNS-12560/ORA-12560
Action: Perform the appropriate action:
■ Increase the number of processes by setting the PROCESSES parameter in the
database initialization file to a larger value.
■ Check the listener.log file for detailed error stack information

Next

Cause: The listener received a request to establish a connection to a database or
other service. The connect descriptor received by the listener specified a service
name for a service (usually a database service) that has either not yet dynamically
registered with the listener or has not been statically configured for the listener.
This may be a temporary condition such as after the listener has started, but before
the database instance has registered with the listener.
Action: Perform the following steps:
1. Wait a moment, and then try to connect a second time.
2. Check which services are currently known by the listener by running the
Listener Control utility STATUS or SERVICES command.
3. Check that the SERVICE_NAME parameter in the connect descriptor specifies a
service name known by the listener.
4. Check for an event in the listener.log file.

Next

Cause: The type of service handler requested by the client is incorrect or not
registered for the requested SERVICE_NAME/INSTANCE_NAME, or the database
instance is not registered with the listener.
Action: If you suspect the problem is the wrong type of service handler, then
perform the following steps:
1. If (server=value) is set in the connect descriptor, then ensure that the value is
set to the appropriate service handler type for the database, that is, dedicated
for dedicated server or shared for dispatchers. You can use the Listener.

Control utility SERVICES command to see what service handlers are currently
registered with the listener.
2. If USE_DEDICATED_SERVER is set to ON in the sqlnet.ora file, then ensure the
database is configured to use dedicated servers. If it is not, then set this
parameter to OFF.
3. Ensure that the database instance is running. If the instance not running, then
start it so that it can register with the listener

Next

Cause: The instance name in the connect descriptor is incorrect, or the database
instance is not registered with the listener.
Action: Perform the following steps:
1. Ensure that the service name specified in the connect descriptor is correct.
2. Ensure that the database instance is running. If the instance not running, then
start it so that it can register with the listener. You can use the Listener Control
utility SERVICES

Next

Cause: The client failed to complete its connect request in the time specified by
the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file.
This error may be a result of network or system delays, or it may indicate that a
malicious client is trying to cause a denial-of-service attack on the listener.
Action: If the error occurred due to system or network delays that are normal for
the particular environment, then reconfigure the INBOUND_CONNECT_TIMEOUT_
listener_name parameter in listener.ora to a larger value.
If you suspect a malicious client, then perform the following steps:
1. Locate the IP address of the client in the listener.log file to identify the
source. Remember that an IP address can be forged.
For example, the following listener.log excerpt shows a client IP address of
192.168.2.35.
03-MAY-2009 16:42:35 * <unknown connect data> *
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.35)(PORT=53208)) * establish *
<unknown sid> * 12525
TNS-12525: TNS:listener has not received client's request in time
allowed
TNS-12604: TNS: Application timeout occurred

Restrict access to the client. For example, you can configure parameters for
access rights in the sqlnet.ora file.

Next

Cause: The protocol specific parameters in the ADDRESS section of the designated
connect descriptor are incorrect.
Action: Correct the protocol address.

Note: This error is often caused by hand-editing of the tnsnames.ora
file.

Next

Cause: An internal limit has been exceeded. Possible limits include:
■ Number of open connections that Oracle Net can process simultaneously
■ Number of memory buffers which can be used simultaneously
■ Number of processes a particular database instance is allowed
The first two are examples of hard limits. The third is an example of a limit which
can be increased by setting PROCESSES parameter in the database initialization file
to a larger value. In this case, a TNS-12500/ORA-12500 error is also returned.
In some cases, these errors can be caused by the same conditions which cause
TNS-12549/ORA-12549 and TNS-00519 errors.
Action: Wait for the open connections to close and retry. If the error persists, then
check the sqlnet.log or listener.log file for detailed error stack information.

Next

Cause: The connection request could not be completed because the listener is not
running.
Action: Perform the following actions:
■ Ensure that the supplied destination address matches one of the addresses
used by the listener.
■ Verify that the listener is running at the address specified by the request.
■ Ensure the listener is listening on the host and port specified by the request.
■ Verify the client is pointing to the listener.

Next

Cause: A quota or hard limit imposed by the operating system has been exceeded.
Possible limits include:
■ The maximum number of processes allowed for a single user
■ The operating system is running low on paging space

Action: Perform the appropriate action:
■ Increase the number of processes by setting the PROCESSES parameter in the
database initialization file to a larger value.
■ Check the sqlnet.log or listener.log file for detailed error stack
information, such as an operating system error code to help identify which
quota has been exceeded.

Next

Cause: There was an error when using a particular protocol. This error may be
due to incorrect configuration of an ADDRESS parameter or may occur due to errors
returned from the underlying protocol or operating system interface.
In some cases, these errors are caused by the same conditions which cause
TNS-00510, TNS-00519, TNS-12540/ORA-12540, TNS-12549/ORA-12549 errors.
Action: This error occurs on Microsoft Windows systems only. Perform the
following actions:
1. Select Run from the Microsoft Windows Start menu.
2. Enter MSCONFIG in the Open field.
3. Go to the Services tab.
4. Enable OracleServicesid if it is disabled.
5. Restart the computer.
6. Check that Oracle Services has started

Next

please remember in order to produce reports, access AWR views, or use the diagnostic information from any part of the Automatic Workload Repository, the Diagnostic Pack License is required. This includes the production of AWR reports, ADDM reports and ASH reports even if these are requested by product support or other agencies.

AWR reports are an extremely useful diagnostic tool for the determination of the potential cause of database wide performance issues.

Typically when a performance issue is detected, you would collect an AWR report covering the period of the poor performance. It is best to use a reporting period no longer than 1 hour as otherwise specifics can be lost.

t is also prudent to Gather AWR reports during times when performance is acceptable to provide baselines for comparison when there is a problem.  Ensure that the baseline snapshot duration is the same as the problem duration to facilitate like with like comparison

Automatic Workload Repository (AWR) is a licensed feature that allows information to be recorded on a database for multiple purposes including detection and elimination of performance issues.

Next

Automatic Workload Repository (AWR) is a collection of persistent system performance statistics owned by the SYS user.
It resides in SYSAUX tablespace.
By default snapshot are generated once every 60min and maintained for 8 days to ensure the capture of an entire week of performance data (7 days in Oracle 10g).

An AWR report outputs a series of statistics based on the differences between snapshots that may be used to investigate performance and other issues.

Running a Basic Report

With appropriate licenses for AWR, you may generate an AWR report by executing
the following script and pick the two snapshots you want to use for the sample;

Steps To Generate a report:

Run the Following against the database with issues:

$ORACLE_HOME/rdbms/admin/awrrpt.sql

Depending on the reasons for collecting the report, the default can be used, or for a more focused view, a short 10-15 minute snapshot could be used.

You will also be asked for the format of the report (text or html) along with the report name.

Generating Various Types of AWR Reports

AWR reports can be generated by running various SQL scripts to satisfy various requirements. Each report is available in HTML or TXT format:

• awrrpt.sql
  Displays various statistics for a range of snapshots Ids.
• awrrpti.sql
  Displays statistics for a range of snapshot Ids on a specified database and instance.
• awrsqrpt.sql
  Displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a particular SQL statement.
• awrsqrpi.sql
  Displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.
• awrddrpt.sql
  Compares detailed performance attributes and configuration settings between two selected time periods.
• awrddrpi.sql
  Compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.

Next

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (43200 = 30 Days).
                               -- Current value retained if NULL.
    interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

Next

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_snapshot();
END;
/

Next

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
(low_snap_id=>40,
High_snap_id=>80);
END;
/

Next

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 10, 
    end_snap_id   => 100,
    baseline_name => 'AWR First baseline');
END;
/

In 11g, there is a newly introduced procedure DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE that specifies a template for how baselines should be created for future time periods:

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
start_time => to_date('&start_date_time','&start_date_time_format'),
end_time => to_date('&end_date_time','&end_date_time_format'),
baseline_name => 'MORNING',
template_name => 'MORNING',
expiration => NULL ) ;
END;
/

"expiration => NULL" means that this baseline will be kept forever.

Next

BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'AWR First baseline');
END;
/

ou can also drop a baseline created on a different or old database:

We can also specify a template for creating and dropping baseline based on repeating time periods:

Next

Workload Repository Views

The following workload repository views are available:

• V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
• V$METRIC - Displays metric information.
• V$METRICNAME - Displays the metrics associated with each metric group.
• V$METRIC_HISTORY - Displays historical metrics.
• V$METRICGROUP - Displays all metrics groups.
• DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
• DBA_HIST_BASELINE - Displays baseline information.
• DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
• DBA_HIST_SNAPSHOT - Displays snapshot information.
• DBA_HIST_SQL_PLAN - Displays SQL execution plans.
• DBA_HIST_WR_CONTROL - Displays AWR settings.

Automation of AWR reports ?

There is no product functionality to produce the AWR reports in an automated way. In regards to the Oracle product, the production of a report is a manual step. However, one could use the dbms_workload_repository.awr_report_text in a (bash for instance for Unix like OS's) script. It would be scheduled (using the crontab for Unix like OS's) and be instructed with or determine the variables to provide to the awr_report_text function. There are such scripts available on the web, but not officially provided by Oracle as part of the product.

License Needed to Use AWR

Please remember that in order to use AWR license is needed.  Statspack can be used if AWR license is not acquired. See:

Next

The ADDM report for the same period as that covered by the snapshot interval can identify and suggest potential resolution options for many issues. The ADDM report provides a number of recommendations on various areas such as CPU bottlenecks, undersized memory structures, I/O capacity issues, high-load SQL statements etc. that otherwise may need experience and/or be difficult and time consuming to produce. For more on ADDM reports see

Next

The header section contains useful information that can help set the context of the report you are looking at. For example, the report contains a number of sections that quote specific counts of various statistics. Without a timescale, these numbers are meaningless.

• Release
  Depending on the problem, the database version may be important. If the version is old or is not the latest patchset release then the most up to date fixes may not be applied which has the potential to open the database up to issues.
• RAC
  If the database is running in a Real Application Cluster configuration then you may need to look at information from the other instances to get a full picture of the database performance
• Platform
  There may be platform specific issues that have a bearing on the system
• CPUs/Cores
  In a multi-processor environment, the "wall clock" time is not necessarily a good indicator of how much work the database can do since multiple operations can be pursued simultaneously. You can use cores for an indication of how much CPU work can likely be done at once.
• Snap Time
  The Snap time shows the times for the starting and ending snapshots for the report period. Does this cover the time of problem that is being encountered?
• Elapsed time
  The elapsed time indicates the duration of the report between the 2 selected snapshots. Any other duration figures can be compared back to this. When looking at this figure, is the duration reasonable? If the duration is too short then important information may be missed. If it is too long then findings may be diluted. A 30-60 minute reporting period is usually recommended. In terms of AWR snapshots, as much as possible snapshots should be minimum 10 minutes, maximum 30 minutes.
• DB time
  The DB Time is the time spent in the database for the period of the report. If this is significantly higher than the Elapsed time then this is a good indicator of a heavily loaded system. Remember that on a multi-processor system, you might expect the DB Time to be able to exceed the elapsed time. Additionally, the db time includes the time waiting for the CPU to become available, so this number can be higher than the Elapsed time X Cores.
  In the example above, the numbers say that the database worked for 2193 minutes in 15 minutes of elapsed time. Whether that is an indication of a problem depends on the capacity and concurrency capabilities of the system. Looking at the numbers, 2193:15 is a ratio of 146:1, so, in this case, if they had significantly less than 146 cpus it is likely that there is some overloading issues.  Remember that the user perception is also a significant factor in whether there is a "performance issue" - if the system delivers what the users want then there might not be a problem!
• Sessions
  You can use the sessions information along with the DB time to give an average amount of DB time per session. Are there a large number or a small number of connections?

Next

The load profile section can provide you with a more detailed impression of where the database is loaded. Information is provided "Per Second" and "Per Transaction" for most statistics and also "Per Exec" and "Per Call" for DB Time and CPU.

Suggested interpretations:

• DB CPU(s)
  The DB CPU(s) figure shows the amount of the CPU being used by the database. You can use this alongside the actual number of cores to give you an idea of how much of the total available machine CPU is actually being used by this instance.
• DB Time(s) Here the "Per Second" information gives you another version of the total DB time used, just in this case expressed as every second as opposed to the full elapsed period.

Other statistics should be looked at within the context of the overall elapsed time and also in the context of the top waits, once you have looked at these later. For example:

• Top events indicate library cache or cursor contention
  In this case it would be sensible to look at the load in terms of Parse and Hard Parse statistics. The number of parses per execution could also be a relevant indicator
• Top events are related to reading of blocks
  In this case, do we see mainly physical or logical reads? If it is physical then are the explain plans for top queries such as to encourage more logical reads?

Next

Looking at these in the context of a specific wait is far more beneficial than attempting to reach 100%. If the bottleneck is elsewhere, attempting to change individual statistics will have little or no impact on the overall system. For example, in the Instance Efficiency Percentages above, the "Buffer Hit %" is 99.88%. If there is no contention for buffers and no waits for buffers, then what is the benefit in making changes to try to improve this number? 

You should also look at the numbers in the context of the other numbers. For example, in the case above, let us say that there is a performance issue and the top timed events showed that CPU usage was a significant resource. Looking at the "Parse CPU to Parse Elapsd %" alone, this says that 26.87% of the total parse time is CPU and maybe you would prefer a lower percentage (although 26% seems quite reasonable).  Since the:  "% Non-Parse CPU" is 98.07% this means that only 1.03% of the total CPU usage is parsing, so even if you reduced that 26.87% to the impossible value of zero then you would only gain 1% extra CPU overall. It is likely that you would need to look elsewhere for the cause of your CPU resource issue.

Examine Top 5 Timed Events for highest resource users

Once you have looked at the background information, the Top 5 Timed Events section is the place to start in order to tell what is taking up the largest proportion of the database time. Based upon the general feeling for the system, the top resource users are put in context and can be investigated to determine a root cause. This topic is covered in more detail in the following article:

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
           Buffer Nowait %:   99.91       Redo NoWait %:  100.00
           Buffer  Hit   %:   98.14    In-memory Sort %:   99.98
           Library Hit   %:   99.91        Soft Parse %:   94.48
        Execute to Parse %:   99.45         Latch Hit %:   99.97
Parse CPU to Parse Elapsd %:   71.23     % Non-Parse CPU:   99.00

Next

Once you have looked at the background information, the Top 5 Timed Events section is the place to start in order to tell what is taking up the largest proportion of the database time. Based upon the general feeling for the system, the top resource users are put in context and can be investigated to determine a root cause. This topic is covered in more detail in the following article:

op 5 Timed Events

As mentioned, the Top waits section is the most important single section in the whole report being as it quantifies and allows comparison of the primary diagnostic: what each session is waiting for. An example output is provided below:

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db file scattered read           10,152,564      81,327      8   29.6   User I/O
db file sequential read          10,327,231      75,878      7   27.6   User I/O
CPU time                                         56,207          20.5
read by other session             4,397,330      33,455      8   12.2   User I/O
PX Deq Credit: send blkd             31,398      26,576    846    9.7      Other
         -------------------------------------------------------------

The Top 5 Waits section reports on a number of useful topics related to Events. It records the number of waits encountered in the period and the total time spent waiting together with the average time waited for each event. The section is ordered by the %age of the total call time that each Event is responsible for.

Dependent on what is seen in this section, other report sections may need to be referenced in order to quantify or check the findings. For example, the wait count for a particular event needs to be assessed based upon the duration of the reporting period and also the number of users on the database at the time; 10 Million waits in 10 minutes is far more significant than 10 Million in 10 hours, or if shared among 10 users as opposed to 10,000.

In this example report, almost 60% of the time is spent waiting for I/O related reads.

• Event 'db file scattered read ' is typically used when fetching blocks for a full table scan index fast full scan and performs multi-block IO.
• Event 'db file sequential read'  is a single block read and is typically engaged for any activity where  multi-block IO is unavailable (for example index reads).

Another 20% of the time is spent waiting for or using CPU time. High CPU usage is often a symptom of poorly tuned SQL (or at least SQL which has potential to take less resource) of which excessive I/O can also be a symptom. More on CPU usage follows later.

Based on this, we would investigate whether these waits indicate a problem or not. If so, resolve the problem, if not, move on to the next wait to determine if that is a potential cause.

There are 2 main reasons why I/O related waits are going to be top of the waits:

• The database is doing lots of reads
• The individual reads are slow

The Top 5 events show us information that helps us here :

• Is the database doing lots of reads?:
  The section shows > 10 Million reads for each of these events in the period.
  Whether this is a lot depends on whether the report duration is 1 hour or 1 minute.
  Check the report duration to asses this.
  If the reads do seem excessive, then why would the database do a lot of reads?
  The database only reads data because the execution of SQL statements has instructed it to do so. To investigate further refer to the SQL Statistics Section.
• Are the individual reads slow?
  The section shows waits of <=8 ms for the 2 I/O related events.
  Whether this is fast or slow is dependent on the hardware underlying the I/O subsystem, but typically anything under 20 ms is acceptable.If the I/O was slow, then you can get further information from the 'Tablespace IO Stats ' section:

  Tablespace IO Stats                       DB/Inst: VMWREP/VMWREP  Snaps: 1-15
  -> ordered by IOs (Reads + Writes) desc
  
  Tablespace
  ------------------------------
                  Av      Av     Av                       Av     Buffer Av Buf
          Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
  -------------- ------- ------ ------- ------------ -------- ---------- ------
  TS_TX_DATA
     14,246,367     283    7.6     4.6  145,263,880    2,883  3,844,161    8.3
  USER
        204,834       4   10.7     1.0   17,849,021      354     15,249    9.8
  UNDOTS1
         19,725       0    3.0     1.0   10,064,086      200      1,964    4.9
  AE_TS
      4,287,567      85    5.4     6.7          932        0    465,793    3.7
  TEMP
      2,022,883      40    0.0     5.8      878,049       17          0    0.0
  UNDOTS3
      1,310,493      26    4.6     1.0      941,675       19         43    0.0
  TS_TX_IDX
      1,884,478      37    7.3     1.0       23,695        0     73,703    8.3
  SYSAUX
        346,094       7    5.6     3.9      112,744        2          0    0.0
  SYSTEM
       101,771       2    7.9     3.5       25,098        0        653    2.7

  Specifically, look for the timing under Rd(ms).  If it is higher than 20 milliseconds per read and reads are high, then you may want to start investigating a potential I/O bottleneck from the os.

Next

Performance tuning using the Oracle performance method is driven by identifying and eliminating bottlenecks in the database, and by developing efficient SQL statements. Database tuning is performed in two phases: proactively and reactively.

In the proactive tuning phase, you must perform tuning tasks as part of your daily database maintenance routine, such as reviewing ADDM analysis and findings, monitoring the real-time performance of the database, and responding to alerts.

In the reactive tuning phase, you must respond to issues reported by users, such as performance problems that may occur for only a short duration of time, or performance degradation to the database over a period of time.

SQL tuning is an iterative process to identify, tune, and improve the efficiency of high-load SQL statements.

Applying the Oracle performance method involves the following:

• Performing pre-tuning preparations, as described in "Preparing the Database for Tuning"
• Tuning the database proactively on a regular basis, as described in "Tuning the Database Proactively"
• Tuning the database reactively when performance problems are reported by the users, as described in "Tuning the Database Reactively"
• Identifying, tuning, and optimizing high-load SQL statements, as described in "Tuning SQL Statements"

To improve database performance, you must apply these principles iteratively.

Next

Determine the scope of the performance project and subsequent performance goals, and determine performance goals for the future. This process is key for future capacity planning.

Next

Check for hardware or operating system resources that are fully utilized. List any overused resources for possible later analysis. In addition, ensure that all hardware is functioning properly

Next

Ensure that the STATISTICS_LEVEL initialization parameter is set to TYPICAL (default) or ALL to enable the automatic performance tuning features of Oracle Database, including AWR and ADDM.

Next

Ensure that the CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter is set to DIAGNOSTIC+TUNING (default) or DIAGNOSTIC to enable ADDM

Next

ADDM automatically detects and reports on performance problems with the database, including most of the "Common Performance Problems Found in Databases". The results are displayed as ADDM findings on the Database Home page in Oracle Enterprise Manager Cloud Control (Cloud Control). Reviewing these findings enables you to quickly identify the performance problems that require your attention.

Next

Implement the ADDM recommendations, as described in Automatic Database Performance Monitoring.

With each ADDM finding, ADDM automatically provides a list of recommendations for reducing the impact of the performance problem. Implementing a recommendation applies the suggested changes to improve the database performance

Next

The Performance page in Cloud Control enables you to identify and respond to real-time performance problems. By drilling down to the appropriate pages, you can identify and resolve performance problems with the database in real time, without having to wait until the next ADDM analysis.

10/22

4 Monitoring Real-Time Database Performance

 

Monitoring Instance Activity

Below the Average Active Sessions chart on the Performance page are other charts that you can use to monitor database instance activity. As explained in "Customizing the Database Performance page", you can also customize the Performance page so that the most useful instance activity charts are displayed by default.

You can use the instance activity charts to perform the following tasks:

• Monitoring Throughput
• Monitoring I/O
• Monitoring Parallel Execution
• Monitoring Services

 

 

Monitoring Throughput

Database throughput measures the amount of work the database performs in a unit of time. The Throughput charts show any contention that appears in the Average Active Sessions chart.

Compare the peaks on the Throughput charts with the peaks on the Average Active Sessions chart. If the Average Active Sessions chart displays a large number of sessions waiting, indicating internal contention, but throughput is high, then the situation may be acceptable. The database is probably also performing efficiently if internal contention is low but throughput is high. However, if internal contention is high but throughput is low, then consider tuning the database.

To monitor throughput:

1. Access the Database Home page.See "Accessing the Database Home Page" for more information.
2. From the Performance menu, select Performance Home.If the Database Login page appears, then log in as a user with administrator privileges. The Performance page appears.
3. Click the Throughput tab.
4. Select one of the following Instance Throughput Rate options.
   • Per SecondTwo charts appear. One shows the number of logons and transactions per second and the other shows the physical reads and redo size per second.Figure 4-10 shows the Throughput charts with the Instance Throughput Rate of Per Second selected. The bar in the middle of the figure indicates a portion of the charts (from approximately 1:37 to 1:52) that has been removed for space considerations. In Figure 4-10, the most transactions occurred from 1:15 to 1:27 p.m. and from 2:08 to 2:12 p.m.
   • Per TransactionOne chart appears that shows the number of physical reads and redo size per transaction.

Figure 4-10 Monitoring Throughput

Description of "Figure 4-10 Monitoring Throughput"

 

 

Monitoring I/O

The I/O charts show I/O statistics collected from all database clients. The I/O wait time for a database process represents the amount of time that the process could have been doing useful work if a pending I/O had completed. Oracle Database captures the I/O wait times for all important I/O components in a uniform fashion so that every I/O wait by any Oracle process can be derived from the I/O statistics.

Figure 4-11 Monitoring I/O

Description of "Figure 4-11 Monitoring I/O"

The Latency for Synchronous Single Block Reads chart shows the total perceived I/O latency for a block read, which is the time difference between when an I/O request is submitted and when the first byte of the transfer arrives. Most systems are performing satisfactorily if latency is fewer than 10 milliseconds. This type of I/O request is the best indicator of I/O performance for the following reasons:

• Write operations may exhibit good performance because of write caches in storage.
• Because multiblock I/O requests have varying sizes, they can take different amounts of time.
• The latency of asynchronous I/O requests does not represent the full I/O wait time.

The other charts shown depend on your selection for I/O Breakdown, as described in the following sections:

• Monitoring I/O by Function
• Monitoring I/O by Type
• Monitoring I/O by Consumer Group

 

 

Monitoring I/O by Function

The I/O Function charts determine I/O usage level by application or job. The component-level statistics give a detailed view of the I/O bandwidth usage, which you can then use in scheduling jobs and I/O provisioning. The component-level statistics fall in the following categories:

• Background typeThis category includes ARCH, LGWR, and DBWR.
• ActivityThis category includes XDB, Streams AQ, Data Pump, Recovery, and RMAN.
• I/O typeThe category includes the following:
  • Direct WritesThis write is made by a foreground process and is not from the buffer cache.
  • Direct ReadsThis read is physical I/O from a data file that bypasses the buffer cache and reads the data block directly into process-private memory.
  • Buffer Cache Reads
• OthersThis category includes I/Os such as control file I/Os.

To monitor I/O by function:

1. Access the Performance page, as explained in "Monitoring User Activity".
2. In the instance activity chart, click I/O.The Latency for Synchronous Single Block Reads, I/O Megabytes per Second, and I/O Requests per Second charts appear.
3. For I/O Breakdown, select I/O Function.The I/O Megabytes per Second by I/O Function and I/O Requests per Second by I/O Function charts appear.The example in Figure 4-11 shows that a significant amount of I/O is being performed by the log writer. The log writer activity peaked at approximately 600 I/O requests per second.
4. Click the largest colored area on the chart or the corresponding function in the legend to drill down to the function with the highest I/O rate.An I/O Throughput by I/O Function page appears with details about the selected category.You can view real-time or historical data for details on I/O megabytes or I/O requests.

See Also:

• Oracle Database Concepts to learn about database background processes such as ARCH, LGWR, and DBWR

 

 

Monitoring I/O by Type

The I/O Type charts enable you to monitor I/O by the types of read and write operations. Small I/Os are requests smaller than 128 KB and are typically single database block I/O operations. Large I/Os are requests greater than or equal to 128 KB. Large I/Os are generated by database operations such as table/index scans, direct data loads, backups, restores, and archiving.

Description of the illustration GUID-17EB6E79-4966-4C55-92EC-FFDE061459A6-default.gif

When optimizing for short transaction times, such as in an OLTP environment, monitor latency for small I/Os. High latencies typically indicate that the storage system is a bottleneck.

When optimizing for large queries, such as in a data warehouse, performance depends on the maximum throughput the storage system can achieve rather than the latency of the I/O requests. In this case, monitor the I/O megabytes per second rather than the synchronous single-block I/O latencies.

To monitor I/O by type:

1. Access the Performance page, as explained in "Monitoring User Activity".
2. In the instance activity chart, click I/O.The I/O Megabytes per Second and I/O Requests per Second charts appear.
3. For I/O Breakdown, select I/O Type.The I/O Megabytes per Second by I/O Type and I/O Requests per Second by I/O Type charts appear.In the above sample chart, the number of small writes per second increased to more than 600. These writes correspond to the log writer I/O requests shown in Figure 4-11.
4. Click the largest colored area on the chart or the corresponding function in the legend to drill down to the function with the highest I/O rate.The I/O Details page appears.You can view real-time or historical data for details on I/O megabytes or I/O requests.

 

 

Monitoring I/O by Consumer Group

When Oracle Database Resource Manager is enabled, the database collects I/O statistics for all consumer groups that are part of the currently enabled resource plan. The Consumer Group charts enable you to monitor I/O by consumer group.

A resource plan specifies how the resources are to be distributed among various users (resource consumer groups). Resource consumer groups enable you to organize user sessions by resource requirements. Note that the _ORACLE_BACKGROUND_GROUP_ consumer group contains I/O requests issued by background processes.

Description of the illustration GUID-33B8399F-4B00-4A92-A49E-906DF968EC4D-default.gif

To monitor I/O requests by consumer group:

1. Access the Performance page, as explained in "Monitoring User Activity".
2. In the instance activity chart, click I/O.The I/O Megabytes per Second and I/O Requests per Second charts appear.
3. For I/O Breakdown, select Consumer Group.The I/O Megabytes per Second by Consumer Group and I/O Requests per Second by Consumer Group charts appear.

 

 

Monitoring Parallel Execution

The Parallel Execution charts show system metrics related to parallel queries. Metrics are statistical counts per unit. The unit could be a time measure, such as seconds, or per transaction, or session.

A parallel query divides the work of executing a SQL statement across multiple processes. The charts show parallel queries that were waiting for a particular wait event that accounted for the highest percentages of sampled session activity.

Figure 4-12 Monitoring Parallel Execution

Description of "Figure 4-12 Monitoring Parallel Execution"

To monitor parallel execution:

1. Access the Performance page, as explained in "Monitoring User Activity".
2. In the instance activity chart, click Parallel Execution.The Parallel Execution charts appear.Two pairs of charts are shown. The first pair shows the number of sessions on the y-axis, whereas the second pair shows the per second rate on the y-axis.In the example shown in Figure 4-12, query parallelization was active from 12:30 p.m. to 4 p.m.

 

 

Monitoring Services

Services represent groups of applications with common attributes, service-level thresholds, and priorities. For example, the SYS$USERS service is the default service name used when a user session is established without explicitly identifying a service name.

Figure 4-13 Monitoring Services

Description of "Figure 4-13 Monitoring Services"

To monitor services:

1. Access the Performance page, as explained in "Monitoring User Activity".
2. In the instance activity chart, click Services.The Services chart appears. The Services chart shows services waiting for the corresponding wait event during the time period shown. Only active services are shown.In Figure 4-13, the SYS$USERS service has the greatest number of active sessions.
3. Click the largest colored area on the chart or the corresponding service in the legend to drill down to the service with the highest number of active sessions.The Service page appears, showing the Activity subpage.You can view real-time data showing the session load for all wait classes associated with the service.

Next

Respond to performance-related alerts, as described in Monitoring Performance Alerts .

The Database Home page in Cloud Control displays performance-related alerts generated by the database. Typically, resolving the problems indicated by these alerts improves database performance.

12/22

6 Monitoring Performance Alerts

Next

Validate that any changes have produced the desired effect, and verify that the users experience performance improvements

Next

CPU bottlenecks

Is the application performing poorly because the system is CPU-bound? Performance problems caused by CPU bottlenecks are diagnosed by ADDM, as described in Automatic Database Performance Monitoring. You can also identify CPU bottlenecks by using the Performance page in Cloud Control, as described in "Monitoring CPU Utilization".

Monitoring CPU Utilization

Next

Are the Oracle memory structures such as the System Global Area (SGA), Program Global Area (PGA), and buffer cache adequately sized? Performance problems caused by undersized memory structures are diagnosed by ADDM, as described in Automatic Database Performance Monitoring. You can also identify memory usage issues by using the Performance page in Cloud Control, as described in "Monitoring Memory Utilization".

Monitoring Memory Utilization

Next

Is the I/O subsystem performing as expected? Performance problems caused by I/O capacity issues are diagnosed by ADDM, as described in Automatic Database Performance Monitoring. You can also identify disk I/O issues by using the Performance page in Cloud Control, as described in "Monitoring Disk I/O Utilization".

Monitoring Disk I/O Utilization

Next

Is the application making suboptimal use of Oracle Database? Problems such as establishing new database connections repeatedly, excessive SQL parsing, and high levels of contention for a small amount of data (also known as application-level block contention) can degrade the application performance significantly. Performance problems caused by suboptimal use of Oracle Database by the application are diagnosed by ADDM, as described in Automatic Database Performance Monitoring. You can also monitor top activity in various dimensions—including SQL, session, services, modules, and actions—by using the Performance page in Cloud Control, as described in "Monitoring User Activity".

Monitoring Real-Time Database Performance

Next

Is the database performing suboptimally due to a high degree of concurrent activities in the database? A high degree of concurrent activities might result in contention for shared resources that can manifest in the form of locks or waits for buffer cache. Performance problems caused by concurrency issues are diagnosed by ADDM, as described in Automatic Database Performance Monitoring. You can also identify concurrency issues by using Top Sessions in Cloud Control, as described in "Monitoring Top Sessions".

Monitoring Top Sessions

Next

Is the database configured optimally to provide desired performance levels? For example, is there evidence of incorrect sizing of log files, archiving issues, too many checkpoints, or suboptimal parameter settings? Performance problems caused by database configuration issues are diagnosed by ADDM, as described in Automatic Database Performance Monitoring.

http://docs.oracle.com/database/121/TDPPT/tdppt_method.htm#TDPPT018

Next

Are users complaining about short-lived or intermittent performance problems? Depending on the interval between snapshots taken by AWR, performance problems that have a short duration may not be captured by ADDM. You can identify short-lived performance problems by using the Active Session History report, as described in Resolving Transient Performance Problems .

Resolving Transient Performance Problems

Next

Is there evidence that the database performance has degraded over time? For example, are you or your users noticing that the database is not performing as well as it was 6 months ago? You can generate an AWR Compare Periods report to compare the period when the performance was poor to a period when the performance is stable to identify configuration settings, workload profile, and statistics that are different between these two time periods. This technique helps you identify the cause of the performance degradation, as described in Resolving Performance Degradation Over Time .

Resolving Performance Degradation Over Time

 

Using the AWR Compare Periods Reports

After an AWR Compare Periods report is generated for the time periods you want to compare, you can use it to analyze performance degradation. To learn how to create the report, see "Running the AWR Compare Periods Reports".

Figure 9-1 shows a portion of an AWR Compare Periods report.

Figure 9-1 AWR Compare Periods Report

Description of "Figure 9-1 AWR Compare Periods Report"

The AWR Compare Periods report is divided into the following sections:

• Summary of the AWR Compare Periods Report
• Details of the AWR Compare Periods Report
• Supplemental Information in the AWR Compare Periods Report

 

 

Summary of the AWR Compare Periods Report

The report summary is at the beginning of the AWR Compare Periods report, and summarizes information about the snapshot sets and loads used in the report. The report summary contains the following sections:

• Snapshot Sets
• Host Configuration Comparison
• Cache Sizes
• Load Profile
• Top Timed Events

 

 

Snapshot Sets

The Snapshot Sets section displays information about the snapshot sets used for this report, such as instance, host, and snapshot information.

In the example shown in Figure 9-1, the first snapshot period corresponds to the time when performance was stable on February 7 from 10:50 to 11:20. The second snapshot period corresponds to the time when performance degradation occurred on the same day from 12:50 to 13:00.

 

 

Host Configuration Comparison

The Host Configuration Comparison section compares the host configurations used in the two snapshot sets. For example, the report compares physical memory and number of CPUs. Differences in the configurations are quantified as percentages in the %Diff column.

 

 

Cache Sizes

The Cache Sizes section compares the database configurations used in the two snapshot sets. For example, the report compares the SGA and log buffer size. Differences in the configurations are quantified as percentages in the %Diff column.

 

 

Load Profile

The Load Profile section compares the loads used in the two snapshot sets. Differences in the loads are quantified as percentages in the %Diff column.

Description of the illustration GUID-901D05EC-DA77-4676-80A0-A36E8CC36092-default.gif

In this example, the DB time per second was 100% higher in the first period. CPU time per second was 100% higher.

 

 

Top Timed Events

The Top Timed Events section is one of the most useful sections in the report. This section displays the five timed events or operations that consumed the highest percentage of total DB time in each of the snapshot sets.

Description of the illustration GUID-EA7C3DC4-CEC0-4D8F-9B41-31B7B6ABA7BD-default.gif

In this example, CPU time and the number of waits for database file sequential reads are significantly higher in the first period than in the second.

 

 

Details of the AWR Compare Periods Report

The Report Details section follows the summary of the AWR Compare Periods report, and provides statistics about the snapshot sets and loads used in the report. For example, the section includes statistics for time model statistics, wait events, SQL execution time, and instance activity.

 

 

Supplemental Information in the AWR Compare Periods Report

The supplemental information is at the end of the AWR Compare Periods report, and provides additional information about initialization parameters and SQL statements. The init.ora Parameters section lists all the initialization parameter values for

Next

Are any SQL statements using excessive system resources that impact the system? Performance problems caused by high-load SQL statements are diagnosed by ADDM, as described in Automatic Database Performance Monitoring and "Identification of High-Load SQL Statements Using ADDM Findings". You can also identify high-load SQL statements by using Top SQL in Cloud Control, as described in "Identifying High-Load SQL Statements Using Top SQL". After they have been identified, you can tune the high-load SQL statements using SQL Tuning Advisor, as described in Tuning SQL Statements .

Identifying High-Load SQL Statements

Next

Are any database objects the source of bottlenecks because they are continuously accessed? Performance problems caused by object contention are diagnosed by ADDM, as described in Automatic Database Performance Monitoring. You can also optimize the data access path to these objects using SQL Access Advisor, as described in Optimizing Data Access Paths .

Optimizing Data Access Paths

Next

Is the performance of SQL statements degrading after they have been tuned? Tuning SQL statements may cause changes to their execution plans, resulting in a significant impact on SQL performance. In some cases, the changes may result in the improvement of SQL performance. In other cases, the changes may cause SQL statements to regress, resulting in a degradation of SQL performance.

Before making changes on a production system, you can analyze the impact of SQL tuning on a test system by using SQL Performance Analyzer. This feature enables you to forecast the impact of system changes on a SQL workload by:

• Measuring the performance before and after the change
• Generating a report that describes the change in performance
• Identifying the SQL statements that regressed or improved
• Providing tuning recommendations for each SQL statement that regressed
• Enabling you to implement the tuning recommendations when appropriate

To learn how to use SQL Performance Analyzer, see Oracle Database Testing Guide.

You can run SQL Performance Analyzer on a production system or a test system that closely resembles the production system. Testing a system change on a production system will impact the system's throughput because SQL Performance Analyzer must execute the SQL statements that you are testing. Any global changes made on the system to test the performance effect may also affect other users of the system. If the system change does not impact many sessions or SQL statements, then running SQL Performance Analyzer on the production system may be acceptable. However, for systemwide changes—such as a database upgrade—using a production system is not recommended. Instead, consider running SQL Performance Analyzer on a separate test system so that you can test the effects of the system change without affecting the production system. Using a test system also ensures that other workloads running on the production system will not affect the analysis performed by SQL Performance Analyzer. Running SQL Performance Analyzer on a test system is the recommended approach and the methodology described here. If you choose to run the SQL Performance Analyzer on the production system, then substitute the production system for the test system where applicable.

Analyzing the SQL performance effect of system changes using SQL Performance Analyzer involves the following steps, as illustrated in Figure 2-1:

1. Capture the SQL workload that you intend to analyze and store it in a SQL tuning set, as described in "Capturing the SQL Workload".
2. If you plan to use a test system separate from your production system, then perform the following steps:
   1. Set up the test system to match the production environment as closely as possible.
   2. Transport the SQL tuning set to the test system.

   For more information, see "Setting Up the Test System".

3. On the test system, create a SQL Performance Analyzer task, as described in "Creating a SQL Performance Analyzer Task".
4. Build the pre-change SQL trial by test executing or generating execution plans for the SQL statements stored in the SQL tuning set, as described in "Measuring the Pre-Change SQL Performance"
5. Perform the system change, as described in "Making a System Change"
6. Build the post-change SQL trial by re-executing the SQL statements in the SQL tuning set on the post-change test system, as described in "Measuring the Post-Change SQL Performance"
7. Compare and analyze the pre-change and post-change versions of performance data, and generate a report to identify the SQL statements that have improved, remained unchanged, or regressed after the system change, as described in "Comparing Performance Measurements"
8. Tune any regressed SQL statements that are identified, as described in "Fixing Regressed SQL Statements".
9. Ensure that the performance of the tuned SQL statements is acceptable by repeating steps 6 through 8 until your performance goals are met.For each comparison, you can use any previous SQL trial as the pre-change SQL trial and the current SQL trial as the post-change SQL trial. For example, you may want to compare the first SQL trial to the current SQL trial to assess the total change, or you can compare the most recent SQL trial to the current SQL trial to assess just the most recent change.

Next

Run ADDM manually to diagnose current and historical database performance when performance problems are reported by the users, as described in Manual Database Performance Monitoring.

Next

Resolve transient performance problems, as described in Resolving Transient Performance Problems .

The Active Session History (ASH) reports enable you to analyze transient performance problems with the database that are short-lived and do not appear in the ADDM analysis.

Next

Resolve performance degradation over time, as described in Resolving Performance Degradation Over Time .

The Automatic Workload Repository (AWR) Compare Periods report enables you to compare database performance between two periods of time, and resolve performance degradation that may happen from one time period to another.

Next

Validate that the changes made have produced the desired effect, and verify that the users experience performance improvements.

Next

Repeat these steps until your performance goals are met or become impossible to achieve due to other constraints.

Next

Identify high-load SQL statements, as described in Identifying High-Load SQL Statements .

Next

Tune high-load SQL statements, as described in Tuning SQL Statements .

You can improve the efficiency of high-load SQL statements by tuning them using SQL Tuning Advisor.

Next

Optimize data access paths, as described in Optimizing Data Access Paths .

You can optimize the performance of data access paths by creating the proper set of materialized views, materialized view logs, and indexes for a given workload by using SQL Access Advisor.

Next

Analyze the SQL performance impact of SQL tuning and other system changes by using SQL Performance Analyzer.

To learn how to use SQL Performance Analyzer, see Oracle Database Testing Guide.

Next

Repeat these steps until all high-load SQL statements are tuned for greatest efficiency

Complete Course