R12

[satish]

Hi Erman,

we are facing issue with concurrent requests today.We are on R12.2.5.Concurrent Requests Remain in Status Pending Normal For a minute Before running.we have 20 processes defined for standard manager,sleep seconds=30 and cache size=40

Issue:

At the time of issue,there were only 6 requests in pending normal and 2 requests in running state.then we have submitted active users request and it took almost 1minute in pending normal and then started running

Can you suggest what might be wrong

Thank You

[ErmanArslansOracleBlog]

Concurrent queue check may be taking long time.
Did you check the number of records in related FND tables?
Do you purge your concurrent log and data periodically?
did you check the AWR and see if there is any concurrent sql related performance problem?

I mean, the db work that the managers do may be taking a long time.

Analyze your env, with the above comment in your mind.

Normally,

If manager is not processing any requests, there can only be few reasons:

 1) Specialization rules prohibits processing any requests (Note 1311526.1)
 2) Program with Run alone flag is submitted with pending or running status
 3) All the Managers are all busy.
 4) Concurrent Program has crashed and managers are still waiting for completion.
 5) Concurrent Managers have crashed and somehow not recovered yet .

[satish]

Thanks for quick update.will get back to you

[satish]

1)Purge Concurrent Request and/or Manager Data is scheduled to run every day at 04:00 PM IST

Log from yesterday request with

Current system time is 24-AUG-2017 04:00:19

+---------------------------------------------------------------------------+

Purged 5918 entrie(s) from FND_CONCURRENT_REQUESTS :-24-AUG-2017 04:03:54
Purged 0 entrie(s) from FND_CONC_DEFERRED_ARGUMENTS :-24-AUG-2017 04:03:54
Purged 0 entrie(s) from FND_FILE_TEMP :-24-AUG-2017 04:03:54
Purged 2882 entrie(s) from FND_CRM_HISTORY :-24-AUG-2017 04:03:54
Purged 0 entrie(s) from FND_TM_EVENTS :-24-AUG-2017 04:03:54
Purged 0 entrie(s) from FND_TEMP_FILES :-24-AUG-2017 04:03:54
Purged 0 entrie(s) from FND_ENV_CONTEXT :-24-AUG-2017 04:03:54
Purged 0 entrie(s) from FND_DUAL :-24-AUG-2017 04:03:54
Purged 0 entrie(s) from FND_CONFLICTS_DOMAIN :-24-AUG-2017 04:03:54
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
Successfully resubmitted concurrent program FNDCPPUR with request ID 2233686 to start at 25-AUG-2017 04:00:00 (ROUTINE=AFPSRS)


parameters used:

entity:REQUEST
Mode:Age
Mode value:60
username:SYSADMIN
Report:Yes
Purge Other:Yes


2)current number of records:

SQL> select count(*) from apps.fnd_concurrent_Requests;

  COUNT(*)
----------
    937766


3)Standard manager is queue is not long,it has 20 threads and 2 requests running.At this time i have submitted the active users reqeust and it is in pending state for 1 minute and then started running.
4)Managers are not busy and running fine and requests getting completed successfully
5)settings.cache size 45,sleep seconds 30 and processes defined 20 for standard manager

[ErmanArslansOracleBlog]

What about the queries that the concurrent managers do for checking their queues? (as I mentioned earlier)

I mean, if you have lots of concurrent processes , then your concurrent queue check queries may hang the database. Also if you  have a low sleep value, the same will happen.
Because they are checking their queues all the time.

Try decreasing the process count to 5 and retest.

This subject is a little complex understand.

I mean, setting the cache size, process counts and sleep times is not so straight forwards.
There are some wrong myths there.

Read my book.

http://www.apress.com/br/book/9781484214237

Chapter 13: Oracle E-Business Suite 12.2 Performance Recommendations
Section : EBS 12.2 Tuning the Concurrent Processing

[Syed Tanweer Ali]

