Concurrent Managers are consuming high CPU and memory (Doc ID 1075684.1)

Last updated on SEPTEMBER 01, 2016

Applies to:

Oracle EBS Applications Performance - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Oracle Concurrent Processing - Version 11.5.10.2 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.

Symptoms

Overall database performance degrades and FNDLIBR processes consumes high CPU and memory on the server.

AWR reports point to following SQLs with very high execution counts :

SELECT R.Conc_Login_Id, R.Request_Id, R.Phase_Code, R.Status_Code, P.Application_ID, P.Concurrent_Program_ID, P.Concurrent_Program_Name, R.Enable_Trace, R.Restart, DECODE(R.Increment_Dates, 'Y', 'Y', 'N'), R.NLS_Compliant, R.OUTPUT_FILE_TYPE, E.Executable_Name, E.Execution_File_Name, A2.Basepath, DECODE(R.Stale, 'Y', 'C', P.Execution_Method_Code), P.Print_Flag, P.Execution_Options, DECODE(P.Srs_Flag, 'Y', 'Y', 'Q', 'Y', 'N'), P.Argument_Method_Code, R.Print_Style, R.Argument_Input_Method_Code, R.Queue_Method_Code, R.Responsibility_ID, R.Responsibility_Application_ID, R.Requested_By, R.Number_Of_Copies, R.Save_Output_Flag, R.Printer, R.Print_Group, R.Priority, U.User_Name, O.Oracle_Username, O.Encrypted_Oracle_Password, R.Cd_Id, A.Basepath, A.Application_Short_Name, TO_CHAR(R.Requested_Start_Date, 'YYYY/MM/DD HH24:MI:SS'), R.Nls_Language, R.Nls_Territory, DECODE(R.Parent_Request_ID, NULL, 0, R.Parent_Request_ID), R.Priority_Request_ID, R.Single_Thread_Flag, R.Has_Sub_Request, R.Is_Sub_Request, R.Req_Information, R.Description, R.Resubmit_Time, TO_CHAR(R.Resubmit_Interval), R.Resubmit_Interval_Type_Code, R.Resubmit_Interval_Unit_Code, TO_CHAR(R.Resubmit_End_Date, 'YYYY/MM/DD HH24:MI:SS'), Decode(E.Execution_File_Name, NULL, 'N', Decode(E.Subroutine_Name, NULL, Decode(E.Execution_Method_Code, 'I', 'Y', 'J', 'Y', 'N'), 'Y')), R.Argument1, R.Argument2, R.Argument3, R.Argument4, R.Argument5, R.A rgument6, R.Argument7, R.Argument8, R.Argument9, R.Argument10, R.Argument11, R.Argument12, R.Argument13, R.Argument14, R.Argument15, R.Argument16, R.Argument17, R.Argument18, R.Argument19, R.Argument20, R.Argument21, R.Argument22, R.Argument23, R.Argument24, R.Argument25, X.Argument26, X.Argument27, X.Argument28, X.Argument29, X.Argument30, X.Argument31, X.Argument32, X.Argument33, X.Argument34, X.Argument35, X.Argument36, X.Argument37, X.Argument38, X.Argument39, X.Argument40, X.Argument41, X.Argument42, X.Argument43, X.Argument44, X.Argument45, X.Argument46, X.Argument47, X.Argument48, X.Argument49, X.Argument50, X.Argument51, X.Argument52, X.Argument53, X.Argument54, X.Argument55, X.Argument56, X.Argument57, X.Argument58, X.Argument59, X.Argument60, X.Argument61, X.Argument62, X.Argument63, X.Argument64, X.Argument65, X.Argument66, X.Argument67, X.Argument68, X.Argument69, X.Argument70, X.Argument71, X.Argument72, X.Argument73, X.Argument74, X.Argument75, X.Argument76, X.Argument77, X.Argument78, X.Argument79, X.Argument80, X.Argument81, X.Argument82, X.Argument83, X.Argument84, X.Argument85, X.Argument86, X.Argument87, X.Argument88, X.Argument89, X.Argument90, X.Argument91, X.Argument92, X.Argument93, X.Argument94, X.Argument95, X.Argument96, X.Argument97, X.Argument98, X.Argument99, X.Argument100, R.number_of_arguments, C.CD_Name, NVL(R.Security_Group_ID, 0) FROM fnd_con current_requests R, fnd_concurrent_programs P, fnd_application A, fnd_user U, fnd_oracle_userid O, fnd_conflicts_domain C, fnd_concurrent_queues Q, fnd_application A2, fnd_executables E, fnd_conc_request_arguments X WHERE R.Status_code = 'I' And ((R.OPS_INSTANCE is null) or (R.OPS_INSTANCE = -1) or (R.OPS_INSTANCE = decode(:dcp_on, 1, FND_CONC_GLOBAL.OPS_INST_NUM, R.OPS_INSTANCE))) And R.Request_ID = X.Request_ID(+) And R.Program_Application_Id = P.Application_Id(+) And R.Concurrent_Program_Id = P.Concurrent_Program_Id(+) And R.Program_Application_Id = A.Application_Id(+) And P.Executable_Application_Id = E.Application_Id(+) And P.Executable_Id = E.Executable_Id(+) And P.Executable_Application_Id = A2.Application_Id(+) And R.Requested_By = U.User_Id(+) And R.Cd_Id = C.Cd_Id(+) And R.Oracle_Id = O.Oracle_Id(+) And Q.Application_Id = :q_applid And Q.Concurrent_Queue_Id = :queue_id And (P.Enabled_Flag is NULL OR P.Enabled_Flag = 'Y') And R.Hold_Flag = 'N' And R.Requested_Start_Date <= Sysdate And ( R.Enforce_Seriality_Flag = 'N' OR ( C.RunAlone_Flag = P.Run_Alone_Flag And (P.Run_Alone_Flag = 'N' OR Not Exists (Select Null From Fnd_Concurrent_Requests Sr Where Sr.Status_Code In ('R', 'T') And Sr.Enforce_Seriality_Flag = 'Y' And Sr.CD_id = C.CD_Id)))) And Q.Running_Processes <= Q.Max_Processes And R.Rowid = :reqname And ((P.Execution_Method_Code != 'S' OR (R.PROGRAM_APPLICATION_ID, R.CONCURRENT_PROGRAM_ID) IN ((0, 98), (0, 100), (0, 3 1721), (0, 31722), (0, 31757))) AND (NOT((R.PROGRAM_APPLICATION_ID, R.CONCURRENT_PROGRAM_ID) IN ((101, 47416), (101, 52869), (200, 42308), (222, 42568), (222, 53138), (401, 33263), (401, 48734))) AND NOT((R.PROGRAM_APPLICATION_ID, R.CONCURRENT_PROGRAM_ID) IN ((101, 101))) AND R.ORACLE_ID NOT IN (185) AND (R.PROGRAM_APPLICATION_ID, R.CONCURRENT_PROGRAM_ID) NOT IN ((0, 31659), (0, 46423), (101, 46443), (101, 46885), (101, 46886), (160, 20393), (175, 54373), (201, 45582), (201, 51686), (201, 51687), (201, 51783), (222, 21000), (222, 42448), (222, 42569), (222, 42728), (222, 43928), (222, 46665), (222, 48181), (222, 48264), (222, 48785), (222, 48787), (222, 48790), (222, 48909), (222, 48929), (222, 49015), (222, 50857), (222, 51377), (222, 51817), (222, 51819), (222, 52101), (222, 52247), (222, 52248), (222, 52267), (222, 53193), (222, 53313), (222, 53353), (222, 53354), (222, 53433), (222, 53434), (222, 53435), (222, 53436), (222, 53855), (222, 53856), (222, 53857), (222, 53858), (222, 53859), (222, 53860), (222, 53893), (222, 53894), (222, 54053), (222, 54353), (222, 54453), (222, 54633), (222, 54653), (222, 54654), (222, 54655), (222, 54813), (222, 54833), (222, 55146), (222, 55149), (222, 55356), (222, 56256), (401, 46223), (401, 46463), (401, 46483), (401, 46503), (401, 46725), (401, 46726), (401, 46727), (401, 46905), (401, 47029), (401, 50900), (401, 51682), (401, 51683), (401, 51684), (401, 51809), (401, 54674), (401, 54675), (401, 54693), (510, 40112), (510, 40113), (510 , 45309), (510, 45311), (530, 44515), (530, 44516), (535, 45673), (535, 45674), (535, 45676), (660, 42528), (660, 42529), (660, 48781), (660, 48782), (660, 48783), (660, 48784), (660, 48786), (660, 48788), (660, 48789), (660, 51438), (660, 51577), (660, 51617), (660, 52669), (660, 52769), (660, 52889), (660, 54093), (660, 55876), (665, 48363), (665, 48364), (665, 53693), (665, 53713), (665, 53853), (665, 53854), (700, 46523), (700, 46524), (704, 20418), (704, 31556), (704, 31690), (704, 31902), (704, 31955), (704, 33136), (704, 33137), (704, 33138), (704, 33140), (704, 33141), (704, 33148), (704, 33151), (704, 33210), (704, 33415), (704, 37191), (704, 48037)))) FOR UPDATE OF R.status_code NoWait


Select R.Rowid From Fnd_Concurrent_Requests R Where R.Hold_Flag = 'N' And R.Status_Code = 'I' And R.Requested_Start_Date <= Sysdate And ((R.OPS_INSTANCE is NULL) OR (R.OPS_INSTANCE = -1) OR (R.OPS_INSTANCE = FND_CONC_GLOBAL.OPS_INST_NUM)) AND EXISTS ( Select Null From Fnd_Concurrent_Programs P Where P.Enabled_Flag = 'Y' And R.Program_Application_Id = P.Application_Id And R.Concurrent_Program_Id = P.Concurrent_Program_Id AND EXISTS ( Select Null From Fnd_Oracle_Userid O Where R.Oracle_Id = O.Oracle_Id AND EXISTS ( Select Null From Fnd_Conflicts_Domain C Where P.Run_Alone_Flag = C.RunAlone_Flag And R.CD_Id = C.CD_Id ) ) And (P.Execution_Method_Code != 'S' OR (R.PROGRAM_APPLICATION_ID, R.CONCURRENT_PROGRAM_ID) IN ((0, 98), (0, 100), (0, 31721), (0, 31722), (0, 31757))) AND (NOT((R.PROGRAM_APPLICATION_ID, R.CONCURRENT_PROGRAM_ID) IN ((101, 47416), (101, 52869), (200, 42308), (222, 42568), (222, 53138), (401, 33263), (401, 48734))) AND NOT((R.PROGRAM_APPLICATION_ID, R.CONCURRENT_PROGRAM_ID) IN ((101, 101))) AND R.ORACLE_ID NOT IN (185) AND (R.PROGRAM_APPLICATION_ID, R.CONCURRENT_PROGRAM_ID) NOT IN ((0, 31659), (0, 46423), (101, 46443), (101, 46885), (101, 46886), (160, 20393), (175, 54373), (201, 45582), (201, 51686), (201, 51687), (201, 51783), (222, 21000), (222, 42448), (222, 42569), (222, 42728), (222, 43928), (222, 46665), (222, 48181), (222, 48264), (222, 48785), (222, 48787), (222, 48790), (222, 48909), (222, 48929), (222, 49015), (222, 50857), (222, 51377), (222, 51 817), (222, 51819), (222, 52101), (222, 52247), (222, 52248), (222, 52267), (222, 53193), (222, 53313), (222, 53353), (222, 53354), (222, 53433), (222, 53434), (222, 53435), (222, 53436), (222, 53855), (222, 53856), (222, 53857), (222, 53858), (222, 53859), (222, 53860), (222, 53893), (222, 53894), (222, 54053), (222, 54353), (222, 54453), (222, 54633), (222, 54653), (222, 54654), (222, 54655), (222, 54813), (222, 54833), (222, 55146), (222, 55149), (222, 55356), (222, 56256), (401, 46223), (401, 46463), (401, 46483), (401, 46503), (401, 46725), (401, 46726), (401, 46727), (401, 46905), (401, 47029), (401, 50900), (401, 51682), (401, 51683), (401, 51684), (401, 51809), (401, 54674), (401, 54675), (401, 54693), (510, 40112), (510, 40113), (510, 45309), (510, 45311), (530, 44515), (530, 44516), (535, 45673), (535, 45674), (535, 45676), (660, 42528), (660, 42529), (660, 48781), (660, 48782), (660, 48783), (660, 48784), (660, 48786), (660, 48788), (660, 48789), (660, 51438), (660, 51577), (660, 51617), (660, 52669), (660, 52769), (660, 52889), (660, 54093), (660, 55876), (665, 48363), (665, 48364), (665, 53693), (665, 53713), (665, 53853), (665, 53854), (700, 46523), (700, 46524), (704, 20418), (704, 31556), (704, 31690), (704, 31902), (704, 31955), (704, 33136), (704, 33137), (704, 33138), (704, 33140), (704, 33141), (704, 33148), (704, 33151), (704, 33210), (704, 33415), (704, 37191), (704, 48037)))) ORDER BY NVL(R.priority, 999999999), R.Priority_Request_ID, R.Request_ID


Manager log files are also flooded with the following messages:

Cause: UPDATE_STATUS failed due to ORA-01002: fetch out of sequence.

The SQL statement being executed at the time of the error was: update
fnd_concurrent_requests set phase_code = :phase ,status_code = :status,
completion_text = :text where request_id = :reqid and was executed from the
file &ERRFILE.  User XXX or Responsibility YYYY has expired. Cannot
process request XXXX. APP-FND-01564: ORACLE error 1002 in UPDATE_STATUS

This problem usually affects 11G database users. But problem is also reported against 9i and 10G databases.

Changes

Upgraded database to 11G .

End dated a user or a responsibility of a user, but there is a scheduled request.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms