RE: RESOURCES ARE NOT FREED WHEN A SESSION DIES ABNORMALLY.
Date: Mon, 4 Aug 2008 08:38:28 +1000
Exactly what OS are you using?
If you are using Windows 2003 (R2) with the Scalable Networking Pack
(SNP) enabled (such as SP2) then you may find you are running into a
problem with Window's keepalivetime function not working due to the TCP Offload Engine (TOE).
To see if you have offloaded network connections, run netstat -t on the
To see if this is the problem you could briefly (& dynamically) disable the Offload Chimney via "netsh int ip set chimney disable"
Note that disabling the Offload Chimney may reduce network file performance which may be restored by disabling RSS (Receive Side Scaling) and TCPA (TCP Auto tuning) - I initially found performance reductions of up to 22 - 43% in copy speed, depending on file size.
Note that turning off TOE may increase the CPU load on the server.
Some relevant information can be found at:
Whilst you can turn it off yourself, a hotfix has been released to turn SNP off - http://support.microsoft.com/kb/948496
Refers to netstat -t - will show if connections are offloaded. Also Broadcom Control Suite "Resource Allocations" tab will showif any are offloaded.
http://support.microsoft.com/kb/912222 - Summary of how Offload works, the various options and the registry keys
http://support.microsoft.com/kb/936594 - Summary of SNP issues
From 936594 above. Discusses may need to disable RSS and Task Offload in registry as well as RSS in the NIC. Has references to KB articles for these. netsh int ip show offload Shows which offload options are available to SNP (ie notdisabled in NIC)
Many details on TCP chimney -
http://support.microsoft.com/kb/942861 - Chimney offload problems with Broadcoms
<< Describes impact on SQL Server >> Registry params to alter if performance decreases
You may also be able to alter these functions at the NIC Driver level - eg via the Broadcom Control Suite.
This problem seems to be related to the Broadcom chipset, and on some servers, updating NIC firmware and drivers + BIOS may also resolve the problem.
If this is your problem, I could reproduce it just using psexec
(www.sysinternals.com) with no Oracle involved.
Naturally test all this on a test serve first.
Date: Fri, 1 Aug 2008 14:27:47 -0700 (PDT) From: Asif Momen <asif_oracle_at_yahoo.com>
Thanks to everybody for their replies.
Yes, the database is also on Windows box (I actually ran a query against V$VERSION). I have opened an SR with Oracle Support and they say they are unable to reproduce it on their test environment.
I know we can kill the waiting/blocking session using "ALTER SYSTEM" or using orakill, but in a highly transactional database, this is really not affordable.
This e-mail and any attachments are private and confidential and may contain privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed.