How to Analyze Performance Problems :

How to generate a trace file:
Depending on the nature of the performance problem, there are different methods to capture the raw trace file.
How to generate a trace file (forms) :
If the performance problem occurs while using Oracle forms, a trace file can be obtained using the following steps :
a. Navigate to the point in the application right before you are experiencing the problem.
b. Turn trace on by:  Help > Diagnostics > Trace > Trace with Binds and Waits
c. Duplicate the error and then stop immediately afterwards.
d. Retrieve trace file from the user_dump_dest, which can be located as follows: select value from V$PARAMETER where name like 'user%';

How to generate a trace file (HTML) :
If the performance problem occurs while using web pages, a trace file can be obtained using the following steps :
1. In the System Administrator responsibility set the system profile FND: Diagnostics to YES at user level. (Note: Enabling this profile will consume greater system resources and may impend database and applications performance. Thus tracing an event during hours of low system usage may be a consideration).

2. Login to self service using the user id for which the above profile is enabled and select the responsibility applicable to the issue being traced.

3. In a self service application page click on the global button Diagnostic.

5. Choose the option Set trace level and set it to Trace with Binds and Waits.

6. Navigate Back to Home and go to the self service page for which the issue is reproducible.

7. Reproduce the issue while ensuring that the performance problem is captured in the trace.

8. Click on the Diagnostic link and select the Set trace level option. Numbers will appear on the left side of the screen. Note down these numbers as these are the identifiers of the trace files on the server. Disable the tracing.

9. Using these identifiers the DBA/System Administrator can get all the trace files from the server directory location where the database trace are saved.

How to generate a trace file (Concurrent Request) before 11.5.10 :

If the performance problem occurs while running a concurrent program (including reports), a trace file can be obtained using one of the following methods :
Prior to 11.5.10, the following steps can be used to generate a trace file :
1. Navigate Responsibility: System Administrator > Profile > System >Query User: User submitting the Report
Profile: Initialization SQL Statement - Custom

2. Click on User column - Edit Field and enter

begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET EVENTS='||''''||'10046 TRACE NAME CONTEXT FOREVER,LEVEL 12'||''''); end;

3. Set Profile Option: SLA: Enable SQL Trace - Yes

4. Reproduce the issue by submitting the concurrent program

5. Use the following SQL to locate the trace file:

select value from v$parameter where name = 'user_dump_dest';


How to generate a trace file (Concurrent Request) on 11.5.10 and above :
From 11.5.10 onwards, the above method can still be used to create a trace file, however, there is an easier method to generate the trace file for concurrent programs. The following steps can be used :
1 - Navigate to System Administrator responsibility
2 - Navigate to Profiles->System
3 - Query the Profile Option Concurrent: Allow Debugging and set it to Yes at User level
4 - Navigate to Purchasing responsibility
5 - From the Requests form, choose the Concurrent Program and set the required Parameters
6 - Click the Debug button
7 - Check the SQL Trace checkbox and specify Trace with Binds and Waits
8 - Submit the Concurrent program
9 - Retrieve the trace file created.

How to create a tkprof file :
TKPROF reformats the raw data so that it is easier to review and also shows the time taken by each SQL statement and can be used to determine the expensive SQL statements. Once the trace file has been gathered, a tkprof file can be created from the raw trace using the steps below :

a. Retrieve the trace file.
b. Issue a command like the following to create a TKPROF version of the trace file. This command sorts the results with the longest running queries first:
tkprof <filename.trc> <output_filename> sys=no explain=apps/<password>
sort='(prsela,exeela,fchela)'

c. Additionally, the following command can be used which will create a tkprof that limits the results to the top ten queries:
tkprof <filename.trc> <output_filename> sys=no explain=apps/<password>
sort='(prsela,exeela,fchela)' print=10

Gathering Statistics Concurrent Requests

The following recommended concurrent requests are available in Oracle Applications for gathering statistics:

