How to get the statment sql that caused the lock

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Sat, 27 Oct 2018 19:49:16 -0300
Message-ID: <CAJdDhaNojtFBaYfpaz31uHwTApvZUpPFTYsLTyeHp=j9syKLFg_at_mail.gmail.com>

Hello,

I using Oracle 12c R2.
I have this query that shows blocked sid:

Query 1 :
select * from gv$lock where sid in
(select sid from gv$session where blocking_session is not null);

and here , the query shows the blocker sid. These sessions are IDLE. Query 2
select * from gv$lock where sid in
(select blocking_session from gv$session where blocking_session is not null);

My doubt is : what is the query that caused the lock that ran in the blocker sessions?

I tried the query below, but did not get any query using the locked table.

select * from
(
select sql_id

,to_char(last_active_time,'dd-hh24:mi:ss') last_active
,executions
,elapsed_time/1000000 elap_tot
,decode(executions,0,elapsed_time,(elapsed_time/executions))/1000000
elap_exec

,decode(executions,0,disk_reads,(disk_reads/executions)) disk_exec
,decode(executions,0,buffer_gets,(buffer_gets/executions)) buffer_exec
,tot
,sql_text

from (select s.sql_id

            ,substr(s.sql_text,1,225) sql_text
            ,max(last_active_time) last_active_time
            ,sum(executions) executions
            ,sum(elapsed_time) elapsed_time
            ,sum(disk_reads) disk_reads
            ,sum(buffer_gets) buffer_gets
            ,count(*) tot
      from gv$sql s
          ,gv$open_cursor o
      where s.inst_id = o.inst_id
        and s.sql_id = o.sql_id
        and o.user_name = s.parsing_schema_name
        and o.sid=&vSid
        and o.inst_id = nvl('&vInstId',1)
        and s.parsing_schema_name <> 'SYS'
      group by s.sql_id
              ,substr(s.sql_text,1,225))

order by to_char(last_active_time,'yyyymmddhh24mi'), elap_exec )
where elap_exec > &vElap;

What is the way to find out the query that caused the lock (in sessions showed in the Query 2 above)

Regards
Eriovaldo

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Oct 28 2018 - 00:49:16 CEST