Locking and blocking information and solution

  1. Details about specified sid
  2. Script provided by oracle
  3. Find blocking session
  4. Find kill details
  5. kill -3/9 can be run on spid
  6. Queries running from more than 5 sec
  7. Details of locking objects
  8. sql being run by blocking sid
  9. Query information against the process from unix
  10. Queries running from more than 5 sec
  1. To see the details about the specified sid
    set linesize 200
    select blocking_session, sid, serial#, wait_class, seconds_in_wait
    from  gv$session where blocking_session is not null order by blocking_session
    ;
    blocking_sessionsid    serial# wait_classseconds_in_wait
    ---------------- ---------- ---------- -------------------- --------
                 148        135      61521 idle                              64

    Note: We found that session 148 is blocking session 135 and has been for 64 seconds.

  2. Run the script provided by oracle.
    @?/rdbms/admin/utllockt.sql
  3. Simple query to find the blocking session information.
    select object_id,session_id,process,locked_mode from gv$locked_object where session_id in (&session_id);
    1. This will give easy readable output for locking objects.
      set lines 100
      set pages 500
      col "lock particulars" format a100
      select
      (select username from gv$session where sid=a.sid) || '('||a.sid||')'||
      ' is blocking to '||
      (select username from gv$session where sid=b.sid) || '('||b.sid||')' "lock particulars"
      from gv$lock a, gv$lock b where a.block = 1 and b.request> 0 and a.id1 = b.id1 and a.id2 = b.id2
      ;
      
      lock particulars
      ----------------------------------------------------------------------------------------------------
      qsitbba(9) is blocking to vitvcd(7)
      qsitbba(72) is blocking to vitvcd(7)
      qsitbba(9) is blocking to  qtnsdp(72)
  4. Use below to find kill details.

    Note: need to provide the sid from above query.

    setlinesize 200
    col username format a15
    colschemaname format a15
    col program format a15
    
    select sid,serial#,username,status,schemaname,program,to_char(logon_time,'dd/mm/yyyy hh24:ss:mm') logon_time
    from v$session where sid in(&sid);
  5. >kill -3/9 can be run on spid provided by below query.

     

    set linesize 100
    column spid format a10
    column username format a10
    column program format a45
    
    selects.inst_id,
    s.sid,
    s.serial#,
    p.spid,
    s.username,
    s.program
    from gv$session s
    join gv$process p on p.addr = s.paddr and p.inst_id = s.inst_id
    where  s.sid=1950 ;
  6. >To see queries running from more than 5 sec.

    Note: new feature of 11g.

    set linesize 200
    select  s.sid, serial#, s.sql_id, (sysdate-sql_exec_start)*24*60*60 secs, sql_text from v$session s, v$sqltext t
    wheres.sql_id = t.sql_id
    and sql_exec_start is not null and piece = 0
    and (sysdate-sql_exec_start)*24*60*60 > 5
    ;
  7. To find the details of locking objects.
    set lines 180
    set pages 500
    col owner format         a15
    col object_name format  a20
    col object_type format  a20
    col sid                 a8
    col serial#     format  a50
    col status   format     a 10
    col osuser   format     a10
    col machine format     a60
    
    select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine
    from v$locked_object a , v$session b, dba_objects c
    where b.sid = a.session_id and a.object_id = c.object_id ;
  8. To find the sql being run by blocking sid
    select s.sid, s.serial#, t.sql_fulltext,t.sql_id,s.sql_hash_value,t.hash_value from v$session s, v$sql t where s.sql_address = t.address and 
    s.sql_hash_value = t.hash_value and s.sid=1950 ;
  9. To find out the query information against the process which you take from unix.
    select
    	sql_text,
    	optimizer_mode,
    	module,
    	action
    from
    	v$sqlarea
    where
    	hash_value in (
    			select
    				sql_hash_value
    			from
    				v$session
    			wherepaddr=(
    					select
    						addr
    					from
    						v$process
    					where
    						spid=&process_id
    				     )
    		      );
  10. To see the queries running from more than 5 sec
    set linesize 200
    select  s.sid, serial#, s.sql_id, (sysdate-sql_exec_start)*24*60*60 secs, sql_text from v$session s, v$sqltext t
    where s.sql_id = t.sql_id
    and sql_exec_start is not null and piece = 0
    and (sysdate-sql_exec_start)*24*60*60 > 5 ;
    
    sid    serial# sql_idsecssql_text
    ---------- ---------- ------------- ---------- ----------------------------------------------------------------
           492      46694 a0f74y2n959cbt         12 select  /*+  first_rows(1)  use_nl (p t)  */ path_name,path_doci

November 7, 2013 В· Sanjay В· No Comments
Posted in: 3. All Scripts

Leave a Reply

You must be logged in to post a comment.