SQL Queries Trace – How to Trace SQL Queries in ArcGIS Server to Oracle Database

arcgis-10.0arcgis-serverlogoracle-spatialsql

I'd like to generate a log file containing all SQL queries sent by ArcGIS Server (ArcSDE) to Oracle database.
Is there a way to do so?
I'm using Oracle 11g and ArcGIS Server 10.0 on Windows. ArcSDE is used in direct connection.

Best Answer

There are actually a number of ways of tracing any ArcSDE connection. Calls between the client application and the ArcSDE client are logged in the SDE Trace file, between the ArcSDE client and server in the SDE Intercept file, the ArcSDE server will log certain events in the service or direct connect log, and database calls are logged in the DBMS logfiles.

-------------------------------------------------------------
|                                                           |
|  Client (ArcObject, ArcCatalog, ArcGIS Server, ArcIMS...) |
|                                                           |
-------------------------------------------------------------
      |
      |
     \|/
------------------ --------> SDE Trace
|                |  
|  ArcSDE Client |
|                |  
------------------ --------> SDE Intercept
      |
      |
     \|/
------------------- --------> SDE Intercept
|                 | 
|  ArcSDE Server  | --------> ArcSDE Service Logfile, or direct connect log
|                 |  
------------------- 
      |
      |
     \|/
------------------
|                |  
|  DBMS          | -----------> DBMS logfiles or trace
|                |  
------------------

The ArcSDE Trace files log every call made to the ArcSDE client. These files are usually large and noisy. Look at the SDETraceLoc and SDETraceMode in the dbinit help. These values can also be set as environment variables before you start the application, this works for application and direct connections.

ArcSDE Intercept files are usually more helpful. They will show what time is being spent in what call. A word of caution though, SDE works off a concept of streams. Certain commands (like inserts, updates and deletes) set information on the stream, then execute the command. Usually the stream number is the first integer after the command in the intercept file. This can get confusing if you have many streams (I have seen up to 26 streams). You can look at SDEIntercept and SDEInterceptLoc in the dbinit help or this KB article on SDE Intercept files for some more information and examples.

The ArcSDE service logfiles, in the %SDE_HOME%\etc folder, or the direct connect logfiles, in the %SDE_HOME%\etc or %TEMP% folders, contain general information about whats going on with the service, or connection. The amount of information being logged can be increased with the SDEVerbose variable (dbinit help).

DBMS logfiles and traces are very useful. But they do only give you part of the picture. Additionally, some database (like Oracle) don't actually include all types of errors in the DBMS trace. There are many ways to enable SQL tracing, Devdatta's comment above links to more information.

Other links: Digging deeper – Troubleshooting geoprocessing errors when using ArcSDE data

Related Question