Monday, March 31, 2008

ORACLE APPLICATIONS-11i: TRACE/PERFORMANCE ANALYSIS:

ORACLE APPLICATIONS-11i: TRACE/PERFORMANCE ANALYSIS:

Contents
Introduction
Application Trace and Analyzing Performance: Collecting the Trace
Application Trace and Analyzing Performance: Locating the Trace File
Application Trace and Analyzing Performance: TKPROF the Trace File
Application Trace and Analyzing Performance: TKPROF Output Analysis
Application Trace and Analyzing Performance: Cost-Based Statistics


Introduction
TKPROF Format and its use.
Unix-account> tkprof naerpcnv_1105.trc naerpcnv_1105.txt explain=apps/apps

Overview

This documentation offers step by step instructions to aid in retrieving and analyzing a trace for the
purpose of troubleshooting a performance problem. Additionally, general information is also provided
to help determine if there is a problem with the Oracle code vs. a problem with the current status of the
database.


Oracle Note: 117129.1

Additional References:
Oracle Note 100964.1 - Troubleshooting Performance Issues Relating to the Database and Core/MFG MRP
Oracle Note 100960.1 - Troubleshooting Performance Issues Relating To Database Objects And Core/Mfg MRP
Oracle Note 102334.1 - How to automate ANALYZE TABLE when changes occur on tables
Oracle Note 114671.1 - Gathering Statistics for the Cost Based Optimizer
Oracle Note 116178.1 - Analyze Command - Compute Statistics vs. Estimate Statistics









Application Trace and Analyzing Performance: Collecting the Trace

Performance problems can best be diagnosed by determining the portion of the code that is causing the problem.
The only way to do this is by obtaining an SQL Trace of the process that is performing poorly. Depending
on the process, there are several ways to get a trace.


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.

Login to the Database Server:
1. Login to the Database Server
Set the oracle user’s database-application environment by selecting from the available Instances
[unix] $ sqlplus
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

Note: Alternatively the init.ora ($ORACLE_HOME/dbs/init.ora) parameter
TIMED_STATISTICS can be changed to TRUE and the instance bounced to activate
the new TIMED_STATISTICS parameter. However, Oracle 7 and Oracle 8, permit
the Dynamic Setting of TIMED STATISTICS for Database Sessions.


Choosing the Best Type of Trace:

1. Form Trace:

Used whenever the poor performance is happening on-line. Form traces can diagnosis an inquiry that
is taking a long time to complete. Before turning trace on at the form level, close the application and then
re-enter it. This causes any open cursors from your previous session to close. Additionally, do not turn
trace on until just before you are ready to duplicate the problem.

Tool Bar: HELP -> TOOLS -> TRACE. (Turns Trace ON)

Reproduce the problem.

Tool Bar: HELP -> TOOLS -> TRACE. (Turns Trace OFF)



TIMED_STATISTICS = TRUE (continued)
Choosing the Best Type of Trace:

2. SQL Trace:

Used whenever you need to trace a background or concurrent process. There are a couple of ways to
set the system up to get a SQL Trace.

a. Some applications now have profiles that allow you to turn trace on within the application.
These profiles are can be changed at the user level and can be turned on just for the current user.
These Application Profile Trace parameters perform the same as setting SQL_TRACE=TRUE in
the init.ora ($ORACLE_HOME/dbs/init.ora), but avoid the unpleasantness of bouncing the
instance.

Oracle Application Profile Trace Parameters:

AX: Trace Mode
Debug: Trace Level for Danish
INV: Debug Trace
MRP: Trace Mode
OE: Debug Trace
Utilities: SQL Trace

b. Alternatively, SQL_TRACE can be turned ON for the Oracle Session:
Login to the Database Server:
Set the oracle user’s database-application environment by selecting from the available Instances
[unix] $ sqlplus
ALTER SESSION SET SQL_TRACE = TRUE;



After executing the PL/SQL process(s), turn off trace when you are done.

ALTER SESSION SET SQL_TRACE = FALSE;

Note: Alternatively the init.ora ($ORACLE_HOME/dbs/init.ora) parameter
TIMED_STATISTICS can be changed to TRUE and However, Oracle 7 and Oracle 8, permit
the Dynamic Setting of TIMED STATISTICS for Database Sessions.

c. As a last resort, edit init.ora parameter ($ORACLE_HOME/dbs/init.ora), set
SQL_TRACE = TRUE, and bounce the instance to activate the new TIMED_STATISTICS parameter.
Unfortunately SQL_TRACE turned ON at the Database level, will not only be tracing your process,
but any other process that is occurring in the database. Therefore, there may be complications/difficulties
associated with locating the trace file associated with your Oracle Process.

Note: Some Oracle Application processes, (MRP/DRP) create many trace file. Since the
Concurrent Process spawns other concurrent processes. To better isolate the correct
Trace File, empty the USER_DUMP_DEST directory before beginning the trace.

Additionally, MRP/DRP traces are best when the MRP:Snapshot Workers, Parameter
is set to 0 (zero). This Option forces the Memory-based Snapshot to produce only
one (1) Trace File.
Application Trace and Analyzing Performance: Locating the Trace File

Trace files are saved to the USER_DUMP_DEST directory on the UNIX Database server.

Login to the Database Server:
Set the oracle user’s database-application environment by selecting from the available Instances
[unix] $ sqlplus system/
SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME = ‘user_dump_dest’

NAME VALUE
-------------------------- ---------------------------------------------------------------
user_dump_dest /d01/oracle/naerpcnvdb/8.1.6/admin/udump


EXIT

[unix] $ cd
[unix] $ ls -ltr -or-
[unix] $ grep -i ‘Item Interface’


