服务器oracle进程满,分析案例：应用服务器W3WP进程CPU持续超过百分之九十（Oracle客户端Bug）...

问题描述：

项目反馈应用负载的其中一台服务器业务操作的响应非常慢，登录该服务器发现W3WP进程CPU持续超过90%，哪怕在业务低峰期也是如此？远程查看后发现该应用服务器承载的请求确实很低，why？？？

原因分析：

抓取w3wp进程的dump发现，正在运行的线程都没有我们系统的堆栈代码。并且长时间运行的工作线程的栈顶基本都是Oracle.DataAccess.Client.OracleTuningAgent.DoScan() ----》Oracle.DataAccess.Client.OracleTuningAgent.TuningFunction()相关的调用。

根据Oracle.DataAccess.Client.OracleTuningAgent.TuningFunction()等代码提示查阅资料，发现貌似和Oracle服务器及客户端版本有关系，经对比该应用和其他应用服务器，发现有问题的应用服务器上的Oracle客户端版本确实比较低(11.2.0.1)，其他响应正常应用服务器的Oracle客户端版本为11.2.0.3。联系现场升级Oracle客户端版本后问题消失。

This has been fixed in 11.2.0.2 and in Patch 9966926 ORACLE 11G 11.2.0.1 PATCH 5 BUG FOR WINDOWS (64-BIT AMD64 AND INTEL EM64T).

Or WORKAROUND: is to disable self tuning by adding "Self Tuning=false" to the connection string.

dump日志如下：

0:000> !runaway

User Mode Time

Thread Time

60:13fdc 2 days 19:54:05.922

62:3314 2 days 19:25:29.443

64:e0c 2 days 16:46:44.319

75:ef2c 0 days 2:06:17.155

67:198ac 0 days 1:54:59.175

76:1afe4 0 days 1:41:53.460

20:19f0c 0 days 0:04:41.145OS Thread Id: 0x13fdc (60)

Child SP IP Call Site

0000000030c9dcc8 00000000779cd3fa[InlinedCallFrame: 0000000030c9dcc8] System.Exception.GetMessageFromNativeResources(ExceptionMessageKind,System.Runtime.CompilerServices.StringHandleOnStack)

0000000030c9dcc8 000007fef86e5aca[InlinedCallFrame: 0000000030c9dcc8] System.Exception.GetMessageFromNativeResources(ExceptionMessageKind,System.Runtime.CompilerServices.StringHandleOnStack)

0000000030c9dca0 000007fef86e5aca *** WARNING: Unable to verify checksum for mscorlib.ni.dll

System.Threading.ThreadAbortException..ctor()

0000000030c9df40 000007fef974a7f3[GCFrame: 0000000030c9df40]0000000030c9dfc8 000007fef974a7f3[GCFrame: 0000000030c9dfc8]0000000030c9e130 000007fef974a7f3[GCFrame: 0000000030c9e130]0000000030c9e5c8 000007fef974a7f3[HelperMethodFrame: 0000000030c9e5c8]0000000030c9e6f0 000007fe9a1774fa *** ERROR: Module load completed but symbols could not be loaded for Oracle.DataAccess.dll

Oracle.DataAccess.Client.OracleTuningAgent.DoScan()

0000000030c9e740 000007fe9a1688e5 Oracle.DataAccess.Client.OracleTuningAgent.TuningFunction()

0000000030c9e7c0 000007fef86339a5 System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object,Boolean)

0000000030c9e920 000007fef8633719 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object,Boolean)

0000000030c9e950 000007fef86336f7 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback,System.Object)

0000000030c9e9a0 000007fef864adc1 System.Threading.ThreadHelper.ThreadStart()

0000000030c9ecb8 000007fef974a7f3[GCFrame: 0000000030c9ecb8]0000000030c9f008 000007fef974a7f3[DebuggerU2MCatchHandlerFrame: 0000000030c9f008]0000000030c9f198 000007fef974a7f3[ContextTransitionFrame: 0000000030c9f198]0000000030c9f3b8 000007fef974a7f3[DebuggerU2MCatchHandlerFrame: 0000000030c9f3b8]OS Thread Id: 0x20bd4 (61)

Child SP IP Call Site

00000000031bdc58 00000000779cd96a[HelperMethodFrame: 00000000031bdc58]System.AppDomain.nUnload(Int32)

00000000031bdd50 000007fef8e3e892 System.AppDomain.Unload(System.AppDomain)

