This script is one of our favorites. It finds the top N sessions that have been affected by a particular type of resource wait, and enables event 10046, level 8 in those sessions for the specified period. This event captures every wait event and its wait parameters, and writes the information to the process trace file together with the normal sql_trace output. The wait parameters can then be analyzed to understand exactly what is causing the performance problem.
declare
type sid_type is table of number index by binary_integer;
type serial_type is table of number index by binary_integer;
cursor waiters is
select
e.sid,
s.serial#
from
sys.v_$session_event e,
sys.v_$session s
where
e.event = '&Wait' and
s.sid = e.sid
order by
total_waits desc;
sid sid_type;
serial serial_type;
n binary_integer := 0;
begin
open waiters;
loop
n := n + 1;
exit when n = &Sessions + 1;
fetch waiters into sid(n), serial(n);
exit when waiters%notfound;
sys.dbms_system.set_ev(sid(n), serial(n), 10046, 8, '');
end loop;
if n > 1 then
sys.dbms_lock.sleep(&Sleep);
loop
n := n - 1;
exit when n = 0;
sys.dbms_system.set_ev(sid(n), serial(n), 10046, 0, '');
end loop;
else
sys.dbms_output.put_line('No sessions to trace.');
end if;
end;
