• Find us:
    +1-669-900-5138   |   +44-203-372-5553
  • Free Newsletter

    Get Latest Updates

  • Make Training Enquiry


    Company

  • Categories

  • Archive

  • Troubleshooting/Tuning Slow Form in Apps 11i

    Posted by "" in "troubleshooting" on 2008-06-20

    Share on FacebookTweet about this on TwitterShare on Google+Share on LinkedInEmail this to someone

    User reported that one form was taking too much time to save record and after sometime form was hanging (records were not saved).

    My initial question to user was

    1) Is this happening for all forms or just one form ?
    2) Is this issue happening for all users or a particular user ?

    and answer was, issue effecting all users but limited to a specific form.

    I could easily rule out network issue as problem was specific to particular form and for all users hence it was time to generate trace on particular form.

    Navigate to form right before user was experiencing problem and enabled trace from front end ( Help–>Debug–> Trace–> Trace with bind & wait) ;

    Trace in Apps 11i 

    If you don’t see option Debug under Help then check “Profile Option” “Hide Diagnostics menu entry” and ensure value is set to “No” 

    After enabling trace, carry out action and this should generate trace file

    Location of Trace File  Trace file should be in udump directory of database. To know location of udump directory login to database as sys or system run query “SQL> show parameter user_dump_dest” This will give you location of user dump directory to find trace file generated .

    Generate TKPROFNext step was to convert raw trace file into tkprof version.

    tkprof<tracefile> <tkprofoutput> explain=apps/passwd sys=no sort(with the options for parse,fetch and execute).

    To know more about raw trace & tkprof visit following links
     
    SQL Trace & TKPROF in 10g R2  and following Metalink Note

    224270.1 – Trace Analyzer TRCANLZR – Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046
    39817.1  – Interpreting Raw SQL_TRACE and DBMS_SUPPORT. START_TRACE output
    117129.1 – How to get a Trace for And Begin to Analyze a Performance Issue
    296559.1 – FAQ: Common Tracing Techniques within the Oracle Applications 11i

    While accessing the tkprof file i found noticed a particular “select statement” and its execution plan like 


    Read through TKPROF file and raw Trace
    While accessing the tkprof file I found one select statement and its execution plan like

    Rows     Execution Plan
    ——-  —————————————————
          0  SELECT STATEMENT   MODE: ALL_ROWS
       2232   SORT (ORDER BY)
       2232    NESTED LOOPS
       2232     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                    ‘xxxx’ (TABLE)
     791231      INDEX (RANGE SCAN) OF ‘xxxx_I10′ (INDEX)
       2232     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                    ‘YYYY'(TABLE)
       2232      INDEX   MODE: ANALYZED (UNIQUE SCAN) OF
                     ‘YYYY_U1′ (INDEX (UNIQUE))

    EXECUTION PLAN Before executing an Oracle Statement, Oracle first parse statement and develop execution plan. Execution Plan is sequence of operations Oracle Performs to run a statement. To know more about EXECUTION PLAN click here

    EXPLAIN PLAN statement allows you to submit SQL statement to Oracle and have database prepare execution plan for statement without actually executing it. The execution plan is available to you in form of row inserted in special table called PLAN TABLE.  You can query plan table to see steps of execution plan for statement. To know more about it click here

    -

    Compare same form with other instance 
     Generated explain plan for the same query to other instance where the form was working fine.  Like EXPLAIN PLAN [SET STATEMENT_ID= <string in single quote> ] [INTO <plan table name>] FOR <query>;  
        I got explain plan from plan_table, there I found some discrepancy between both the plans. Line  INDEX (RANGE SCAN) OF ‘xxxx_I10′ (INDEX) —- was missing in the explain plan… so  I concluded there is some problem with index on the table. I asked user to tune the query as this was custom query

    To know more above INDEX RANGE SCANfrom performance tuning Guide Click Here

    3 Responses to “Troubleshooting/Tuning Slow Form in Apps 11i”

    1. lakshmikanthan says:

      Thank you priyanka,

      This metalink id and yours notes steps very useful for me.
      Keep it up.

      Thank u
      lakshmi

    2. VIZITH says:

      Dear Priyanka,

      Nice article,I had peculiar issue over here,Hope you can help me finding a solution.
      We have a batch creation form in oracle apps 11.5.10,where recently we migrated oracle 10g r2 database.Now we are facing a problem with that form.That form is very slow for a particular USER named casting.And its working fine with other users.What could be the reaason.This is a long pending issue.Can you tell me any solution for this.

      thanks in advance,

      regards
      vizith

    3. rahil says:

      HI

      i have 20 forms & 30 reports developed in 6i developer version, i want to upgrade this forms & reports in to advance version (10g or 11g) in EBS.
      how can i do? & which version is best to upgrade?
      please give me brief knowledge or step by step methods.

      Thanks in Advance
      Rahil

    Leave a Reply



  • K21 Technologies is among the most experienced Oracle Gold Partner for Identity Access Management service providers. We work with application development companies and in-house technology division to help achieve significant returns on their IT security investment. Our clientele includes some of the globally renowned corporate, which speaks of our expertise in our field.

    We have the most talented and experienced team that can swiftly deploy security solutions even in complex IT ecosystem. Our clients highly appreciate our timely implementation, interactive training, on-demand support and community resources.
  • CONTACTS

    K21 Technologies
    8 Magnolia Place, Harrow,
    London, HA2 6DS

    UK: +44(0)7476444481
    USA: +1-888-414-1821

  • 2014, K21 Technologies. All rights reserved DMCA.com
  • TOP