in Oracle

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

  • Related Content by Tag