Note: ls -ltr lists the trace files in chronological order from first to last.
You can usually tell which Trace File is yours by the file time-stamp.
Alternatively, you can search the Trace Files for a specific ‘literal.’

Application Trace and Analyzing Performance: TKPROF the Trace File

Tkprof analyzes the Trace File and creates a readable output file useful for trouble-shooting.

Login to the Database Server:
Set the oracle user’s database-application environment by selecting from the available Instances
[unix] $ tkprof sys=no explain=apps/
sort='(prsela,exeela,fchela)' print=10

Note: TKProf will sort the SQL by placing the worst performing SQL
statement at the top of the output file.


tkprof output (example):

UPDATE exp_relief_interface
SET request_id = :sql_req_id,
process_status = 3
WHERE inventory_item_id IN
(SELECT inventory_item_id
FROM exp_relief_interface rel2
WHERE rel2.request_id IS NULL
AND rel2.error_message IS NULL
AND rel2.relief_type = 1
AND rel2.process_status = 2
AND rownum <= :batch_size
AND NOT EXISTS
(SELECT 'x'
FROM mrp_form_query
WHERE query_id = :in_process_items
AND number1 = rel2.inventory_item_id))
AND request_id IS NULL
AND error_message IS NULL
AND relief_type = 1
AND process_status = 2

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------
Parse 2 0.02 0.02 0 0 0 0
Execute 2 239.39 1003.16 274981 3792129 534 242
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ---------
total 4 239.41 1003.18 274981 3792129 534 242

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 41 (APPS)

tkprof output (example): (continued)

Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: RULE
0 UPDATE OF 'exp_relief_interface'
242 NESTED LOOPS
234 VIEW
242 SORT (UNIQUE)
242 COUNT (STOPKEY)
242 FILTER
1886651 TABLE ACCESS (BY INDEX ROWID) OF
'exp_relief_interface'
1886652 INDEX (RANGE SCAN) OF 'exp_relief_interface_N2'
(NON-UNIQUE)
234 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'MRP_FORM_QUERY_N89' (NON-UNIQUE)
3597 TABLE ACCESS (BY INDEX ROWID) OF 'exp_relief_interface'
3831 INDEX (RANGE SCAN) OF 'exp_relief_interface_N2'
(NON-UNIQUE)

Application Trace and Analyzing Performance: TKPROF Output Analysis

Each SQL statement in the tkprof’d output file has three sections:

a. The SQL statement
b. Diagnostics: CPU, elapsed time, disk reads, logical reads, and number of rows returned from the query.
c. Execution-Plan listing all of the tables, views, and indexes that were accessed by the query.

Hit Ratio: (indication of how often your query had to go to the disk vs. memory to get the rows needed)
A ‘good’ hit ratio will fall somewhere between 99% and 100%. Anything lower could indicate fragmentation or a problem with the size of your memory buffer. The hit ratio is calculated
as follows:

Logical Reads - Physical Reads -or- (Query + Current) - Disk
------------------------------ ------------------------
Logical Reads (Query + Current)


In the example on the previous page, the hit ratio = 3517682/3792663 = 92%


Cost Based Optimizer vs. Rule BasedOptimizer:
The tkprof output will NOT clearly indicate whether the Cost-Based or Rule-Based optimizer was used.
Unfortunately, the Execution-Plan always says, "Goal: Rule". Therefore, if there are any hints in the SQL statement
other than /*+Choose or /*+Rule, then you can assume that the Cost-Based optimizer was used to create the Execution-Plan.

Note: PL/SQL hints always follows this sequence of characters: /*+.

Cost-Based and Rule-Based optimizers use different methods to determine what indexes or tables
Should be used when searching for data. The optimizer also determines how the index will be
scanned (full, range, etc..) when searching for data. The Cost-Based optimizer depends on whether
or not Database Statistics (ANALYZE) have been collected on the database objects in order to
determine the best execution plan for the SQL statement. If statistics have not been recently collected,
the cost-based optimizer may not always make a good decision with regards to the use of indexes, etc.
when creating an execution plan for a SQL statement. Therefore, if you have determined that the
Cost-Based optimizer was used on a ‘sluggish’ SQL statement, please make sure that statistics
have been collected on all of the tables in the execution plan.


Application Trace and Analyzing Performance: Cost-Based Statistics

Collecting Statistics in Oracle Applications Release 11.0.3 or lower

Login to the Database Server:
Set the oracle user’s database-application environment by selecting from the available Instances
[unix] $ sqlplus
ANALYZE TABLE COMPUTE STATISTICS


Oracle Note 102334.1 - How to automate ANALYZE TABLE when changes occur on tables
Oracle Note 114671.1 - Gathering Statistics for the Cost Based Optimizer
Oracle Note 116178.1 - Analyze Command - Compute Statistics vs. Estimate Statistics


Collecting Statistics in Oracle Applications Release 11i

Cost-Based Optimizer (CBO) is the default optimizer. Therefore please execute the
GATHER SCHEMA STATISTICS (Concurrent Program) on a regular basis to insure optimum
performance.

Note: GATHER SCHEMA STATISTICS executes the FND_STATS function.
FND_STATS, however, is NOT the same as the Oracle ANALYZE Command.
FND_STATS and ANALYZE return different results.

Login to the Oracle Applications:
Select the System Administration Responsibility
Concurrent Request Gather Schema Statistics

Oracle Note 122371.1 - How to gather statistics for Oracle Applications
Oracle Note 116571.1 - Cost Based Optimiser and Applications Release 11i
Oracle Note 117363.1 - Analysing 11i Tables for Performance

No comments: