Blocking Session

Blocking Sessions 

Get Active session details

select a.sid, a.serial#, a.username,a.sql_id,
decode(a.command,
  1,'Create table' , 2,'Insert',
  3,'Select' , 6,'Update',
  7,'Delete' , 9,'Create index',
  10,'Drop index' ,11,'Alter index',
  12,'Drop table' ,13,'Create seq',
  14,'Alter sequence' ,15,'Alter table',
  16,'Drop sequ.' ,17,'Grant',
  19,'Create syn.' ,20,'Drop synonym',
  21,'Create view' ,22,'Drop view',
  23,'Validate index' ,24,'Create procedure',
  25,'Alter procedure' ,26,'Lock table',
  42,'Alter session' ,44,'Commit',
  45,'Rollback' ,46,'Savepoint',
  47,'PL/SQL Exec' ,48,'Set Transaction',
  60,'Alter trigger' ,62,'Analyze Table',
  63,'Analyze index' ,71,'Create Snapshot Log',
  72,'Alter Snapshot Log' ,73,'Drop Snapshot Log',
  74,'Create Snapshot' ,75,'Alter Snapshot',
  76,'drop Snapshot' ,85,'Truncate table',
  0,'No command', '? : '||a.command) SQLTYPE,
  a.machine,
 trunc(a.last_call_et/60) || ' mins, ' || mod(a.last_call_et,60) ||
' secs' dur,
to_char(a.logon_time,'dd/mm/yyyy hh24:mi:ss')   slogon_time,
a.OSUSER,
a.status,
a.program,
a.action,
a.module,
a.resource_consumer_group,
a.client_identifier,
a.action,
b.sql_text
from v$sql b, v$session a
where a.username is not null
--and a.last_call_et > 5*60
--and a.status = 'ACTIVE'
and a.sql_address = b.address
order by last_call_et DESC
/



Use the view v$session_wait to see what the session is waiting on at the moment 

select event, wait_time, seconds_in_wait, state
from v$session_wait
where sid = 963 ;

Below view provide wait time information for the life of the session- Not just the real time

 select event, total_waits, time_waited
 from v$session_event
 where sid = 963
 order by 3 desc


Historical session details :

select SERVICE_NAME,CLIENT_IDENTIFIER,LAST_CALL_ET from v$session where sid='1537'; ( last active)

select SESSION_ID,SESSION_SERIAL#,USER_ID,SQL_ID,PROGRAM,MODULE,CLIENT_ID from v$active_session_history where SESSION_ID='1829'


ASH report
> ?/rdbms/admin/ashrpt.sql
We can find bad queries also **


Identifying and killing blocking session 

select   'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking

from    v$lock l1, v$lock l2

where  l1.block =1 and l2.request > 0

and      l1.id1=l2.id1

and      l1.id2=l2.id2;

With time frame 

select blocking_session, sid, wait_class,

seconds_in_wait

from gv$session

where blocking_session is not NULL

order by blocking_session;

example is

      SID 'ISBLOCKING'         SID

---------- ------------- ----------

        70  IS BLOCKING          83

       169  IS BLOCKING          93

        70  IS BLOCKING         252

        70  IS BLOCKING         401

       

Get all the sql’s for the above sid

SQL> Select sid, serial#, SQL_ID from v$session where sid in ('70','83','169','93','252','401');

       SID    SERIAL# SQL_ID

---------- ---------- -------------

        70      11636 fub5sph9bggks

        83       1756 fub5sph9bggks

        93      40773 8ng1qkuz68h8n

       169      37220

       252      35544 fub5sph9bggks

       401      15470 fub5sph9bggks

       

Find out any sql having the statement “DELETE FROM Content WHERE element_id like 'T%' AND create_date < :1” and kill that session

SQL> select SQL_ID,SQL_FULLTEXT from V$SQLAREA where sql_id in ('fub5sph9bggks','8ng1qkuz68h8n');

SQL_ID

-------------

SQL_FULLTEXT

--------------------------------------------------------------------------------

8ng1qkuz68h8n

SELECT 1 FROM Relationship WHERE (name = :1 AND object_id <> :2 AND tail_id = :3

fub5sph9bggks

DELETE FROM Content WHERE element_id like 'T%' AND create_date < :1

And kill those sessions

SQL> alter system kill session '70,11636';

System altered.

SQL> alter system kill session ’83, 1756’;

System altered.

SQL> alter system kill session '252, 35544’;

System altered.

SQL> alter system kill session '401, 15470’;

System altered.