Mazzotti: /* Tracing other user's sessions */

2011-11-02T09:31:17Z

Tracing other user's sessions

Mazzotti: Creata pagina con 'This article lists the commands required to '''trace SQL statements''' executed by a user, an application or the entire database. == Tracing a SQL session == === Start ses...'

2011-11-02T09:01:28Z

Creata pagina con 'This article lists the commands required to '''trace SQL statements''' executed by a user, an application or the entire database. == Tracing a SQL session == === Start ses...'

Nuova pagina

This article lists the commands required to '''trace [[SQL]] statements''' executed by a user, an application or the entire database.

== Tracing a SQL session ==

=== Start session trace ===
To start a SQL trace for the current session, execute:
ALTER SESSION SET sql_trace = true;

You can also add an identifier to the trace file name for later identification:

ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = mysqltrace;

=== Stop session trace ===
To stop SQL tracing for the current session, execute:
ALTER SESSION SET sql_trace = false;

=== Tracing other user's sessions ===
DBA's can use '''DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION''' to trace problematic database sessions. Steps:

* Get the SID and SERIAL# for the process you want to trace.

SQL> select sid, serial# from sys.v_$session where ...
SID SERIAL#
---------- ----------
8 13607

* Enable tracing for your selected process:

SQL> ALTER SYSTEM SET timed_statistics = true;
SQL> execute dbms_system.set_sql_trace_in_session(8, 13607, true);

* Ask user to run just the necessary to demonstrate his problem.

* Disable tracing for your selected process:

SQL> execute dbms_system.set_sql_trace_in_session(8,13607, false);

* Look for trace file in USER_DUMP_DEST:

$ cd /app/oracle/admin/oradba/udump
$ ls -ltr
total 8
-rw-r----- 1 oracle dba 2764 Mar 30 12:37 ora_9294.trc

== Tracing an entire database ==
To enable SQL tracing for the entire database, execute:
ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY;

To stop, execute:
ALTER SYSTEM SET sql_trace = false SCOPE=MEMORY;

== Identifying trace files ==
Trace output is written to the database's [[UDUMP]] directory.

The default name for a trace files is INSTANCE_PID_ora_TRACEID.trc where:
* INSTANCE is the name of the Oracle instance,
* PID is the operating system process ID (V$PROCESS.OSPID); and
* TRACEID is a character string of your choosing.

== Size of trace files ==

The trace file size is limited by the parameter MAX_DUMP_FILE_SIZE. The unit of this parameter, if you don't specify the K or M option, is in OS block size.

Be sure this parameter is set to a value high enough for your purpose (e.g. some MB). Of course this depends on the amount and complexitiy of statements which have to be run while tracing. If this value is set too low, possibly the dump file size limit will be reached before the execution of the crucial statements and the trace file will be closed before the interesting parts can be recorded in it.

On the other hand, when this parameter is set to UNLIMITED (default value), if the program to be traced is working forth and forth and the trace mode is not finished, the trace file can grow without limit which means until the associated file system or disk is full. A DBA can stop the trace of a session using the DBMS_MONITOR (10g and up), DBMS_SYSTEM or DBMS_SUPPORT package.

== Formatting output ==
Trace output is quite unreadable. However, Oracle provides a utility, called [[TKProf]], that can be used to format trace output.

==Also see==
* [[TKProf]], Oracle's utility for formatting SQL_TRACE output.

==External links==
* [http://www.ubtools.com/web/public itrprof SQL Analyzer], web based tool which analysing SQL_TRACE and Event 10046 trace files.
* [http://www.easymatica.com/flextracer FlexTracer], commercial client-side SQL tracer for Oracle.
* [http://www.queryadvisor.com QueryAdvisor], commercial GUI based client-side SQL tracefile analyzer for Oracle single instance and RAC.

[[Category:SQL_Utility_script]]
[[Category:ORACLE]]

@@ Riga 21: / Riga 21: @@
 * Get the SID and SERIAL# for the process you want to trace.
-  SQL> select sid, serial# from sys.v_$session where ...
+  SQL> select service_name, username, sid, serial# from sys.v_$session where ...
-         SID    SERIAL#
+         SID    SERIAL#   USERNAME SERVICE_NAME
-  ---------- ----------
+  ---------- ---------- ---------- ------------
-      13607
+      13607    xxxxxxx    yyyyyyyyy
 * Enable tracing for your selected process:

ORACLE TRACE - Cronologia

Mazzotti: /* Tracing other user's sessions */

Mazzotti: Creata pagina con 'This article lists the commands required to '''trace SQL statements''' executed by a user, an application or the entire database. == Tracing a SQL session == === Start ses...'