Re: How to get the statment sql that caused the lock

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 28 Oct 2018 11:37:19 +0000
Message-ID: <CWXP265MB1493011EE62D17FBC42272B5A5F20_at_CWXP265MB1493.GBRP265.PROD.OUTLOOK.COM>

There is no guaranteed mechanism for finding the blocking statement. It doesn't even need to be in memory any more. See: https://jonathanlewis.wordpress.com/2009/04/19/locking-sql/

The best you can do is a "reasonable guess" - check v$open_cursor for the blocker to see if it reports any statements that look as if they have modified the table that the blocked statement is modifying. If you find any such statements then check actual SQL to decide whether or not each statement could be the blocker.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Eriovaldo Andrietta <ecandrietta_at_gmail.com> Sent: 27 October 2018 23:49:16
To: ORACLE-L
Subject: How to get the statment sql that caused the lock

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 - 12:37:19 CET