Analyze All Index Columns
Backup Table Statistics
Gather Schema Statistics
Gather Table Statistics
Restore Table Statistics

Please refer to the Oracle Application System Administration Guide for concurrent request parameters. Here are common parameters and their meanings:

• Schemaname Enter the three character schema to analyze. An example for entry would be MRP or INV or ONT or ALL. You may enter ALL to analyze every defined App schema.
• Estimate_percentPercentage of rows to estimate. If left empty it will default to 10%. The valid range is 0-99. A higher percentage will be more accurate, but take longer to run. If the object(s) that you are gathering statistics for do not change often or the object(s) has data entered that is very similiar you may choose a lower number. However, if the data changes frequently a larger number entered for this parameter would be recommended to provide a more accurate representation of your data.
• DegreeEnter the Degree of parallelism. If not entered, it will default to min(cpu_count, parallel_max_servers). Modifying the degree of parallelism on a table can cause the plan to change. Increasing the degree of parallelism is likely to make full table scans appear cheaper and more attractive while reducing it will make Full Table Scans look less attractive.
• Internal FlagIf the value is 'NOBACKUP' then it won't take a backup of the current statistics and should run quicker. If the value is
  'BACKUP' then it does an export_table_stats prior to gathering the statistics.
• Restart Request IdEnter the request id that should be used for recovering gather_schema_stats if this request should fail. You may leave
  this parameter null.

Summary of FND_STATS Subprograms

CREATE_STAT_TABLE
This creates the table where the statistics can be backed up. This table is created in the AOL (APPLSYS)schema and is called FND_STATTAB.

BACKUP_TABLE_STATS
Backups the statistics of the given table into FND_STATTAB. Statistics can be backed up with different statid . The default statid is 'BACKUP'. It is possible to keep different versions of the backup as different statid's. This also backs up the related index stats by default.

Concurrent Request equivalent is "Backup Table Statistics".

BACKUP_SCHEMA_STATS
Backs up the statistics of all the objects of the given schema into FND_STATTAB. Statistics can be backed up with different statid. The default statid is NULL. It is possible to keep different versions of the backup as different statids.

RESTORE_SCHEMA_STATS
Restore the previously backed up schema statistics.

RESTORE_TABLE_STATS
Restores the previously backed up table statistics from a given statid.
The default statid is 'BACKUP'.

Concurrent request equivalent is "Restore Table Statistics".

RESTORE_TABLE_STATS
This procedure retrieves statistics for a particular table from the FND_STATTAB for the given statid(optional)and stores them in the dictionary. Cascade results in all index and column stats associated with the specified table being imported as well.

GATHER_SCHEMA_STATS
This procedure gathers statistics for all objects in a schema. If schema name is specified as 'ALL' then all the Apps specific schema (having an entry in FND_PRODUCT_INSTALLATIONS table) statistics are gathered. Before gathering the
statistics, this also takes a backup of the existing statistics so that in case the database slows down after gathering statistics, things can be restored to its previous status. The statid used for this backup is 'NULL'. Also after gathering the schema level statistics this procedure creates the histogram for the specified columns in the FND_HISTOGRAM_COLS tables. And lastly it populates a default statistics for all the INTERFACE tables as specified in the FND_EXLCUDE_TABLE_STATS table.

If the procedure fails at any time during operation, it can be restarted by supplying the request id of the run that failed. The request id can be captured when the procedure is started from concurrent request manager.

Concurrent request equivalent is "Gather Schema Statistics".

GATHER_SCHEMA_STATISTICS
This is a simpler call for using GATHER_SCHEMA_STATS from the SQL prompt. This should be used for gathering schema statistics for a single schema only. Schema name should rarely be 'ALL'. Downside to using 'ALL' is that you will
not know if there was indeed any error. If it fails at any time during operation, it can be restarted by supplying the request id of the run that failed. The request id. To identify the request id for the last run select the maximum value for request_id from table FND_STATS_HIST.

This internally calls GATHER_SCHEMA_STATS. The only difference here is that this procedure doesn't have a output parameter and hence is easier to call from the SQL prompt.

