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?
