RE: RESOURCES ARE NOT FREED WHEN A SESSION DIES ABNORMALLY.
- From: "Reardon, Bruce \(RTABBAY\)" <Bruce.Reardon@xxxxxxxxxxxx>
- To: <asif_oracle@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 4 Aug 2008 08:38:28 +1000
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
if 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
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
disabled in NIC)
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@xxxxxxxxx>
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
