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;

    12.1 Feature in Concurrent Processing

    How concurrent program against a specific RAC instance with PCP/RAC setup?

    You can specify 'Target Instance' . In 12.1.3, you can optionally specify a Real Application Cluster (RAC) instance on which the program will run. When requests for this program are submitted, they run on this instance if possible.

    Navigation
    System Administrator Responsibility > Concurrent > Program > Define > Click 'Session Control' 

    Reference

    Note 1129203.1 How to run a concurrent program against a specific RAC instance with PCP/RAC setup? 


    Useful Queries related to Concurrent Requests

    As part of day to day work, we need to use lot of queries to check the information about
    concurrent requests. Here I am providing few queries which I frequently use for
    day to day works and troubleshooting concurrent request / manager issues.


    Note: The below queries needs to be run from APPS schema.

    1. Scheduled concurrent requests:

    Lot of times we need to find out the concurrent programs scheduled. USers can schedule
    the concurrent requests in three ways (To run once at a specified time / To run periodically /
    To run on specific days of the month or week).

    The below query will return all the concurrent requests which are scheduled using
    any of the above methods:

    SELECT cr.request_id,
           DECODE (cp.user_concurrent_program_name,
                   'Report Set', 'Report Set:' || cr.description,
                   cp.user_concurrent_program_name
                  ) NAME,
           argument_text, cr.resubmit_interval,
           NVL2 (cr.resubmit_interval,
                 'PERIODICALLY',
                 NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
                ) schedule_type,
           DECODE (NVL2 (cr.resubmit_interval,
                         'PERIODICALLY',
                         NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
                        ),
                   'PERIODICALLY', 'EVERY '
                    || cr.resubmit_interval
                    || ' '
                    || cr.resubmit_interval_unit_code
                    || ' FROM '
                    || cr.resubmit_interval_type_code
                    || ' OF PREV RUN',
                   'ONCE', 'AT :'
                    || TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
                   'EVERY: ' || fcr.class_info
                  ) schedule,
           fu.user_name, requested_start_date
      FROM apps.fnd_concurrent_programs_tl cp,
           apps.fnd_concurrent_requests cr,
           apps.fnd_user fu,
           apps.fnd_conc_release_classes fcr
     WHERE cp.application_id = cr.program_application_id
       AND cp.concurrent_program_id = cr.concurrent_program_id
       AND cr.requested_by = fu.user_id
       AND cr.phase_code = 'P'
       AND cr.requested_start_date > SYSDATE
       AND cp.LANGUAGE = 'US'
       AND fcr.release_class_id(+) = cr.release_class_id
       AND fcr.application_id(+) = cr.release_class_app_id;
      

    PS: The "SCHEDULE" column in the above query returns a string of zeros and ones for
    the requests which are scheduled on specific days of the month or week.

    Positions 1 through 31:  Specific day of the month.
    Position  32:    Last day of the month
    Positions 33 through 39:  Sunday through Saturday


    2.  Checking the duplicated schedules of the same program with the same arguments:

    The below query can be used to check the duplicated schedule of the same program
    with the same arguments. This can be used to alert the users to cancel these duplicated
    schedules.

    PS: This query will return even though the request was submitted using a different responsibility.
    SELECT   request_id, NAME, argument_text, user_name
        FROM (SELECT cr.request_id,
                     DECODE (cp.user_concurrent_program_name,
          ��                  'Report Set', 'Report Set:' || cr.description,
                             cp.user_concurrent_program_name
                            ) NAME,
                     argument_text, fu.user_name
                FROM apps.fnd_concurrent_programs_tl cp,
                     apps.fnd_concurrent_requests cr,
                     apps.fnd_user fu
               WHERE cp.application_id = cr.program_application_id
                 AND cp.concurrent_program_id = cr.concurrent_program_id
                 AND cr.requested_by = fu.user_id
                 AND cr.phase_code = 'P'
                 AND cr.requested_start_date > SYSDATE
                 AND cp.LANGUAGE = 'US'
                 AND fu.user_name NOT LIKE 'PPG%') t1
       WHERE EXISTS (
                SELECT   1
                    FROM (SELECT cr.request_id,
                                 DECODE (cp.user_concurrent_program_name,
                                         'Report Set', 'Report Set:'
                                          || cr.description,
                                         cp.user_concurrent_program_name
                                        ) NAME,
                                 argument_text, fu.user_name
                            FROM apps.fnd_concurrent_programs_tl cp,
                                 apps.fnd_concurrent_requests cr,
                                 apps.fnd_user fu
                           WHERE cp.application_id = cr.program_application_id
                             AND cp.concurrent_program_id =
                                                          cr.concurrent_program_id
                             AND cr.requested_by = fu.user_id
                             AND cr.phase_code = 'P'
                             AND cr.requested_start_date > SYSDATE
                             AND cp.LANGUAGE = 'US'
                             AND fu.user_name NOT LIKE 'PPG%') t2
                   WHERE t1.NAME = t2.NAME
                     AND t1.argument_text = t2.argument_text
                     AND t1.user_name = t2.user_name
                GROUP BY NAME, argument_text, user_name
                  HAVING COUNT (*) > 1)
    ORDER BY user_name, NAME


    3. Average pending time per request:

    This is a very useful query to check the performance of the concurrent managers.
    Average pending time for a request is calculated like below:
    ("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) / Total requests

    A Request can be in Pending state for variety of reasons like conflict with other requests,
    improperly tuned managers (sleep seconds / cache size / number of managers etc)

    We can schedule this script to gather data regularly for historical analysis as we normally
    purge the concurrent requests regularly.


    SELECT   TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY,
             concurrent_queue_name,
               (SUM (  (  actual_start_date
                        - (CASE
                              WHEN requested_start_date > request_date
                                 THEN requested_start_date
                              ELSE request_date
                           END
                          )
                       )
                     * 24
                     * 60
                     * 60
                    )
               )
             / COUNT (*) "Wait_Time_per_Req_in_Secs"
        FROM apps.fnd_concurrent_requests cr,
             apps.fnd_concurrent_processes fcp,
             apps.fnd_concurrent_queues fcq
       WHERE cr.phase_code = 'C'
         AND cr.actual_start_date IS NOT NULL
         AND cr.requested_start_date IS NOT NULL
         AND cr.controlling_manager = fcp.concurrent_process_id
         AND fcp.queue_application_id = fcq.application_id
         AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
    GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name
    ORDER BY 2

    PS: Depending on the purging schedules some requests might miss if the corresponding
    data in fnd_concurrent_processes is purged.


    4. Checking which manager is going to execute a program:

    The below query identifies the manager which will be executing a given program.
    This query is based on the specialization rules set for the managers.


    SELECT user_concurrent_program_name, user_concurrent_queue_name
      FROM apps.fnd_concurrent_programs_tl cp,
           apps.fnd_concurrent_queue_content cqc,
           apps.fnd_concurrent_queues_tl cq
     WHERE cqc.type_application_id(+) = cp.application_id
       AND cqc.type_id(+) = cp.concurrent_program_id
       AND cqc.type_code(+) = 'P'
       AND cqc.include_flag(+) = 'I'
       AND cp.LANGUAGE = 'US'
       AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME'
       AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
       AND NVL (cqc.queue_application_id, 0) = cq.application_id
       AND cq.LANGUAGE = 'US'
      
    5. To see all the pending / Running requests per each manager wise:


    SELECT   request_id, phase_code, status_code, user_name,
             user_concurrent_queue_name
        FROM apps.fnd_concurrent_worker_requests cwr,
             apps.fnd_concurrent_queues_tl cq,
             apps.fnd_user fu
       WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')
         AND cwr.hold_flag != 'Y'
         AND cwr.requested_start_date <= SYSDATE
         AND cwr.concurrent_queue_id = cq.concurrent_queue_id
         AND cwr.queue_application_id = cq.application_id
         AND cq.LANGUAGE = 'US'
         AND cwr.requested_by = fu.user_id
    ORDER BY 5

    PS: The same information can be seen in Administer Concurrent Manager form for each manager.

    6. Checking the incompatibilities between the programs:

    The below query can be used to find all incompatibilities in an application instance.
    SELECT a2.application_name, a1.user_concurrent_program_name,
                    DECODE (running_type,
                            'P', 'Program',
                            'S', 'Request set',
                            'UNKNOWN'
                           ) "Type",
                    b2.application_name "Incompatible App",
                    b1.user_concurrent_program_name "Incompatible_Prog",
                    DECODE (to_run_type,
                            'P', 'Program',
                            'S', 'Request set',
                            'UNKNOWN'
                           ) incompatible_type
               FROM apps.fnd_concurrent_program_serial cps,
                    apps.fnd_concurrent_programs_tl a1,
                    apps.fnd_concurrent_programs_tl b1,
                    apps.fnd_application_tl a2,
                    apps.fnd_application_tl b2
              WHERE a1.application_id = cps.running_application_id
                AND a1.concurrent_program_id = cps.running_concurrent_program_id
                AND a2.application_id = cps.running_application_id
                AND b1.application_id = cps.to_run_application_id
                AND b1.concurrent_program_id = cps.to_run_concurrent_program_id
                AND b2.application_id = cps.to_run_application_id
                AND a1.language = 'US'
                AND a2.language = 'US'
                AND b1.language = 'US'
                AND b2.language = 'US'


    The table apps.fnd_concurrent_program_serial has the information about incompatibilities.

    cmclean.sql internal job

    Update process status codes to TERMINATED
    Updating invalid process status codes in FND_CONCURRENT_PROCESSES
    Set all managers to 0 processes
    prompt -- Updating running processes in FND_CONCURRENT_QUEUES
    prompt -- Setting running_processes = 0 and max_processes = 0 for all managers

    Reset control codes
    Updating invalid control_codes in FND_CONCURRENT_QUEUES
    Also null out target_node for all managers
    REM Set all 'Terminating' requests to Completed/Error
    REM Also set Running requests to completed, since the managers are down

    Updating any Running or Terminating requests to Completed/Error
    Set all Runalone flags to 'N'

    INDEX REBUILD

    ORACLE
    When should you rebuild index and how?
    Oracle's index rebuilding guidelines appear in recommends that indexes be periodically examined to see if they are candidates for an index rebuild:
    “When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance.  It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.”
    Oracle index nodes are not physically deleted when table rows are deleted, nor are the entries removed from the index. Rather, Oracle "logically" deletes the index entry and leaves "dead" nodes in the index tree where that may be re-used if another adjacent entry is required.  However, when large numbers of adjacent rows are deleted, it is highly unlikely that Oracle will have an opportunity to re-use the deleted leaf rows, and these represent wasted space in the index. In addition to wasting space, large volumes of deleted leaf nodes will make index fast-full scans run for longer periods.  These deleted leaf nodes can be easily identified by running the IDL.SQL script.

    The number of deleted leaf rows

    The term "deleted leaf node" refers to the number of index inodes that have been logically deleted as a result of row deletes. Remember that Oracle leaves "dead" index nodes in the index when rows are deleted. This is done to speed up SQL deletes, since Oracle does not have to allocate resources to rebalance the index tree when rows are deleted.

    Index height

    The height of the index refers to the number of levels that are spawned by the index as a result in row inserts. When a large amount of rows are added to a table, Oracle may spawn additional levels of an index to accommodate the new rows.

    Oracle indexes can support many millions of entries in three levels.  Any Oracle index that has spawned to a 4th level followed by a large delete job might benefit from rebuilding to restore the index to its pristine state.

    Gets per index access

    The number of "gets" per access refers to the amount of logical I/O that is required to fetch a row with the index. As you may know, a logical "get" is not necessarily a physical I/O since much of the index may reside in the Oracle buffer cache.

    Unfortunately, Oracle does not make it easy to capture this information. In Oracle we must issue these commands to populate the statistics in dba_indexes and related dictionary tables:

    ANALYZE INDEX index_name COMPUTE STATISTICS
    ANALYZE INDEX index_name VALIDATE STRUCTURE

    We might want to rebuild an index if the “block gets” per access is excessive.  This happens when an index becomes "sparse" after high delete activity, making full-index scans requires unnecessary I/O. Another rebuild condition would be cases where deleted leaf nodes comprise more than 20% of the index nodes.

    How to rebuild index?
    As you may know, you can easily rebuild an Oracle index with the command:

    ALTER INDEX index_name REBUILD tablespace FLOP;

    Done properly during scheduled downtime, rebuilding an index is 100% safe.  Note the use of the tablespace option.  When rebuilding multi-gigabyte indexes, many DBA's will rebuild partitioned indexes into a fresh, empty tablespace for greater manageability.

    The ALTER INDEX index_name REBUILD command is very safe way to rebuild indexes. Here is the syntax of the command:

    alter index index_name
    rebuild
    tablespace tablespace_name
    storage (initial new_initial next new_next freelists new_freelist_number )

    Unlike the traditional method where we drop the index and recreate it, the REBUILD command does not require a full table scan of the table, and the subsequent sorting of the keys and rowids. Rather, the REBUILD command will perform the following steps:
    1.         Walk the existing index to get the index keys.
    2.         Populate temporary segments with the new tree structure.
    3.         Once the operation has completed successfully, drop the old tree, and rename the temporary segments to the new index.
    As you can see from the steps, you can rebuild indexes without worrying that you will accidentally lose the index. If the index cannot be rebuilt for any reason, Oracle will abort the operation and leave the existing index intact. Only after the entire index has been rebuilt does Oracle transfer the index to the new b-tree.

    Note that the TABLESPACE clause should always be used with the ALTER INDEX REBUILD command to ensure that the index is not rebuilt within the default tablespace (usually SYS). It's always a good idea to move an index into another tablespace and you must have enough room in that tablespace to hold all of the temporary segments required for the index rebuild, so most Oracle administrators will double-size index tablespaces with enough space for two full index trees.


    LOCAL LISENER vs REMOTE LISTENER

    you're right, LOCAL_LISTENER on each node should point to the listener on that node.
    REMOTE_LISTENER should point to all listeners on all nodes if you want server side load balancing,
    otherwise don't set REMOTE_LISTENER.
    Use listener_<instance_name> as the alias name for the local listener, and listeners_<servicename> for the remote listener alias.

         Quick explanation :
         The SERVICE_NAME=VIS is standing for the 4 RAC instances : VIS1, VIS2, VIS3 and VIS4.
         The Apps user will connect to the 11i/RAC via the SERVICE_NAME=VIS, and the Rac engine will
          randomly switch the users to "one of" the RAC instance (VIS1, VIS2,VIS3 or VIS4)  where has the
          lower load compare to the 3 other instances, this mechanism is calling the RAC Load balancing.

    the remote_listener parameter points to your SCAN listener(s) which will do the load balancing, and local_listener points to the node listener which will do the spawn-and-bequeath.
    When a SCAN Listener receives a connection request, the SCAN Listener will check for the least loaded instance
    providing the requested service. It will then re-direct the connection request to the local listener on the node where the
    least loaded instance is running. Subsequently, the client will be given the address of the local listener. The local listener
    will finally create the connection to the database instance.
      
    http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf
    How To Find Out The Example of The LOCAL_LISTENER and REMOTE_LISTENER Defined In The init.ora When configuring the 11i or R12 on RAC ? [ID 744508.1]
    http://www.comp.dit.ie/btierney/oracle11gdoc/install.111/b28264/undrstnd.htm

    Solving Concurrent Program “Pending Standby” status

    1. Concurrent request status is PENDING STANDBY status?
    Pending Standby  - Phase Pending and Status Standby means Program to run request is incompatible with other program(s) currently running.
     I have submitted one concurrent request at 19:00 Hrs (Day1) and this request is to be run by a concurrent manager which has work shift of 00:00 Hrs to 08:00 Hrs (Day 2). The moment when I submit this concurrent request it’s status will be “Inactive No Manager” until this manager goes active. Suppose I have many requests pending with this concurrent and this request couldn’t start.
    Now what will be status of this request after 08:00 Hrs (Day 2) when it’s manager goes down? Here in my case it is still “Inactive No Manager” though I think it should be “Pending Standby”
    This could be for different reasons (other pending requests, no place for new requests, ...etc).
    Solving Concurrent Program “Pending Standby” status (Oracle EBS 11i)
    There are instances when we run the Concurrent Program, it goes to “Pending Standby” status. Sometimes we can see this status get changed after few minutes but,
    there are instances it will be in the same status forever. This means the request is not progressing and will not be completed ever.
    To make that request to progress again, the following steps need to be carried out; Go to System Administrator Responsibility;
    ——>>System Administrator
    ————>Concurrent manager
    ——————–>Conflict Resolution Manager
    See whether the Conflict Resolution manager is running, if is it not, activate it and it will solve the standby status issue.
    If the Conflict Resolution manager is running
    ——->Click “Verify” button
    Once completed with the verification, resubmit the previous hanging request, and it should run without any problem.