Oracle Tracing & Bind Variables

[NathanielWingfield]

Oracle tracing with TKPROF is the recommended way to troubleshoot data access performance problems with SDE. Unfortunately, queries that differ in any way are considered "different" and summarized independently. For our long-running geoprocessing task, millions of queries like this are generated:

SELECT A, B, C FROM TABLE WHERE (OBJECTID = 123);

The TKPROF output for this query shows stats for EVERY SINGLE CALL of this type, because the OBJECTID differs in each case. For example:

SELECT A, B, C FROM TABLE WHERE (OBJECTID = 123);

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1

...

SELECT A, B, C FROM TABLE WHERE (OBJECTID = 124);

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1

etc...

Millions of stats like this are pretty useless. They need to be summarized for every query that follows the pattern "SELECT A, B, C FROM TABLE WHERE (OBJECTID = x)".

If Oracle bind variables are used, however, the TKPROF output is more helpful. For example:

INSERT INTO TABLE (A, B, C)
VALUES
 ( :a1, :a2, :a3)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      687      0.01       0.01          0          0          0           0
Execute    687      0.08       0.10          4        689       4851         687
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1374      0.09       0.12          4        689       4851         687

So the question is: is there any way to force ArcSDE/ArcObjects to use bind variables when constructing Oracle queries?

If not, how do you recommend performance tuning for a long-running geoprocessing routine like this?

[anthonysanchez]

Hello Nathaniel,
Altering your oracle init parameter cursor sharing will accomplish this. It can be done at the session level via a login trigger or via arcobjects.

Alter session set cursor_sharing=similar;

Aside from I noticed that your job gets one row at a time. This will likely be slow even if optimized. I don't know what your process is doing but if the calls can be reduced and commits less frequent it will likely perform better.

Anthony

[ForrestJones]

Hi Nathaniel,

What long-running geoprocessing task is being run where you see all these queries? Is it a specific tool or several pieces of a larger model? There might be a way to optimize it in the model, or there may be something in a specific gp tool that should be investigated in the core tool on our side.

Thanks,

[NathanielWingfield]

Altering your oracle init parameter cursor sharing will accomplish this. It can be done at the session level via a login trigger or via arcobjects.

Alter session set cursor_sharing=similar;

It worked! I added this line to my code:

ws.ExecuteSQL("alter session set cursor_sharing = 'similar'");

Now the output looks more like this:

SELECT A, B, C FROM TABLE WHERE (OBJECTID = :"SYS_B_0");

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      648      0.03       0.05          0        169          0           0
Execute    648      0.05       0.05          0          0          0           0
Fetch      648      0.27       1.87        819       3513          0        2089
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1944      0.36       1.98        819       3682          0        2089

[ForrestJones]

Hi Nathaniel,

That is great it is working now, but you should not have to change that parameter... We should investigate further. Would you be able to open an incident with support or at least describe the tools being used so we can determine why it is occurring, please?

Thanks,

[NathanielWingfield]

fojo-

We are not doing geoprocessing with a capital G. Just writing ArcObjects code in ASP.NET/C#. The code is issued against an AGS Geodata service with ArcSDE/Oracle 11g behind it. Maybe this will help:

conn = new GISServerConnection();
conn.Connect(ConfigurationManager.AppSettings["AGS_HOST"]);
som = conn.ServerObjectManager;

// Get the connection to the database
gdbContext = som.CreateServerContext(ConfigurationManager.AppSettings["geodataService"], "GeodataServer");
geoDataServer = (IGeoDataServer)gdbContext.ServerObject;

// Get the workspace from the table
gdsObjects = (IGeoDataServerObjects)geoDataServer;
gdsWS = gdsObjects.DefaultWorkingWorkspace;
featWS = (IFeatureWorkspace)gdsWS;

// Set tracing parameters
gdsWS.ExecuteSQL("alter session set sql_trace = true");
gdsWS.ExecuteSQL("alter session set tracefile_identifier = 'intdb'");
gdsWS.ExecuteSQL("alter session set cursor_sharing = 'similar'");

I don't intend to open a support incident, thanks.

[ForrestJones]

Hi Nathaniel,

Ah ok. So after you get the IFeatureWorkspace, how do you query the records? For example are you using an IGeodatabaseBridge.GetFeatures() call or maybe an IFeatureClass.GetFeature(oid) in a loop?

Thanks,

[NathanielWingfield]

Predominately IFeatureClass.GetFeature(oid) in a loop.

[ForrestJones]

Ok - that probably explains the many queries that aren't using the binds. I would suggest using the IGeodatabaseBridge.GetFeatures() api instead of the IFeatureClass.GetFeature(oid) in a loop. This will run one query with the objectid's needed and improve the performance as well. It will return an IFeatureCursor which can then be used in a loop.

For example:

private static void GetFeatureExample(IFeatureClass featureClass, int[] oidList)
{
  int nameFieldIndex = featureClass.FindField("NAME");
  foreach (int oid in oidList)
  {
    IFeature feature = featureClass.GetFeature(oid);
    Console.WriteLine("NAME: {0}", feature.get_Value(nameFieldIndex));
  }
}

vs.

private static void GetFeaturesExample(IFeatureClass featureClass, int[]
  oidList)
{
  int nameFieldIndex = featureClass.FindField("FIPSSTCO");
  using(ComReleaser comReleaser = new ComReleaser())
  {
    IGeoDatabaseBridge geodatabaseBridge = new GeoDatabaseHelperClass();
    IFeatureCursor featureCursor = geodatabaseBridge.GetFeatures(featureClass,
      ref oidList, true);
    comReleaser.ManageLifetime(featureCursor);

    IFeature feature = null;
    while ((feature = featureCursor.NextFeature()) != null)
    {
      Console.WriteLine("NAME: {0}", feature.get_Value(nameFieldIndex));
    }
  }
}

From:

http://resources.esri.com/help/9.3/ArcGISEngine/dotnet/09bd8059-f031-4b88-bac8-3b4b73dccb05.htm

[anthonysanchez]

Ok - that probably explains the many queries that aren't using the binds. I would suggest using the IGeodatabaseBridge.GetFeatures() api instead of the IFeatureClass.GetFeature(oid) in a loop. This will run one query with the objectid's needed and improve the performance as well. It will return an IFeatureCursor which can then be used in a loop.

FJ - this is great.  By implementing this would the rdbms result be one query with an IN list in the where clause?  If so, performance would be much better from the rdbms perspective.

thanks

Anthony