DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Thursday, December 29, 2011

To Identify the manager for a given concurrent program

The script given below identifies which manager is going to execute a given Concurrent program:

SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl fcp, apps.fnd_concurrent_queue_content fcqc, apps.fnd_concurrent_queues_tl cq
fcqc.type_application_id(+) = fcp.application_id AND
fcqc.type_id(+) = fcp.concurrent_program_id AND
fcqc.type_code(+) = 'P' AND
fcqc.include_flag(+) = 'I' AND
fcp.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

Wednesday, December 28, 2011

To identify long running concurrent requests for more than 1 hour.

# To identify the long running concurrent requests for more than 1 hour
Mailid=<List of Mail ids>
<Set the Environment>
sqlplus -s 'apps/<apps password><<EOF >> /usr/tmp/logli1.log
spool /usr/tmp/longrunco.log
set line 200
set pagesize 500
col PROGRAM_NAME format a30
col concreq format a8
col Username format a10
col opid format a4
col dbuser format a6
SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
           fcp.USER_CONCURRENT_PROGRAM_NAME "Program_Name",
                fu.user_name "Username",
               round((sysdate - actual_start_date) * 24 ,2) "Running_Hrs",
           SUBSTR(proc.os_process_id,1,15) clproc,
           SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
           SUBSTR(vsess.username,1,10) dbuser,
           SUBSTR(vproc.spid,1,10) svrproc,
           vsess.sid sid,
           vsess.serial# serial#
    FROM   fnd_concurrent_requests req,
           fnd_concurrent_processes proc,
           fnd_lookups look,
           fnd_lookups look1,
           V\$process vproc,
           V\$session vsess,
           fnd_concurrent_programs_vl fcp,
        fnd_user fu
    WHERE  req.controlling_manager = proc.concurrent_process_id(+)
    AND    req.status_code = look.lookup_code
    AND    look.lookup_type = 'CP_STATUS_CODE'
    AND    req.phase_code = look1.lookup_code
    AND    look1.lookup_type = 'CP_PHASE_CODE'
    AND    look1.meaning = 'Running'
    AND    proc.oracle_process_id =
    AND    vproc.addr = vsess.paddr(+)
    AND    fu.user_id = req.requested_by
    AND    round((sysdate - actual_start_date) * 24) > 1;
spool off
echo `date` >> $ORACLE_HOME/admin/$CONTEXT_NAME/udump/longrun_concurrent.log
cat /usr/tmp/longrunco.log >> $ORACLE_HOME/admin/$CONTEXT_NAME/udump/longrun_concurrent.log
cat /usr/tmp/longrunco.log |mailx -s "Alert:Long Running Concurrent requests Check" $Mailid
rm -rf /usr/tmp/longrunco.log

Saturday, December 24, 2011

Apex Listener Configuration for Apex

1) Check java version after completion of glassfish installation
. /java -version

2) Go to location /oracle/glassfishv3/jdk/bin and

Run ./java -jar /oracle/apex/apex.war,
(where /oracle/apex/ is the location of apex listener software )
will get the below screenshot
And provide the preferred username and password for adminlistener and managerlistener
Username and password for the above can be anything.


bash-3.00$ /oracle/glassfishv3/jdk6/jdk/bin/java -jar apex.war
INFO: Starting: /oracle/glassfishv3/apex.war
See: 'java -jar apex.war --help' for full range of configuration options
INFO: Extracting to: /oracle/.apex/8080
Enter the path to the directory containing the APEX static resources
Example: /Users/myuser/apex/images
or press Enter to skip: /oracle/apex_4.0.2_en/apex/images
INFO: Using classpath: file:/oracle/.apex/8080/apex/____embedded/start.jar:file:/oracle/.apex/8080/apex/WEB-INF/lib/commons-fileupload-1.2.1.jar:file:/oracle/.apex/8080/apex/WEB-INF/lib/ojdbc6.jar:file:/oracle/.apex/8080/apex/WEB-INF/lib/ojmisc.jar:file:/oracle/.apex/8080/apex/WEB-INF/lib/poi-3.6-20091214.jar:file:/oracle/.apex/8080/apex/WEB-INF/lib/ucp.jar:file:/oracle/.apex/8080/apex/WEB-INF/lib/apex.jar:
INFO: Starting Embedded Web Container in: /oracle/.apex/8080
Enter a username for the APEX Listener Administrator [adminlistener]: admin123
Enter a password for admin123:admin321
Confirm password for admin123:admin321
Enter a username for the APEX Listener Manager [managerlistener]: manager
Enter a password for manager:manager123
Confirm password for manager:manager123
Apr 21, 2011 2:45:50 PM ____bootstrap.Deployer deploy
INFO: Will deploy application path=/oracle/.apex/8080/apex/WEB-INF/web.xml
Apr 21, 2011 2:45:53 PM ____bootstrap.Deployer deploy
INFO: deployed application path=/oracle/.apex/8080/apex/WEB-INF/web.xml
Apr 21, 2011 2:45:53 PM com.sun.grizzly.Controller logVersion
INFO: Starting Grizzly Framework 1.9.18-o - Thu Apr 21 14:45:53 PDT 2011
INFO: Please complete configuration at: http://localhost:8080/apex/listenerConfigure

