Detecting Tracing

Sometimes we need to know if are there acive traces in the system, of course, there are several ways. Here I like to show, maybe, the most easy way. You need query the v$session, with the columns show below you can quickly identified sessions with activet traces;

 
 -- version 10g ---
 SQL_TRACE                      
 SQL_TRACE_WAITS                
 SQL_TRACE_BINDS                

 NOTE: SQL_TRACE_PLAN_STATS does't exists in this version 

 -- version 11g and 12c ---
 SQL_TRACE                              
 SQL_TRACE_WAITS                        
 SQL_TRACE_BINDS                        
 SQL_TRACE_PLAN_STATS                   

From the Oracle Documentation, the concept for each fields is like show below;

  • SQL_TRACE; Indicates whether SQL tracing is enabled (ENABLED) or disabled (DISABLED)
  • SQL_TRACE_WAITS; Indicates whether wait tracing is enabled (TRUE) or not (FALSE)
  • SQL_TRACE_BINDS; Indicates whether bind tracing is enabled (TRUE) or not (FALSE)
  • SQL_TRACE_PLAN_STATS; Frequency at which row source statistics are dumped in the trace files for each cursor: (never, first_execution or all_executions)

An example;

 
SFFF*ANTO> column program format a25
SFFF*ANTO> select sid, username, program, sql_trace, sql_trace_waits, sql_trace_binds, sql_trace_plan_stats
  2  from v$session
  3  where
  4  sql_trace != 'DISABLED' OR
  5  sql_trace_waits != 'FALSE' OR
  6  sql_trace_binds != 'FALSE' OR
  7  SQL_TRACE_PLAN_STATS != 'FIRST EXEC'
  8  /
SFFF*ANTO> 
SFFF*ANTO> EXEC DBMS_MONITOR.session_trace_enable;

Procedimiento PL/SQL terminado correctamente.

SFFF*ANTO> select sid, username, program, sql_trace, sql_trace_waits, sql_trace_binds, sql_trace_plan_stats
  2  from v$session
  3  where
  4  sql_trace != 'DISABLED' OR
  5  sql_trace_waits != 'FALSE' OR
  6  sql_trace_binds != 'FALSE' OR
  7  SQL_TRACE_PLAN_STATS != 'FIRST EXEC'
  8  /

    SID USERNAME        PROGRAM                   SQL_TRAC SQL_T SQL_T SQL_TRACE_
_______ _______________ _________________________ ________ _____ _____ __________
     63 ANTONION        sqlplusw.exe              ENABLED  TRUE  FALSE FIRST EXEC

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s