Introduction:
Prior to Release 11i, Oracle Applications have defaulted to use the Rule-Based Optimizer.
Oracle's Applications development team has reviewed SQL statements in 11i to tune them for use by the Cost-Based Optimizer (CBO).
CBO uses statistics to decide the execution of SQL statements.
FND_STATS is a vital part of collecting this data.
CBO stands for Cost Based Optimization. The R11i application was written to
use the CBO. It determines the most efficient way to execute a SQL statement
based on statistical data stored in the data dictionaries. These stored
statistics are used to compute the cost of executing the SQL. The execution
plan with the lowest cost is the one chosen by the optimizer.
References:
419728.1 How To Gather Statistics On Oracle Applications 11.5.10 - Concurrent Process,Temp Tables, Manually
232419.1 How to Troubleshoot Performance Issues
113653.1 OM-PERF: Performance Tuning - Setup
113573.1 OM-PERF: Performance Tuning - FAQ
744143.1 Tuning performance on eBusiness suite
156968.1 --> coe_stats.sql - Automates CBO Stats Gathering using FND_STATS and Table sizes
170647.1 Does Gather Schema Statistics collect statistics for indexes?
427878.1 Which Method To Gather Statistics When On DB 10g
Note 244040.1 - Oracle E-Business Suite Recommended Performance Patches
Note 122371.1 - How to gather statistics for Oracle Applications
Note 116571.1 - Cost Based Optimiser and Applications Release 11i
Note 117363.1 - Analysing 11i Tables for Performance
Note 102334.1 - How to automate ANALYZE TABLE when changes occur on tables
Note 114671.1 - Gathering Statistics for the Cost Based Optimizer
Note 116178.1 - Analyze Command - Compute Statistics vs. Estimate Statistics
Verifying Statistics
============
Note 163208.1 bde_last_analyzed.sql - Verifies CBO Statistics
Automatic Statistic Gathering
====================
Note 377152.1 Best Practices for automatic statistics collection on 10g
Note 465787.1 Managing CBO Stats during an upgrade to 10g or 11g
368252.1 EBPERF FAQ - Collecting Statistics with Oracle Apps 11i
394371.1 adpatch performance is slow
Note 370583.1 - Basic troubleshooting of JVM consuming cpu or too many JDBC connections in Apps 11i
Note 304099.1 - Using J2SE Version 5.0 with Oracle E-Business Suite 11i
Note 401561.1 - Using J2SE Version 6 with Oracle E-Business Suite 11i
The Rule Based Optimizer (RBO) uses a fixed ranking system to determine the
most efficient access method when executing a SQL statement. This method is
not as flexible as CBO; which takes into consideration the data distribution
characteristics of the database. CBO also offers a number of enhancements
which are not available with RBO.
RBO still exists. By default the 11i apps will use CBO. However, by
the use of hints, RBO can still be invoked.
Statistics must be gathered for Application Releases; 10.7, 11.0 and 11i.
Applications 11i must use FND_STATS. Applications 10.7 and 11.0 can use any method
ANALYZE Command - Compute Statistics vs. Estimate Statistics
COMPUTE STATISTICS
When computing statistics, an entire object is scanned to
gather data about the object. This data is used by Oracle
to compute exact statistics about the object. Slight variances
throughout the object are accounted for in these computed
statistics. Because an entire object is scanned to gather
information for computed statistics, the larger the size of
an object, the more work that is required to gather the necessary
information.
ESTIMATE STATISTICS
When estimating statistics, Oracle gathers representative
information from portions of an object. This subset of information
provides reasonable, estimated statistics about the object. The
accuracy of estimated statistics depends upon how representative
the sampling used by Oracle is. Only parts of an object are
scanned to gather information for estimated statistics, so an
object can be analyzed quickly. You can optionally specify the
number or percentage of rows that Oracle should use in making the
estimate.
Should I gather statistics for SYS schema?
- On 8i, never gather stats for the SYS schema, because it is not supported or recommended.
- On 9i, gathering stats for SYS schema is supported, but not necessary.
- On 10g, it is mandatory to collect stats for SYS schema.
How to collect statistics for SYS schema ?
You must have the SYSDBA (or both ANALYZE ANY DICTIONARY and ANALYZE ANY) system privilege to execute this procedure.
execute dbms_stats.gather_schema_stats(''SYS'', method_opt=>''for all columns size 1'', degree=>30,estimate_percent=>100,cascade=>true);
If you are using Database version 10G, you can use the following syntax instead
execute dbms_stats.gather_dictionary_stats ();
this procedure will gather statistics for all system schema's, including SYS and SYSTEM, and other optional schema's, such as CTXSYS and DRSYS.
There are no eBusiness Suite specific guidelines on how often these statistics need to be refreshed.
Normally, you would need to re-gather stats on those objects if there is a significant change in the environment (a lot of DB users created, a lot of new objects created or rebuilt, etc).
How do I collect statistics for fixed objects (V$ views) in 10G databases?
Run the following command during a typical workload
dbms_stats.gather_fixed_objects_stats;
Note: the script $APPL_TOP/admin/adstats.sql requires the Database to be in restricted mode and could be used only when running upgrades. For optimal performance, statistics on fixed objects should be gathered when there is a typical load in the system.
There are no eBusiness Suite specific guidelines on how often these statistics need to be refreshed
Normally, you would need to re-gather stats on fixed objects after upgrades or if the workload changes.
For additional guidelines, refer to
Note 465787.1 Managing CBO Stats during an upgrade to 10g or 11g
Checks:
1.Run the Verify Stats report to confirm that statistics are current.
This report is a utility provided with FND_STATS, and can be run as follows:
SQL> set server output on
SQL> set long 10000
SQL> exec FND_STATS.VERIFY_STATS(OM, OE_ORDER_HEADERS_ALL)
This procedure takes a schema name and/or a list of comma separated
table_names and checks the stats on all these objects and spools out a
report. Some important columns to look at are LAST_ANALYZED and NUM_ROWS.
2. Ensure that the database initialization parameters which affect CBO are set
up correctly.
Run the following script to verify the init.ora parameters are correct:
$FND_TOP/sql/AFCHKCBO.sql
The output of this report shows the customer's current settings vs. the
recommended settings for CBO.
3. When slow performance is encountered check that the profile
options OM: Debug is set to 'No' and OM: Debug Level is 'null'.
If these profile options are set to a value of 'yes' for
OM: Debug or other than NULL for OM: Debug Level, then system
performance maybe adversely affected.
What to do?
If you use Oracle Applications, it is recommended that you run the concurrent programs for gathering statistics
To run concurrent program Gather Schema Statistics:
1. Log on to Oracle Applications with
Responsibility = System Administrator
2. Submit Request Window
Navigate to: List > Request > Run.
3. Enter the appropriate parameters. This can be run for specific
schemas by specifying the schema name or entering 'ALL' to gather
statistics for every schema in the database.
4. Submit the gather schema statistics program.
The following concurrent requests are available in Oracle Applications for gathering statistics:
Gather All Column Statistics
Gather Column Statistics
Gather Schema Statistics
Gather Table Statistics
For Oracle Applications 11i it is recommended to use only the 'Gather Schema Statistics' or the 'Gather Table Statistics'. Rest are not supported, and results in sub-optimal plans.
When you gather statistics for a table or a whole schema, it cascades down and gathers also the statistics for all indexes on each table and all columns for that particular table or schema.
Gather Schema Statistics Concurrent Request calls Gather_Schema_Stats as part of FND_STATS package.
The package specification and package body for FND_STATS are located in
$FND_TOP/admin/sql/ AFSTATSS.pls and AFSTATSB.pls
AFSTATSB.pls:
--------------
FND_STATS.Gather_Schema_Stats calls
FND_STATS.GATHER_TABLE_STATS with cascade = TRUE for each schema calls
DBMS_STATS.GATHER_TABLE_STATS with cascade_true
Cascade option of GATHER_TABLE_STATS:
--------------------------------------
Running procedure DBMS_STATS.GATHER_TABLE_STATS with parameter cascade set to
the non-default value TRUE means that indexes for the table are analyzed.
Using the cascade option is equivalent to running the gather_index_stats
procedure on each of the table's indexes, however, Applications customers are
always recommended to use the Gather Schema Statistics concurrent request
rather than analyze Apps objects manually.
parameters:
· Schemaname
You may enter ALL to analyze every defined App schema.
· Estimate_percent
Percentage of rows to estimate. If left empty it will default to 10%. The valid range is 0-99. A higher percentage will be more accurate, but take longer to run. If the object(s) that you are gathering statistics for do not change often or the object(s) has data entered that is very similiar you may choose a lower number. However, if the data changes frequently a larger number entered for this parameter would be recommended to provide a more accurate representation of your data.
· Degree
Enter the Degree of parallelism. If not entered, it will default to min(cpu_count, parallel_max_servers). Modifying the degree of parallelism on a table can cause the plan to change. Increasing the degree of parallelism is likely to make full table scans appear cheaper and more attractive while reducing it will make Full Table Scans look less attractive.
· Backup Flag
If the value is 'NOBACKUP' then it won't take a backup of the current statistics and should run quicker. If the value is 'BACKUP' then it does an export_table_stats prior to gathering the statistics.
· Restart Request Id
Enter the request id that should be used for recovering gather_schema_stats if this request should fail. You may leave
this parameter null.
· Gather Options
As of 11.5.10, FND_STATS.GATHER_SCHEMA_STATS introduced a new parameter called OPTIONS that, if set to GATHER AUTO, allows FND_STATS to automatically determine the tables for which statistics should be gathered based on the change threshold. The Modifications Threshold can be adjusted by the user by passing a value for modpercent, which by default is equal to 10. GATHER AUTO uses a database feature called Table Monitoring, which needs to be enabled for all the tables. A procedure called ENABLE_SCHEMA_MONITORING has been provided to enable monitoring on all tables for a given schema or all Applications schemas .
Manual Execution
=============
In R11i customers should be using the FND_STATS command.
Do not use the ANALYZE command or DBMS_STATS package directly,
as doing so may cause incomplete statistics to be generated.
Use the following command to gather schema statistics:
exec fnd_stats.gather_schema_statistics('ONT') < For a specific schema >
exec fnd_stats.gather_schema_statistics('ALL') < For all schemas >
Use the following command for gathering statistics on a temporary table
(ie: temporary tablename = TEMP_tmp in schema ABC):
exec fnd_stats.gather_table_stats('ABC','TEMP_tmp');
# sqlplus apps/
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
Troubleshooting tips:
1.Ensure the initialization parameters for eBusiness suite are set correctly.
It can be checked by using bde_chk_cbo.sq l .Then you verify the result with one of the following two notes :
Note 216205.1 Database Initialization Parameters for Oracle Applications 11i
Note 396009.1 Database Initialization Parameters for Oracle Applications Release 12
2.Make sure Gather Shema Stats is running on periodical basis. It can be checked with bde_last_analyzed.sql (Note 163208.1) which verifies stats by schema and index.
3.For 10g Database users, we recommend to enable ASMM(Automatic shared memory management). This puts Oracle in control of allocating memory within the SGA. The SGA_TARGET parameter sets the amount of memory available to the SGA. This parameter can be altered dynamically up to a maximum of the SGA_MAX_SIZE parameter value. Provided the STATISTICS_LEVEL is set to TYPICAL or ALL and the SGA_TARGET is set to a value other than "0" Oracle will control the memory pools which would otherwise be controlled by the following parameters:
DB_CACHE_SIZE (default block size)
SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
4.If all the above settings are fine, and still you face slow performance, then either AWR or Statspack report is must checking.
-For10g Database users, refer to Note 276103.1 PERFORMANCE TUNING USING 10g ADVISORS AND MANAGEABILITY .
-For 9i Database users, refer to Note 94224.1 Title: FAQ- Statspack Complete Reference
Best practices for tuning performance
Database tier
Refer to Recommended Performance Patches for the Oracle E-Business Suite.
There are several optimizer related patches that need to be applied on top of 10.2.0.(2/3). Refer to Note 244040.1 for an updated list.
Convert to the OATM Tablespace Model for the EBusiness Suite. See Note 404954.1 How to run OATM migration utility.
Applications tier
Deploy with socket mode for internal users: R12: Refer to Note 384241.1.
Enable Forms Dead Client Detection
Value specified in minutes : FORMS_TIMEOUT=10
• Terminates fwebmx processes for dead clients.
• Enable Forms Abnormal Termination Handle
Do not set FORMS_CATCHTERM
Disable Cancel Query
• Cancel Query increases middle-tier CPU as well as DB CPU
• Refer to MetaLink Note 138159.1 on how to enable and tune Cancel query related parameters
• To Disable Cancel Query : Set the Profile “FND: Enable Cancel Query” to ‘No’
For tuning JVM/OC4J Refer to Note 362851.1:Guidelines to setup the JVM in Apps 11i
Use one JVM per 2 CPUs
• No more than one JVM/CPU
• No more than 100 concurrent users per JVM
Tracing The Performance of a Process:
1. Navigate to SQLPLUS.
2. At the SQL prompt, type the following:
SQL> alter system set timed_statistics = true;
Whenever troubleshooting an application performance problem, it is a good idea
to collect timed_statistics to determine which piece of code is taking the
longest amount of time to execute. Before running a trace, make sure that
timed_statistics is turned on at the database level. This will in no way affect
performance for the users that are currently using the database.
1. Form Trace:
To turn
trace on at the form level, go to the toolbar and navigate to
HELP -> TOOLS -> TRACE. By clicking on the word trace, a checkmark will appear
next to it, indicating that trace has been turned on. Reproduce the problem.
Navigate to HELP -> TOOLS -> TRACE, and click on the word trace to uncheck
trace and turn it off.
2.SQL Trace
Some applications now have profiles that allow you to turn trace
on within the application. These profiles are usually updateable at
the user level and can be turned on just for the user who is trying
to trace a process. By turning trace on with the use of one of these
profile options, you are effectively doing the same thing as setting
SQL_TRACE=TRUE in the init.ora, but you DO NOT have to bounce the
database. See the below list of current Trace Profile Options:
AX: Trace Mode
Debug: Trace Level for Danish
INV: Debug Trace
MRP: Trace Mode
OE: Debug Trace
Utilities: SQL Trace
If you do not have the luxury of a profile option to turn SQL trace
on, you can also turn trace on by typing the following at the SQL
prompt:
SQL> alter session set sql_trace = true;
After setting SQL Trace to true, run the process and then turn off trace
when you are done:
SQL> alter session set sql_trace = false;
3.tkprof:
tkprof sys=no explain=apps/
sort='(prsela,exeela,fchela)'
The above mentioned command sorts the SQL by placing the worst performing
SQL statement at the top of the output file
