Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

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

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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