Enable extended Oracle tracing for an ArcGIS connection

Summary

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.

Note:
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.

Procedure

1. Create a view of all active database sessions:
   

   Code:
   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';

   
   
   

   Note:
   If desired, create a public synonym and grant privileges to it so any connected user may query the view:
   

   Code:
   create public synonym active_sessions for active_view;
   
   grant select on active_sessions to public;

   
   
   
2. Query the active_sessions synonym:
   

   Code:
   select * from active_sessions;

   
   
3. Find the sid and serial number values for the connected ArcGIS session to be traced by finding the record whose USERNAME value corresponds to the User Name value used to connect with ArcGIS.
   

   Note:
   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.

   
   
4. 
   

   Note:
   This step is only applicable if timed statistics are not enabled for the instance

   
   Configure Oracle to record timing statistics for the session you want to trace, inserting the sid and serial# values obtained from step 2
   

   Code:
   exec dbms_system.set_bool_param_in_session(sid,serial#,'timed_statistics',true);

   
   
   

   Warning:
   DO NOT OMIT THIS STEP OR THE ORACLE TRACE FILE WILL HAVE NO TIMING STATISTICS, RENDERING IT USELESS TO DIAGNOSE MOST PERFORMANCE PROBLEMS

   
   
5. Configure Oracle's trace file size parameter to a size large enough to contain the trace information
   

   Code:
   exec dbms_sytem.set_int_param_in_session(sid,serial#,'max_dump_file_size',<NUMBER>);

   
   
   

   Note:
   Choose a NUMBER value large enough to contain significant information, for example, the trace file machine's operating system file size limit.

   
   
6. Turn on extended Oracle tracing for the session
   

   Code:
   exec dbms_system.set_ev(sid,serial#,10046,12,'');

   
   
   

   Note:
   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:
   dbms_system.set_ev(SID,SERIAL#,EVENT,LEVEL,'')
   
   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

   
   
7. Perform the action in the application corresponding to the session being traced.
8. Turn off extended tracing in the session.
   

   Code:
   exec dbms_system.set_ev(sid,serial#,10046,0,'');

   
   
9. Retrieve the trace file from the pathway obtained in step 2.
   
   

   Note:
   Detailed documentation for Oracle tracing:
   Optimizing Oracle Performance
   Oracle Database Performance Tuning Guide 10g Release 1 (10.1).