之前有网友在t.askmac.cn提问版上提问关于远程PL/SQL Developer连接Oracle数据库发现查询v$session会hang住的问题，而本地连接(local connection)则可以正常查询。

 

分析了问题相关的10046 trace和MOS上的一些案例发现可能是由Bug 9087501: SELECT FROM V$SESSION AND SOME OTHER DICTIONARY VIEWS HANG FROM A REMOTE CLIENT      所引起， 该Bug Note 还指出在Windows Vista上存在因为操作系统默认启用了TCP/IP Auto-tuning(TCP Window Scaling)从而可能导致使用如PL/SQL Developer这样的客户端登录数据库后某些查询Hang住的问题。 而在其他Windows版本中(如Win 7)没有默认启用该TCP/IP Auto-tuning特性，所以默认不存在该问题； 看起来Vista还真是一个失败的作品。

 

该TCP Window Scaling(接收窗口自动调谐级别)用以优化TCP网络的吞吐量，更多信息参考一下文本:

 

TCP Window Scaling
For larger window sizes to accommodate high-speed transmission paths, RFC 1323 (ietf.org/rfc/rfc1323.txt) defines window scaling that allows a receiver to advertise a window size larger than 65,535 bytes. A TCP Window Scale option includes a window scaling factor that, when combined with the 16-bit Window field in the TCP header, can increase the receive window size to a maximum of approximately 1GB. The Window Scale option is sent only in synchronize (SYN) segments during the connection establishment process. Both TCP peers can indicate different window scaling factors to use for their receive window sizes. By allowing a sender to send more data on a connection, TCP window scaling allows TCP nodes to better utilize some types of transmission paths with high BDPs.
Although the receive window size is important for TCP throughput, another important factor for determining the optimal TCP throughput is how fast the application retrieves the accumulated data in the receive window (the application retrieve rate). If the application does not retrieve the data, the receive window can begin to fill, causing the receiver to advertise a smaller current window size. In the extreme case, the entire maximum receive window is filled, causing the receiver to advertise a window size of 0 bytes. In this case, the sender must stop sending data until the receive window has been cleared. Therefore, to optimize TCP throughput, the TCP receive window for a connection should be set to a value that reflects both the BDP of the connection’s transmission path and the application retrieve rate.
Even if you could correctly determine both the BDP and the application retrieve rate, they can change over time. The BDP rate can vary based on the congestion in the transmission path and the app retrieve rate can vary based on the number of connections on which the app is receiving data.

 

 

为了在Windows Vista上正常使用受此影响的网络客户端程序，我们需要手动禁用该TCP/IP Auto-tuning特性，步骤如下：

 

1. 点击开始->ALL Programs -> Accessories(附件) -> 右键以管理员身份运行Command Prompt(命令提示符)

 

2.  输入命令”netsh interface tcp show global”

 

C:\Windows\system32>netsh interface tcp show global
Querying active state...

TCP Global Parameters
----------------------------------------------
Receive-Side Scaling State          : enabled
Chimney Offload State               : automatic
NetDMA State                        : enabled
Direct Cache Acess (DCA)            : disabled
Receive Window Auto-Tuning Level    : normal
Add-On Congestion Control Provider  : none
ECN Capability                      : disabled
RFC 1323 Timestamps                 : disabled

 

若命令输出的Receive Window Auto-Tuning Level(接收窗口自动调谐级别)值 为normal则说明系统启用了Auto-Tuning特性。

 

3. 输入命令netsh interface tcp set global autotuninglevel=disabled 以禁用Auto-Tuning特性：

 

C:\Windows\system32>netsh interface tcp set global autotuninglevel=disabled
Ok.

C:\Windows\system32>netsh interface tcp show global
Querying active state...

TCP Global Parameters
----------------------------------------------
Receive-Side Scaling State          : enabled
Chimney Offload State               : automatic
NetDMA State                        : enabled
Direct Cache Acess (DCA)            : disabled
Receive Window Auto-Tuning Level    : disabled
Add-On Congestion Control Provider  : none
ECN Capability                      : disabled
RFC 1323 Timestamps                 : disabled

 

4.  重启计算机，以便设置生效(restart computer)

 

5. 如今后希望再次启用该autotuning特性则可以在命令行中输入”netsh interface tcp set global autotuninglevel=normal”命令，并重启计算机

 

C:\Windows\system32>netsh interface tcp set global autotuninglevel=normal
Ok.

C:\Windows\system32>netsh interface tcp show global
Querying active state...

TCP Global Parameters
----------------------------------------------
Receive-Side Scaling State          : enabled
Chimney Offload State               : automatic
NetDMA State                        : enabled
Direct Cache Acess (DCA)            : disabled
Receive Window Auto-Tuning Level    : normal
Add-On Congestion Control Provider  : none
ECN Capability                      : disabled
RFC 1323 Timestamps                 : disabled

 

Reference:
http://support.microsoft.com/kb/934430
http://technet.microsoft.com/en-us/magazine/2007.01.cableguy.aspx