CPU intensive long running Concurrent Program

Step1: Find the process that are consuming high CPU on DB Node.
oracledb=>prstat
   PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP
 26477 oracledb  2407M 2369M cpu2     0    0   2:59:40  13% oracle/39
  1958 oracledb  2393M 2355M cpu6     0    0   1:54:27  12% oracle/1
  2124 oracledb  2393M 2355M cpu2     0    0   1:53:35  12% oracle/1
  2202 oracledb  2393M 2355M cpu5     0    0   1:52:07  12% oracle/1



Step2: Identify the PROCESS(Apps Node) from DB.

SQL>select s.sid, s.serial#, s.process, s.program, s.module 
    from v$session s, v$process p
    where s.paddr =p.addr and p.spid in ('26477','2124','1958','2202');

       SID    SERIAL# PROCESS      PROGRAM                                          MODULE
---------- ---------- ------------ ------------------------------------------------ --------------------
        93      35457 17991
       

Step3:On the APPLICATION Server, grep for the PROCESS:
apps=>ps -ef|grep 17991
apps 27982 25127  0 14:14:24 pts/2    0:00 grep 17991
apps 17991  9806  0 10:40:02 ?        0:02 ar60run P_CONC_REQUEST_ID=13318110 P_CORP='055' P_COMPANY='004' P_AS_OF_DATE='1


Step4:Get the Concurrent Request details
select /*+ CHOOSE*/
      'Node Name..............................: ' || q.node_name                          || chr(10) ||
      'Req id.................................: ' || Request_Id                           || chr(10) ||
      'Requestor..............................: ' || User_Name                            || chr(10) ||
      'Manager................................: ' || Q.User_Concurrent_Queue_Name         || chr(10) ||
      'Status code............................: ' || decode(status_code,'A', 'Waiting','B',
      'Resuming','D', 'Cancelled','E', 'Error', 'G', 'Warning', 'H',
      'On Hold', 'R', 'Normal','S', 'Suspended', 'T', 'Terminating',
                      'W', 'Paused', 'X','Terminated', status_code)                                       || chr(10) ||
      'Phase code.............................: '  || decode(phase_code, 'C', 'Completed',
      'I', 'Inactive', 'P', 'Pending', 'R', 'Running', phase_code)                        || chr(10) ||
      'Priority...............................: ' || Fcr.priority                         || chr(10) ||
      'Program................................: ' || Fcp.User_Concurrent_Program_Name     || chr(10) ||
      'Time so far ...........................: ' || trunc((sysdate-Fcr.actual_start_date)*24*60,2)||' min'|| chr(10) ||
      'Avg execution time in  30 days.........: ' ||  trunc(AVG_TIME,2)  ||' min'          || chr(10) ||
      'Max execution time in 30 days..........: ' ||  trunc(MAX_TIME,2)  ||' min'         || chr(10) ||
      'Fastest execution Time in 30 days......: ' ||trunc( MIN_TIME,2) || ' min'          || chr(10) ||
      'Number of executions in last 30 days...: ' ||occurance                             || chr(10) ||
      'ClientPID..............................: ' || Fcr.OS_PROCESS_ID                    || chr(10) ||
      'ServerPID..............................: ' || Fcr.ORACLE_PROCESS_ID     || chr(10) ||
      'Arguments passed to the program .......: ' ||Fcr.argument_text
       from apps.Fnd_Concurrent_Requests Fcr,
    apps.Fnd_Concurrent_Programs_vl Fcp,
    apps.Fnd_Oracle_Userid O,
    apps.Fnd_Concurrent_Processes P,
    apps.Fnd_Concurrent_Queues_vl Q,
    apps.Fnd_User,(select
    concurrent_program_id
    ,count(concurrent_program_id) occurance
    ,min(actual_completion_date-actual_start_date)*24*60 MIN_TIME
    ,max(actual_completion_date-actual_start_date)*24*60 MAX_TIME
    , avg(actual_completion_date-actual_start_date)*24*60 AVG_TIME
    from apps.fnd_concurrent_requests
    where status_code='C' and phase_code='C'
    and trunc(actual_start_date)>trunc(sysdate-30)
    group by concurrent_program_id
    having max(actual_completion_date-actual_start_date)*24*60 >5) CREQ
    where Controlling_Manager = Concurrent_Process_ID
    and ( P.Concurrent_Queue_ID=Q.Concurrent_Queue_ID AND P.Queue_Application_ID = Q.Application_ID )
    and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('&REQID')
    and( Fcr.Program_Application_Id=Fcp.Application_Id
    and  Fcr.Concurrent_Program_Id=Fcp.Concurrent_Program_Id)
    and Requested_By = User_Id
    and Phase_Code = 'R' and status_code in ('R','T')
    --adding joins with new
    and Fcr.Concurrent_Program_Id=CREQ.Concurrent_Program_Id(+)
    Order By   Q.User_Concurrent_Queue_Name,q.node_name, Actual_Start_Date,Request_Id;
Enter value for reqid: 13318110
old  40:     and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('&REQID')
new  40:     and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('13318110')

Node Name..............................: server02
Req id.................................: 13318110
Requestor..............................: USER10
Manager................................: Standard Manager
Status code............................: Normal
Phase code.............................: Running
Priority...............................: 50
Program................................: Some Long Running Program
Time so far ...........................: 218.63 min
Avg execution time in  30 days.........:  min
Max execution time in 30 days..........:  min
Fastest execution Time in 30 days......:  min
Number of executions in last 30 days...:
ClientPID..............................:
ServerPID..............................:
Arguments passed to the program .......: , , , , 055, 004, , , , , 10-JUL-2014, , Merchant Number, , 71, Y, N

Concurrent Program issue --- fndcpesr

http://oracle.ittoolbox.com/groups/technical-functional/oracle-apps-l/concurrent-requst-invoking-shellscript-via-fndcpesr-stops-working-why-4133965

How To Clear Caches (Apache/iAS, Cabo, Modplsql, Browser, Jinitiator, Java, Portal, WebADI) for E-Business Suite?

How To Clear Caches (Apache/iAS, Cabo, Modplsql, Browser, Jinitiator, Java, Portal, WebADI) for E-Business Suite? (Doc ID 742107.1)

Apache / iAS 
For 11i and earlier versions:
- shutdown iAS server
- go to $OA_HTML (for 11.5.9) or $COMMON_TOP (for 11.5.10.x) directory
- backup the directory _pages and delete its contents by running for instance:

  rm -rf $COMMON_TOP/_pages/*

- for modplsql caches remove contents of  $IAS_ORACLE_HOME/Apache/modplsql/cache directory
- restart iAS server


To clear middle tier cache in release 12:

- go to "Functional Administrator" responsibility
- select Core Services => Caching Framework => Global Configuration => Clear cache
(please review Note 759038.1 for details).
In case you have login issue after accidentally cleared the _pages instead of using the method above for r12
please review Note 433386.1 to recompile jsp files.

Cabo
Images and style sheets can be corrupted or out of sync in the cabo caches,
you may need to clear the related directories after backup:

$OA_HTML/cabo/images/cache
$OA_HTML/cabo/styles/cache


Web Browser

for Internet Explorer:
- go to menu Tools => Internet Options,
- select 'General' tab,
- click on button 'Delete Files' in 'Temporary Internet files' area
  or
- click on button 'Delete...' then in 'Delete Browsing History' pop-up window click on 'Delete Files...'
- close all IE windows and restart new browser session.

for Mozilla Firefox:
- go to menu Tools
- select 'Clear Private Data...' or 'Clear Recent History...' then check 'Cache'
  or
- go to menu Edit or Tools
- select Preferences or Options.
- expand the 'Advance' options and choose 'Cache' or Privacy
- click the button called 'Clear Cache'.

for Safari:
- go to Safari menu
- select Empty Cache => Click 'Empty' in the dialogue box
for Netscape:
- go to menu Edit => Preferences,
- choose 'Cache' in 'Advanced' category,
- click on buttons 'Clear Memory Cache' and 'Clear Disk Cache'.
- close all Netscape windows and restart new browser session.

Jinitiator

Two possibilities depending of the Jinitiator version:

for 1.1.8.x versions:- delete all files in directory:

C:\Program Files\Oracle\Jinitiator <version>\jcache\

for 1.3.1.x versions:
- go to Start => Parameters => Control Panel
- double-click on "Jinitiator <version>" icon
- in the new pop-up window, click on "Cache" tab
- click on "Clear Jar Cache " button. On prompt, click Yes.

(you can also delete directly all files under directory:
C:\Documents and Settings\<your user name>\Oracle Jar Cache)


Java
Java/JRE plug-in (Windows)- go to Start => Parameters => Control Panel
- double-click on 'Java' icon
- in the new pop-up window, click on 'General' tab
- click on 'Setting...' button in 'Temporary Internet files' area then click on 'Delete Files...' button
  (you can select Applets, Applications or other files)

JVM

- go to responsibility "Functional Administrator"
- click on "Core Services" tab then the "Caching Framework" sub-tab
- click on "Global Configuration" link then click on "Clear all cache" button to clear all of the Java Cache's


Portal
- go to the url: http://<host.domain>:<port>/pls/admin_/gateway.htm or cache.htm
- click on "Cache Settings" option and note the "Cache Directory"
- go to this directory and delete all the cache files in the directory and sub directories

See also modplsql caches in Apache/iAS section above.


WebADI / BNE cache
For instance when enabling BNE log
- go to url: http://<host.domain>:<port>/oa_servlets/oracle.apps.bne.framework.BneAdminServlet
- click on the "clear-cache" link
- at the bottom of the page you should see 'Cache Cleared'

---

FRM-92050 failed to connect to server

Forms 11i issues:





Solution:
Forms service was down, started it.

sqlplus hangs indefinetly --- ORA-01075

Resolve ORA-01075

SVC disk on a client crashed and as the result Oracle database crashed. After resolving disk isue, I was getting ORA-01075 error on sqlplus to startup DB.

> sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on 
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-01075: you are currently logged on

Checking the process, nothing from Oracle user was up and running.
Eventually, It turned out that the issue is related to stuck shared memory and semaphore.
To check out if this is the case for you, follow below steps :

1. Login as Oracle 
    (or owner of software)

2. Make sure there is no process up as oracle 
  (Assume Oralce is the owner of oracle binaries)

3.run ipcs 
   * Check for any shared memory segment for Oracle
   * Check for any semaphore for Oracle

4. Kill shared memory segment or semaphore 
   which is owned by oracle
   * ipcrm -m shmid_from_step_3 
     (to cleanup shared memory segment)
   * ipcrm -s semid_from_step_3 
     (to cleanup semaphore from step 3)
     
  5. Retry sqlplus

source: http://oradbatips.blogspot.com/2011/06/tip-92-resolve-ora-01075.html

https://community.oracle.com/thread/3544149