用户用邮件发来一份alert.log,反馈说数据库连接出现严重的超时现象。具体表现在alert.log中高频率出现以下错

Wed Sep  5 09:36:47 2012

WARNING: inbound connection timed out (ORA-3136)

Wed Sep  5 09:36:52 2012

WARNING: inbound connection timed out (ORA-3136)

以上,在1s内发生74次。在MetaLink和Google上查询了该错误,有以下描述:

该现象常见于以下场景

  1. 用户使用错误的登录凭证登录,60s内没有关闭会话。导致超时。该错误不会引起数据库crash等错误

    如果不希望再看到此错误,在Oracle中禁用INBOUND_CONNECT_TIMEOUT参数即可

   a. 在listener.ora中添加INBOUND_CONNECT_TIMEOUT_listener_name=0

   b. 在服务器上的sqlnet.ora文件中添加SQLNET.INBOUND_CONNECT_TIMEOUT=

   c. 重启监

  1. 当数据库负载较重的时候,也会报这个错误

   请在OS或者OEM(Enterprise Managment)查看一下9:35-9:38这段时间的系统负载。

经检查,与第一种场景不符合。从场景二入手检查系统负载

more

抓取了问题发生时的AWR报告,发现以下几个疑点

            Top 5 Timed Events                                                                                                                               **Event**        **Waits**        **Time(s)**        **Avg Wait(ms)**        **% Total Call Time**        **Wait Class**                  **latch: library cache**        **232,061**        67,163        289        149.5        Concurrency                  CPU time                 1,625                 3.6                           latch: shared pool        7,246        348        48        0.8        Concurrency                  latch free        1,714        312        182        0.7        Other                  db file scattered read        69,482        183        3        0.4        User I/O          

&#160

          Load Profile                                                                                  **Per Second**        **Per Transaction**                  Redo size:        263,664.42        394,051.19                  Logical reads:        32,092.33        47,962.56                  Block changes:        957.59        1,431.14                  Physical reads:        529.31        791.06                  Physical writes:        50.7        75.77                  User calls:        142.33        212.72                  Parses:        190.15        284.18                  **Hard parses:**        **10.76**        **16.08**                  Sorts:        67.61        101.04                  Logons:        0.1        0.14                  Executes:        274.43        410.14                  Transactions:        0.67                   

 

以上表格1,出现了latch:library cache出现了大量的Waits,可以推断是大量的语句未被解析,未离开Library Cache。

表格2,Hard Parses出现的频率达到10.76 Per Second,印证了数据库hang住是由于大量的硬解析耗尽CPU时间,而且Library Cache被未解析的语句占满,系统事务征用Library Cache不到,所以抛出了ORA-3136错误。

硬解析一般是系统开发人员不够专业,在编写SQL时少用变量,导致每一条SQL都被Oracle视为全新的语句重新解析。我们要做的就是揪出这些语句,帮助开发人员改正。

查询PGA中执行次数多的语句,揪出来扔给开发人员解决

SELECT sql_text "SQL",

count(*) ,

sum(executions) "TotExecs"

FROM v$sqlarea

WHERE executions < 5

GROUP BY sql_text

HAVING count(*) > 30

ORDER BY 2;

以下是查询结

          **SQL**        **COUNT(*)**        **TotExecs**                  select rowid, CompensateNo,Lflag,CaseNo,        202        449                  select rowid, ComCode,UserCode,GradeCode        210        495                  select rowid, CompensateNo,RiskCode,Poli        217        434                  SELECT * FROM PrpDprofit WHERE profitCo        231        615                  select rowid, RegistNo, RiskCode, Serial        242        664                  select rowid, FlowID,LogNo,ModelNo,NodeN        380        871                  select rowid, BusinessNo, SerialNo, Loss        1152        2334                  select rowid, repolicyno from resrecerti        22015        88060          

 

最后一行的SQL执行计数达到了令人咋舌的22015,无怪乎能把Power 7的小机都拖跨掉。

 

相关参考

参考链接:

盖国强 - Oracle10gR2 ORA-3136 错误解决

杨廷琨 - [Oracle10g的ORA-3136错误](http://yangtingkun.itpub.net/post/468/260531

程序员 - Latch闩锁统计信息