应用节点访问缓慢
环境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.
Listener的配置刚刚更改，因为只有一个IP和端口，所以建了一个listener监听两个实例，动态静态都启用了。 listener的日志也并未发现问题，如需要可上传。
sqlnet.ora我也在加一些条件再试试。
请问下各位前辈，我的思路和处理是否正确？请指导
应用端执行sql trace 部分节选。
select employee0_.nemployeid as nemployeid118_, employee0_.nemployeetype as
nemploye2_118_, employee0_.NORGAID as NORGAID118_, employee0_.sadder as
sadder118_, employee0_.saddtime as saddtime118_, employee0_.semployeecode
as semploye5_118_, employee0_.semployeename as semploye6_118_,
employee0_.sremark as sremark118_, employee0_.ssex as ssex118_,
employee0_.stelphone as stelphone118_, employee0_.supdater as supdater118_,
employee0_.supdatetime as supdate11_118_
from
XTGL_EMPLOYEE employee0_, xtgl_user user1_ where employee0_.nemployeid=
user1_.NEMPLOYEID and user1_.susername='snqwangxiaoyan'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 10 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 10 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS (cr=10 pr=0 pw=0 time=90 us)
1 TABLE ACCESS FULL XTGL_USER (cr=8 pr=0 pw=0 time=103 us)
1 TABLE ACCESS BY INDEX ROWID XTGL_EMPLOYEE (cr=2 pr=0 pw=0 time=21 us)
1 INDEX UNIQUE SCAN SYS_C005394 (cr=1 pr=0 pw=0 time=10 us)(object id 51568)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.32 0.55 给出原始的2个 10046 raw trace 感谢关注 Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.32 0.55
给出ping log，给出ftp 一个500MB文件的 FTP LOG 刘大稍等一会，客户不在，好了我在群里at您，不好意思，让您久等 刘大，因为网闸的限制，只能从应用端连数据库端，端口只开了1521，只有ping log 。 没法做sftp/ftp
问题解决，如刘大所说，我改ping了大包，超过25152就ping不通了。是网闸问题，已和客户联系。
页:
[1]