2

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?

5
  • What is the client? ArcGIS Desktop + ArcSDE should definitely using bind variables. Feb 21, 2011 at 16:49
  • Client is C#.NET+ArcObjects.
    – nw1
    Feb 21, 2011 at 16:55
  • Can you post more about the AO code being used to generate this SQL? Feb 21, 2011 at 17:08
  • see above for an example
    – nw1
    Feb 21, 2011 at 17:15
  • Have you tried making fewer calls to GetRow? "millions" sounds expensive. Using a cursor might improve performance, e.g. using GetRows. Feb 21, 2011 at 19:46

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.