Identify SQL in blocking and waiting sessions

col Event format a25
col DML_BLOCKING format a45
col DML_In_Waiting format a45
set linesize 400
set pagesize 3000

-- LABEL: STATEMENT A
select  distinct
        a.sid                     as Waiting_SID
       ,d.sql_text                as DML_In_Waiting
       ,o.Owner                   as Object_Owner
       ,o.Object_Name             as Locked_Object
       ,a.Blocking_Session        as Blocking_SID
       ,c.sql_text                as DML_Blocking
from
        v$session                 a
       ,v$active_session_history  b
       ,v$sql                     c
       ,v$sql                     d
       ,all_objects               o
where
        a.event                   = 'enq: TX - row lock contention'
and     a.sql_id                  = d.sql_id
and     a.blocking_session        = b.session_id
and     c.sql_id                  = b.sql_id
and     a.Row_Wait_Obj#           = o.Object_ID
and     b.Current_Obj#            = a.Row_Wait_Obj#
and     b.Current_File#           = a.Row_Wait_File#
and     b.Current_Block#          = a.Row_Wait_Block#

-- LABEL: STATEMENT B
select  distinct
        a.sid                     as Waiting_SID
       ,a.event                   as Event
       ,c.sql_text                as DML_Blocking
       ,b.sid                     as Blocking_SID
       ,b.event                   as Event
       ,b.sql_id                  as Blocking_SQL_ID
       ,b.prev_sql_id             as Blocking_Prev_SQL_ID
       ,d.sql_text                as DML_Blocking
from
        v$session                 a
       ,v$session                 b
       ,v$sql                     c
       ,v$sql                     d
where
        a.event                   = 'enq: TX - row lock contention'
and     a.blocking_session        = b.sid
and     c.sql_id                  = a.sql_id
and     d.sql_id                  = nvl(b.sql_id,b.prev_sql_id);

The first statement is generally more accurate than the second, since
the session holding the lock may have issued several DML
statements since issuing the lock, so the DML that is output
as holding the lock may not be related to the blocking DML.

Occasionally you may find that the SQL_ID for the
session holding the lock is NULL, reason being that since issuing the
lock some PL/SQL code may have executed or a COMMIT is being performed
or some other ongoing latch activity is busy, in which case there
is no SQL_ID to identify the session with.

In such cases, join to NVL(B.SQL_ID,B.PREV_SQL_ID).


Sample output after simulating a blocking and waiting session;

In session 1, issue a full table lock;

create table test_lock (a varchar2(2), bb varchar2(2));
insert into test_lock values ('i', '1');
insert into test_lock values ('ii','2');
commit;
select * from test_lock for update;

In session 2, issue an update on one of the rows in table TEST_LOCK

update test_lock set a='i' where bb='1';

The above UPDATE statement will simply hang. Do not issue a rollback
in session1.

In Session 1, run the first SQL statement, labelled as "STATEMENT A" above.

Sample output;

WAITING_SID DML_IN_WAITING                          OBJECT_OWNER LOCKED_OBJECT                  BLOCKING_SID DML_BLOCKING
----------- --------------------------------------- ------------ ------------------------------ ------------ ----------------------------------
       2081 update test_lock set a='i' where bb='1' USER1        TEST_LOCK                               382 select * from test_lock for update

Identify blocking session for an UPDATE

Simulate a blocking session first:

Open 2 session windows, either in SQL Plus or any other cilent tool
you may be using.
In session 1 create a table and insert some data

create table test_lock (a varchar2(2), bb varchar2(2));
insert into test_lock values ('i','1');
insert into test_lock values ('ii', '2');
select * from test_lock;
commit;

Before starting the simulation, obtain the unique session ID's
for both sessions by using the following statement;

select distinct sid from v$mystat;

Obtain an exclusive lock in session 1 as follows;

select * from test_lock for update;



Now, in session 2, run these queries to identify blocking sessions;

update test_lock set a='i' where bb='1';

This statement will simply hang, since session 1 holds an exclusive lock on all rows within the table. Do not issue a rollback or commit;

Now query V$LOCK in session 1 using the following 3 statements;

set pagesize 3000
set linesize 1000
select * from v$lock;

select l1.sid, ' IS BLOCKING ', l2.sid, l1.lmode, l2.request
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;

select    s1.username
       || '@'
       || s1.machine
       || ' ( SID='
       || s1.sid
       || ' )  is blocking '
       || s2.username
       || '@'
       || s2.machine
       || ' ( SID='
       || s2.sid
       || ' ) ' AS blocking_status
