Trace Files -- 3 : Tracing for specific SQLs

11g allows definition of tracing by SQL_ID as well.

Here is an example.

Given a particular SQL that has been executed in the past, which we've identified as :

SQL> select sql_id, sql_text, executions from v$sql where sql_id='06d4jjswswagq';

SQL_ID       SQL_TEXT              EXECUTIONS
------------- ------------------------------------------------------------------------------------- ----------
06d4jjswswagq select department_id, sum(salary) from hr.employees group by department_id order by 1      1

SQL> 

We could use either ALTER SESSION (from the same session) or ALTER SYSTEM (from another session, to trace all sessions) to enable tracing specifically for this SQL alone.

SQL> connect system/oracle
Connected.
SQL> alter system set events 'sql_trace [sql:06d4jjswswagq] wait=true, plan_stat=all_executions';

System altered.

SQL> 

(note : The options for "plan_stat" are "never", "first_execution", "all_executions").  This allows us to capture execution plan statistics.
Once I have enabled SQL-specific tracing, it is not limited to a session but can run across all sessions that execute the SQL.  Even if I execute other SQLs from the same session that executed this SQL, the other SQLs are *not* traced.

Thus, I started another session that executed :

SQL> select department_id, sum(salary) from hr.employees group by department_id order by 1;

DEPARTMENT_ID SUM(SALARY)
------------- -----------
    10      4400
    20     19000
    30     24900
    40      6500
    50    156400
    60     28800
    70     10000
    80    304500
    90     58000
   100     51608
   110     20308

DEPARTMENT_ID SUM(SALARY)
------------- -----------
       7000

12 rows selected.

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

SQL> select count(*) from hr.departments;

  COUNT(*)
----------
 27

SQL>

The trace file only captured the target SQL. The other two SQLs were *not* in the trace file.  Tracing is not bound to a session, so if you have multiple sessions executing the target SQL, each session creates a trace file.

Tracing is disabled with :

SQL> alter system set events 'sql_trace [sql:06d4jjswswagq] off';

System altered.

SQL> 

Thus, just as in the previous post where I demonstrated tracing by module and action, we can enable tracing for a specific SQL.
.
.
.