RE: RESOURCES ARE NOT FREED WHEN A SESSION DIES ABNORMALLY.

Asif,

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 server.
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

http://blogs.technet.com/networking/archive/2007/11/04/sp2-scalable-netw orking-pack-connectivity-issues.aspx

	Refers to netstat -t - will show if connections are offloaded.
	Also Broadcom Control Suite "Resource Allocations" tab will show

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         

http://blogs.technet.com/sbs/archive/2007/04/24/common-networking-issues -after-applying-windows-server-2003-sp2-on-sbs.aspx

	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 not

Many details on TCP chimney -
http://download.microsoft.com/download/5/b/5/5b5bec17-ea71-4653-9539-204 a672f11cf/TCP_Chimney.doc         

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.

Regards,
Bruce Reardon

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.

Regards
Asif Momen

NOTICE
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.

--
http://www.freelists.org/webpage/oracle-l