There are multiple scenarios that cause this error. Check the following list of solutions:
Lets devide it into two scenarios where we get this error.
1.DB Connection issue :-
1. Verify there is no firewall or router setting which terminates connections which are active for longer than x minutes.
2. Verify there is no firewall or router setting which terminates connections which are idle for longer than x minutes.
3. Verify the Oracle configuration. Let the database send a packet every x minutes, so that the firewall, router, etc does not close the connection due to being 'idle'.
1. The Oracle documentation lists the following details for the sqlnet.ora parameter: SQLNET.EXPIRE_TIME
Purpose:
Use parameter SQLNET.EXPIRE_TIME to specify the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.
Limitations on using this terminated connection detection feature are:
- It is not allowed on bequeathed connections.
- Though very small, a probe packet generates additional traffic that may downgrade network performance.
- Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.
Default: 0
Minimum Value: 0
Recommended Value: 10
Example:
SQLNET.EXPIRE_TIME=10Perform the following test:
- Stop your Oracle database instance
- Sdit the $ORACLE_HOME/network/admin/sqlnet.ora file and add SQLNET.EXPIRE_TIME=3
- Restart the Oracle database instance
- Do a full connect with the Oracle WP driver via ODBCTest as SYSTEM/MANAGER
- Execute the following stored procedure: {call dbms_lock.sleep(3000)} (sleeps for 50 minutes). Adapt the value to your needs.
2. Check the Oracle setting 'connection idle time limit.
- This can be set in the Oracle Enterprise Manager Console.
- Select the database you are using and click down to the "security\profile\\idle time".
- Setting this to something lower than 60 minutes will cause the user to be disconnected after that XX minutes of inactivity.
2. Code Bug :-
Yes, There could be a code bug which is causing the issue. you just need to monitor the session to get the sql_id which is causing wait event to get fired again and again.
Use the below query to get the sql details and everything related to the concurrent program:-
SELECT fcr.request_id "Req ID",
parent_request_id "Parent",
DECODE (fcpt.user_concurrent_program_name,
'Check Periodic Alert', 'Alert: ' || fcr.description,
fcpt.user_concurrent_program_name)
"Name",
r.responsibility_key "Responsibility",
gvs.module "Module",
gvs.sql_id "SQL ID",
gvs.event "Wait event",
fcr.priority "Priority",
fcr.requested_start_date "Requested Start",
fcr.actual_start_date "Actual Start",
fcr.actual_completion_date "Actual End",
DECODE (fcr.status_code,
'C', 'Complete',
'E', 'Error',
'R', 'Running',
'I', 'Pending',
'G', 'Warning',
'Q', 'Scheduled',
'D', 'Cancelled',
'W', 'Waiting',
'X', 'Terminated',
status_code)
"Status",
fcr.phase_code,
fcr.status_code,
DECODE (fcr.hold_flag, 'Y', 'Hold', 'Normal') "Hold?",
ROUND (
( DECODE (fcr.actual_completion_date,
NULL, SYSDATE,
fcr.actual_completion_date)
- actual_start_date)
* 24
* 60,
2)
"Time(m)",
ROUND (
( DECODE (fcr.actual_completion_date,
NULL, SYSDATE,
fcr.actual_completion_date)
- actual_start_date)
* 24
* 60
* 60,
2)
"Time(s)",
argument_text "Arguments",
ROUND (
( DECODE (fcr.actual_start_date,
NULL, SYSDATE,
fcr.actual_start_date)
- requested_start_date)
* 24
* 60
* 60,
1)
"Start Delay (s)",
r.responsibility_key "Responsibility",
fu.user_name "User",
gvs.inst_id "Inst",
gvs.sid "SID",
gvs.serial# "Serial#",
gvs.sql_id "SQL ID",
gvs.event "Wait event",
gvs.status "S.Status",
gvs.service_name "Service",
gvs.sql_child_number "SQL Child",
gvs.last_call_et "Call time",
connstr1 "Conn Str",
gvs.module "Module",
argument_text "Arguments",
fcr.priority_request_id "Priority Req ID",
fcr.logfile_node_name,
fcr.oracle_session_id,
fcr.oracle_process_id spid,
fcr.concurrent_program_id,
fcr.status_code,
fcp.os_process_id applmgr_pid,
fu.user_id,
fcr.concurrent_program_id "Prog ID",
queue_id,
fcqt.user_concurrent_queue_name queue_name,
fcr.logfile_name,
fcr.enable_trace
FROM apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_user fu,
apps.fnd_responsibility r,
gv$session gvs,
apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_queues_tl fcqt,
apps.fnd_concurrent_processes fcp
WHERE fcpt.application_id = fcr.program_application_id
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcr.oracle_session_id = gvs.audsid(+)
AND r.responsibility_id = fcr.responsibility_id
AND fcr.requested_by = fu.user_id
AND fcr.controlling_manager = fcp.concurrent_process_id(+)
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id(+)
AND fcq.concurrent_queue_id = fcqt.concurrent_queue_id(+)
AND fcr.parent_request_id = :p_request_id
ORDER BY 10 DESC NULLS FIRST, 5 DESC;
Get the sql details from the above query for the running concurrent program and check the sql which is firing again and again and going in the wait event.
SELECT * FROM gv$sql where sql_id = :p_sql_id;
get the sql details and you will see the sql which is causing the performance issue and analyse the same.
No comments:
Post a Comment