Para ejecutar en SQL*Plus y obtener una salida rápida de los eventos de espera mas usuales, la información de la sesión asociada, usuario de BD, usuario de SOP, y la sentencia «kill session» en caso de que sea necesario.
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
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
Eventos de Espera por Gisela Velazco se encuentra bajo una Licencia Creative Commons Atribución-NoComercial-SinDerivadas 3.0 Unported.
hola como recupear espacio despues de un delete algo similar shrink de sql server? si mi base se encuentra en ASM stand alone..
Hola que tal, si existe la misma sentencia para Oracle:
SQL> alter table mytable enable row movement;
Table altered
SQL> alter table mytable shrink space;
Table altered
Saludos!!!