GATHER_INDEX_STATS
This procedure gathers index statistics. It is equivalent to running ANALYZE INDEX[ownname.]indname [PARTITION partname] COMPUTE STATISTICS | ESTIMATE STATISTICS SAMPLE estimate_percent PERCENT.

It does not execute in parallel.

If the value of backup_flag is 'BACKUP' then it does an export_table_stats before gathering the stats. The exported data is stored in FND_STATTAB . If the value of backup_flag is anything other than 'BACKUP' export_table_stats
is not performed.


GATHER_TABLE_STATS
This procedure gathers table and column (and index) statistics. It attempts to parallelize as much of the work as possible. This operation does not parallelize if the user does not have select privilege on the table being analyzed. If the value of backup_flag is 'BACKUP' then it does an export_table_stats before gathering the stats. The exported data is stored in FND_STATTAB. If the value of backup_flag is anything other than 'BACKUP' export_table_stats is not performed.


ANALYZE_ALL_COLUMNS
This procedure analyzes all the indexed columns for all the tables in a given schema.

Concurrent request equivalent is "Analyze All Index Columns".

LOAD_XCLUD_STATS
This procedure loads the default stats as specified in the SEED data table FND_EXCLUDE_TABLE_STATS. There are two versions of this procedure. One loads for all the tables for a particular schema and the other one loads for a given table in a given schema.

LOAD_XCLUD_TAB
This procedure is for Oracle internal purpose only and should not be used by the customers. This is used for SEED data manipulation for interface tables.

LOAD_HISTOGRAM_COLS
This procedure is for Oracle internal purpose only and should not be used by the customers. This is used for SEED data manipulation for histogram columns.

CHECK_HISTOGRAM_COLS
This gives a report on which indexed columns for a list of given tables is a good candidate for histograms. For a given list of comma separated tables, this procedure checks the data in all the leading columns of all the non-unique indexes of those tables and figures out if histograms needs to be created for those columns. A count(*) of at least 3000 rows in the table(s) is
recommended. The procedure needs to be run from the SQL prompt after setting the serveroutput on.

VERIFY_STATS
This gives a report on the stats of the given list of objects.
For a given list of comma separated tables, or for the given schema name, this procedure reports the stats in the data-dictionary tables for the tables, indexes and the histograms. Run FND_STATS.VERIFY_STATS at any time to check
when statistics were last gathered. 

Apps DBA useful scripts :

Apps DBA Scripts

To know instance full detalis

 SQL> col instance_name format a13

       col host_name format a15

       col version format a9

       col release format a9

      select i.instance_name, i.host_name, f.release_name release, i.version

         from v$instance i, apps.fnd_product_groups f

          where i.instance_name = f.applications_system_name;

 

To know entries in the database :

 

SQL> select node_name "Node Name", node_mode "Mode", support_cp "C",support_web "W", support_admin "A", support_forms "F" from FND_NODES;

 

1. Provide Concurrent Program Name, It will list out all concurrent requests sets names that has concurrent program in it?

SELECT DISTINCT user_request_set_name
  FROM FND_REQUEST_SETS_TL
 WHERE request_set_id IN
          (SELECT request_set_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE concurrent_program_id =
                     (SELECT CONCURRENT_PROGRAM_ID
                        FROM fnd_concurrent_programs_tl
                       WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));

2. Provide Concurrent Request Set Name, It will list out all the concurrent programs It has?

SELECT USER_CONCURRENT_PROGRAM_NAME
  FROM fnd_concurrent_programs_tl
 WHERE CONCURRENT_PROGRAM_ID IN
          (SELECT concurrent_program_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE request_set_id =
                     (SELECT request_set_id
                        FROM FND_REQUEST_SETS_TL
                       WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));

3. Find out Inactive Form sessions for X hours?

set lines 200 pages 200
col event for a30

select a.sid,a.serial#,a.action,a.status,a.event,round(a.last_call_Et/60/60) LSTCALL_Hrs, 
round(a.seconds_in_wait/60/60) SCNDINWAIT_Hrs,
       a.process, b.spid
  from v$session a, v$process b
 where a.action like 'FRM%'
   and a.paddr = b.addr
   and a.last_call_Et/60/60 > &&No_Of_Hours_Old order by 6,5;

4. Find out Application Names (Products) in Oracle Apps?

set lines 200

set pagesize 300

col APPLICATION_NAME for a70

select a.APPLICATION_NAME, b.APPLICATION_SHORT_NAME from apps.fnd_application b, apps.fnd_application_tl a  where a.APPLICATION_ID=b.APPLICATION_ID; 

5. Find out What are all concurrent programs are assigned to What are all concurrent Managers?

set lines 200 pages 300

col USER_CONCURRENT_QUEUE_NAME for a50
col CONCURRENT_PROGRAM_NAME for a50

break on USER_CONCURRENT_QUEUE_NAME skip 1;
SELECT C.USER_CONCURRENT_QUEUE_NAME,B.CONCURRENT_PROGRAM_NAME,A.INCLUDE_FLAG
FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT A, APPLSYS.FND_CONCURRENT_PROGRAMS B, APPS.FND_CONCURRENT_QUEUES_VL C
WHERE type_id = b.concurrent_program_id and c.concurrent_queue_id = a.concurrent_queue_id order by C.USER_CONCURRENT_QUEUE_NAME;

6. Find out session details  of a concurrent Request?

set lines 200 pages 300
col USER_CONCURRENT_PROGRAM_NAME for a40

select USER_CONCURRENT_PROGRAM_NAME,c.request_id,s.sid,p.spid,s.process,s.osuser,s.username,s.program,s.status,logon_time,last_call_et
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id
and request_id=&creq_id
/

7. Find out All Concurrent Queue/Manager sizes/Processes?

set lines 200 pages 300
select a.concurrent_queue_name,b.min_processes,b.max_processes from apps.fnd_concurrent_queues a,apps.fnd_concurrent_queue_size b where a.concurrent_queue_id=b.concurrent_queue_id;

8. To know the SID ,Sireal# to find OS level :

SQL> SELECT ses.sid, 

            ses.serial# 

       FROM v$session ses, 

            v$process pro 

           WHERE ses.paddr = pro.addr 

                AND pro.spid IN (SELECT oracle_process_id 

                                           FROM fnd_concurrent_requests 

                                        WHERE request_id = &request_id);

To Know the SPID :

select

sid,

spid

from v$process p, v$session s

where paddr = addr

and s.sid in(&SID)

order by s.sid;

9. To know CONCURRENTMANAGER completed

SELECT fcr.REQUEST_ID,fc.USER_CONCURRENT_PROGRAM_NAME,TO_CHAR(fcr.actual_start_date,'DD-MON HH24:MI:SS') actual_start_date,TO_CHAR(fcr.actual_completion_date,'DD-MON HH24:MI:SS') actual_completion_date,fcr.STATUS_CODE

FROM FND_CONCURRENT_REQUESTS fcr,

                FND_CONCURRENT_PROGRAMS_TL fc

WHERE fcr.phase_code='C'

                AND fc.concurrent_program_id=fcr.concurrent_program_id

                AND fc.USER_CONCURRENT_PROGRAM_NAME='&CONC_req_name' order by fcr.actual_start_date ;

10. To find the start & end date of Conc – Req info :

SELECT qt.user_concurrent_queue_name

, fcr.Request_Id Request_id

, fu.User_name

, p.spid

, s.sid ||','|| s.serial# SIDSERIAL

, substr( Fcpv.Concurrent_Program_Name ||'-'|| Fcpv.User_Concurrent_Program_Name, 1,46) Program

, to_char( fcr.actual_start_date, 'mm/dd hh24:mi') actual_start_date

, phase_code, status_code

