buffer busy waits and fnd conc tables

[Laurel]

Hi Erman!
Congrats on Top60 Sites! Well deserved :) !
Would appreciate advise.
After upgrade from 12.1.3 ->12.2.5 noticed thaty we have on 12.2.5 env huge contention for 5 min with  concurrent mgrs. It was minor and shorter contention in 12.1.3

Now its >= 5 min and major.
Buffer busy wait is main wait and sqls are
select (RUNNING_PROCESSES-MAX_PROCESSES) ,MAX_PROCESSES ,NVL(SLEEP_SECONDS,0) ,DIAGNOSTIC_LEVEL into :b0,:b1,:b2,:b3:b4
from FND_CONCURRENT_QUEUES
where ((APPLICATION_ID=:b5 and CONCURRENT_QUEUE_ID=:b6) and (TARGET_NODE=:b7 or (TARGET_NODE is null and :b7 is null )))
AND
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.Node_Name1 is null or (R.Node_Name1 is not null and FND_DCP.target_node_mgr_chk(R.request_id) = 1)) And (R.Edition_Name is null or R.Edition_Name <= sys_context('userenv', 'current_edition_name')) 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 ((23,630),(23,631),(203,6932),(203,693),(213,634),(202,89330),(303,9229),(213,8560),(212,629),(23,30))) ORDER BY NVL(R.priority, 999999999), R.Priority_Request_ID, R.Request_ID

I have increased sleep time 30->60  and increased cache size 1->5
No much help...
Now  I am thinking should I look in decreasing the mgrs amount...
Why so dramatic change in behaviour.. 12c db maybe

Any suggestions ?
Thanks for help as always, Laurel

[ErmanArslansOracleBlog]

Hi Laurel

Thanks a lot :)

Okay.. Normally I would suggest the following;

reduce the manager process counts
increase the sleep times, but it seems this is a different case.

You are using the same configuration that you are using in 12.1.3, and you are seeing lots of concurrent related queries appearing top of the AWR reports..

1)Do you see any error messages in log files of the concurrent managers?
Especially internal manager's log file.. (something like ORA-01002?)

2)What does the execution plan says for this? Did you check it? if you see an interesting event there, then we can find a way to solve it for 12C.. (I mean db wise)

3)The concurrent check sqls may be changed in 12.2.5.. So if that's the case, then it may be normal for you to see these sqls on top.. So it is logical to decrease the concurrent manager counts. (only increase it in peak times) What do you think?

Because there are cases and there are bugs.. I didn't see one for 12.2.5 and 12C yet, but it could be..

Please, kindly answer the above questions first.. Then we will think what we can do..

[Laurel]

Hi Erman!

Thanks for suggestions

1. Checked -> No any ORA- errors what so ever.

2. Checked execution plan
a)
select (RUNNING_PROCESSES-MAX_PROCESSES) ,MAX_PROCESSES ,NVL(SLEEP_SECONDS,0) ,DIAGNOSTIC_LEVEL into :b0,:b1,:b2,:b3:b4
from FND_CONCURRENT_QUEUES
where ((APPLICATION_ID=:b5 and CONCURRENT_QUEUE_ID=:b6) and (TARGET_NODE=:b7 or (TARGET_NODE is null and :b7 is null )))

Index Unique scan  in old 12.1.3 ( we have old test) and in 12.2
Execution plan did not change.

b)Sql  a bit different if compare with 12.1.3 -> extra clause for edition in 12.2-> (R.Edition_Name is null or R.Edition_Name <= sys_context('userenv', 'current_edition_name'))


In 1226 Used Index N2 and in 1213 N9

Added hint, the performance/ timing is the same , Cost with N2 is much less,

Tested in 12.2.6 manually:
Select /*+ INDEX (R FND_CONCURRENT_REQUESTS_N9) */ 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.Node_Name1 is null or (R.Node_Name1 is not null and FND_DCP.target_node_mgr_chk(R.request_id) = 1)) And (R.Edition_Name is null or R.Edition_Name <= sys_context('userenv', 'current_edition_name')) 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 ((20003,91329),(20003,94329))) ORDER BY NVL(R.priority, 999999999), R.Priority_Request_ID, R.Request_ID

Conclusion: Not Explain plans are the cause


3.  Checked the ADDM/AWR
 Buffer Busy and similar sqls-> Contention on FND_CONCURRENT_QUEUES

select (RUNNING_PROCESSES-MAX_PROCESSES) ,MAX_PROCESSES ,NVL(SLEEP_SECONDS,0) ,DIAGNOSTIC_LEVEL into :b0,:b1,:b2,:b3:b4
from FND_CONCURRENT_QUEUES
where ((APPLICATION_ID=:b5 and CONCURRENT_QUEUE_ID=:b6) and (TARGET_NODE=:b7 or (TARGET_NODE is null and :b7 is null )))

SELECT MAX_PROCESSES, RUNNING_PROCESSES, TARGET_NODE, SLEEP_SECONDS, DIAGNOSTIC_LEVEL
FROM FND_CONCURRENT_QUEUES
WHERE APPLICATION_ID = :B2 AND CONCURRENT_QUEUE_ID = :B1


The main Concurrency and buffer busy waits for these sqls from time to time.


So, yep I guess I need to decrease a number of concurrent managers...

I guess I need to create a statistics first  on each of them  and then start decrease first 10% ...
Maybe  you have such scripts that you can share that can help me with stats ?

Hmmm.. I could do also rebuild table Fnd_concurrent_queues and requests  and indexes offline and
for Table/indexes possibilities:  higher PCTFREE/ reduce number of rows per block/Increase INITRANS , reduce PCTUSED , Increase of number Freelists

We have Automatic segment management(ASSM), no ASMi and no RAC.
Some of what I listed should not be relevant for Assm anymore :)
We have scheduled   maintenance of rebuilding of those objects in summer usually.

Thanks alot,Laurel

[ErmanArslansOracleBlog]

As for the tables that are highly fragmented because of sparse deletes or because of a purge operation,
we recommend defragmentation. Defragmentation should be done carefully, as the method may change
according to the application modules referencing those tables. That is, as for the concurrent tables such as
FND_CONCURRENT_REQUESTS, FND_CONCURRENT_PROCESSES, FND_CRM_HISTORY, FND_ENV_
CONTEXT, FND_TEMP_FILES, and FND_CONFLICTS_DOMAIN, Oracle suggests shutting down the
concurrent manager and doing a table move operation such as alter table <owner>.<table_name> move;
followed by index rebuilds and statistics gathering.
(this is from my book :)

So, it is a good idea to do that.

Read this one -> Concurrent Processing - Best Practices for Performance for Concurrent Managers in E-Business Suite (Doc ID 1057802.1)

and take a look at this one ->  CP Analyzer by reading Oracle Support document “Concurrent Processing - CP Analyzer for E-Business Suite” (Doc ID 1411723.1).