In the below screen, Provide username as APEX_PUBLIC_USER , password of apex_public_user as per Section 1,1.ii, hostname & port of apex database , SID of Apex database
And click on apply button which will appear below in the firfox screen.

Installation of Glassfish for configuring apex frontend

1. Download the Glassfish
Example File name :

2. Download the Apex listener : apex_listener.1.1.2

3) Reset the password for APEX_040000 and apex_public_user
i. Unlock the Apex user Acount. SQL> ALTER USER APEX_040000 ACCOUNT UNLOCK; User altered

ii. Change password for Apex public user.
User altered.
2) Start VNCServer and Start installation in VNC
3) Run the script, sh (/oracle/apex)

Apex Installation for 11g Instance

Pre Installation Tasks:

1. Create Tablespace for Apex Data and Files.
SQL> create tablespace apex datafile '//apexd01.dbf' size 500m;
Tablespace created.

2. Ensure Shared pool size is atleast 100m (suggested 250m)

3. Download the Apex Installation Software from the below Link and move the software to server
Oracle Application Express 4.0.2 - English language only
4. Unzip the apex Installation software and change directory into extracted folder

Installation Tasks:
1. Login as sysdba: sqlplus ‘/as sysdba’
2. Installation Command: @apexins.sql apex apex temp /i/
3. Admin Username Password Change:
SQL> @apxchpwd
Enter a value below for the password for the Application Express ADMIN user.
Enter a password for the ADMIN user []
Session altered.
...changing password for ADMIN
PL/SQL procedure successfully completed.
Commit complete.

Concurrent Processing - Best Practices for Performance for Concurrent Managers in E-Business Suite

Generic Tips

1) Sleep Seconds - is the number of seconds your Concurrent manager waits between checking the list of pending concurrent requests (concurrent requests waiting to be started).

Tip: Set the sleep time to be very brief during periods when the number of requests submitted is expected to be high. Otherwise set the sleep time to a high number (e.g. 2 minutes) . This avoids constant polls to check for new requests.

2) Increase the cache size (number of requests cached) to at least twice the number of target processes.
For example, if a manager's work shift has 1 target process and a cache value of 3, it will read three requests, and try to run those three requests before reading any new requests.

Tip: Enter a value of 1 when defining a manager that runs long, time-consuming jobs, and a value of 3 or 4 for managers that run small, quick jobs.

3) Create specialized concurrent managers to dedicate certain process either short or long running programs to avoid queue length.

4) To maximize throughput consider reducing the sleep time of the Conflict Resolution Manager (CRM). The default value is 60 seconds. You can consider setting to 5 or 10 seconds.

5) Avoid enabling an excessive number of standard or specialized managers. It can degrade the performance due polling on queue tables
(FND_CONCURRENT_REQUESTS...). You need to create specialized managers only if there is a real need.

6) Set the system profile option "Concurrent: Force Local Output File Mode" to "Yes" if required . You need to apply patch 7530490 for R12 (or) 7834670 for 11i to get this profile.
Refer Note.822368.1: Purge Concurrent Request FNDCPPUR Does Not Delete Files From File System or Slow performance

Note:- The profile option "Concurrent: Force Local Output File Mode" is set to "No" by default. After applying the patch, set the profile option to YES will cause FNDCPPUR to always access files on the local file system, hence FNDCPPUR will remove the OS files faster.To enable this feature, All Concurrent Manager nodes must be able to access the output file location via the local filesystem

7) Truncate the reports.log file in log directory. Refer Note.844976.1 for more details
Truncation of file "reports.log" is a regular maintenance work of Application DBA. Make sure that reports log file size should not increase to its maximum limit of 2 GB. There is no purge program to truncate file "reports.log". This maintenance needs to be done manually and regularly depending on number of concurrent program which uses "reports.log". You can safely truncate "reports.log". The "reports.log" file can be located under $APPLCSF/$APPLLOG.

8) Ensure "Purge Concurrent Request and/or Manager Data, FNDCPPUR," is run at regular intervals with "Entity" parameter as "ALL". A high number of records in FND_CONCURRENT tables can degrade the performance.

