Wednesday, August 29, 2012

Upgrade Advisor:

Upgrade Advisor: E-Business Suite (EBS) Upgrade from 11.5.10.2 to 12.1.3 [ID 269.1]

Master Class in Apps 11i Performance Tuning

PPT presentation for best performance tips:

  • Tuning the E-Business Suite (PDF, 2.0 MB)
    (Username: cboracle, Password: oraclec6 to download)
  • Performance: How to enable Database trace for the Apps session which is already started running

    You know how to enable Database trace for Forms & concurrent request, if you know in advance. But if you wish to enable Database trace for the Apps session which already started, then you can use oradebug .

    This is required if you wish to do DB tracing when a program suddenly hangs / slow   and you do not want to cancel and resubmit.


    Steps to enable DB trace dynamically

    1) Identify the sid using below sql.

    select sid,program,module from v$session
    where program like '%f60web%';

    2) Identify the SPID

    select spid from 
    v$process 
    where addr in (
         select paddr 
           from v$session 
          where sid in (&&<from step1>)
                            );

    3) Enable trace using ORADEBUG.

    sqlplus /nolog
    connect / as sysdba
    set echo on
    ORADEBUG SETOSPID <spidfrom step2>
    ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 8

    4) This enables trace for the running session.
      4.1)  Trace file will be found in the user_dump_dest. 
      4.2)  tkprof tracefile.trc output.txt explain=apps/<passwd> sort=prsela,exeela,fchel

    5) If you want to disable , use below command from the same session (step 3)
    ORADEBUG EVENT 10046 TRACE NAME CONTEXT off (Rem to turn off; to Switch off the Trace)


    Hope this helps.

    Gather Schema Statistics

    The Gather Schema Statistics concurrent will do the following:
    • Flush database monitoring info from SGA to dba_tab_modifications view
    • Gather statistics for tables that cumulative modifications compared to the dba_tables.num_rows are more than Modification Threshold percents.
    • Gather statistics for table that never analyzed (last_analyzed in NULL).
    • Gather statistics for tables which does not have monitoring enabled and afterwards enable monitoring for them so we have data in dba_tab_modifications next time we Gather Statistics.
    The Gather Schema Statistics concurrent, based on fnd_stats package, gather statistics for the required schema(s) with some additional parameters: 
    • Schema Name - the schema in which statistics should gathered. You can specify 'ALL' for gather statistics for all schemas registered in FND_PRODUCT_INSTALLATIONS table.
    • Estimate Percent - The sampling percents. Default value is 10.
    • Degree of parallelism - The degree of parallelism for gathering statistics. Default value is MIN (parallel_max_servers, cpu_count). (database init parameters)
    • Backup Flag - Indicate whether to backup last statistics (current situation). BACKUP/NOBACKUP - indicate to save/not save current statistics.
    • Restart Request Id - if Gather Schema Statistics concurrent failed, you can rerun it with this parameter set to the failed request_id. The concurrent will continue from where the failed request stopped.
    • History Mode - indicate how much history will save. History includes for each object when statistics gather for it, when it ends and more info'. Parameter options are: LAST_RUN - will save history only for the last time for each object. FULL - will save additional history each run. None - don't save history.
    • Gather Options - indicates for which objects statistics will gather. Parameter options are: GATHER (the classic & default) - gather stat' for all objects in the appropriate schema. GATHER_AUTO - This option considers how much DML operations executed on each object to decide whether gather statistics or not, I will explain later in details. GATHER_EMPTY - will gather stat only for tables/indexes with no statistics. LIST_AUTO - provide a list of objects for gather statistics if GATHER_AUTO is used. LIST_EMPTY - provide a list of objects for gather statistics if GATHER_EMPTY is used.
    • Modifications Threshold - relevant only when using GATHER_AUTO/LIST_AUTO in the previous parameter. This parameter specifies the percentage of modifications on objects that must execute before gathering statistics. I will explain later in details.
    • Invalidate Dependent - indicate whether to invalidate cursor for the analyzed objects. The default is 'Y'.
    The Gather Schema Statistics concurrent should schedule to run periodically (usually once a week/2 weeks).
    With gather options parameter set to GATHER and schema set to ALL, it will take a very long time to run since it gather statistics for all objects in database (for schemas registered in FND_PRODUCT_INSTALLATIONS table).


    Using "Monitoring" with Gather Schema Statistics

    To reduce Gather Schema Statistics concurrent run time, we can set the Gather Options parameter to "GATHER AUTO", it indicates to gather stats only for tables that have [Modifications Threshold] % changes since last analyze.

    First we should enable monitoring for the relevant schemas.
    We can do it by executing the following:
    exec fnd_stats.ENABLE_SCHEMA_MONITORING (SCHEMA_NAME);


    You can specify ‘ALL’ as parameter to enable monitoring for all tables in registered schemas.


    The following concurrent programs have been defined to gather and maintain various statistics using the FND_STATS package.

    Concurrent Programs
    • Analyze All Index Columns
    • Gather Table Statistics
    • Backup Table Statistics
    • Restore Table Statistics
    • Gather Schema Statistics
    Note:  When invoked using the seeded Concurrent Programs, only use the 'Gather Schema Statistics' or the 'Gather Table Statistics'.  

    Additional Information: The Optimizer, Oracle8i Concepts

    When using the 'Gathering Schema Statistics' concurrent program, it is recommended that you only pass the schema name parameter and let the other parameters default to their default values.  The schema name passed can be a specific schema name (e.g. GL) or 'ALL' if you prefer to gather CBO stats for Apps modules.  This choice uses the “estimate” method with a sample size of 10% (default).  

    When using the 'Gather Table Statistics' concurrent program, only pass the owner of the table (schema name) and the table name.  Let all other parameters default automatically, except when the table is a partitioned table.  

    To manually execute FND_STATS from SQL*Plus to gather CBO stats for one or all schemas, or for a particular table, use the following syntax:

      SQL> exec fnd_stats.gather_schema_statistics('GL');          <- One schema
      SQL> exec fnd_stats.gather_schema_statistics('ALL');         <- All schemas
      SQL> exec fnd_stats.gather_table_stats('GL','GL_JE_LINES');  <- One table

    The DBMS_STATS package contains all the procedures required to maintain the optimizer statistics of any schema.

    you may gather schema statistics once per week with a specific estimate_percent value and gather schema index statistics every night, for this purpose the following simple PL/SQL may be used . I recommend if you create jobs and schedule them as appropriate.

    -- Gathering Schema Statistics
    execute dbms_stats.gather_schema_stats('SCHEMA1', estimate_percent=>39, cascade=>TRUE);
    execute dbms_stats.gather_schema_stats('SCHEMA2', estimate_percent=>45, cascade=>TRUE);
    -- Gathering Index Statistics
    set serveroutput on
    begin
    for ind in
    (select object_name
    from user_objects
    where object_type='INDEX'
    and object_name like 'YOUR_INDEX_PATTERN_HERE'
    )
    loop
    dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||'.....');
    dbms_stats.gather_index_stats('SHEMA1', ind.object_name , estimate_percent=>100);
    dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||' is Complete!');
    end loop;
    end;
    /



    In fact, gather schema statistics become significantly more efficient, less run time, since we gather statistics for heavy using tables only.
    This enables us to run Gather statistics more frequently for less time.


    Important Documents:
    • What is the Gather Statistic Program or Gather Schema Statistics? (Doc ID 155766.1) 
    • How Often Should Gather Schema Statistics Program be Run? (Doc ID 168136.1) 
    • Gather Schema Statistics with LASTRUN Option does not Clean FND_STATS_HIST Table (Doc ID 745442.1) 
    • Does Gather Schema Statistics collect statistics for indexes? (Doc ID 170647.1) 
    • Definition of Parameters Used in Gather Schema Statistics Program (Doc ID 556466.1) 
    • Gathering Schema or Database Statistics Automatically in 8i and 9i - Examples (Doc ID 237901.1) 
    • Gather Schema Statistics Fails for Some Tables Percentages Not Displaying Properly (Doc ID 149603.1) 
    • Gather Schema Statistics (FNDGSCST) Hangs with DR$ Tables With Monitoring (Doc ID 435482.1) 
    • Gather Schema Statistics Hangs & Returns ORA-600: Internal Error, Arguments: [25012] (Doc ID 454205.1) 
    • Gather schema statistics occupies huge TEMP space without releasing it (Doc ID 731674.1) 
    • Gather Schema Statistics Completed Err ORA-20000 Index in unusable state (Doc ID 373488.1) 
    • How To Gather Table Statistics For Custom Schema Tables (Doc ID 389944.1) 
    • Gather Schema Statistics Program Fails When Scheduled (Doc ID 1050582.1) 
    • Running Gather Optimizer Statistics On Schema Generates Error (Doc ID 854527.1) 
    • Gather Schema Statistics Fails With Errors ORA-04063 and ORA-04068 (Doc ID 215897.1) 

    Some of the known issues on Forms in EBS

    [Document 758661.1]


    [Document 973632.1]


    [Document 1269544.1]


    [Document 1062246.1]


    [Document 465926.1]


    [Document 429627.1]


    [Document 779065.1]


    [Document 1175400.1]


    [Document 734077.1



    Note 1258305.1 Users Receive FRM-92101 oracle.forms.net.ConnectionException Errors When Launching E-Business Suite Forms Responsibility and Function
    Note 454427.1 R12: "FRM-92101:There was a failure in the Forms Server during startup" Error When Attempting to Launch Forms
    Note 438231.1 FRM-92050 Connecting and "Failed to exec runform f60webmx" Starting Forms Server
    Note 1103744.1 R12: "FRM-92101: There Was a Failure in the Forms Server During Startup" Error When Loading Forms
    Note 1056333.1 Forms Error With FRM-91126 invalid value timezlrg#dat
    Note 744093.1 FRM-92050 With "Connection reset by peer: JVM_recv in socket input stream read" Error When Launching Form On Microsoft Servers
    Note 1203773.1 Forms raises FRM-92100 Caused By Java.IllegalStateException:Cannot Open System Clipboard
    note 130686.1 How to Generate Form, Library and Menu for Oracle Applications
    Note 438652.1 R12: Forms Runtime Diagnostics (FRD), Tracing And Logging For Forms In Oracle Applications
    Note 840577.1 Getting FRM-41839 FRM-41092 FRM-40737 FRM-40734 When Using Mouse Scroll on Oracle Applications 12.0.4
    Note 550364.1 FRM-40735: Pre-Update Trigger Raised Unhandled Exception ORA-04062
    Note 754347.1 FRM-40833: Could not completely load the dynamic user exit libraries
    Note 399652.1 Getting FRM-41800 when navigating to any LOV and Focus Lost
    Note 1369830.1 R12 Forms Slow performance / Freezes / Hangs for large # Record details using View -> Last -> Record


    For troubleshooting you may review the following:

    Note 365529.1 Troubleshooting FRM-92XXX Errors in Oracle Applications
    Note 311019.1 Troubleshooting FRM-92100 Issues In Oracle Applications 11i
    Note 402674.1 Tips and Queries for Troubleshooting Forms Session Timeout Issues
    Note 164220.1 Troubleshooting "FRM-92160: Web client version too old" Error in R11i
    Note 810084.1 Troubleshooting"Forms CGI error: URL Contains One Or More Restricted Characters" In Apps 11i
    Note 136027.1 Troubleshooting Oracle Forms 6i Issues With Oracle Applications 11i
    Note 299187.1 11i: How to Troubleshoot Error "FRM-92050 Failed to connect to the server"
    Note 164220.1 Troubleshooting "FRM-92160: Web client version too old" Error in R11i

    Generate and Review Trace for a Concurrent Program

     1. Run the following query:  
    select max(log_sequence) from fnd_log_messages 
    Make a note this log_seq. 

    2. Set the following profile options at the user level: 
      FND: Debug Log Enabled = YES 
      FND: Debug Log Filename = NULL 
      FND: Debug Log Level = STATEMENT 
      FND: Debug Log Module = % 

    3. Enable trace for the concurrent program: In System Administrator responsibility Navigate to: Concurrent -> Program -> Define, query up the concurrent program you wish to trace. Make sure that the "enable trace" checkbox is UN-checked.

    4. Turn on tracing: Responsibility: System Administrator 
        Navigate: Profiles > System 
        Query Profile Option Concurrent: Allow Debugging 
        Set profile to Yes
    5. Submit the Concurrent Request, but before submitting, click Debugging Options, and select for SQL Trace: Trace With Binds And Waits. 
    Make a note of your request id.

    6. After the request finishes, please retrieve the trace file by running this sql query which will give the exact trace file name, and will prompt you for the request id:
        SELECT 'Request id: '||request_id ,  
    'Trace id: '||oracle_Process_id,  
    'Trace Flag: '||req.enable_trace,  
    'Trace Name:  
    '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',  
    'Prog. Name: '||prog.user_concurrent_program_name,  
    'File Name: '||execname.execution_file_name|| execname.subroutine_name ,  
    'Status : '||decode(phase_code,'R','Running')  
    ||'-'||decode(status_code,'R','Normal'),  
    'SID Serial: '||ses.sid||','|| ses.serial#,  
    'Module : '||ses.module  
    from fnd_concurrent_requests req, v$session ses, v$process proc,  
    v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,  
    fnd_executables execname  
    where req.request_id = &request  
    and req.oracle_process_id=proc.spid(+)  
    and proc.addr = ses.paddr(+)  
    and dest.name='user_dump_dest'  
    and dbnm.name='db_name'  
    and req.concurrent_program_id = prog.concurrent_program_id  
    and req.program_application_id = prog.application_id  
    and prog.application_id = execname.application_id  
    and prog.executable_id=execname.executable_id;  

    7. Run the following sql:   
     select * from fnd_log_messages 
     where log_sequence > &log_seq_noted_above 
     order by log_sequence   
     
    8.Upload the data from the above SQL in an xls file. 

    9. Convert the trace file into tkprof format, using the following command:
        tkprof <filename.trc> <output_filename_SORT.txt> explain=apps/<password>

    10. Review trace,log and tkprof files.

    Queries Help Analysing Concurrent Requests

     - Connect sqlplus as apps user and execute:

    NOTE : modify the select statement to change <request_id> for the request in position wherever needed.

    SELECT request_id, status_code, phase_code
    from fnd_concurrent_requests
    WHERE request_id = <request_id>;

    SELECT b.concurrent_queue_name, a.TYPE_APPLICATION_ID,a.TYPE_ID, a.INCLUDE_FLAG, a.TYPE_CODE
    FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b,
    fnd_concurrent_requests c
    WHERE a.queue_application_id = b.application_id
    and a.concurrent_queue_id = b.concurrent_queue_id
    and a.TYPE_ID = c.concurrent_program_id
    and a.TYPE_APPLICATION_ID = c.program_application_id
    and c.request_id = <request_id>;

    select distinct type_code
    from FND_CONCURRENT_QUEUE_CONTENT;

    SELECT b.concurrent_queue_name, a.TYPE_APPLICATION_ID,a.TYPE_ID, a.INCLUDE_FLAG,a.TYPE_CODE
    FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b
    WHERE a.queue_application_id = b.application_id
    and a.concurrent_queue_id = b.concurrent_queue_id
    order by b.concurrent_queue_name;

    SELECT creq_run.request_id, fcq.concurrent_queue_name,
    creq_run.program_application_id, creq_run.concurrent_program_id, creq_run.REQUESTED_BY
    FROM    fnd_concurrent_processes fcp,
              fnd_concurrent_queues fcq,
              fnd_concurrent_requests creq_run
    WHERE  fcp.concurrent_process_id = creq_run.controlling_manager
    and fcp.concurrent_queue_id = fcq.concurrent_queue_id
    AND fcp.queue_application_id = fcq.application_id
    AND creq_run.request_id = <request_id>;

    select
    d.CONCURRENT_TIME_PERIOD_ID, d.SLEEP_SECONDS, d.MIN_PROCESSES, c.CONCURRENT_QUEUE_NAME
    from fnd_concurrent_queue_size d, fnd_concurrent_queues c
    where d.CONCURRENT_QUEUE_ID = c.CONCURRENT_QUEUE_ID
    order by c.CONCURRENT_QUEUE_NAME;

    SELECT cprog.TO_RUN_APPLICATION_ID, cprog.TO_RUN_CONCURRENT_PROGRAM_ID
    FROM  fnd_concurrent_requests creq_run,
    fnd_concurrent_program_serial cprog
    WHERE creq_run.program_application_id = cprog.RUNNING_APPLICATION_ID
    AND creq_run.concurrent_program_id = cprog.RUNNING_CONCURRENT_PROGRAM_ID
    AND creq_run.request_id = <request_id>;

    SELECT cprog.RUNNING_APPLICATION_ID, cprog.RUNNING_CONCURRENT_PROGRAM_ID
    FROM  fnd_concurrent_requests creq_run,
    fnd_concurrent_program_serial cprog
    WHERE creq_run.program_application_id = cprog.TO_RUN_APPLICATION_ID
    AND creq_run.concurrent_program_id = cprog.TO_RUN_CONCURRENT_PROGRAM_ID
    AND creq_run.request_id = <request_id>;

    SELECT creq_run.request_id,
          cprog.concurrent_program_name,
          cprog.run_alone_flag,
          creq_run.number_of_arguments,
          creq_run.argument_text,
          creq_run.REQUESTED_BY,
          creq_run.cd_id,
          fcq.concurrent_queue_name,
          to_char(creq_run.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
          to_char(creq_run.actual_completion_date, 'DD-MON-RR HH24:MI:SS'),
          to_char(creq_base.request_id),
          to_char(creq_base.requested_start_date, 'DD-MON-RR HH24:MI:SS'),
          to_char(creq_base.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
          to_char(creq_base.actual_completion_date, 'DD-MON-RR HH24:MI:SS'),
          creq_base.single_thread_flag,
          creq_base.request_limit,
          creq_base.REQUESTED_BY,
          creq_run.cd_id
    FROM  fnd_concurrent_requests creq_run,
           fnd_concurrent_programs cprog,
           fnd_concurrent_requests creq_base,
           fnd_concurrent_processes fcp,
           fnd_concurrent_queues fcq
    WHERE (    (creq_run.program_application_id = cprog.application_id)
    AND (creq_run.concurrent_program_id = cprog.concurrent_program_id)
    AND (nvl(creq_base.actual_start_date,sysdate) >=
    creq_run.actual_start_date)  
    AND (creq_base.requested_start_date <=
    nvl(creq_run.actual_completion_date,sysdate))
    AND (creq_base.request_id = <request_id>))
    and fcp.concurrent_process_id = creq_run.controlling_manager
    and fcp.concurrent_queue_id = fcq.concurrent_queue_id
    AND fcp.queue_application_id = fcq.application_id
    order by creq_run.actual_start_date;

    SELECT creq_run.request_id,
          creq_run.number_of_arguments,
          creq_run.argument_text,
          to_char(creq_run.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
          to_char(creq_run.actual_completion_date, 'DD-MON-RR HH24:MI:SS'),
          to_char(creq_base.requested_start_date, 'DD-MON-RR HH24:MI:SS'),
          to_char(creq_base.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
          to_char(creq_base.actual_completion_date, 'DD-MON-RR HH24:MI:SS')
    FROM  fnd_concurrent_requests creq_run,
           fnd_concurrent_program_serial cprog,
           fnd_concurrent_requests creq_base
    WHERE (    (creq_run.program_application_id = cprog.RUNNING_APPLICATION_ID)
    AND (creq_run.concurrent_program_id = cprog.RUNNING_CONCURRENT_PROGRAM_ID)
    AND (creq_base.program_application_id = cprog.TO_RUN_APPLICATION_ID)
    AND (creq_base.concurrent_program_id = cprog.TO_RUN_CONCURRENT_PROGRAM_ID)
    AND (nvl(creq_base.actual_start_date,sysdate) >=
    creq_run.actual_start_date)  
    AND (creq_base.requested_start_date <=
    nvl(creq_run.actual_completion_date,sysdate))
    AND (creq_base.request_id = <request_id>));

    select fo.profile_option_name, level_id, level_value,nvl(to_char(fpv.profile_option_value), 'NULL')
    from fnd_profile_option_values fpv, fnd_profile_options fo
    where fo.PROFILE_OPTION_NAME Like 'CONC%'
    and fpv.profile_option_id(+)=fo.profile_option_id;