I am going through same experience as you have posted in blogs "Concurrent Request keeps on running". I am facing issue over a weekend and had to terminate manually from front end (Terminating).
As given in your blog it suggest to update FND_CONCURRENT_REQUEST . If i update will it send a mail as it has to send mail (Customized Alert Check Periodic Alert).(for eg birthday/anniversary alert are designed to send mail send to manager).
Please suggest me as i am having this serious case in prod. Similarly i scheduled same concurrent request with same set up in dev where it completed without any delay and error.
Dawaa Tamangg: More analysis is needed on why the requests are running for longer time. Updating the fnd_concurrent_request in my blog was written for those requests which were not performing any action and were creating high load on servers , which then finally needed to be terminated. In you case , please check why these requests are running for longer time:
1: Check the database SID for the request and based on the SID , check gv$session to get more details on the session. 2. Based on sql_id , check what sql is running in the database. 3. Based on the sql running , analyse if the tables/objects has stats run on it and if you have proper indexing on those tables.
4. Perform a health check on the server on which these requests are running.
5. Also compare the execution Plan.
6. If these requests are completing on few days and not on few other days, run a AWR report and analyse what caused the delay during those days. Compare it with those days were it ran successfully.
Customized Alert Check Periodic Alert : These are Alerts setup through alert manager which runs as a concurrent job. You can define these to send emails after a certain course of action.
1.. Enter value for request_id: 2057856 old 1: SELECT a.request_id, d.sid, d.serial# , c.SPID FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v$process c, v$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid =b.oracle_process_id AND b.session_id=d.AUDSID AND a.request_id = &Request_ID AND a.phase_code ='R' new 1: SELECT a.request_id, d.sid, d.serial# , c.SPID FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v$process c, v$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid =b.oracle_process_id AND b.session_id=d.AUDSID AND a.request_id = 2057856 AND a.phase_code ='R'
Dawaa: Looking at the details provided , there are couple of things to be analysed here :
1. Since there is no sql_id associated, most likely the request isnt performing any action. select program,module,machine,status,sid,serial# from v$session where sid='';
Please analyse more on what database conenctionwas amde and what was the progress on it. Did it really start performing anything or not?
2. If the request has performed all the action , then it will take a ahwile writing all log and out messages AND thats when you will not find any sql_id associated.
Make sure you have enough space on log/out/temp directories. Check the tablespace for any space issues/ Make sure I/O on this servers is good.
1.Did you get a chance to perform server health check. 2. Run AWR report and find out what is different on those days when its completing fine and when it is not. 3. Also check alert log for any database related issues.
Enable debug and run the request. Analyse the trace file tkprof and see what is causing the bottleneck.
This blog very easily understandable. Thanks for sharing such an informative post with us. This is a nice post in an interesting line of content. Oracle Apps DBA Training in Bangalore
Hi sandesh, How can I forcible send a user request to particular node in a multi node setup. PCP enabled.
ReplyDeleteHi Sandesh Sir
ReplyDeleteI am going through same experience as you have posted in blogs "Concurrent Request keeps on running". I am facing issue over a weekend and had to terminate manually from front end (Terminating).
eg:- Monday - completed
Tuesday - Running (Terminated)
Wednesday - Running
Thursday - Completed
Friday - Running (Terminated)
Saturday - Running (Terminated)
Sunday - Completed
As given in your blog it suggest to update FND_CONCURRENT_REQUEST . If i update will it send a mail as it has to send mail (Customized Alert Check Periodic Alert).(for eg birthday/anniversary alert are designed to send mail send to manager).
Please suggest me as i am having this serious case in prod. Similarly i scheduled same concurrent request with same set up in dev where it completed without any delay and error.
Dawaa Tamangg: More analysis is needed on why the requests are running for longer time. Updating the fnd_concurrent_request in my blog was written for those requests which were not performing any action and were creating high load on servers , which then finally needed to be terminated.
ReplyDeleteIn you case , please check why these requests are running for longer time:
1: Check the database SID for the request and based on the SID , check gv$session to get more details on the session.
2. Based on sql_id , check what sql is running in the database.
3. Based on the sql running , analyse if the tables/objects has stats run on it and if you have proper indexing on those tables.
4. Perform a health check on the server on which these requests are running.
5. Also compare the execution Plan.
6. If these requests are completing on few days and not on few other days, run a AWR report and analyse what caused the delay during those days. Compare it with those days were it ran successfully.
Customized Alert Check Periodic Alert : These are Alerts setup through alert manager which runs as a concurrent job. You can define these to send emails after a certain course of action.
Thank you.
Regards
Sandesh Achar
This comment has been removed by the author.
ReplyDelete1.. Enter value for request_id: 2057856
ReplyDeleteold 1: SELECT a.request_id, d.sid, d.serial# , c.SPID FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v$process c, v$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid =b.oracle_process_id AND b.session_id=d.AUDSID AND a.request_id = &Request_ID AND a.phase_code ='R'
new 1: SELECT a.request_id, d.sid, d.serial# , c.SPID FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v$process c, v$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid =b.oracle_process_id AND b.session_id=d.AUDSID AND a.request_id = 2057856 AND a.phase_code ='R'
REQUEST_ID SID SERIAL# SPID
---------- ---------- ---------- ------------------------------------------------------------------------
2057856 419 63 14515
2.. [oraprod@oser60001a archive]$ ps -ef | grep -i 14515
oraprod 9916 9796 0 17:22 pts/0 00:00:00 grep -i 14515
oraprod 14515 1 0 Jan30 ? 00:00:00 oraclePROD (LOCAL=NO)
3.. SQL> Select status,sql_id from v$session where SID = 419;
STATUS SQL_ID
-------- -------------
INACTIVE
SQL>
4.. SQL> select TYPE,LMODE from V$LOCK where SID = 419;
TY LMODE
-- ----------
AE 4
UL 6
simialry i tried to generate Trace file and tkprof for the same. Except couple of lines there was nothing in it.
Similary I executed same Alert (Check Periodic alert with same set up) in dev instance. It completed without any errror.
All Check periodic alert are assign to Standard Concurrent manager.
Database :-11.2.0.3 (Non Rac)
EBS:-12.1.3
Dawaa: Looking at the details provided , there are couple of things to be analysed here :
ReplyDelete1. Since there is no sql_id associated, most likely the request isnt performing any action.
select program,module,machine,status,sid,serial# from v$session where sid='';
Please analyse more on what database conenctionwas amde and what was the progress on it. Did it really start performing anything or not?
2. If the request has performed all the action , then it will take a ahwile writing all log and out messages AND thats when you will not find any sql_id associated.
Make sure you have enough space on log/out/temp directories.
Check the tablespace for any space issues/ Make sure I/O on this servers is good.
Thanks
Regards
Sandesh Achar
Dawaa:
ReplyDelete1.Did you get a chance to perform server health check.
2. Run AWR report and find out what is different on those days when its completing fine and when it is not.
3. Also check alert log for any database related issues.
Enable debug and run the request. Analyse the trace file tkprof and see what is causing the bottleneck.
This comment has been removed by the author.
ReplyDeleteHello sandesh,
ReplyDeleteDo you have any document for configuration of Oracle EBS in mobile, please share some document.
Regards,
Qadar
mohdqadermohiuddin@gmail.com
Hello sandesh,
ReplyDeleteDo you have any document for configuration of Oracle EBS in mobile, please share some document.
Regards,
Qadar
mohdqadermohiuddin@gmail.com
See this. http://www.algarytm.com/wp-content/uploads/2017/06/End-to-End-How-to-Guide-for-ionic-Mobile-apps-MCS-and-Oracle-EBS.pdf
ReplyDeleteHi, See this a free demo class on Oracle Apps DBA Training
ReplyDeleteHi! Any one here DBA expert???
ReplyDeleteThis blog very easily understandable. Thanks for sharing such an informative post with us. This is a nice post in an interesting line of content.
ReplyDeleteOracle Apps DBA Training in Bangalore
Appreciate you for sharing. power bi training
ReplyDeletethank you for sharing. Best Oracle Fusion HCM Online Training
ReplyDelete