, to_char( trunc(sysdate) + ( sysdate - fcr.actual_start_date )

, 'hh24:mi:ss' ) duration

FROM apps.Fnd_Concurrent_Queues Fcq

, apps.fnd_concurrent_queues_tl qt

, apps.Fnd_Concurrent_Requests Fcr

, apps.Fnd_Concurrent_Programs Fcp

, apps.Fnd_User Fu

, apps.Fnd_Concurrent_Processes Fpro

, v$session s

, v$process p

, apps.Fnd_Concurrent_Programs_Vl Fcpv

WHERE phase_code = 'C'

AND status_Code = 'X'

AND s.paddr = p.addr

AND fcr.requested_by = user_id

AND fcq.application_id = qt.application_id

AND fcq.concurrent_queue_id = qt.concurrent_queue_id

AND userenv('lang') = qt.language

AND fcr.os_process_id = s.process

AND fcr.Controlling_Manager = Concurrent_Process_Id

AND (fcq.concurrent_queue_id = fpro.concurrent_queue_id

AND fcq.application_id = fpro.queue_application_id )

AND (fcr.concurrent_program_id = fcp.concurrent_program_id

AND fcr.program_application_id = fcp.application_id )

AND (fcr.concurrent_program_id = fcpv.concurrent_program_id

AND fcr.program_application_id = fcpv.application_id )

ORDER BY fcr.actual_start_date;

11. Req running Duration...

    *********************

   

    set linesize 152

              col USER_CONCURRENT_PROGRAM_NAME for a30

              col status format a5

              col phase  format a5

              col COMPL_DATE FOR A10

              col START_DATE FOR A10

              col user_name format a20

              col hrs_running format 999.999

               select

                 '*'||a.status_code ||'*' st,

                 '#'||a.phase_code||'#' ph,

                 a.USER_CONCURRENT_PROGRAM_NAME,

                 b.user_name,

                 to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,

                 to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE,

                 (sysdate-actual_start_date)*24 Hrs_Running

               from

                 apps.fnd_conc_req_summary_v a,

                 apps.fnd_user  b

               where

                 --phase_code='C'and

                 a.REQUESTED_BY=b.user_id and

                 a.REQUEST_ID =&REQID

             order by b.user_name asc,a.ACTUAL_START_DATE asc ;

12. Long Running Concurrent Manager Requests

