>  Operating System
>  VMWARE
>  Installations - Apps11i
>  R12 Topics
>  Installations - Apps R12
>  Patching
>  Cloning
>  Oracle Apps Upgrades
>  System Administration - R11/R12
>  Advance Features - Oracle Apps R12
>  Want to Become an Apps DBA ?
>  Certification - Apps DBA
>  Apps Performance
>  Oracle Apps DBA Interview Questions
>  Apps Day to Day Queries

Home

Username

Password

Remember Me







Installation
RMAN - Backup & Recovery
Replication
Convert DB Character Set
DATAPUMP
Recoveries
Oracle 11g Topics

   Installation

Performance Topics - System Administration

Gather Schema and Table Statistics
Performance Problem with Form
Performance Problem with Concurrent Program
How To Kill Concurrent Request
Concurrent Program Tracing
Tuning Concurrent Manager
Schedule - Purge Concurrent Requests
Schedule - Purge Obsolete workflow Runtime Data
Performance Queries



Created on 09/10/2008 01:28 AM by akr14feb
Updated on 07/16/2012 09:10 AM by akr14feb
 Printable Version

The comments are owned by the poster. We are not responsible for its content.
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