Additionally, the following are very good methods to follow for optimizing the process:

• Run the job in hours with low workload. Doing this after hours will lessen the contention on the tables from running against your daily processing.
• To get the requests under control, run the FNDCPPUR program with Age=20 or Age=18 would be a good method. That means, all requests older than 18 or 20 days will be purged.
• Once the requests are under control, run the FNDCPPUR program with Age=7 to maintain an efficient process. This would solely depend on the level of processing that is performed at your site

9) Ensure that the log/out files are removed from the locations shown below as you run "Purge Concurrent Request and/or Manager Data program".


In the event that it does not remove the log/out files, over a period of time it will slow down the performance. Please refer to the following note which suggests the patch which fixes it.

Note.822368.1: Purge Concurrent Request FNDCPPUR Does Not Delete Files From File System or Slow performance

10) Defragment the tables periodically to reclaim unused space / improve performance


10.1) alter table . move;
10.2) Note that, some indexes might become unusable after table is moved, check the index status from dba_indexes for the table moved and rebuild them too as explained in next bullet.

select owner, index_name, status from dba_indexes where table_owner = upper('&OWNER') and table_name = upper('&SEGMENT_NAME');

10.3) alter index . rebuild online;

Note: Ensure the tablespace in which the object currently exists has got sufficient space before you move/defragment . Always take backup of the tables before moving the data. It is recommended to perform thsaction on Test instance initially then testing thoroughly before performing it on Production instance.

10.4) You will need to collect the statistics for the tables.

For example:
exec fnd_stats.gather_table_stats ('APPLSYS','FND_CONCURRENT_REQUESTS',PERCENT=>99);

Transaction Manager (TM)

11 ) Profile Concurrent:Wait for Available TM - Total time to wait for a TM before switchover to next available TM. Consider setting this to 1 (second).
12) Ensure enough TMs exist to service the incoming request load.
13) When the load is high, set the following profile to optimum values to achieve better results.
PO: Approval Timeout Value - Total time for workflow call (When initiated from Forms) to time out.
14) Set the sleep time on the Transaction Manager to a high number (e.g. 10 minutes), this avoids constant polls to check for shutdown requests.

Parallel Concurrent Processing (PCP) Environment

15) If the failover of managers is taking too long refer to Note:551895.1: Failover Of Concurrent Manager Processes Takes More than 30 Minutes

16) Set profile option 'Concurrent: PCP Instance Check' to 'OFF' if instance-sensitive failover is not required. Setting it to 'ON' means that concurrent managers will fail over to a secondary application tier node if the database instance to which it is connected goes down.

17) Transaction Manager uses DBMS_PIPE to communicate with application session prior to 11i.ATG_PF.H RUP3. DBMS_PIPE in turn uses OS Pipe.We might use Advance Queue(AQ) with 11i.ATG_PF.H RUP3 by setting System Profile "Concurrent: TM Transport Type" to

Note Pipes are more efficient but require a Transaction Manager to be running on each DB Instance (RAC). So you might want to use "Queue" for easy maintenance.

18) Add these parameters depends on your Database version
+ _lm_global_posts=TRUE
+ _immediate_commit_propagation=TRUE (11g RAC)
+ max_commit_propagation_delay=0 (9i RAC)

Concurrent Processing Server Tuning

1. There are way too many site specific factors that needs to be considered for optimum CP throughput: from machine hardware, to user request volume, to required Work
Shifts, to programs run time characteristics (long / short running)--not to mention also testing and benchmarking. Such a tasks, is beyond the scope of ATG Support.

2. The "Tuning Concurrent Processing" chapter of the white paper "A Holistic Approach To Performance Tuning Oracle Applications Systems Release 11 and 11i" Note 69565.1 may provide some basic insight. Also reference the "Defining Concurrent Managers" and the "Setting Up and Starting Concurrent Managers" chapters of the "Oracle Applications System Administrator's Guide - Configuration".

3. As per Note 69565.1 "A Holistic Approach to Performance Tuning Oracle Applications Systems", "50% of concurrent processing performance tuning is in the business!"

4. Visit the Concurrent Processing Product Information Center (PIC) Note 1304305.1 for additional performance and setup documentation.

Start Tuning....

How to Start tuning: Server Level (Focus from Oracle Database)

Whenever there is server level issue, The tuning starts from the server
.. Just remember the phrase “The person who has the problems has the symptoms for solutions” So let us start the tuning from server..

1. First we need to find out what the server utilization is and who is utilizing the most.

How to achieve it: we can use os specific commands like Topas in AIX,TOP in linux environments,Prstat/topas in solaris, task manager in windows.

