Oracle SQL: WARNING: Inbound Connection Timed Out (ORA-3136)

Updated May 19, 2018

I have Oracle 10g database. Database performance has been quite slow for the past days.

Below is part of my alert log. Was wondering if the select statement is causing the slowness? If it is, how can I rectify it?

Wed Jul 3 03:44:51 2013

SELECT /*pkg.CustAcct "CustAcct" ,
pkg.CustName "CustName",
pkg.SubKeyVal "SubKeyVal",
pkg.SubsNum "SubsNum",*/
pkg.PkgName "PkgName" ,

pkg.ServName "ServName",
pkg.PACKAGE "Package" ,
pkg.Service "Service",
pkg.Feat "Feature",

pkg.GLCode "GLCode" ,
pkg.TransType "TransType" ,
pkg.AccountSegment "AccountSegment"
/* pkg.CreditSegment "CreditSegment" ,
pkg.RevenueSegment "RevenueSegment",
pkg.TargetSeg "TargetSeg" ,

pkg.PayMode "PayMode",
pkg.ProdCat "ProdCat",
pkg.ProdLine "ProdLine",
pkg.ProdFam "ProdPlatform",
pkg.TechType "TechType" ,

pkg.AccessType "AccessType",
pkg.AccessMode "AccessMode" ,
pkg.AcccessMedium "AccessDevice",
pkg.Branch "Branch",

pkg. Branch_Prov "BranchProv",
pkg.Bus_Regions "BusRegions"*/

FROM
(SELECT b.cust_keyvalue AS CustAcct ,
b.first_name
||:"SYS_B_00"
||b.last_name AS CustName ,
b.keyvalue

AS SubKeyVal ,
b.msisdn_in_use AS SubsNum ,

/*CASE
WHEN
Wed Jul 3 04:14:28 2013
Thread 1 advanced to log sequence 249304 (LGWR switch)

Current log# 1 seq# 249304 mem# 0: /vol2/logs/redo1c.log
Current log# 1 seq# 249304 mem# 1: /home/app/oracle/oradata/ABS/redo1b.log
Wed
Jul 3 06:01:03 2013
Thread 1 advanced to log sequence 249305 (LGWR switch)
Current log# 2 seq# 249305 mem# 0: /vol2/logs/redo2c.log
Current log# 2 seq# 249305 mem# 1: /home/app/oracle/oradata/ABS/redo2b.log
Wed Jul 3 07:50:58 2013
Thread 1 advanced to log sequence 249306 (LGWR switch)
Current log# 3 seq# 249306 mem# 0: /vol2/logs/redo3c.log
Current log# 3 seq# 249306 mem# 1: /home/app/oracle/oradata/ABS/redo3b.log
Wed Jul 3 09:14:59 2013
Thread 1 advanced to log sequence 249307 (LGWR switch)
Current log# 4 seq# 249307 mem# 0: /home/app/oracle/oradata/ABS/redo4a.log
Current log# 4 seq# 249307 mem# 1: /vol2/logs/redo4c.log
Wed Jul 3 09:57:04 2013
Thread 1 advanced to log sequence 249308 (LGWR switch)
Current log# 1 seq# 249308 mem# 0: /vol2/logs/redo1c.log
Current log# 1 seq# 249308 mem# 1: /home/app/oracle/oradata/ABS/redo1b.log
Wed Jul 3 10:14:27 2013
Thread 1 advanced to log sequence 249309 (LGWR switch)
Current log# 2 seq# 249309 mem# 0: /vol2/logs/redo2c.log
Current log# 2 seq# 249309 mem# 1: /home/app/oracle/oradata/ABS/redo2b.log
Wed Jul 3 10:32:34 2013
Thread 1 advanced to log sequence 249310 (LGWR switch)
Current log# 3 seq# 249310 mem# 0: /vol2/logs/redo3c.log
Current log# 3 seq# 249310 mem# 1: /home/app/oracle/oradata/ABS/redo3b.log
Wed Jul 3 10:42:46 2013
WARNING: inbound connection timed out (ORA-3136)
Wed Jul 3 10:47:11 2013
Thread 1 advanced to log sequence 249311 (LGWR switch)
Current log# 4 seq# 249311 mem# 0: /home/app/oracle/oradata/ABS/redo4a.log
Current log# 4 seq# 249311 mem# 1: /vol2/logs/redo4c.log
Wed Jul 3 11:04:44 2013
Thread 1 advanced to log sequence 249312 (LGWR switch)
Current log# 1 seq# 249312 mem# 0: /vol2/logs/redo1c.log
Current log# 1 seq# 249312 mem# 1: /home/app/oracle/oradata/ABS/redo1b.log
Wed Jul 3 11:26:28 2013
Thread 1 advanced to log sequence 249313 (LGWR switch)
Current log# 2 seq# 249313 mem# 0: /vol2/logs/redo2c.log
Current log# 2 seq# 249313 mem# 1: /home/app/oracle/oradata/ABS/redo2b.log

Comments

09 Jul 2013 11:34 am Sushma
Hi,
I am just wondering how you connected the WARNING ORA-3136, and this SELECT quoted in the post??

Unless you have CLEAR and definite evidence of that query causing the performance issue, then DO NOT waste time on concentrating on the wrong area.

If you are getting a LOT of the inbound connection timeouts then DO look at what clients connect into the DB, and if there is some application layer (using JDBC for instance, or some middle-tier running some Oracle application, e.g. HRMS, or other Business Intelligence layer, or Discoverer etc) that could be at fault, or some network related issue causing it.

If you feel that there IS some underlying performance issue, the try to get a better idea where the bottleneck is. I would suggest you deploy 10gagent, the EM console, and run Oracle Enterprise Manager to see what is going on. If you are licensed for the performance packs, the you should be able to run ASH reports and snapshots to compare things, and also look at the "top usage SQL" statements, and home in on the ones that will give the most improvement. OEM will also show if the I/O or CPU is the bottleneck, if used wisely.

Do NOT change anything unless you have some before and after snapshot to see if it makes a difference.

It is always possible that if the DB system is undersized as regards memory, and is using a lot of SWAP, then this might affect the way that the system is able to respond do the TCP comms layer, but that would be unusual I think.

The fact that it is an INBOUND timeout might point to a sluggish system, but it might be some other factor (e.g. looping Java processes hogging the system in some way).

If you are NOT the DBA , then DO liaise with him AND the system administrator, to work together to get more information as to what is going on, and to find where best to concentrate the diagnostic and testing effort.

09 Jul 2013 11:37 am Sushma
Hi,
I forgot to say, if you do (from the Oracle account, with a good environment, with correct $ORACLE_HOME and $ORACLE_SID):
"lsnrctl status LISTENER" (or whatever your listener name is) this should echo back the path (and filename) of the listener log file.

If you KNOW an exact time when you get one of these 3136 errors, try to find the area in that log file for a couple of minutes before and after that error time, so see if that log data might show any more clue to aid your problem diagnosis, and get you a bit closer to the problem area.

The later levels (11g) have this output as a "log.xml" file (off a "diag" directory structure, then another 4 levels down), e.g.:
/MYORACLE_APPS/oracle/OraHome11g/log/diag/tnslsnr/M Y_SID/listener/alert/log.xml

Equivalent to:
${ORACLE_HOME}/log/diag/tnslsnr/${ORACLE_SID}/listen er/alert/log.xml