Apps - Performance Tuning - Oracle Application DBA

Hi Posted on: 2008-09-25 05:32:50 By: Anonymous
Hi Kishore... Your blog is really very much help full..... Many thanks for you continous efforts... Hitesh Mishra

Thanks A Lot ... Posted on: 2008-09-27 11:49:11 By: Anonymous
Thank you very much for posting valuable documents... Sincerely, Sruti

Re: Thanks A Lot ... Posted on: 2010-10-13 19:47:04 By: akr14feb
Diagnosing and Resolving Error ORA-04031 [Video] (Doc ID 146599.1) NOTE:396940.1 - Troubleshooting and Diagnosing ORA-4031 Error [Video]

Performance Links
Posted on: 2010-11-24 21:08:16 By: akr14feb

Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12 (Doc ID 362851.1)

For OACoreGroup:

Assumption: eBusiness Suite is running in a 2-tier environment (middle tier in a separate machine)

- No more than 100 active users per JVM/OC4J instance

- There should be no more than 1 active JVM/OC4J instance per CPU (ideally, there should be 1 JVM per 2 CPUs)

- Watch out for available memory (make sure that you have enough memory to run all the configured JVMs/OC4J instances without swapping)

Note: Each 'core' in a multi-core system is considered to be a separate CPU. For example, a dual quad-core chip would be the same as stating 2 x 4 x CPU or 8 CPUs.

In regards to hyper-threading cores, in theory the same thing applies, but in some cases it didn't equate to the exact same as a separate core. One could tune greater then a single CPU, but not treating the same as two CPUs.

Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12 (Doc ID 362851.1)

Tuning performance on eBusiness suite (Doc ID 744143.1)

Configuring various JVM tuning parameters for Oracle E-Business suite 11i and R12 (Doc ID 567551.1)

Purpose
This note describes how to investigate overall slow performace on eBusiness suite. In particular , we highlight the the most common wait events and how to interpret them in AWR/Statspack report. at the end we provide best practices on tuning performance on Database / Applications tier.

Instructions for the Reader
A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.
Troubleshooting Details
1.Ensure the initialization parameters for eBusiness suite are set correctly.

It can be checked by using Note 174605.1 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.

Do not gather statistics excessively on entire schemas or the entire database such as nightly or weekly.
Do not gather statistics on permanent objects during peak intervals.
Gathering statistics invalidates cursors .Unless you use the ‘No Invalidate’ option.
Gathering statistics requires dictionary and object level locks.
The option 'GATHER_AUTO' can be used, to gather statistics only on objects that have changes above the specified 'Modification Threshold' (percentage of DML compared to the number of rows of the table).

Plans are not likely to change if the data distribution has not changed.
Use only FND_STATS or the Gather Schema and Gather Table Statistics Concurrent Programs.
Do NOT USE the analyze or dbms_stats command directly. It is not supported, and results in sub-optimal plans.

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/
SQL> exec fnd_stats.gather_schema_statistics('MRP'); exec fnd_stats.gather_schema_statistics('ALL'); exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES');

Re: Performance Links
Posted on: 2010-11-24 21:09:05 By: akr14feb

NOTE:1020008.6 - SCRIPT: FULLY DECODED LOCKING
NOTE:1020012.6 - SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO
NOTE:122371.1 - How To Gather Statistics For Oracle Applications Prior to 11.5.10
NOTE:149124.1 - Creating a StatsPack performance report
NOTE:153507.1 - Oracle Applications and StatsPack
NOTE:169935.1 - Troubleshooting Oracle Applications Performance Issues
NOTE:174605.1 - bde_chk_cbo.sql - Reports Database Initialization Parameters related to an Apps 12 or 11i instance
NOTE:216205.1 - Database Initialization Parameters for Oracle Applications Release 11i
NOTE:228913.1 - Systemwide Tuning using STATSPACK Reports
NOTE:276103.1 - PERFORMANCE TUNING USING 10g ADVISORS AND MANAGEABILITY FEATURES
NOTE:34566.1 - WAITEVENT: "enqueue" Reference Note
NOTE:362851.1 - Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12
NOTE:396009.1 - Database Initialization Parameters for Oracle Applications Release 12
NOTE:94224.1 - FAQ- Statspack Complete Reference
http://blogs.oracle.com/stevenChan/2008/10/tuning_all_layers_of_the_oracle_e-business_suite.html

Re: Performance Links
Posted on: 2010-11-24 21:10:02 By: akr14feb

Bug 6519700: ESC: CSE: R12SIP: 6513826 FRMWEB RUNAWAY PROCESS CONSUMING 100% CPU-MIDDLE TIER
top
TOP

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
21745 oacoestg 25 0 112m 63m 18m R 100 0.5 50:19.44 frmweb

3 root 34 19 0 0 0 S 0 0.0 0:00.39 ksoftirqd/0

ps -ef | grep 21745oacoestg 21745 6357 77 11:39 ? 00:51:15 frmweb webfile=5,1265,1,/apps/local/COESTG/inst/apps/COESTG_oa1-itst-01/logs/ora/10.1.2/forms/socket.log

the verification of the issue is to run a select * from v$session where process='’ , if there is no output, it may be considered a runaway ‘frmweb process’
Per Bug 6519700 and 745711.1 following is the solution

Forms Process (FRMWEB) Consumes 100% of CPU in Oracle Applications R12 (Doc ID 745711.1)

Metalink Notes Posted on: 2010-12-24 12:43:52 By: akr14feb
How to Identify Resource Intensive SQL for Tuning (Doc ID 232443.1) Example "Top SQL" queries from V$SQLAREA (Doc ID 235146.1)

Many Thanks for a great effort Posted on: 2011-01-02 10:50:47 By: kathamchaitanya	Edited By: kathamchaitanya On: 2011-01-02 10:52:47
hello sir This is an incredible library for me it helped me a lot to learn many things and a great effort by you sir god bless you ....! Many Many never ending thanks to you sir Regards chaitanya

Having performance issues
Posted on: 2011-04-27 18:27:54 By: akr14feb

Do you run Gather Statistics for ALL on a regular basis?

Refer to note Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046 (Doc ID 224270.1) , will look
Also to note SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly (Doc ID 215187.1)
will look
Provide both Raw and TKPROFed form traces of the function that is slow. Provide both TRCANLZR and SQLTXPLAIN against the race file

Thank you very much sir Posted on: 2011-12-14 15:26:09 By: narayanajuttika
your blog is very help to us Thanks .... Thanks & Regards narayana

Long running jobs
Posted on: 2012-10-31 19:29:20 By: akr14feb

-- Identify the locks

select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;

-- Identify what's happening

select b.name, a.value vlu
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and sid =&sid
and a.value != 0
and b.name like '%row%'

-- for full table scan tables

set lines 150
col username format a20
col opname format a30
col target format a40
select sid,opname,target,
to_char(start_time,'DD-MON-YY HH24:MI') START_TIME,
time_remaining/60 "Time Remaining in Mins",username
from v$session_longops where time_remaining>1 order by time_remaining
/

No Subject Posted on: 2012-11-17 09:41:56 By: curtilina
Thanks for the nice blog. It was very useful for me. Keep sharing such ideas in the future as well. This was actually what I was looking for, and I am glad to came here! Thanks for sharing the such information with us. fitnash health

Theme design by R. Scott Baer