Thanks for the update,will get the book.

For now,sleep seconds for standard manager is 30,its not low or high.coming to processes they are only 20 but cache size is 40.How decreasing the processes will help in this case

[satish]

Support has updated the records in below tables are very high and the issue is because of these records

TABLE # ROWS
FND_CONCURRENT_PROCESSES 2,261
FND_CONCURRENT_REQUESTS 935,470
FND_CONC_PP_ACTIONS 68,703
FND_CONC_REQUEST_ARGUMENTS 19,545
FND_CONC_STAT_LIST
FND_CONC_STAT_SUMMARY
FND_DUAL 1
FND_ENV_CONTEXT 162,391
FND_RUN_REQUESTS 14,690
FND_RUN_REQ_PP_ACTIONS 14,514

I have seen tables with more than the above records for other clients and the performace looks good for them.Need your suggestions on above support updates.Do you this the issue is really with above records

[ErmanArslansOracleBlog]

set cache size to 1.

Set processes to 5 and retry.

I suspect, you may be a database performance problem /sql performance problem making your queue checks take longer time.

2017-08-24 14:04 GMT+03:00 Syed Tanweer Ali [via Erman Arslan's Oracle Forum] <[hidden email]>:

Thanks for the update,will get the book.

For now,sleep seconds for standard manager is 30,its not low or high.coming to processes they are only 20 but cache size is 40.How decreasing the processes will help in this case

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/R12-tp3336p3349.html

To start a new topic under EBS 12.2, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

--

[ErmanArslansOracleBlog]

Yes. Supports says the same as you see.
They also think that, these records  make your concurrent sqls take longer time, as I said.

[satish]

can we dynamically change the cache size setting?we can make processes to 5 at this time as there are requests occupied all the threads.If possible can you also let us know how cache size and processes affect this?

[ErmanArslansOracleBlog]

Conc manager Count:

Frequently checking the queues lets the idle concurrent managers start executing the concurrent
requests more quickly when they are submitted, but it may also introduce a performance problem in
the database, as this will make the concurrent manager processes execute the same queries frequently.

So, your processes execute the same queries frequently and as these tables had lots of rows, your conc manager queries may take long time and as a result, they may be late to start executing the programs .

That's why I suggested decreasing the manager process counts.. To save the day...


About your CACHE question:

 First, the concurrent manager cache size is actually defined per process, and second, there is no
coordination between the concurrent processes to decide which requests are already cached and which
are not.

That's why , No need to have a big cache size .

I recommend a value of 1 for setting the cache sizes of the concurrent managers that
consist of several concurrent processes. Having a big cache size (like 30, 60, and so on) is only meaningful
when the relevant concurrent manager has a few concurrent processes (1, 2, or 3).


DEFRAG recommendation:

Defragmentation may also help in this situation.

For FND_CONCURRENT_REQUESTS, FND_CONCURRENT_PROCESSES, Oracle suggests shutting down the
concurrent manager and doing a table move operation such as alter table <owner>.<table_name> move;

[Sri]

Excellent.Thanks for the info. Lastly one question. Is sleep seconds has any relation with cache size.

Thank you

[ErmanArslansOracleBlog]

Hi Sri,

Read this -> http://ermanarslan.blogspot.com.tr/2016/04/ebs-r12-concurrent-manager-facts-cache.html

When you will read above blog post, you will be informed about your queries and the subject.

[satish]

This cleared my concern about cache size but still there was a little confusion about sleep seconds.In our environment all most all the time requests keep on running,we have 40 process in standard manager and sleep seconds is 30.In This case how sleep seconds work.Appreciate your explanation.

Thanks,
Lawrence

[ErmanArslansOracleBlog]

Hi lawrence,

As I explained my blog post; we can conclude that, cache  give us benefits when there are few number of concurrent processes available. For the environments where we have several concurrent processes like 10 standard manager process or so,  a general recommended setting can be "1" for it.

Please read that post carafully, and you will get the idea.

There is a formula there, use it to calculate the sleep time.
Cache size should be set to 1.

About caching -> read this to understand :

process 1 caches 30 requests (1-30) and start executing request 1
process 2 caches 30 requests (2-31) and start executing request 2, as request 1 is already run by Process 1
process 3 caches 30 requests (3-32) and start executing request 3,  as request 2 is already run by Process 1
....
.....
process 30 caches 30 requests (30-59) and start executing request 3,  as request 29 is already run by Process 1
...
suppose now process 1 finishes the executing of request 1;
process 1 checks request 2 and see it is locked, as it is executed by another process.
process 1 checks request 3 and see it is locked, as it is executed by another process.
process 1 checks request 4 and see it is locked, as it is executed by another process.
process 1 checks request 5 and see it is locked, as it is executed by another process.
....
process 1 checks request 30 (which is the last request in its cache) and see it is locked, as it is executed by another process.
What process 1 does than?
it caches again. It caches another 30 request. """""So where it the benefit of caching?"""


http://ermanarslan.blogspot.com.tr/2016/04/ebs-r12-concurrent-manager-facts-cache.html

[satish]

I am confused about sleep secondsit was confusion not only to me but many of viewers,I know that you can very well explain better and it is not an easy thing.

Thank you

[ErmanArslansOracleBlog]

Lawrence,

Sleep seconds is per process.
Let's give an example to make you understand better.

Suppose you want to make your concurrent programs  to wait in the queue 20 seconds max.
In order to do this, you need mainly 2 things. 1) appropriate concurrent manager count 2) appropriate sleep time setting.

