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.
2
-
3You can use Oracle's tracing & Auditing. Have a look at this question: stackoverflow.com/questions/7914354/oracle-sql-query-logging– Devdatta TengsheCommented May 24, 2013 at 10:08
-
You can use Toad Quest for the real time tracking log.– user3120Commented Jun 3, 2013 at 12:57
Add a comment
|
1 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
-
The Trace and Intercept locations in this diagram are incorrect (trace is inside the API between ArcSDE client and ArcSDE server, and intercepts are between the Server and RDBMS). Application logging such as the output of ArcGIS Server sits between the client application and ArcSDE API.– VinceCommented Oct 16, 2013 at 18:07
-
@Vince, I think there is some confusion here. I updated the diagram in am attempt to better illustrate my point. The Trace lists command that are issued to the SDE Client (through the SDE API) but not necessarily to the SDE Server (eg. SE_coordref_free, SE_shape_get_binary_size). The Intercept contains commands that trigger a round trip to the SDE Server, but not necessarily the DBMS (eg. QueryWithInfo, StreamSetState). Logging between SDE and the DBMS is dependent on the DBMS and connection type (OCI, OleDB, ODBC).– travisCommented Oct 28, 2013 at 17:20
-
Granted, ASCII isn't the best way to diagram this, but it would help if the two "ArcSDE Client"s were marked "ArcSDE Client API" and "ArcSDE Server". The SDETRACE is captured at the interface between client app and the API (echoing parameters as they cross the API in either direction). I believe SDEINTERCEPT lives on both sides of the SES function interface in the gsrvr DLL (as manifested by either application server or Direct Connect), and includes both messages received from the API and calls made to the DBMS (move the intercept on the upper client to bottom of lower).– VinceCommented Oct 28, 2013 at 17:44
-
Ya, the two SDE Clients were a copy paste error. During runtime, there isn't really an API...just the client (thread(s) and memory) and the server (thread(s) and memory). But I agree that the SDETRACE echos parameters as they cross that. I am fairly certain that by default, the SDEINTERCEPT does not log anything to do with the DBMS directly (eg SQL). There may have been other parameters that enabled SQL logging, but they would be implemented independently for each DBMS. And I don't know what they are.– travisCommented Oct 28, 2013 at 18:35
-
I don't generally look at intercept output, but I just ran a simple set of API calls ('sdelist -o layers') with both trace and intercept enabled, and it does seem to generate two intercept files (without the SQL interaction I remembered), so it looks like we can agree on this :)– VinceCommented Oct 28, 2013 at 19:48