应用节点访问数据库缓慢

三菜

环境
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

xifenfei

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
HGMSUP	165868252	hgmsup	1	10.2.0.1.0	NO	ZHUBAO-246

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	16	30-Jan-15 10:00:54	30	9.4
End Snap:	17	30-Jan-15 11:00:34	82	3.6
Elapsed:		59.65 (mins)
DB Time:		0.32 (mins)

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
CPU time		17		88.5
control file sequential read	2,344	3	1	15.9	System I/O
SQL*Net more data from client	14	1	92	6.8	Network
os thread startup	55	0	6	1.6	Concurrency
control file parallel write	1,193	0	0	1.3	System I/O

你这里本身db time 小，而且control file sequential read 本身就很小，所以基本上可以忽略该等待，慢，不用纠结在这个上面
从awr里面看，数据库本身无负载，瓶颈感觉不在db上，也没有明显bug

三菜

谢谢飞哥指点，确实一针见血。