Concurrent manager processes sleeps (does not do any queue checking) till they reach the given sleep time.

so, if you set 10 concurrent manager processes ( for ex: 10 standard manager processes) and if you set 20 seconds for the sleep time look whats happens;

in second 1- > std mgr process 1 will check its queue (and start waiting for 20 seconds)
in second 2 -> std mgr process 2 will check its queue (and start waiting for 20 seconds)
in second 3 -> std mgr process 3 will check its queue (and start waiting for 20 seconds)

--> you see we checked the queues 3 times in 3 seconds..

in seconds 4 -> std mgr process 4 will check its queue (and start waiting for 20 seconds)
in seconds 5 -> std mgr process 5 will check its queue (and start waiting for 20 seconds)

..
...
....
in seconds 10 -> std mgr process 10 will check its queue (and start waiting for 20 seconds)  at this time std manager above has 10 seconds left to check its queue again.

in second 20 -> std mgr process 1 will check its queue (and start waiting for 20 seconds -- this is the second time for process 1)
in second 21 -> std mgr process 2 will check its queue (and start waiting for 20 seconds -- this is the second time for process 1)


You see, we check the queues very often.. If you do the math, you ll see we check the queues almost 30 times. Checking the queues 30 times in a minute with 10 managers to supply max 20 second wait? No , it is not necessary.

The formula and the logic to calculate the sleep times is as follows;

Sleep time= "# of process" * (1-Avg Utilization percentage ) * Avg Time(seconds) for a request allowed to be pending

#of process= 30
suppose we have only 1 hour peak time , 1 hour in a day whic we have important conc requests
Suppose these concurrent requests are critical and they should be wait max 10 seconds

30 * (1-1/24)*10 =  280  -> sleep time should be 280 seconds. (approximately.)

So, as we have 30 processes and they are idle most of the time, they will check the  queues in every 280/30=  9 seconds. (in average)

If this formula calculates a sleep time lower than the  "Avg Time(seconds) for a request allowed to be pending", then it means "the concurrent manager process count is not enough", the solution should be adding concurrent manager processes.

[satish]

Highly appreciated.Thank you very much.

Hope everyone who reads his updates will now have clear idea about sleep seconds.