Tuesday, 19 October 2010

finding blocking sessions

Well I've told you how to kill a session from the database, whatever the reason. One of the reasons may be because it's a blocking session.
There are many ways of finding blocking sessions. I personally use Toad for Oracle for browsing the database, it has a Session Browser, and a Locks tab to find locking sessions.
 Also the following sql will give you the waiting and holding sessions on your instance:

SELECT w.*,
       (SELECT status
          FROM v$session ses
         WHERE ses.sid = w.holding_session)
          holding_ses_status,
       (SELECT sql_text
          FROM v$session ses, v$sql sq
         WHERE     ses.sql_address = sq.address
                and ses.sql_hash_value=sq.hash_value
               AND ses.sid = w.waiting_session
               AND ROWNUM = 1)
          waiting_sql,
       (SELECT sql_text
           FROM v$session ses, v$sql sq
         WHERE     ses.sql_address = sq.address
                and ses.sql_hash_value=sq.hash_value
               AND ses.sid = w.holding_session
               AND ROWNUM = 1)
          holding_sql
  FROM dba_waiters w

 
This sql will give you both the ORACLE SID of both the sessions and the current sql  both of them are currently running.

If you want to detect developer sessions on the production database, the following sql can be used:

 select ses.sid,pr.spid,  ses.status, ses.schemaname, ses.machine, ses.terminal, ses.osuser,ses.program,ses.module, ses.action , ses.logon_time
from v$session ses,
v$process pr
where ses.paddr=pr.addr
and lower(ses.program) in ('plsqldev.exe','toad.exe','sql developer')
order by  pr.pga_used_mem desc


It depends on the company profile if you are allowed or not to kill sessions, it's better to know that before you try to kill any session, or sometimes it's better to give them a report with what session is holding another session.

No comments:

Post a Comment