应用节点访问数据库缓慢
环境Windows 2k3,raid 5, 硬盘5400转。 数据库和应用服务器之间有个网闸，仅开通1521 端口。
问题描述
从应用服务器B 向数据库服务器A ，通过SQLPLUS或pl sqldeveloper 跑任意 SQL 均会出现延迟现象。 如在A（也使用TNS方式登录）跑select需要0.3秒以内，到了B就需要1.2秒。
处理过程
先做了AWR report，见附件。发现有wait class 有network内容且较高。苦于缺乏证据，tnsping和ping均在10ms以内。
然后按刘大指导，做了10046 trace，对比了远端和本地，并未发现什么问题，只是远端出现了Misses in library cache during parse: 2。查看了AWR，觉得soft parse > 95%，应该不是parse问题。
AWR report top 5 里有control file sequential read ， MOS说是一个bug, 所以system io的瓶颈也无从下手。
Bug 8682160 - AWR snapshot causes many "control file sequential read" waits (文档 ID 8682160.8)
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 12.1
Versions confirmed as being affected
11.2.0.1
11.1.0.7
Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in
12.1.0.1 (Base Release)
11.2.0.2 (Server Patch Set)
11.2.0.1 Bundle Patch 3 for Exadata Database
11.1.0.7 Patch 46 on Windows Platforms
Symptoms:
Related To:
Waits for "control file sequential read"
Workload repository / reporting
Description
AWR snapshots could take a long time flushing one of the tables (WRH$_THREAD)
due to a bad plan.
Rediscovery Notes:
AWR snapshots take a lot of time flushing WRH$_THREAD, with many
'control file sequential read' waits and a poor plan with cartesian joins.
附件里是awr report 和 trace file
DB NameDB IdInstanceInst numReleaseRACHost
HGMSUP165868252hgmsup110.2.0.1.0NOZHUBAO-246
Snap IdSnap TimeSessionsCursors/Session
Begin Snap:1630-Jan-15 10:00:54309.4
End Snap:1730-Jan-15 11:00:34823.6
Elapsed: 59.65 (mins)
DB Time: 0.32 (mins)
EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class
CPU time1788.5
control file sequential read2,3443115.9System I/O
SQL*Net more data from client141926.8Network
os thread startup55061.6Concurrency
control file parallel write1,193001.3System I/O
你这里本身db time 小，而且control file sequential read 本身就很小，所以基本上可以忽略该等待，慢，不用纠结在这个上面
从awr里面看，数据库本身无负载，瓶颈感觉不在db上，也没有明显bug
谢谢飞哥指点，确实一针见血。
页:
[1]