English

FAQ: What is the meaning of the 'Search SQL:' statement in the ArcSDE error log with Oracle?

Question

What is the meaning of the 'Search SQL:' statement in the ArcSDE error log with Oracle?

Answer

When the ArcSDE SDEVERBOSE variable has been set in the $SDEHOME/etc/dbinit.sde file or in the environment starting the ArcSDE service, spatial queries executed by each client are written to the ArcSDE instance's sde error log with Oracle.

The value of logging each spatial SQL statement is to make the content easily available to the administrator or user. This allows the administrator or user to extract the SQL statement and execute the same query using SQL*Plus, which can help identify performance or resource contention issues.

The following output is an excerpt from an ArcSDE error log:

Code:
[Wed May 09 11:51:46 2007] [36] [ETHAN] Search SQL: << SELECT PARCELS.OBJECTID, PARCELS.SHAPE
FROM ASSESSOR.PARCELS PARCELS WHERE sde.st_envintersects (PARCELS.SHAPE,:1,:2,:3,:4) = 1 >>

[Wed May 09 11:51:46 2007] [36] [ETHAN] Bind ST_GEOM :0 -0.15 :1 -0.11 :2 0.15 :3 0.11

[Wed May 09 11:51:46 2007] [36] [ETHAN] :1 1 :2 1 :3 1 :4 1
[Wed May 09 11:51:46 2007] [36] [ETHAN] :5 718924277 :6 678506410 :7 416841664 :8 457893537


The timestamp [Wed May 09 11:51:46 2007] represents the moment the query was executed. The value [36] is the session's unique sde connection identifier (the value from the table sde.process_information.sde_id attribute), and [ETHAN] is client's machine name that executed the spatial query.

The select statement proceeding the 'Search SQL:' message is the exact SQL statement that is executed by ArcSDE in Oracle for a spatial query. Having the SQL syntax, one can then extract the statement and execute the SQL within SQL*Plus.

Because ArcSDE is optimized for performance and scalability, each predicate in the SQL statement's WHERE clause uses a bind variable. Bind variables allow the Oracle clients to change the bind value on each execution to avoid parsing the statement. Therefore, to execute the exact same statement with bind variables in SQL*Plus, these variables must be declared and the values assigned.

The line '[Wed May 09 11:51:46 2007] [36] [ETHAN] Bind ST_GEOM :0 -0.15 :1 -0.11 :2 0.15 :3 0.11' reports the four variables used in the 'Search SQL:' SQL statement's WHERE clause. The :0 represents the first bind variable, :1 the second, :2 the third and :3 the fourth.

The last two lines, in the example, are the bind variables used by the recursive SQL statement executed against the ST_Geometry spatial index. To obtain the recursive SQL statement executed against the spatial index, capture an Oracle trace of the 'Search SQL:' statement, find the recursive SQL statement, and substitute the bind variables to allow the statement to be executed in SQL*Plus.

The following example demonstrates how to create variables, set a variable's value, and execute the 'Search SQL:' statement within SQL*Plus. Since numeric bind variable names are not valid in SQL*Plus, the bind variable names must be renamed to bind1, bind2, etc.

Code:
SQL> VARIABLE bind1 NUMBER;
SQL> VARIABLE bind2 NUMBER;
SQL> VARIABLE bind3 NUMBER;
SQL> VARIABLE bind4 NUMBER;
SQL> EXECUTE :bind1 := -0.15;

PL/SQL procedure successfully completed.

SQL> EXECUTE :bind2 := -0.11;

PL/SQL procedure successfully completed.

SQL> EXECUTE :bind3 := 0.15;

PL/SQL procedure successfully completed.

SQL> EXECUTE :bind4 := 0.15;

PL/SQL procedure successfully completed.


Because the parcels.shape attribute is a user defined type, it cannot be displayed in SQL*Plus. Use the st_astext operator to return the geometry's well-known text representation. Change PARCELS.SHAPE to st_astext(PARCELS.SHAPE) to execute the statement.

Code:
SQL> SELECT PARCELS.OBJECTID, st_astext(PARCELS.SHAPE)
2 FROM ASSESSOR.PARCELS PARCELS
3 WHERE sde.st_envintersects(PARCELS.SHAPE,:1,:2,:3,:4) = 1;

Related Information