select  'ID='|| to_char(r.request_id) job_id, r.status_code,decode( r.description,null, '* ' ||

p.user_concurrent_program_name,r.description ) description,

       f.user_name requested_by,

       to_char( trunc(sysdate) + ( sysdate - r.actual_start_date ), 'hh24:mi:ss' ) duration

       ,to_char( r.actual_start_date,'mm/dd hh24:mi:ss' ) actual_start_date,

        r.argument_text

  from apps.fnd_concurrent_programs_tl p,

       apps.fnd_oracle_userid u,

       apps.fnd_user f,

       apps.fnd_concurrent_requests r

 where r.oracle_id = u.oracle_id

   and r.requested_by = f.user_id

   and r.program_application_id = p.application_id

   and r.concurrent_program_id = p.concurrent_program_id

   and p.language = 'US'

   and r.phase_code = 'R' and r.status_code='R'

      and decode( r.description,null, p.user_concurrent_program_name,r.description ) not in ('Adapter Startup','Gather Schema

Statistics','Planning Manager','DRWIP Discrete Job Pick List Report')

      and (sysdate - r.actual_start_date) * 1440 > &how_long

   order by r.request_id

/

13. To find Conc - Reqs particular date Information on particular date :

set verify off

set feedback off

set pagesize 59

set linesize 180

set recsep off

compute sum of cnt on actual_start_date;

compute sum of elapsed on actual_start_date;

compute sum of average on actual_start_date;

compute sum of waited on actual_start_date;

compute sum of avewait on actual_start_date;

column elapsed format 99999.99 heading 'TOTAL|HOURS';

column average format 9999.99 heading 'AVG.|HOURS';

column waited format 99999999.99 heading 'WAITED|HOURS';

column avewait format 9999.99 heading 'AVG.|WAIT';

column cnt format 999,999 heading 'COUNT';

set feedback off

select    q.concurrent_queue_name,

                count(*) cnt,

                sum(r.actual_completion_date - r.actual_start_date) * 24 elapsed,

                avg(r.actual_completion_date - r.actual_start_date) * 24 average,

                sum(r.actual_start_date - r.requested_start_date) * 24 waited,

                avg(r.actual_start_date - r.requested_start_date) * 24 avewait

from      apps.fnd_concurrent_programs p,

                apps.fnd_concurrent_requests r,

                apps.fnd_concurrent_queues q,

                apps.fnd_concurrent_processes pr        

where   r.program_application_id = p.application_id and

                r.concurrent_program_id = p.concurrent_program_id and

                r.status_code in ('C','G') and

                r.controlling_manager=pr.concurrent_process_id and

                q.concurrent_queue_id=pr.concurrent_queue_id and

                r.concurrent_program_id=p.concurrent_program_id and

        actual_start_date between '04-NOV-2012' and '07-NOV-2012'

group by q.concurrent_queue_name;

set feedback on

***********************************

SET PAGES 3000 LINESIZE 400 TRIMS ON ECHO OFF VERIFY OFF FEEDBACK OFF tab off

col Concurrent_Queue_Name for a9

col User_name for a18

col USER_CONCURRENT_PROGRAM_NAME for a55

col Concurrent_Program_Name for a10

Select Concurrent_Queue_Name Manager,

Request_Id Request, User_name,

fcpt.USER_CONCURRENT_PROGRAM_NAME,

Concurrent_Program_Name Program,

To_Char(Actual_Start_Date, 'DD-MON-YY HH24:MI:SS') Started,

To_Char(Actual_Completion_Date, 'DD-MON-YY HH24:MI:SS') Completed,

round(to_number(to_char(Actual_Completion_Date,'SSSSS')-to_number(to_char(Actual_Start_Date,'SSSSS')))) time_sec

from Fnd_Concurrent_Queues Fcq, Fnd_Concurrent_Requests Fcr,

Fnd_Concurrent_Programs Fcp, Fnd_User Fu, Fnd_Concurrent_Processes

Fpro,FND_CONCURRENT_PROGRAMS_TL fcpt

where

Phase_Code in ('C','R','P') And

Fcr.Controlling_Manager = Concurrent_Process_Id       And

Fcr.REQUEST_DATE between to_date('28-JAN-2012 00:00:00', 'DD-MON-YY HH24:MI:SS') and to_date('28-JAN-2012

23:59:00','DD-MON-YY HH24:MI:SS') and

(Fcq.Concurrent_Queue_Id = Fpro.Concurrent_Queue_Id    And

Fcq.Application_Id      = Fpro.Queue_Application_Id ) And

(Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And

Fcr.Program_Application_Id = Fcp.Application_Id )     And

Fcr.Requested_By = User_Id                            And

fcp.APPLICATION_ID = fcpt.APPLICATION_ID and

fcp.CONCURRENT_PROGRAM_ID = fcpt.CONCURRENT_PROGRAM_ID and

fcpt.LANGUAGE = userenv('LANG')

order  by time_sec desc;

+++++++++++++++++

Conc-Req – Long running:

SELECT   fcr.phase_code,

         DECODE (fcr.phase_code,'C', 'Completed', 'P', 'Pending', 'R', 'Running', 'I', 'Inactive', fcr.phase_code) phase,

         fcr.status_code,

         DECODE (fcr.status_code,'A', 'Waiting',

                                'B', 'Resuming',

                                'C', 'Normal',

                                 'D', 'Cancelled',

                                'E', 'Error',

                                'F', 'Scheduled',

                                'G', 'Warning',

                                'H', 'On Hold',

                                 'I', 'Normal',

                                'M', 'No Manager',

                                'Q', 'Standby',

                                'R', 'Normal',

                                 'S', 'Suspended',

                                'T', 'Terminating',

                                'U', 'Disabled',

                                'W', 'Paused',

                                'X', 'Terminated',

                                 'Z', 'Waiting',

                                fcr.status_code) status,

         request_date,

          frt.responsibility_name,fu.user_name,

          fcpt.user_concurrent_program_name,

          fcr.request_id,fcr.request_date,fcr.requested_start_date,

          fcr.ACTUAL_START_DATE,fcr.ACTUAL_COMPLETION_DATE

   FROM fnd_concurrent_requests fcr,

         fnd_user fu,

         fnd_responsibility_tl frt,

          fnd_application_tl fat,

         fnd_concurrent_programs_tl fcpt

   WHERE (fu.user_id = fcr.requested_by)

     AND (fat.application_id = fcr.program_application_id)

     AND (fcr.concurrent_program_id = fcpt.concurrent_program_id)

     AND (fcr.responsibility_id = frt.responsibility_id)

     AND fat.LANGUAGE = 'US'

     AND frt.LANGUAGE = 'US'

      AND fcpt.LANGUAGE = 'US'

      and fcr.phase_code='C'

      and status_code='C'

      and fcr.request_date > sysdate-3

    ORDER BY fcr.request_date DESC;

++++++++++++++++++++

14. To find the conc-req id detaisl with trace details ...

column traceid format a8

column tracename format a80

column user_concurrent_program_name format a40

column execname format a15

column enable_trace format a12

set lines 80 pages 22 head off

select

    'Request id: '||request_id ,

    'Trace   id: '||oracle_Process_id,

    'Trace Flag: '||req.enable_trace,

    'Trace  Dir:  Go to directory '||dest.value,

    'Search String: ls -al *'||oracle_process_id||'*',

    '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 apps.fnd_concurrent_requests req, v$session ses, v$process proc,

     v$parameter dest, v$parameter dbnm, apps.fnd_concurrent_programs_vl prog,

     apps.fnd_executables execname

where  req.request_id = &request_id

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;

EX - Output:

Request id: 22849336

Trace   id: 1063                           Trace Flag: N

Trace  Dir:  Go to directory /d01/oracle/uatdb/11.2.0.3/admin/UAT_uatsdl1/diag/rdbms/uqat/UAT/trace

Search String: ls -al *1063*

Prog. Name: PRC: Update Project Performance Data

File  Name: PJI_PJP_SUM_MAIN.SUMMARIZE

Status    : Running-Normal

SID Serial: 8787,55063

Module    : PJI_PJP_SUMMARIZE_INCR

TKPROF command :

tkprof aps_ora_22822.trc aps_ora_22822.txt sys=no explain=apps/apps sort='(prsela,exeela,fchela)'print=20

The more useful scrips of manjunahta

There are few use full scrips for APPSDBA 
+++++++++++++++++++++++++++++++++++++++++++++

TO Know Active/inactive CM on Apps(to give value on ps -ef| grep FNDLIBR --USER NAME)

set head off

set verify off

set echo off

set pages 1500

set linesize 100

set lines 120

prompt

prompt Details of SID / SPID / Client PID

prompt ==================================

select /*+ CHOOSE*/

'Session  Id.............................................: '||s.sid,

'Serial Num..............................................: '||s.serial#,

'User Name ..............................................: '||s.username,

'Session Status .........................................: '||s.status,

'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,

'Server Process ID ......................................: '||p.spid Server,

'Sql_Address ............................................: '||s.sql_address,

'Sql_hash_value .........................................: '||s.sql_hash_value,

'Schema Name ..... ......................................: '||s.SCHEMANAME,

'Program  ...............................................: '||s.program,

'Module .................................................: '|| s.module,

'Action .................................................: '||s.action,

'Terminal ...............................................: '||s.terminal,

'Client Machine .........................................: '||s.machine,

'LAST_CALL_ET ...........................................: '||s.last_call_et,

'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600

from v$session s, v$process p

where p.addr=s.paddr and

s.sid=nvl('&sid',s.sid) and

p.spid=nvl('&spid',p.spid) and

nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

+++++++++++++++++++++++++

To know the all Active Users list

set pages 300

set lines 100

col user_concurrent_program_name format a40 head PROGRAM trunc

col elapsed format 9999

col request_id format 9999999 head REQUEST

col user_name format a20

col oracle_process_id format a5 head OSPID

select  fcp.user_concurrent_program_name

,       fcr.request_id

,       round(24*60*( sysdate - actual_start_date )) elapsed

,       fu.user_name

,       fcr.oracle_process_id

from    apps.fnd_concurrent_requests fcr

,       apps.fnd_concurrent_programs_tl fcp

,       apps.fnd_user fu

where   fcp.concurrent_program_id = fcr.concurrent_program_id

and     fcp.application_id = fcr.program_application_id

and     fu.user_id = fcr.requested_by

and     fcr.phase_code = 'R'

and     fcp.language='US';

++++++++++++++++++++++++++++++

Find process ID for the concurrent request

set lines  200

column process heading "FNDLIBR PID"

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_processes b,

v$process c,

v$session d

WHERE a.controlling_manager = b.concurrent_process_id

AND c.pid = b.oracle_process_id

AND b.session_id=d.audsid

AND a.request_id = &Request_ID

AND a.phase_code = 'R';

++++++++++++++++++++++++++++++++++

First run following SQL

set lines 180

set pages 1000

set verify off

undef spid

column req_id format 99999999999

column OPID format a10

column PPID format a8

column SPID format a8

column ST_CD format a1

column ph_cd format a1

column CNAME format a30

column event format a15

column user_name format a10

column program format a8

column serial# format 999999

column sid format 9999

column username format a8

select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id "PPID",

e.user_concurrent_program_name "CNAME",

f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,

b.serial#,b.program,g.event,

to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,

to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE

from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,

                        c.program,d.spid from v$session c, v$process d

                        where c.paddr=d.addr) b,

                        apps.fnd_concurrent_programs_tl e,

                        apps.fnd_user f,

                        v$session_wait g

                        where a.oracle_process_id=b.spid

                        and a.concurrent_program_id=e.concurrent_program_id

                        and e.language='US'

                        and a.requested_by=f.user_id         

                        and b.sid=g.sid

                                                and a.status_code='R'

                                                and a.phase_code ='R';

++++++++++++++++++ Manjunatha +++++++++++++++++++++++++++

To know the content of .dbc file

#DB Settings
#Sun Apr 06 06:06:06 IST 2011
GUEST_USER_PWD=GUEST/SVRGU3ST
APPL_SERVER_ID=BDD002C92F974766E0401C0A81C96C2098567165823575737795275391398765
FND_JDBC_BUFFER_DECAY_INTERVAL=300
APPS_JDBC_DRIVER_TYPE=THIN
FND_JDBC_BUFFER_MIN=1
GWYUID=APPLSYSPUB/PUB
FND_JDBC_BUFFER_MAX=5
APPS_JDBC_URL=jdbc\:oracle\:thin\:@(DESCRIPTION\=(LOAD_BALANCE\=YES)(FAILOVER\=YES)(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=tcp)(HOST\=svrsupdb1.svr.com)(PORT\=1521)))(CONNECT_DATA\=(SID\=SUPT)))
FND_JDBC_STMT_CACHE_FREE_MEM=TRUE
FND_JDBC_STMT_CACHE_SIZE=200
TWO_TASK=SUPT
FND_MAX_JDBC_CONNECTIONS=500
FND_JDBC_USABLE_CHECK=false
FNDNAM=APPS
FND_JDBC_PLSQL_RESET=false
DB_PORT=1522
FND_JDBC_CONTEXT_CHECK=true
FND_JDBC_BUFFER_DECAY_SIZE=5
DB_HOST=svrsupdb1.svr.com
-----------------------------------------------