-- sqltracing.sql --------------------------------------------------------------------------------------------------------
-- 

There are 3 levels of tracing:

INSTANCE TRACING
----------------
Set the SQL_TRACE initialization parameter to TRUE.


CURRENT SESSION TRACING
-----------------------
To start tracing:
alter session set sql_trace = true;

To stop tracing:
alter session set sql_trace = false;


OTHER SESSION TRACING
---------------------
Starting with Oracle7.2 release you can turn SQL_TRACE on for a user session other than your own.  
You MUST be logged on as SYS to trace a user's session.  You can now turn SQL_TRACE on for the troublesome user with the command as follows. 
10 is the SID and 21 is the SERIAL#.

exec dbms_system.set_sql_trace_in_session(SID,SERIAL#,TRUE);

Example:
exec dbms_system.set_sql_trace_in_session(10,21,TRUE);


The SQL_TRACE output is automatically turned off when the user being traced logs off the database. 
To turn SQL_TRACE off explicitly:

exec dbms_system.set_sql_trace_in_session(SID,SERIAL#,TRUE);

Example:
exec dbms_system.set_sql_trace_in_session(10,21,TRUE);


Where do I find the trace file?
------------------------------- 
You will find the trace file in the directory specified by the INIT.ORA parameter USER_DUMP_DEST. 

Obtain the process id to find out the trace filename that will be generated.  
For example, most Unix machines have the format ora_22222.trc where 22222 is the operating system process id.

SELECT sid, serial#, osuser,username, process
   FROM v$session
;

SELECT sid, serial#, osuser, process
   FROM v$session
  WHERE osuser='xyz';

SID          SERIAL#             OSUSER     PROCESS
-----------------------------------------------------
20             26                xyz        22222



What do I do with the trace file?
---------------------------------

You can run TKPROF against the trace file to obtain the SQL statements execution plans as well as  many runtime 
statistics such as disk I/Os, number of reads from Oracle’s buffer cache and CPU utilisation.
Refer to tkprof.sql for more information on tkprof.