Oracle tracing, bind variables, and ArcSDE

Question

Oracle tracing with TKPROF is the recommended way to troubleshoot data access performance problems with SDE/Oracle. 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 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).

The simple OBJECTID = 123 filter is generated by GetRow().

Here is an example of the C# code driving other problematic queries:

IQueryFilter filter = (IQueryFilter)gdbContext.CreateObject("esriGeodatabase.QueryFilter");
filter.WhereClause = "REF_IN_ID  = " + nodeID + " OR NREF_IN_ID = " + nodeID;
streets.Select(filter, esriSelectionType.esriSelectionTypeSnapshot, esriSelectionOption.esriSelectionOptionNormal, null);

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?

Answer 1

The solution was to use Oracle cursor sharing. I added this line to my code:

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

Now the TKPROF 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