00000000031bddb0 000007fef4b0c4a2 *** WARNING: Unable to verify checksum for System.Web.ni.dll

System.Web.HttpRuntime.ReleaseResourcesAndUnloadAppDomain(System.Object)

00000000031bde30 000007fef86339a5 System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object,Boolean)

00000000031bdf90 000007fef8633719 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object,Boolean)

00000000031bdfc0 000007fef866216f System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem()

00000000031be010 000007fef866136a System.Threading.ThreadPoolWorkQueue.Dispatch()

00000000031be578 000007fef974a7f3[DebuggerU2MCatchHandlerFrame: 00000000031be578]00000000031be708 000007fef974a7f3[ContextTransitionFrame: 00000000031be708]00000000031be928 000007fef974a7f3[DebuggerU2MCatchHandlerFrame: 00000000031be928]OS Thread Id:0x3314 (62)

Child SP IP Call Site

0000000003bee5b8 00000000779cd3fa[HelperMethodFrame: 0000000003bee5b8]0000000003bee6e0 000007fe9a1774fa Oracle.DataAccess.Client.OracleTuningAgent.DoScan()

0000000003bee730 000007fe9a1688e5 Oracle.DataAccess.Client.OracleTuningAgent.TuningFunction()

0000000003bee7b0 000007fef86339a5 System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object,Boolean)

0000000003bee910 000007fef8633719 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object,Boolean)

0000000003bee940 000007fef86336f7 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback,System.Object)

0000000003bee990 000007fef864adc1 System.Threading.ThreadHelper.ThreadStart()

0000000003beeca8 000007fef974a7f3[GCFrame: 0000000003beeca8]0000000003beeff8 000007fef974a7f3[DebuggerU2MCatchHandlerFrame: 0000000003beeff8]0000000003bef188 000007fef974a7f3[ContextTransitionFrame: 0000000003bef188]0000000003bef3a8 000007fef974a7f3[DebuggerU2MCatchHandlerFrame: 0000000003bef3a8]OS Thread Id: 0xa130 (63)

Child SP IP Call Site

0000000003ecdc48 00000000779cd96a[HelperMethodFrame: 0000000003ecdc48]System.AppDomain.nUnload(Int32)

0000000003ecdd40 000007fef8e3e892 System.AppDomain.Unload(System.AppDomain)

0000000003ecdda0 000007fef4b0c4a2 System.Web.HttpRuntime.ReleaseResourcesAndUnloadAppDomain(System.Object)

0000000003ecde20 000007fef86339a5 System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object,Boolean)

0000000003ecdf80 000007fef8633719 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object,Boolean)

0000000003ecdfb0 000007fef866216f System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem()

0000000003ece000 000007fef866136a System.Threading.ThreadPoolWorkQueue.Dispatch()

0000000003ece568 000007fef974a7f3[DebuggerU2MCatchHandlerFrame: 0000000003ece568]0000000003ece6f8 000007fef974a7f3[ContextTransitionFrame: 0000000003ece6f8]0000000003ece918 000007fef974a7f3[DebuggerU2MCatchHandlerFrame: 0000000003ece918]OS Thread Id: 0xe0c (64)

Child SP IP Call Site000000000528e918 00000000779cd3fa [HelperMethodFrame: 000000000528e918]000000000528ea40 000007fe9a1774fa Oracle.DataAccess.Client.OracleTuningAgent.DoScan()

000000000528ea90 000007fe9a1688e5 Oracle.DataAccess.Client.OracleTuningAgent.TuningFunction()

000000000528eb10 000007fef86339a5 System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object,Boolean)

000000000528ec70 000007fef8633719 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object,Boolean)

000000000528eca0 000007fef86336f7 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback,System.Object)

000000000528ecf0 000007fef864adc1 System.Threading.ThreadHelper.ThreadStart()

000000000528f008 000007fef974a7f3[GCFrame: 000000000528f008]000000000528f358 000007fef974a7f3[DebuggerU2MCatchHandlerFrame: 000000000528f358]000000000528f4e8 000007fef974a7f3[ContextTransitionFrame: 000000000528f4e8]000000000528f708 000007fef974a7f3[DebuggerU2MCatchHandlerFrame: 000000000528f708]

其他注意事项：

经查触发这个bug的代码时BulkCopy，使用Oracle的BulkCopy还是要慎用。

参考信息：

原文：http://www.cnblogs.com/zhaoguan_wang/p/5851756.html