SDE analysts sometimes ask for extended Oracle trace files when troubleshooting incidents. Oracle provides tools that database administrators can use to capture any connected session's SQL statements and timing statistics.
The administrator can identify the Oracle session corresponding to any application's connection, identify the location for the resultant trace file, and turn tracing on and off for that session.
Enabling the Oracle trace file requires a sid (system identifier) and serial number for a connected session to plug into the Oracle trace command syntax.
Use Oracle's dynamic performance views to obtain the sid and serial number for a session corresponding to the ArcGIS session incurring a problem.
The following document assumes a database administrator logs into Oracle's SQL Plus application with sysdba privileges, subsequently using SQL Plus to alter the necessary parameters for the connected session to trace. Refer to Oracle documentation for information about the different scopes for which parameters can be altered. The same procedure should work regardless of the platform.
grant select on v_$process to public;
grant select on v_$session to public;
grant select on v_$instance to public;
grant select on v_$parameter to public;
create or replace view active_view as
select b.username, b.sid, b.serial#, to_char(b.logon_time, 'MON-DD HH:MI:SS PM') LOGON,
c.instance_name || '_ora_' || ltrim(to_char(a.spid)) ||'.trc' tracefile, d.value
from v$process a, v$session b, v$instance c, v$parameter d
where a.addr = b.paddr
and b.username not in ('SYS','SYSTEM')
and d.name = 'user_dump_dest';
If desired, create a public synonym and grant privileges to it so any connected user may query the view:
create public synonym active_sessions for active_view;
grant select on active_sessions to public;
select * from active_sessions;
This article assumes each user has a unique User Name value used for signing into the Oracle database through an ArcGIS application. This unique name appears in the USERNAME column of the active_sessions view, making each session corresponding to an ArcGIS connection easy to identify. Even so, in cases where USERNAME is unique, the same user opening one ArcCatalog module and one ArcMap module has two records with the same value in the USERNAME column. In addition, if ArcGIS users do not log in with a unique name, the USERNAME values for each session record will be the same, making the LOGON column value the most viable distinguishing factor between active sessions and the specific ArcGIS connection they correspond to. While logon time can be used to distinguish between identical USERNAME records, a unique USERNAME for each ArcGIS connection makes identifying the session easier.
This step is only applicable if timed statistics are not enabled for the instance
DO NOT OMIT THIS STEP OR THE ORACLE TRACE FILE WILL HAVE NO TIMING STATISTICS, RENDERING IT USELESS TO DIAGNOSE MOST PERFORMANCE PROBLEMS
Choose a NUMBER value large enough to contain significant information, for example, the trace file machine's operating system file size limit.
Extended Oracle tracing can trace not only SQL statements and timing statistics, but also wait events and bind variable values. The general extended trace syntax follows:
Level 12 tracing of the 10046 event provides the information necessary to diagnose most problems that occur with ArcGIS applications.
A brief description of available levels is included here. Consult Oracle documentation for specific information about the different values possible for these parameters.
0 prints no statistics to trace file
1 prints APPNAME, PARSING IN CURSOR,
PARSE ERROR, EXEC, FETCH, UNMAP,
SORT UNMAP, ERROR, STAT, and
XCTEND lines to trace file
2 identical to level 1
4 prints level 1 and includes bind variables
8 prints level 1,4, and includes extended timing information (wait events)
12 prints level 1,4, and 8
Detailed documentation for Oracle tracing:
Optimizing Oracle Performance
Oracle Database Performance Tuning Guide 10g Release 1 (10.1).