Concurrent manager: Library Cache: Mutex X waits

Hi Everyone!!

Today we had issues in 2 node Exadata RAC cluster with high concurrency wait "library cache: mutex X". Posting this as it would help some one out there later point in time.

OEM was showing high concurrency waits on the graph which was not normal.
After checking the concurrent top SQL, one of the select statement was causing 99% of the issue.

 SQL running behind was from concurrent manager. !!
That gave the idea to check out all the concurrent manager related logs.

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.Argument6,  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, 
... Truncated the sql for readability purpose
(20214,39804),(20214,39838),(20214,40937)) AND R.REQUESTED_BY NOT IN (20829,21465,192077))) FOR UPDATE OF R.status_code NoWait

Standard manager log was having below error message

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 PAULPET or Responsibility AR Corp Pmt Processing User has expired.  Cannot process request 45692490.
APP-FND-01564: ORACLE error 1002 in UPDATE_STATUS

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

Then it became for us to understand the cause. User has scheduled the jobs where user ID was terminated.  Concurrent manager processes started spinning around. we have terminated standard manager and removed the scheduled jobs from the system. Systems wait event graph came back to normal.

Metalink suggested fix patch for this issue. :)

We have fix patch 9109247 for this issue “Scheduled Requests Running for END_DATED Users 100% CPU and Giving ORA-01002 [ID 1080047.1]”

Hope this will help someone.!!


Anonymous said...

Thank you so much for posting this. You saved me many frustrating hours trying to find out why we were experiencing this problem after our 11g upgrade.

Anonymous said...

Thank you so much for posting this. You saved me many frustrating hours trying to find out why we were experiencing this problem after our 11g upgrade.

Anonymous said...

Thank you a lot for posting this! Exactly our issue, applying the patch in test at the moment