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?