sql trace by session, sid and system

We can enable sql trace in various ways.. Here are a few

Credits to : https://dbaclass.com/article/tracing-sessions-in-oracle/

1. Enabling tracing for all session of a user.
For this we need to create a trigger.

CREATE OR REPLACE TRIGGER USER_TRACING_SESSION
AFTER LOGON ON DATABASE
BEGIN
IF USER = 'SIEBEL'THEN
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/


2. Enabling trace for a single session(using dbms_system)

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE)

---To disable

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

--- Get the tracefile name:

SELECT p.tracefile FROM   v$session s  JOIN v$process p ON s.paddr = p.addr WHERE  s.sid = 123;
TRACEFILE
------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9699.trc

-- Use tkprof to generate readable file

tkprof /u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9699.trc   trace_output.txt


3.  Enabling trace using oradebug.


--Get the spid from sid.

SELECT p.spid FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id and s.sid=1105;

SPID
-----------------
3248

--- Enable tracing for that spid

SQL> oradebug setospid 3248
Oracle pid: 92, Unix process pid: 3248, image: oracle@sec58-6
SQL> oradebug EVENT 10046 trace name context forever, level 12
Statement processed.

-- Find the trace file name

SQL> oradebug TRACEFILE_NAME

/oracle/app/oracle/diag/rdbms/b2crmd2/B2CRMD2/trace/B2CRMD2_ora_3248.trc

-- Disabling trace:



SQL> oradebug setospid 3248
Oracle pid: 92, Unix process pid: 3248, image: oracle@sec58-6
SQL> oradebug event 10046 trace name context off
Statement processed.


4. 10053 trace:
10053 trace is is known as optimizer trace. Below are steps generating 10053 trace for a sql statement.

Note: To generate 10053 trace, we need to hard parse the query, So flush the sql statement from shared pool .

--- set tracefile name

SQL>alter session set tracefile_identifier='TESTOBJ_TRC';

Session altered.

SQL>alter session set events '10053 trace name context forever ,level 1';

Session altered.

-- hard parse the statement

SQL>Select count(*) from TEST_OBJ;

COUNT(*)
----------
33091072

exit

-- trace file name:

/u01/app/oracle/admin/BBCRMST1/diag/rdbms/bbcrmst1/BBCRMST1/trace/BBCRMST1_ora_9046_TESTOBJ_TRC.trc

Alternatively you can generate the 10053 trace, without executing or without hardparsing the sql statement using DBMS_SQLDIAG

suppose sql_id = dmx08r6ayx800
output trace_file=TEST_OBJ3_TRC

begin
dbms_sqldiag.dump_trace(p_sql_id=>'dmx08r6ayx800',
                        p_child_number=>0,
                        p_component=>'Compiler',
                        p_file_id=>'TEST_OBJ3_TRC');
END;
/


-- Trace file

-bash-4.1$ ls -ltr BBCRMST1_ora_27439_TEST_OBJ3_TRC.trc
-rw-r-----   1 oracle   oinstall  394822 Jun 30 14:17 BBCRMST1_ora_27439_TEST_OBJ3_TRC.trc

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;

[edit]Stop session trace

To stop SQL tracing for the current session, execute:

ALTER SESSION SET sql_trace = false;

[edit]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

********************************************************************************

Few other useful commands

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');

Tracing Individual SQL Statements

SQL trace can be initiated for an individual SQL statement by substituting the required SQL_ID into the following statement.

SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:sql_id]';

alter system set events '8103 trace name errorstack forever, level 3'; 

To turn it off 

alter system set events '8103 trace name errorstack off, level 3'; 

If you want to turn at session level( Helpful when you are able to reproduce error when executed from your session)

Please try to reproduce the error as we need a tracefile to investigate. If possible please run the procedures until we see it reproduce. 

oradebug setmypid 

alter session set max_dump_file_size=unlimited; 
alter session set db_file_multiblock_read_count=1; 
alter session set events 'immediate trace name trace_buffer_on level 1048576'; 
alter session set events '10200 trace name context forever, level 1'; 
alter session set events '8103 trace name errorstack level 3'; 
alter session set events '10236 trace name context forever, level 1'; 
alter session set tracefile_identifier='ORA8103'; 

run the query that produces the error ORA-8103 

alter session set events 'immediate trace name trace_buffer_off'; 
oradebug tracefile_name; 
exit 

When an error is reported please upload the tracefile identified above by oradebug tracefile_name 

Step 3:- Please upload your /var/adm/messages file