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

Login Issue: FRM-92101:There was a failure in the forms server during startup.

[Document 758661.1]

After Upgrading the Forms Tier JDK to the Latest Certified Version of J2SE 5 or 6, Ebusiness Suite Forms Fail to Open With an FRM-92102 Error

[Document 973632.1]

FRM-40735 Error Received When Trying To Define A Responsibility

[Document 1269544.1]

R12: "FRM-92050: Failed to connect to Server:/forms/lservlet:-1" Error When Launching Forms After Adding New Language

[Document 1062246.1]

While Trying To Resolve FRM-92101 On E-Business Suite Release 12 By Recompiling Forms, It Fails With "ldflags: Too many levels of symbolic links"

[Document 465926.1]

R12: "FRM-92101: There Was a Failure in the Forms Server During Startup" Error After Fresh R12 Installation

[Document 429627.1]

"FRM-92050: Failed to connect to the server :/forms/lservlet -1" Error When Attempting to Launch Forms with Reverse Proxy

[Document 779065.1]

R12: "FRM-92050: Failed to connect to the Server: /forms/lservlet: -1" Error After Upgrading JDK

[Document 1175400.1]

R12: Forms Timeout More Than 2 Hrs Is Not Working After R12 Upgrade

[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.