Tracing an Oracle Session

1) Check instance parameters to make sure instance is able to trace:

select name, value
from v$parameter
where name in ('timed_statistics','max_dump_file_size','user_dump_dest');

TIMED_STATISTICS - Should be TRUE.
MAX_DUMP_FILE_SIZE - Should be UNLIMITED or something really large (> 10000).
USER_DUMP_DEST - Should already be set.

2) Turn on SQL trace for the user:

2a) If you have direct access to the session:
This is good if you just need basic trace info, no waits or binds.

ALTER SESSION SET tracefile_identifier='MYTRACE';
ALTER SESSION SET SQL_TRACE=TRUE;

- or to include Wait and Bind info -

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

4=Binds, 8=Waits, 12=Binds+Waits
- or -

exec dbms_support.start_trace(waits=>true,binds=>false);

2b) Or use this method if no direct access to the session:

SELECT OSUSER, 
       SID, 
       SERIAL#, 
       USERNAME,
       STATUS, 
       'EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION('||sid||','||serial#||',true);' as sql_start,
       'EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION('||sid||','||serial#||',false);' as sql_end
FROM v$SESSION where UPPER(OSUSER) = '&osuser'
AND status = 'ACTIVE';

-or-

select status,'exec DBMS_SUPPORT.START_TRACE_IN_SESSION('||sid||','||serial#||',true,false);' as enable,
       'exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION('||sid||','||serial#||');' as disable 
From v$session where username='&osuser';

2c) If you are using connection pooling
If you are dealing with transient connections or connection pooling you may not know the SID because the user hasn't logged in yet. You can create a Login trigger that will set tracing on for all new connections that

create or replace temp_sql_trace after logon on database
begin
  if user = UPPER('&USERNAME.') then
    execute immediate 'alter session set events ''10046 trace name context forever, level 8''';
  end if;
end;

begin
  for i in (select sid, serial# from v$session where username = 'UPPER('&USERNAME.')
  LOOP
    execute immediate 'execute SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION('||i.sid||','||i.serial#||',TRUE)';
  END LOOP;
end;
/

2d) Using DBMS_MONITOR (Alternate Connection Pooling Method)
The following is a low impact logon trigger that will set the v$session.client_identifier column after a user connects.

CREATE OR REPLACE TRIGGER LOGON_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
  my_username varchar2(30);
  my_osuser varchar2(30);
  my_machine varchar2(64);
  my_client_identifier varchar2(64);
BEGIN
  -- Gather information used to build Client Identifier string
  my_username := user;
  SELECT TRIM(UPPER(SYS_CONTEXT('USERENV', 'OS_USER'))), 
         TRIM(UPPER(SYS_CONTEXT('USERENV', 'HOST')))
    INTO my_osuser,
         my_machine
      FROM dual;

  -- Build the Client Identifier String
  v_user_identifier := my_username;
  -- Alternate client_identifier string if you need more granularity
  -- v_user_identifier := SUBSTR(my_username || '|' || my_osuser || '|' || my_machine, 1, 64);

  -- Set the session's Client Identifier
  DBMS_SESSION.SET_IDENTIFIER(v_client_identifier); 
END;
/

The client identifier string can be up to 64 bytes.
Since just the v$session.client_identifier is set, you can toggle tracing on and off like this:

-- Turn tracing on
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id => 'TRACEME', waits => TRUE, binds => FALSE);
-- Turn tracing off
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => 'TRACEME');

2e) SET_EV method

exec dbms_system.set_ev([sid],[serial#],10046,[Trace Level],'');

Where trace level is 4 (binds),8 (waits), or 12 (binds and waits).
Check to make sure trace is running

select * From dba_enabled_traces;

3) Run the SQL Statement

Wait, and watch disk space in UDUMP. A trace file will appear and start growing.

4) Turn off SQL Trace

4a) If you have direct acess to the session:

ALTER SESSION SET SQL_TRACE=FALSE;

-or-

ALTER SESSION SET EVENTS '10046 trace name context off';

-or-

exec dbms_support.stop_trace;

4b) Or use this method if no direct access to the session:

SELECT OSUSER,
SID,
SERIAL#,
STATUS,
'EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION('||sid||','||serial#||',false);' as sql_end
FROM v$SESSION where UPPER(OSUSER) = '&osuser'
AND status = 'ACTIVE';

4c) If you are using connection pooling
If you are dealing with transient connections or connection pooling you may not know the SID because the user hasn't logged in yet. You can create a Login trigger that will set tracing on for all new connections that

drop trigger temp_sql_trace;

begin
  for i in (select sid, serial# from v$session where username = 'UPPER('&USERNAME.')
  LOOP
    execute immediate 'execute SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION('||i.sid||','||i.serial#||',FALSE)';
  END LOOP;
end;
/

4d) Using DBMS_MONITOR package

-- Turn tracing off
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => 'TRACEME');

4e) SET_EV method

exec dbms_system.set_ev([sid],[serial#],10046,0,'');

Set Trace Level = 0 to stop tracing.

5) Generate TKProf output (OS)

Change to UDUMP directory

cd /u01/app/oracle/admin/$ORACLE_SID/udump

You run tkprof manually:

tkprof [input file name] [output file name] EXPLAIN=[user/password] 

Here is a Unix command to TKProf for the most recent trace file in the dump directory:

tkprof `ls -tr *.trc | tail -n -1` `ls -tr *.trc | tail -n -1`.tkprof EXPLAIN=[user]/[password]

TKProf has command line parameters to help narrow down results as well. Try adding this to the tkprof command to limit results to the five SQL statements containing the most elapsed time:

sort=prsela,exeela,fchela print=5

Install DBMS_SUPPORT at $ORACLE_HOME/rdbms/admin/dbmssupp.sql:

-- equivalent to level 8 tracing, bind=>true would be equivalent to level 12 tracing.
execute sys.dbms_support.start_trace_in_session(&&SID, &&SERIAL, waits=>true, binds=>true); 

execute sys.dbms_support.stop_trace_in_session(&&SID, &&SERIAL); -- end tracing.

References:
Oracle Base FAQ