Set serveroutput On Size 1000000;
Set linesize 250;
Set pagesize 9999;
spool esperas.Log
Declare
Cursor Event Is
Select /*+ rule */
Count(*) Tot, w.Event
From V$session_Wait w
Where w.Event Not Like ‘pmon%’
And w.Event Not Like ‘smon%’
And Event Not Like ‘SQL*Net message from client’
Group By w.Event
Order By 1 Desc;
Cursor Event_Count(Ev In V$session_Wait.Event%Type) Is
Select /*+ rule */
a.Sql_Hash_Value Hash,
a.Username,
a.Osuser,
a.Status,
Count(*) Cant,
Avg(b.Seconds_In_Wait) Avg,
Max(b.Seconds_In_Wait) Max,
‘alter system kill session »’ || a.Sid || ‘,’ || a.Serial# || »’;’ Ks
From V$session a, V$session_Wait b
Where a.Sid = b.Sid
And b.Event = Ev
And a.Status Not Like ‘KILLED’
And a.Username Not Like ‘OPS$DAEM_SAP’
Group By Sql_Hash_Value,
a.Username,
a.Osuser,
a.Status,
‘alter system kill session »’ || a.Sid || ‘,’ || a.Serial# ||
»’;’
Having Max(b.Seconds_In_Wait) > 200
Order By Count(*);
Cursor Sql_Text(St In V$session.Sql_Hash_Value%Type) Is
Select /*+ rule */
Sql_Text
From V$sqltext
Where Hash_Value = St
Order By Piece;
Begin
Dbms_Output.Put_Line(‘————————————————————‘);
Dbms_Output.Put_Line(‘TOTAL EVENTO ‘);
Dbms_Output.Put_Line(‘————————————————————‘);
For Ev In Event Loop
Dbms_Output.Put_Line(Ev.Tot || ‘ – ‘ || Ev.Event);
End Loop;
For Ev In Event Loop
Dbms_Output.Put_Line(‘————————————————————‘);
Dbms_Output.Put_Line(‘TOTAL EVENTO ‘);
Dbms_Output.Put_Line(‘————————————————————‘);
Dbms_Output.Put_Line(Ev.Tot || ‘ – ‘ || Ev.Event);
For Ec In Event_Count(Ev.Event) Loop
Dbms_Output.Put_Line(‘ ————————————————–‘);
Dbms_Output.Put_Line(‘ USERNAME = ‘ || Ec.Username);
Dbms_Output.Put_Line(‘ OSUSER = ‘ || Ec.Osuser);
Dbms_Output.Put_Line(‘ STATUS = ‘ || Ec.Status);
Dbms_Output.Put_Line(‘ CANTIDAD = ‘ || Ec.Cant);
Dbms_Output.Put_Line(‘ AVG = ‘ || Ec.Avg);
Dbms_Output.Put_Line(‘ MAX = ‘ || Ec.Max);
Dbms_Output.Put_Line(‘ KILL = ‘ || Ec.Ks);
Dbms_Output.Put_Line(‘ —————————————————————-‘);
For St In Sql_Text(Ec.Hash) Loop
Dbms_Output.Put_Line(‘ ‘ || St.Sql_Text);
End Loop;
End Loop;
End Loop;
End;
/
spool Off