0 Replies Latest reply on Nov 25, 2015 8:20 PM by Sachin Thapa

    Performance issues in EBS

    Sachin Thapa

      I have documented few steps to be performed while checking Performance Issues in EBS systems:

      Please find the details below and suggest if anything else needs to be added:

       

      what is slow?

      entire system?

      subsystem is slow?
      form based applications
      web based applications
      concurrent manager


      particular process is slow?
      eg: saving an invoice–> take a trace and tkprof
      CHECKS BEGIN HERE*****************

      check 1
      was re-org and purging done??

      Desktop check 2

      –resource at desktop
      –check enough cpu/ram is there
      –ensure that no heavy applications are running when accessing applications.
      –required network

      Check 3
      concurrent manager
      tune concurrent manager(NOTE ID 1057802.1)
      –ensure enough CPU/RAM is available on the DB tier
      –check concurrent manager definition for Sleep time and Cache are set correct,, recommended value for sleep between 20-30 and cache (example if standard managers 10 then value shold be 10-15)
      –check if resource-intensive batch requests are scheduled during peak hours.
      resolution:- Separate manager for long running process with less number of managers.

      — check FND_CONCURRENT_REQUESTS & PROCESSES are purged & defragmented regularly.
      –check there are short running requests waiting for a longer time to process
      Resolution:- specialized managers
      –avoid enabling an excessive number of standard or specialized managers.

      Note 1057802.1 Best Practices for Performance for Concurrent Managers.

      check 4
      Forms Server
      — Memory intensive. Ensure RAM & CPU
      — check SAR & TOP (check for spinning f60webmx)
      — check FORMS60_CATCHTERM, FORMS_RECORD_GROUP_MAX=10000(DOC ID 745711.1)spinning f60webmx issue resolved using this.
      –Debug/FRD enabled ??
      –Forms Patch set level with IO patches.

      –Servlet VS Socket Mode

      servlet more cpu consuming, but if we switch of Socket mode we might loose some functionalities, choose wisely.

      check 5
      Applications:
      How many concurrent users are there?
      what are all the modules used?
      any resource-intensive module is used
      — when was gather schema statistics request run last–> this should be run regularly.(% should be 10-40)
      ****Note that Auto Sampling feature has been introduced since 11g for this
      below patches need to be applied:
      Apps Patch 16410424(12.1.X),, 16410424(12.0.X),, 14707975(11.5.10)
      after applying these patches we can leave the % as null , it will itself take the best % required and hence your gather stats will complete faster also.

      — Ensure Diagnostics profiles are disabled as otherwise this will consume lots of resource.
      — Sign on Audit is disabled if not needed.
      — Check if Audit on other tables have been enabled.
      Additional overhead
      — Ensure Purging & defragment of interface/transaction tables is done periodically. (NOTE ID 752322.1)

      check 6
      Database
      –check adequate resource i.e. CPU/RAM is available.
      Monitor SAR/VMSTAT output
      if I/O is high need to check I/O setup & SQL queries
      if CPU is high then check for CPU-intensive queries.

      Monitor top OS process at OS level
      top(HP)/prstat (SUN)/ ps aux (AIX)
      init.ora setup (Note 216205.1) init.ora check

      174605.1

      bde_chk_cbo script file —> checking init.ora file contents

      check 7

      Web Tier

      –check if JVM is hanging (due to Out of Memory issue)
      –check Heap Size and how many JVM threads are defined
      –Disable statement/debug level logs when not needed
      –AOL/J Database Connection Pool Status (DOC ID 278868.1)
      –Review DBC file configuration
      –Review Apache/Jserv configuration

      check 8

      Network

      –Check for sufficient Bandwidth (ping host -l 1024 -t)
      –Network Test(DOC ID 556738.1)
      **************************************
      Additional Information:
      **************************************
      Enabling Trace

      Four Possible levels

      Level 1: Regular trace/SQL trace
      Level 4: Trace with Binds
      Level 8: Trace with Waits –> Preferred
      Level 12:Trace with binds and Waits –> Very Expensive

      Form Specific

      open forms–>Help–>Diagnostics–>Trace–> ??(choose)
      then perform the activity/transaction
      disable the trace.

      Concurrent Request taking longer time ??

      Concurrent Report Tracing

      Set Enable Trace in Concurrent Program Definition form.
      program–>define window–>enable trace check box (Not recommended)

      Enable trace for a specific request (SRS form–>Debug options)
      — the profile “Concurrent:Allow Debgging” should be set to yes

      then that option gets enabled else it stays greyed out,this is a recommended way as only this particulat request will have trace enabled not all the programs being run by different user in case of program level trace enabling.

      after you set this profile option, go to submit concurrent request .. one new option will appear Debug Option–>click–>Pop up will show–>choose which trace option you would prefer.

      Trace enabling at User Level
      **********Initialization SQL Statement – Custom

      Profile–>user name then –>Initialization SQL Statement – Custom
      set at user level
      ******************************************************
      *********ALWAYS INVOKE TKPROF FROM Database Tier and NOT from Application Tier***********

      tkprof tracefile.trc output.txt sort=fchela,exeela,prsela
      in the output you will find out the culprit dml statement

      take the culprit sql statement in a text file

      gather enhanced Explain Plan using SQLTXPLAIN.SQL Note 215187.1
      note down all the tables used in that dml query
      –exec fnd_stats.gather_table_stats

      (‘APPLSYS’,’FND_CONCURRENT_REQUESTS’,PERCENT=>99);

      *******************************************************

      APPLY Performance Patches (DOC ID 244040.1)