Extended traces

 
-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/03/10 
-- # Name: Extended traces
-- ################################################
-- #
-- # Compatible: Oracle 8i 9i 10g 11g 12c
-- #
-- ################################################
-- #
-- # How to put an extended trace for another session
-- # 
-- # level 	Information included
-- # -----------------------------------------------------
-- # 1        Standard SQL_TRACE functionality
-- # 4        As level 1 plus tracing of bind variables
-- # 8        As level 1 plus wait events
-- # 12       As level 1 plus bind variables and wait events.
-- # 
-- # Useful when you don't want to activate traces
-- # on the whole database.
-- # 
-- # New method exists from 10g: DBMS_SESSION DBMS_MONITOR
-- # but I prefer this one
-- # 
-- ################################################

-- First, find the information (sid, serial#) to trap the user session
set lines140
col username format a40
select username, sid, serial# from v$session;

USERNAME                              SID    SERIAL#
------------------------------ ---------- ----------
                                      601      30377
myuser                                651      32901


-- Set 2 parameters in the user session
exec sys.dbms_system.set_int_param_in_session (651, 32901, 'max_dump_file_size', 2147483647)
exec sys.dbms_system.set_bool_param_in_session (651, 32901, 'timed_statistics', true)

-- Activate the traces
exec sys.dbms_system.set_ev(651, 32901 , 10046, 12, '')
-- You can also activate event 10053 level 1 for to trace the optimizer
	/*  Traces ...application runnning.....[ ].......... */

-- Deactivate the traces
exec sys.dbms_system.set_ev(651, 32901, 10046, 0, '')

-- Go to the user_dump_dest folder and make a tkprof on the trc file

-- for example, sort by execution elapsed (sort=exeela)
tkprof qb_ora_7949.trc parse_qb_ora_7949.txt sys=no  aggregate=yes sort=exeela waits=yes

-- Look at the beginning of the report to find bad SQL and verify at the end of the report 
-- that the total elapsed time corresponds to the bad SQL. 
-- If not, perhaps you have a lot of small other SQL in the main body of the report

-- It's also important to look also at the total waits

-- If you did'nt find your problem, you can also sort another predicate (example by parse elapsed or whatever...)
tkprof qb_ora_7949.trc parse_qb_ora_7949.txt sys=no  aggregate=yes sort=prsela waits=yes


-- If you did'nt find your problem, you can also trace the optimizer (EVENT=10053)
exec sys.dbms_system.set_ev(11, 12365, 10053, 1, '')


-- To activate traces for the current session:
alter session set timed_statistics = true; 
alter session set statistics_level=all; 
alter session set max_dump_file_size = unlimited; 
alter session set events '10046 trace name context forever,level 12'; 
alter session set events '10053 trace name context forever, level 1'; 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.