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

Hi Jonathan.

I saw this your post.
I checked v$open_cursor as you said, but without sucess. I get the snap_id with the query below and extracted the AWR , also , without sucess.
select a.*
from Dba_Hist_Active_Sess_History a
where a.SESSION_ID in ( 128 ) --, 549, 1013 ); and Sample_Time Between To_Date('27/10/2018 00:00:00','DD/MM/YYYYHH24:MI:SS') And

                        TO_DATE('28/10/2018

A way to decide about to kill the blocker , with little risk is look at the time how long that the session is IDLE.

Many thanks for you answer.

Regards
Eriovaldo

Em dom, 28 de out de 2018 às 08:37, Jonathan Lewis < jonathan_at_jlcomp.demon.co.uk> escreveu:

>
> 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