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