What you need: once you found what is the utilization and who is doing that..
Then you need to get the server process id (spid) from the os level of the specific process who is utilizing the most.

2. After that login to any user session who can access v$ views.

Execute: ( I attached my queries ,, you can add the columns you need to add )

Select module,action,machine,sql_address,sql_id,status from v$session vs
Where paddr=(select addr from v$process where spid=’&spid’);

3. find out what the sql which is consuming resource.

Select sql_text,sql_fulltext,optimizer_cost from v$sql where sql_id=’&sql_id’;

4. Now you got the details of the sessions,, if you want to do kill the session .. go to your server console as the specific user and then kill the session

How should I do: use ‘’kill -9

5. Don’t stop here .. tune the query and take complete solution to the issue .

How to Start tuning: Session Level (focus from oracle Database)

Refer to the post… How to Start tuning: Server Level (Focus from Oracle Database)
When we found that the specific session taking more bottleneck towards performance. Then The best step to be taken is the tracing the session (ie getting more details) before killing it.
For Example FNDWFBG is taking more cpu usage.. what we need to take step is..
1. As per the guidelines in the post.. first we need to take the Os process id.

2. Login as sysdba

3. i. oradebug setospid &spid
ii. oradebug unlimit
iii. oradebug Event 10046 trace name context forever, level 12
Trace file will be generated with the process id in its name in User dump location.

4. Once the session got completed then execute the below command in the sysdba session where you initiated the oradebug.

oradebug Event 10046 trace name context off;

5. What next.. Generate tkprof for the session dump file to find the expensive query.

Suggested to use as below:

Tkprof explain=/ sys=no sort='(prsela, exeela, fchela)'

Example: tkprof PROD_ora_950460.trc PROD_ora_950460.tkp explain=apps/apps sys=no
sort='(prsela, exeela, fchela)'

The above tkprof options are very usefull.. it will give us the query details in the order of its expensiveness.

6. Beyond this.. DBA needs to work with the respective technical teams to tune the expensive query.

How to start tuning: Code Level

Refer to the post… How to Start tuning: Session Level (focus from oracle Database)
Once we found the most expensive query, we need to tune the query and test it again..
Always advisable to tune and test in your test instance.

Follow the below steps to test and find out whether it is tuned or not…

1. Login to your Application user through which you can execute the statements

2. alter session set tracefile_identifier='10046';

3. alter session set timed_statistics = true;

4. alter session set statistics_level=all;

5. alter session set max_dump_file_size = unlimited;

6. alter session set events '10046 trace name context forever,level 12';

7. Execute the expensive statement

8. select * from dual; --- to ensure the previous cursor is closed

9. alter session set events '10046 trace name context off';

10. Generate the tkprof

Generate the tkprof as explained in the post How to Start tuning: Session Level (focus from oracle Database).. itll the give the details in the order of its expensiveness.

How to start tuning: Oracle Ebs Application Session

As per the previous posts.. we need to trace the session to find out what is the problem.
For E-Business Suite Session .. Here we go.
1. Navigate Responsibility: System Administrator > Profile > System >Query

User: User submitting the Journal entries 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. Save.

4. Reproduce the problem through the user session to find out cause for performance problem.

5 Trace file will be generated with the process id in its name.
6 Generate tkprof for the trace file.

Generate the tkprof as explained in the post How to Start tuning: Session Level (focus from oracle Database).. itll the give the details in the order of its expensiveness.

Apps User who are assigned a particular responsibility

-- This is to list the  Apps User who are assigned a particular responsibility

select a.user_name,b.responsibility_name 
fnd_user a,fnd_responsibility_vl b ,FND_USER_RESP_GROUPS c
a.user_id = c.user_id
and lower(b.responsibility_name) like lower('&Responsibility_name%')
and a.END_DATE is null
(c.END_DATE is null
c.end_date > sysdate)

List Responsibilities That Can Run a Given Concurrent Program


This script helps in identifying the list of responsibilities that can run a given concurrent request

REM Save this file as whocanrun.sql
REM Usage: sqlplus apps/apps @whocanrun
REM or: sqlplus apps/apps @whocanrun
REM Implemented remark 64545.1 to modify script for use with 11i
REM changed to use fnd_concurrent_programs_vl, and fnd_responsibility_vl
REM in place of fnd_concurrent_programs, and fnd_responsibility.

set verify off
set pagesize 1000

column RN format A40 heading "Responsibility Name"

prompt &1 can be run by:

SELECT responsibility_name RN
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND (UPPER(fcpv.concurrent_program_name) = UPPER('&1')
UPPER(fcpv.user_concurrent_program_name) = UPPER('&1'))
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name