from  v$lock    l1
     ,v$session s1
     ,v$lock    l2
     ,v$session s2
where s1.sid    = l1.sid
and   s2.sid    = l2.sid
and   l1.BLOCK  = 1
and   l2.request> 0
and   l1.id1    = l2.id1
and   l2.id2    = l2.id2;

Finally, use the next few statements to identify the object ID and
blocked row information.
Replace the session id in the where clause with the
waiting session id, session 2.

select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session
where sid=&sid;


Replace the SID in the where clause with that of session 2
to obtain the Object name and ROWID

select do.object_name
      ,row_wait_obj#
      ,row_wait_file#
      ,row_wait_block#
      ,row_wait_row#
      ,dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#
                                   ,ROW_WAIT_FILE#
                                   ,ROW_WAIT_BLOCK#
                                   ,ROW_WAIT_ROW# )
from   v$session       s
      ,dba_objects     do
where  sid             = &sid -- this is the SID from session2
and    s.ROW_WAIT_OBJ# = do.OBJECT_ID;



Replace the ROWID from the previous result set to obtain the
actual row data

select *
from test_lock
where rowid='AAAVnHAAQAAAp0tAAA';

Identify most scanned tables

Identify the top 20 most scanned tables.

set linesize 400
set pagesize 3000
col Owner format a15
col Object_Name format a30

select /*+ all_rows */
       *
from
    (select Inst_ID
           ,owner
           ,object_name
           ,value
     from   gv$segment_statistics
     where  statistic_name ='logical  reads'
     and    object_type='TABLE'
     order by 3 desc)
where rownum < 21
/

Generate XML elements from a SQL result set

with t1 as
  (
    select '699' col1 from dual union
    select '996'      from dual
  )
--
select  
    xmlagg (
            xmlelement ("result", xmlforest (col1 "col1"))
           )
from   t1

BULK collect XML into an OBJECT table

create or replace type ty_Rep  as object
(rep_name varchar2(40)
,prg_name varchar2(40)
,app_name varchar2(40));
/
create or replace type tb_Rep as table of ty_Rep;
/


set serveroutput on
declare

l_xml xmltype:= xmltype
('<soap:Envelope
    xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
  <fetchReportDataResponse xmlns="http://SomeLocation/">
   <fetchReportDataResult>
    <feed xmlns="urn:DA.gtt.BBN.SomeLocation.Services">
     <Table xmlns="">
      <Report>Item</Report>
      <Program>PItem</Program>
      <Application>Paris</Application>
     </Table>
     <Table xmlns="">
      <Report>App Chunk</Report>
      <Program>AC</Program>
      <Application>Paris</Application>
     </Table>
     <Table xmlns="">
      <Report>Customer</Report>
      <Program>Cust</Program>
      <Application>London</Application>
     </Table>
     <Table xmlns="">
      <Report>Delivery</Report>
      <Program>Delv</Program>
      <Application>London</Application>
     </Table>
     <Table xmlns="">
      <Report>Process</Report>
      <Program>Pr</Program>
      <Application>London</Application>
     </Table>
    </feed>
   </fetchReportDataResult>
  </fetchReportDataResponse>
 </soap:Body>
</soap:Envelope>');


  l_Rep tb_Rep := tb_Rep();

begin

  select ty_Rep(x.Report, x.Program, x.Application)
  bulk collect  into  l_Rep
  from (select l_XML as m from dual) d
       ,xmltable ('//Table' passing d.m
         columns
          Report varchar2(40) path 'Report'
         ,Program varchar2(40) path 'Program'       
         ,Application varchar2(40) path 'Application') as x;

  dbms_output.put_line(l_Rep.count);
 
  for r in 1..l_Rep.count loop
    dbms_output.put_line('Report: ' || l_Rep(r).rep_name);
    dbms_output.put_line('Program: ' || l_Rep(r).prg_name);
    dbms_output.put_line('Application: ' || l_Rep(r).app_name);
  end loop;

end;

/

Extract data from single XML node

set serveroutput on

declare
l_XML xmltype := xmltype('<?xml version="1.0" encoding="utf-8"?>
                          <customertran>
                            <tran>856</tran>
                          </customertran>');
l_Tran varchar2(10);
begin
  select x.tran
  into l_Tran
  from   (select l_XML as m from dual) d
        ,xmltable ('//customertran' passing d.m
                   columns tran varchar2(40) path 'tran') as x;
  dbms_output.put_line(l_Tran);
end;

/