环境 linux +rac
连接工具pl sql 8.0.1
在 连接数据库之后,执行select * from v$session 一直处于查询状态,出不来结果
在sqlplus 下执行正常.
连接工具pl sql 8.0.1
在 连接数据库之后,执行select * from v$session 一直处于查询状态,出不来结果
在sqlplus 下执行正常.
PS:部分表(用户创建的表)是这种情况,但也有一部分是正常.
此问题 在askmaclean 中提问过
细作了一番实验,发现有几个特征
1、select * from t 查询不出数据,一直处于查询状态
但是把t的字段列举出几个,如select a,b,c from t 是可以查询出数据
或者 select * from t where rownum<10 也能查出数据,但也有表采取以上两种方式仍然查不出数据。
2、当plsql的客户端和数据库服务器在同一个网段时,则不出现此问题。
3、据开发人员介绍在元旦之前是没问题,在元旦期间,中间路由加了防火墙或者调整了网络,之后就出现了这种现象。
4、把tnsname.ora中的host 改为IP,结果还是一样。
5、数据库的两个用户都是这种情况
此问题应该与服务端无关,应该是客户端网络设置的问题。
(续)
这个问题在http://cqsztech.cqbayuan.com/bbs/dispbbs.asp?BoardID=4&ID=107&Page=1 中能找到答案
网关的防火墙是JUNIPER防火墙,在防火墙上取消JUNIPER防火墙应用层网关对SQL的拦截
Metalink ID 1293202.1
High Virtual Circuit Wait Running SQL Through a Juniper Firewall [ID 1293202.1]
修改时间 18-MAR-2011 类型 PROBLEM 状态 PUBLISHED
In this Document Symptoms Changes Cause Solution References
Applies to:
Oracle Net Services - Version: 9.2.0.1 to 11.2.0.2 - Release: 9.2 to 11.2
Information in this document applies to any platform.
Symptoms
From DBControl, High 'virtual circuit wait' is seen on a 'SELECT' SQL.
From ASH Report in 65 minutes elapsed time:
Top User Events
Event "Event Class" % Event "Avg Active Sessions"
virtual circuit wait Network 98.32 7.99
The database is experiencing low performance.
Changes
Firewall policy was implemented for security purposes.
Firewall Device: Juniper Firewall SSG-520 with Firmware Version 6.2.0 R4.0 .
Cause
"SQL" feature in Juniper Firewall Application Layer Gateway (ALG) is causing the hang. This feature is documented as a known issue in several other documents:
Document 1075432.1 - Archive Log Shipments Failing in Environment With Juniper Firewall (Doc ID Document 1068158.1 - Shared Server Connections Hung, Waiting on Virtual Circuit Status via Juniper Firewall
Solution
Disable "SQL" feature in Juniper Firewall.
Solution Step:
From the Admin Interface in Juniper Web Interface,
under Security -> Deep Inspection -> ALG (Application Layer Gateway),
Deselect the "SQL" feature (disable the feature).
The result is as in following graph: