Devart Forums

Hi,
We have a 3-tier application: client - asp.net web service - Oracle database. Sometimes our web service starts to use 99-100% CPU and even when everybody stops to use it, it still stuck at 100% CPU.

Web service is developed in VS2008 with framework 2.0
Our service had IIS6.0 / client has IIS7.5 (both has the same problem)
Web service is compiled in release mode

Steps to recreate problem:
4-6 people start to call the same several procedures again and again. Methods don't contain any loops, just several SQL queries which use UniConnection, UniDataAdapters and UniCommand (version 2.5.7.0). CPU starts to jump to 100% but then eventually goes down. After 5-10 minutes CPU usage stuck at 100% and applications starts to respond very slow. If another user will try to call one of the methods again, web service log shows that request is received, data received from the database, but then it takes time for the web service return data set to the client. After data is returned, CPU went down to 80%, then jumps back to 100% and stays there.

We tried:
1. Multiple working processes. Result - if one process stuck then other processes doesn't have any CPU to use.
2. Limit dataset - the same problem

Please give some advice what we can do.

gala_l wrote:4-6 people start to call the same several procedures again and again.

Could you please Send us the code of these procedures and the corresponding DDL/DML scripts.
Also please specify the following:
1. The mode you're using (direct or OCI).
2. The Oracle Server version.
3. Does the issue occur with particular procedures orwith any ones?

1. #region WebMethod - GetWellByLocation
[WebMethod(Description = "This Method returns hierarchical tables for Well Navigator using search by location")]
public byte[] GetWellsByLoc(string WellAliasTypeId, string MaskId, string DisplayFieldId,
string SortFieldId, string WellList, string SurveySystemId, string UserId)
{
DataSet_Hierarchy dsHierarchy = new DataSet_Hierarchy();

using (UniConnection connDB = CreateUserSession(UserId))
{
try
{
// Fill Wells Hierarchy
string cmdSelectWellsString = @"select '0' as parent_id, wells.* from
table(MANAGE_WELL_DISPLAY.GET_WELLS_BY_LOC(@WellAliasTypeId,
@MaskId, @DisplayFieldId, @SortFieldId, 0, @SurveySystemId)) wells";
if (WellList != "")
cmdSelectWellsString += @" union
select '0' as parent_id, wells.* from
table(MANAGE_WELL_DISPLAY.GET_WELLS_BY_LOC(@WellAliasTypeId1,
'%', @DisplayFieldId1, @SortFieldId1, 0, null))
wells where well_id in (" + WellList + ")";


using (UniCommand cmdSelectWells = new UniCommand(cmdSelectWellsString, connDB))
{
cmdSelectWells.Parameters.Add("@WellAliasTypeId", WellAliasTypeId);
cmdSelectWells.Parameters.Add("@MaskId", MaskId);
cmdSelectWells.Parameters.Add("@DisplayFieldId", DisplayFieldId);
cmdSelectWells.Parameters.Add("@SortFieldId", SortFieldId);
if (SurveySystemId == "")
cmdSelectWells.Parameters.Add("@SurveySystemId", null);
else
cmdSelectWells.Parameters.Add("@SurveySystemId", SurveySystemId);
if (WellList != "")
{
cmdSelectWells.Parameters.Add("@WellAliasTypeId1", WellAliasTypeId);
cmdSelectWells.Parameters.Add("@DisplayFieldId1", DisplayFieldId);
cmdSelectWells.Parameters.Add("@SortFieldId1", SortFieldId);
}
UniDataAdapter daSelectWells = new UniDataAdapter();
daSelectWells.SelectCommand = cmdSelectWells;
dsHierarchy.wells.BeginLoadData();
daSelectWells.Fill(dsHierarchy.wells);
dsHierarchy.wells.EndLoadData();
daSelectWells.Dispose();

}

//// Create list of wells
string cmdSelectJobsString = WellList == "" ? @"Select * FROM vw1_job_zone where well_id in
(select well_id from
table(MANAGE_WELL_DISPLAY.GET_WELLS_BY_LOC(@WellAliasTypeId,
@MaskId, @DisplayFieldId, @SortFieldId, 0, @SurveySystemId)))" :
@"Select * FROM vw1_job_zone where well_id in
(select well_id from
table(MANAGE_WELL_DISPLAY.GET_WELLS_BY_LOC(@WellAliasTypeId,
@MaskId, @DisplayFieldId, @SortFieldId, 0, @SurveySystemId)) union
select well_id from wells where well_id in (" + WellList + "))";
using (UniCommand cmdSelectJobs = new UniCommand(cmdSelectJobsString, connDB))
{
cmdSelectJobs.Parameters.Add("@WellAliasTypeId", WellAliasTypeId);
cmdSelectJobs.Parameters.Add("@MaskId", MaskId);
cmdSelectJobs.Parameters.Add("@DisplayFieldId", DisplayFieldId);
cmdSelectJobs.Parameters.Add("@SortFieldId", SortFieldId);
cmdSelectJobs.Parameters.Add("@SurveySystemId", SurveySystemId);
UniDataAdapter daSelectZones = new UniDataAdapter();
daSelectZones.SelectCommand = cmdSelectJobs;
dsHierarchy.well_jobs.BeginLoadData();
daSelectZones.Fill(dsHierarchy.well_jobs);
dsHierarchy.well_jobs.EndLoadData();
daSelectZones.Dispose();
}

// Fill Jobs Hierarchy
string cmdSelectZonesString = WellList == "" ? @"SELECT * from vw1_zone_job
where well_id in (select well_id from
table(MANAGE_WELL_DISPLAY.GET_WELLS_BY_LOC(@WellAliasTypeId,
@MaskId, @DisplayFieldId, @SortFieldId, 0, @SurveySystemId)))" :
@"SELECT * from vw1_zone_job
where well_id in (select well_id from
table(MANAGE_WELL_DISPLAY.GET_WELLS_BY_LOC(@WellAliasTypeId,
@MaskId, @DisplayFieldId, @SortFieldId, 0, @SurveySystemId)) union
select well_id from wells where well_id in (" + WellList + "))";
using (UniCommand cmdSelectZones = new UniCommand(cmdSelectZonesString, connDB))
{
cmdSelectZones.Parameters.Add("@WellAliasTypeId", WellAliasTypeId);
cmdSelectZones.Parameters.Add("@MaskId", MaskId);
cmdSelectZones.Parameters.Add("@DisplayFieldId", DisplayFieldId);
cmdSelectZones.Parameters.Add("@SortFieldId", SortFieldId);
cmdSelectZones.Parameters.Add("@SurveySystemId", SurveySystemId);
UniDataAdapter daSelectZones = new UniDataAdapter();
daSelectZones.SelectCommand = cmdSelectZones;
dsHierarchy.well_zones.BeginLoadData();
daSelectZones.Fill(dsHierarchy.well_zones);
dsHierarchy.well_zones.EndLoadData();
daSelectZones.Dispose();
}



// rename columns in wells to decrease size of xml (2.8 Mb for 17000 records saving space)
dsHierarchy.wells.well_idColumn.ColumnName = "id";
dsHierarchy.wells.display_fieldColumn.ColumnName = "df";
dsHierarchy.wells.sort_fieldColumn.ColumnName = "sf";
dsHierarchy.wells.zone_idColumn.ColumnName = "zi";
dsHierarchy.wells.parent_idColumn.ColumnName = "pi";
dsHierarchy.wells.surface_locationColumn.ColumnName = "sl";
dsHierarchy.wells.bottomhole_locationColumn.ColumnName = "bh";
dsHierarchy.wells.well_nameColumn.ColumnName = "wn";
dsHierarchy.wells.well_licenseColumn.ColumnName = "wl";
dsHierarchy.wells.icon_idColumn.ColumnName = "i";
dsHierarchy.wells.flag_currentColumn.ColumnName = "f";
// rename columns in well_zones to decrease size of xml (4.1 Mb for 17000 records saving space)
dsHierarchy.well_zones.well_idColumn.ColumnName = "w";
dsHierarchy.well_zones.hierarchy_idColumn.ColumnName = "h";
dsHierarchy.well_zones.hierarchy_nameColumn.ColumnName = "hn";
dsHierarchy.well_zones.formation_idColumn.ColumnName = "f";
dsHierarchy.well_zones.zone_uwiColumn.ColumnName = "z";
dsHierarchy.well_zones.bh_flagColumn.ColumnName = "bh";
dsHierarchy.well_zones.uwi_sortColumn.ColumnName = "u";
dsHierarchy.well_zones.icon_idColumn.ColumnName = "i";
dsHierarchy.well_zones.parent_idColumn.ColumnName = "p";
// rename columns in well_jobs to decrease size of xml (6.6 Mb for 17000 records saving space)
dsHierarchy.well_jobs.well_idColumn.ColumnName = "w";
dsHierarchy.well_jobs.hierarchy_idColumn.ColumnName = "h";
dsHierarchy.well_jobs.hierarchy_nameColumn.ColumnName = "hn";
dsHierarchy.well_jobs.start_dateColumn.ColumnName = "s";
dsHierarchy.well_jobs.job_type_idColumn.ColumnName = "j";
dsHierarchy.well_jobs.parent_idColumn.ColumnName = "p";
dsHierarchy.well_jobs.job_sort_orderColumn.ColumnName = "js";
dsHierarchy.well_jobs.icon_idColumn.ColumnName = "i";
dsHierarchy.well_jobs.job_scheduler_flagColumn.ColumnName = "sf";
dsHierarchy.RemotingFormat = SerializationFormat.Binary;
}
catch (Exception e)
{
appLog.MainLog.Log(LogSeverity.Error, UserId + "#" + LogCategory, e.Message);
}
finally
{
connDB.Close();
}
}

byte[] data = CompressDataSet(dsHierarchy);

return data;
}

private byte[] CompressDataSet(DataSet ds)
{
MemoryStream mem = new MemoryStream();
GZipStream zip = new GZipStream(mem, CompressionMode.Compress);
ds.WriteXml(zip, XmlWriteMode.WriteSchema);
zip.Close();
byte[] bbb = mem.ToArray();
mem.Close();
return bbb;
}
#endregion

2. We connect to the Oracle database using OracleClient
3. Oracle Server - 10g & 11g
4. We always recreate it with this one, but it could happen that we will be able to recreate the issue with other methods as well.
5. Do you have 64 bit version of the component?

There is no reason to think that the problem is caused by our components. Search the code which makes your application hang. Run your project in the debug mode and when it hangs, use a breakpoint. If the issue is caused by our components, then send us the code snippet where the hanging occurs.

Hi,
To solve this problem we worked with Microsoft IIS engineer and did debug of the web service. Here Microsoft results:

- This dump has high CPU as there are multiple threads (23 and 24) looping through a static dictionary
- Here is how the call stack looks like for one of the threads. The application is over-ridding the ShouldSerialize function, actually method is CoreLab.Oracle.OracleConnectionStringBuilder.ShouldSerialize

I do not call ShouldSerialize method anywhere in my code, so it should be something in your component. Could you please provide me with details how I can solve this issue?

ChildEBP RetAddr
1381e260 65597f22 mscorlib_ni!System.Collections.Generic.Dictionary`2[[System.__Canon, mscorlib],[System.Byte, mscorlib]].FindEntry(System.__Canon)+0xffa39f74
1381e270 1206b63c System_Data_ni!System.Data.Common.DbConnectionStringBuilder.ShouldSerialize(System.String)+0x32
1381e284 65596ce7 CoreLab_UniDirect_Oracle!CoreLab.Oracle.OracleConnectionStringBuilder.ShouldSerialize(System.String)+0x6c
1381e2c0 79e71b4c System_Data_ni!System.Data.Common.DbConnectionStringBuilder.get_ConnectionString()+0xdb
1381e2d0 79e8968e mscorwks!CallDescrWorker+0x33
1381e350 79e96d11 mscorwks!CallDescrWorkerWithHandler+0xa3
1381e488 79e96d44 mscorwks!MethodDesc::CallDescr+0x19c
1381e4a4 79e96d62 mscorwks!MethodDesc::CallTargetWorker+0x1f
1381e4bc 79fceef1 mscorwks!MethodDescCallSite::CallWithValueTypes+0x1a
1381e69c 79fcf052 mscorwks!InvokeImpl+0x550
1381e75c 0f085428 mscorwks!RuntimeMethodHandle::InvokeMethodFast+0xbd
1381e7ac 0f08522f mscorlib_ni!System.RuntimeMethodHandle.InvokeMethodFast(System.Object, System.Object[], System.Signature, System.Reflection.MethodAttributes, System.RuntimeTypeHandle)+0x48
1381e7e4 0f0850be mscorlib_ni!System.Reflection.RuntimeMethodInfo.Invoke(System.Object, System.Reflection.BindingFlags, System.Reflection.Binder, System.Object[], System.Globalization.CultureInfo, Boolean)+0x15f
1381e850 12067d3e mscorlib_ni!System.Reflection.RuntimeMethodInfo.Invoke(System.Object, System.Reflection.BindingFlags, System.Reflection.Binder, System.Object[], System.Globalization.CultureInfo)+0x1e
1381e850 1206b543 CoreLab_UniDirect!CoreLab.UniDirect.a4.a(System.Object, System.String, System.Object[], System.String, System.Type)+0x17e
1381e870 12068216 CoreLab_UniDirect!CoreLab.UniDirect.ConnectionStringBuilderImpl.get_ConnectionString()+0x2b

1381edd0 79e96d11 mscorwks!CallDescrWorkerWithHandler+0xa3
1381ef28 79e96d44 mscorwks!MethodDesc::CallDescr+0x19c
1381ef44 79e96d62 mscorwks!MethodDesc::CallTargetWorker+0x1f
1381ef5c 79fceef1 mscorwks!MethodDescCallSite::CallWithValueTypes+0x1a
1381f174 79fcf052 mscorwks!InvokeImpl+0x550
1381f234 0f085428 mscorwks!RuntimeMethodHandle::InvokeMethodFast+0xbd
1381f284 0f0851d6 mscorlib_ni!System.RuntimeMethodHandle.InvokeMethodFast(System.Object, System.Object[], System.Signature, System.Reflection.MethodAttributes, System.RuntimeTypeHandle)+0x48
1381f2bc 0f0850be mscorlib_ni!System.Reflection.RuntimeMethodInfo.Invoke(System.Object, System.Reflection.BindingFlags, System.Reflection.Binder, System.Object[], System.Globalization.CultureInfo, Boolean)+0x106
1381f310 65dd912b mscorlib_ni!System.Reflection.RuntimeMethodInfo.Invoke(System.Object, System.Reflection.BindingFlags, System.Reflection.Binder, System.Object[], System.Globalization.CultureInfo)+0x1e
1381f310 65e1098e System_Web_Services_ni!System.Web.Services.Protocols.LogicalMethodInfo.Invoke(System.Object, System.Object[])+0x8b
1381f354 65e10665 System_Web_Services_ni!System.Web.Services.Protocols.WebServiceHandler.Invoke()+0x156
1381f384 65e10ff7 System_Web_Services_ni!System.Web.Services.Protocols.WebServiceHandler.CoreProcessRequest()+0x28d
1381f398 660b0256 System_Web_Services_ni!System.Web.Services.Protocols.SyncSessionlessHandler.ProcessRequest(System.Web.HttpContext)+0x87
1381f3cc 6608332c System_Web_ni!System.Web.HttpApplication+CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()+0xb6
1381f408 6608eb83 System_Web_ni!System.Web.HttpApplication.ExecuteStep(IExecutionStep, Boolean ByRef)+0x4c
1381f45c 660828ac System_Web_ni!System.Web.HttpApplication+ApplicationStepManager.ResumeSteps(System.Exception)+0x133
1381f470 66085e1c System_Web_ni!System.Web.HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(System.Web.HttpContext, System.AsyncCallback, System.Object)+0x7c
1381f4ac 66085ac3 System_Web_ni!System.Web.HttpRuntime.ProcessRequestInternal(System.Web.HttpWorkerRequest)+0x17c
1381f4bc 66084c5c System_Web_ni!System.Web.HttpRuntime.ProcessRequestNoDemand(System.Web.HttpWorkerRequest)+0x63
1381f530 79faca7d System_Web_ni!System.Web.Hosting.ISAPIRuntime.ProcessRequest(IntPtr, Int32)+0x11c
1381f5e8 79facb8b mscorwks!COMToCLRWorkerBody+0x1de
1381f644 79facff1 mscorwks!COMToCLRWorkerDebuggerWrapper+0x37
1381f828 02a1a295 mscorwks!COMToCLRWorker+0x52f
1381f850 6a2aa20b CLRStub[StubLinkStub]@2a1a295
1381fbb4 6a2aa300 webengine!HttpCompletion::ProcessRequestInManagedCode+0x1cb
1381fbc4 6a2aa2cd webengine!HttpCompletion::ProcessCompletion+0x48
1381fbdc 79eb43fc webengine!CorThreadPoolWorkitemCallback+0x1a
1381fc28 79eb3d0e mscorwks!UnManagedPerAppDomainTPCount::DispatchWorkItem+0x9a
1381fc3c 79eb3e82 mscorwks!ThreadpoolMgr::ExecuteWorkRequest+0xaf
1381fc94 79f0e255 mscorwks!ThreadpoolMgr::WorkerThreadStart+0x20b
1381ffb8 7d4dfe37 mscorwks!Thread::intermediateThreadProc+0x49
1381ffec 00000000 kernel32!BaseThreadStart+0x34

Multiple threads enumerating through a collection is intrinsically not a thread-safe procedure. If the dictionary object accessed by these threads is declared as static (which it is) then the threads can go in an infinite loop while trying to enumerate the dictionary if one of the threads writes to the dictionary while the other threads are reading\enumerating through the same dictionary. You may also experience High CPU during this stage.

For more details refer to http://blogs.msdn.com/b/tess/archive/20 ... onary.aspx

We could not reproduce the issue. Could you please send us a small test project with DDL/DML scripts and the assemblies required for the project.
Describe also the steps for reproducing the issue.