Some SQL queries consume High CPU usage

(Doc ID 2013769.1)

Last updated on MARCH 05, 2017

Applies to:

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

Symptoms

The following Query use around more than 30% usage of cpu due

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 ((R.PROGRAM_APPLICATION_ID, R.CONCURRENT_PROGRAM_ID) IN ((20044, 47683), (20044, 47684), (20044, 47687), (20044, 47692), (20044, 47693), (20044, 47694), (20044, 47698), (20044, 47699), (20044, 47700), (20044, 47701), (20044, 48173), (20044, 48174), (20044, 48725), (20044, 51967), (20044, 53386), (20044, 54868), (20044, 55224), (20044, 55258), (20044, 55259)))) ORDER BY NVL(R.priority, 999999999), R.Priority_Request_ID, R.Request_ID

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