How to get the statment sql that caused the lock

[Eriovaldo Andrietta]

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

[Jonathan Lewis]

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-***@freelists.org <oracle-l-***@freelists.org> on behalf of Eriovaldo Andrietta <***@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

[Eriovaldo Andrietta]

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
23:59:01','DD/MM/YYYYHH24:MI:SS')
order by 2;

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 <

Post by Jonathan Lewis
There is no guaranteed mechanism for finding the blocking statement. It
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
________________________________________
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.
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

[Mladen Gogala]

Hi Eriovaldo!

Queries do not lock tables, unless the queries are distributed, as in
"query over a database link". For everything  else, queries to not lock
tables.

Second, the fact that the sessions are idle is irrelevant. If I execute
"LOCK TABLE SCOTT.EMP IN EXCLUSIVE MODE" and go to lunch, my session
will be idle. It will also keep the lock until the transaction ends.
Locks are caused by 5 statements:  INSERT, UPDATE, DELETE, TRUNCATE and
LOCK. For the purpose of this consideration, we will consider MERGE to
be an update & insert combination.

So, if you have a SID, you should be able to find out what objects does
the session have locked and in what mode. Sometimes, that can be a load
of fun. While I was a DBA, I worked with a developer who learned that
/*+ APPEND */ hint for inserts "makes things go faster". So she has put
the /*+ APPEND */ hint to each and every insert statement in her
multi-user web  application. For some inexplicable reason, things did
not go faster, but I did have some fun.

* Idle sessions can hold locks, for a very long time. That was the
primary reason for inventing resource limits.
* Queries, except in very rare cases, DO NOT LOCK anything.
* V$LOCK will tell what objects are locked and in what mode. Unless
you are using 18.3 or newer, you cannot kill a statement (actually,
not true: you can kill the current statement by using kill -URG, but
very few people know that and even fewer do that).
* Once you figure out that an idle session is blocking you by holding
a lock, you kill it without further ado and ask the application
developer to fix the bug. There is absolutely no reason whatsoever 
for an idle session to hold locks. Locks are means for preserving
consistency. You want to prevent someone else from modifying a
critical resource until your transaction is finished. You don't hold
locks on resources until an upgrade to a new Oracle version takes
you apart. TRANSACTION != MARRIAGE.
* Just for completeness, the /*+ APPEND */ hint causes the insert to
allocate a batch of empty blocks below the high water mark and
effectively extend the table. That requires an exclusive lock on the
table. And exclusive locks can be bad for concurrency.

Post by Eriovaldo Andrietta
Hello,
I using Oracle 12c R2.
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

[Andy Sayer]

“Queries do not lock tables, unless the queries are distributed, as in
"query over a database link". For everything else, queries to not lock
tables.”

Maybe I’m misunderstanding, but a select over a DB link does not lock the
table/row in any way. You do start a transaction and acquire a DX type lock
but that does not effect the table.

A select statement will put a lock on the rows selected when the ‘for
update’ clause is used. These can be very common for some applications and
not used at all in others.

I enjoyed the anecdote about /*+append*/, note that thankfully Oracle is
sensible enough for the hint not to apply for insert..values, only
insert..select statements. You would use insert_values hint if you really
wanted to use direct path insert with insert..values, this could be
legitimate in cases using huge array binds but I’d be skepticle.

Regards,
Andy

Post by Mladen Gogala
Hi Eriovaldo!
Queries do not lock tables, unless the queries are distributed, as in
"query over a database link". For everything else, queries to not lock
tables.
Second, the fact that the sessions are idle is irrelevant. If I execute
"LOCK TABLE SCOTT.EMP IN EXCLUSIVE MODE" and go to lunch, my session will
be idle. It will also keep the lock until the transaction ends. Locks are
caused by 5 statements: INSERT, UPDATE, DELETE, TRUNCATE and LOCK. For the
purpose of this consideration, we will consider MERGE to be an update &
insert combination.
So, if you have a SID, you should be able to find out what objects does
the session have locked and in what mode. Sometimes, that can be a load of
fun. While I was a DBA, I worked with a developer who learned that /*+
APPEND */ hint for inserts "makes things go faster". So she has put the /*+
APPEND */ hint to each and every insert statement in her multi-user web
application. For some inexplicable reason, things did not go faster, but I
did have some fun.
- Idle sessions can hold locks, for a very long time. That was the
primary reason for inventing resource limits.
- Queries, except in very rare cases, DO NOT LOCK anything.
- V$LOCK will tell what objects are locked and in what mode. Unless
you are using 18.3 or newer, you cannot kill a statement (actually, not
true: you can kill the current statement by using kill -URG, but very few
people know that and even fewer do that).
- Once you figure out that an idle session is blocking you by holding
a lock, you kill it without further ado and ask the application developer
to fix the bug. There is absolutely no reason whatsoever for an idle
session to hold locks. Locks are means for preserving consistency. You want
to prevent someone else from modifying a critical resource until your
transaction is finished. You don't hold locks on resources until an upgrade
to a new Oracle version takes you apart. TRANSACTION != MARRIAGE.
- Just for completeness, the /*+ APPEND */ hint causes the insert to
allocate a batch of empty blocks below the high water mark and effectively
extend the table. That requires an exclusive lock on the table. And
exclusive locks can be bad for concurrency.
Hello,
I using Oracle 12c R2.
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
--
Mladen Gogala
Database Consultant
Tel: [phone]

[Mladen Gogala]

Oh yes, queries in the distributed queries do lock tables because it is
not possible to ensure that the query will return only the data
committed before the start of the query without a lock.  You must have a
join with a remote table, but yes, it does happen.

Regards

“Queries do not lock tables, unless the queries are distributed, as in
"query over a database link". For everything  else, queries to not
lock tables.”
Maybe I’m misunderstanding, but a select over a DB link does not lock
the table/row in any way. You do start a transaction and acquire a DX
type lock but that does not effect the table.

[Neil Chandler]

Queries can lock tables:

SELECT... FOR UPDATE;

I find this is a common cause of blocking and is easy to overlook as you might miss the FOR UPDATE.

Table DDL can also lock the resource by dropping a TM lock on there.

Neil.
sent from my phone

On 29 Oct 2018, at 03:36, Mladen Gogala <***@gmail.com<mailto:***@gmail.com>> wrote:


Hi Eriovaldo!

Queries do not lock tables, unless the queries are distributed, as in "query over a database link". For everything else, queries to not lock tables.

Second, the fact that the sessions are idle is irrelevant. If I execute "LOCK TABLE SCOTT.EMP IN EXCLUSIVE MODE" and go to lunch, my session will be idle. It will also keep the lock until the transaction ends. Locks are caused by 5 statements: INSERT, UPDATE, DELETE, TRUNCATE and LOCK. For the purpose of this consideration, we will consider MERGE to be an update & insert combination.

So, if you have a SID, you should be able to find out what objects does the session have locked and in what mode. Sometimes, that can be a load of fun. While I was a DBA, I worked with a developer who learned that /*+ APPEND */ hint for inserts "makes things go faster". So she has put the /*+ APPEND */ hint to each and every insert statement in her multi-user web application. For some inexplicable reason, things did not go faster, but I did have some fun.

* Idle sessions can hold locks, for a very long time. That was the primary reason for inventing resource limits.
* Queries, except in very rare cases, DO NOT LOCK anything.
* V$LOCK will tell what objects are locked and in what mode. Unless you are using 18.3 or newer, you cannot kill a statement (actually, not true: you can kill the current statement by using kill -URG, but very few people know that and even fewer do that).
* Once you figure out that an idle session is blocking you by holding a lock, you kill it without further ado and ask the application developer to fix the bug. There is absolutely no reason whatsoever for an idle session to hold locks. Locks are means for preserving consistency. You want to prevent someone else from modifying a critical resource until your transaction is finished. You don't hold locks on resources until an upgrade to a new Oracle version takes you apart. TRANSACTION != MARRIAGE.
* Just for completeness, the /*+ APPEND */ hint causes the insert to allocate a batch of empty blocks below the high water mark and effectively extend the table. That requires an exclusive lock on the table. And exclusive locks can be bad for concurrency.

On 10/27/18 6:49 PM, Eriovaldo Andrietta wrote:
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

[Eriovaldo Andrietta]

Hi Gogala,

Thanks for your comments.
When I wrote "query" I want mean : sql statements executing : INSERT,
UPDATE, DELETE, TRUNCATE and LOCK (select for update).

I agree, we can get the objetct locked, and for it I used this query:
select
sb.sid sid_blocker
,sb.inst_id inst_id_blocker
,sb.event
,substr(sb.program,1,25) program
,o.OBJECT_NAME
,CASE WHEN
o.object_type = 'TABLE' THEN
dbms_rowid.rowid_create ( 1, o.DATA_OBJECT_ID,
sb.ROW_WAIT_FILE#, sb.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW# )
ELSE
NULL
END rowid_bloocked
from gv$session s
,gv$session sb
,dba_objects o
where s.seconds_in_wait > 60
and s.BLOCKING_SESSION = sb.sid
and s.BLOCKING_INSTANCE = sb.INST_ID
and sb.ROW_WAIT_OBJ# = o.OBJECT_ID;

My purpose is to retrieve the sql_id related to the transaction line in the
view v$transaction that is responsible for the execution of the : INSERT,
UPDATE, DELETE ... in order to show it to the developer and validate the
application if need to add commit in the code, if does not exists.

I received a message from a member of the group , like this :

I asked Oracle (SR 3-12200129251 : Request for ID of SQL responsible for
transaction to be added to v$transaction) and they created an enhancement
request
Bug 24920354 : ADD SQL_ID COLUMN TO V$TRANSACTION OF THE SQL THAT STARTED
THE TRANSACTION

But two years have passed. They did nothing about it.

This is what I am locking for.

Thanks and Regards

Eriovaldo

Post by Neil Chandler
SELECT... FOR UPDATE;
I find this is a common cause of blocking and is easy to overlook as you
might miss the FOR UPDATE.
Table DDL can also lock the resource by dropping a TM lock on there.
Neil.
sent from my phone
Hi Eriovaldo!
Queries do not lock tables, unless the queries are distributed, as in
"query over a database link". For everything else, queries to not lock
tables.
Second, the fact that the sessions are idle is irrelevant. If I execute
"LOCK TABLE SCOTT.EMP IN EXCLUSIVE MODE" and go to lunch, my session will
be idle. It will also keep the lock until the transaction ends. Locks are
caused by 5 statements: INSERT, UPDATE, DELETE, TRUNCATE and LOCK. For the
purpose of this consideration, we will consider MERGE to be an update &
insert combination.
So, if you have a SID, you should be able to find out what objects does
the session have locked and in what mode. Sometimes, that can be a load of
fun. While I was a DBA, I worked with a developer who learned that /*+
APPEND */ hint for inserts "makes things go faster". So she has put the /*+
APPEND */ hint to each and every insert statement in her multi-user web
application. For some inexplicable reason, things did not go faster, but I
did have some fun.
- Idle sessions can hold locks, for a very long time. That was the
primary reason for inventing resource limits.
- Queries, except in very rare cases, DO NOT LOCK anything.
- V$LOCK will tell what objects are locked and in what mode. Unless
you are using 18.3 or newer, you cannot kill a statement (actually, not
true: you can kill the current statement by using kill -URG, but very few
people know that and even fewer do that).
- Once you figure out that an idle session is blocking you by holding
a lock, you kill it without further ado and ask the application developer
to fix the bug. There is absolutely no reason whatsoever for an idle
session to hold locks. Locks are means for preserving consistency. You want
to prevent someone else from modifying a critical resource until your
transaction is finished. You don't hold locks on resources until an upgrade
to a new Oracle version takes you apart. TRANSACTION != MARRIAGE.
- Just for completeness, the /*+ APPEND */ hint causes the insert to
allocate a batch of empty blocks below the high water mark and effectively
extend the table. That requires an exclusive lock on the table. And
exclusive locks can be bad for concurrency.
Hello,
I using Oracle 12c R2.
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
--
Mladen Gogala
Database Consultant
Tel: [phone]

[Jonathan Lewis]

Eriovaldo,

I am surprised that anyone even bothered to create that enhancement request, but someone show have marked it is "ignore" by now.

Consider the following transaction:

update t1 set n1 = 99 where id = :b1;
update t2 set a1 = 66
update t1 set n4 = 33 where id1 = :b2

Your ER asked for the sql_id of the first statement to be stored in v$transaction.
How are you, while trouble-shooting, going to behave it it's actually the third statement that is blocking you ?


Regards
Jonathan Lewis



________________________________________
From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> on behalf of Eriovaldo Andrietta <***@gmail.com>
Sent: 29 October 2018 11:39:03
To: ***@hotmail.com
Cc: Mladen Gogala; ORACLE-L
Subject: Re: How to get the statment sql that caused the lock

Hi Gogala,

Thanks for your comments.
When I wrote "query" I want mean : sql statements executing : INSERT, UPDATE, DELETE, TRUNCATE and LOCK (select for update).

I agree, we can get the objetct locked, and for it I used this query:
select
sb.sid sid_blocker
,sb.inst_id inst_id_blocker
,sb.event
,substr(sb.program,1,25) program
,o.OBJECT_NAME
,CASE WHEN
o.object_type = 'TABLE' THEN
dbms_rowid.rowid_create ( 1, o.DATA_OBJECT_ID, sb.ROW_WAIT_FILE#, sb.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW# )
ELSE
NULL
END rowid_bloocked
from gv$session s
,gv$session sb
,dba_objects o
where s.seconds_in_wait > 60
and s.BLOCKING_SESSION = sb.sid
and s.BLOCKING_INSTANCE = sb.INST_ID
and sb.ROW_WAIT_OBJ# = o.OBJECT_ID;

My purpose is to retrieve the sql_id related to the transaction line in the view v$transaction that is responsible for the execution of the : INSERT, UPDATE, DELETE ... in order to show it to the developer and validate the application if need to add commit in the code, if does not exists.

I received a message from a member of the group , like this :

I asked Oracle (SR 3-12200129251 : Request for ID of SQL responsible for transaction to be added to v$transaction) and they created an enhancement request
Bug 24920354 : ADD SQL_ID COLUMN TO V$TRANSACTION OF THE SQL THAT STARTED THE TRANSACTION

But two years have passed. They did nothing about it.

This is what I am locking for.

Thanks and Regards

Eriovaldo


Em seg, 29 de out de 2018 às 05:51, Neil Chandler <***@hotmail.com<mailto:***@hotmail.com>> escreveu:
Queries can lock tables:

SELECT... FOR UPDATE;

I find this is a common cause of blocking and is easy to overlook as you might miss the FOR UPDATE.

Table DDL can also lock the resource by dropping a TM lock on there.

Neil.
sent from my phone

On 29 Oct 2018, at 03:36, Mladen Gogala <***@gmail.com<mailto:***@gmail.com>> wrote:


Hi Eriovaldo!

Queries do not lock tables, unless the queries are distributed, as in "query over a database link". For everything else, queries to not lock tables.

Second, the fact that the sessions are idle is irrelevant. If I execute "LOCK TABLE SCOTT.EMP IN EXCLUSIVE MODE" and go to lunch, my session will be idle. It will also keep the lock until the transaction ends. Locks are caused by 5 statements: INSERT, UPDATE, DELETE, TRUNCATE and LOCK. For the purpose of this consideration, we will consider MERGE to be an update & insert combination.

So, if you have a SID, you should be able to find out what objects does the session have locked and in what mode. Sometimes, that can be a load of fun. While I was a DBA, I worked with a developer who learned that /*+ APPEND */ hint for inserts "makes things go faster". So she has put the /*+ APPEND */ hint to each and every insert statement in her multi-user web application. For some inexplicable reason, things did not go faster, but I did have some fun.

* Idle sessions can hold locks, for a very long time. That was the primary reason for inventing resource limits.
* Queries, except in very rare cases, DO NOT LOCK anything.
* V$LOCK will tell what objects are locked and in what mode. Unless you are using 18.3 or newer, you cannot kill a statement (actually, not true: you can kill the current statement by using kill -URG, but very few people know that and even fewer do that).
* Once you figure out that an idle session is blocking you by holding a lock, you kill it without further ado and ask the application developer to fix the bug. There is absolutely no reason whatsoever for an idle session to hold locks. Locks are means for preserving consistency. You want to prevent someone else from modifying a critical resource until your transaction is finished. You don't hold locks on resources until an upgrade to a new Oracle version takes you apart. TRANSACTION != MARRIAGE.
* Just for completeness, the /*+ APPEND */ hint causes the insert to allocate a batch of empty blocks below the high water mark and effectively extend the table. That requires an exclusive lock on the table. And exclusive locks can be bad for concurrency.

On 10/27/18 6:49 PM, Eriovaldo Andrietta wrote:
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




--
Mladen Gogala
Database Consultant
Tel: [phone]

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

[Eriovaldo Andrietta]

Hi Jonathan,

I understand that , if we have and index in the column id, the first and
the third line will lock the line, according the bind variable.

it is running in session S1
update t1 set n1 = 99 where id = :b1;
update t2 set a1 = 66
update t1 set n4 = 33 where id1 = :b2

Imagine another session (S2) using the same ID for update , it will be
stopped until the first session apply the commit.
This is a basic concept of the lock.

The issue is that when we have millions of updates, inserts, and deletes in
the production environment, the sql that triggered the transaction may no
longer be in the SGA, even though it triggered a transaction.

Regards
Eriovaldo




Em seg, 29 de out de 2018 às 08:45, Jonathan Lewis <

Post by Jonathan Lewis
Eriovaldo,
I am surprised that anyone even bothered to create that enhancement
request, but someone show have marked it is "ignore" by now.
update t1 set n1 = 99 where id = :b1;
update t2 set a1 = 66
update t1 set n4 = 33 where id1 = :b2
Your ER asked for the sql_id of the first statement to be stored in v$transaction.
How are you, while trouble-shooting, going to behave it it's actually the
third statement that is blocking you ?
Regards
Jonathan Lewis
________________________________________
Sent: 29 October 2018 11:39:03
Cc: Mladen Gogala; ORACLE-L
Subject: Re: How to get the statment sql that caused the lock
Hi Gogala,
Thanks for your comments.
When I wrote "query" I want mean : sql statements executing : INSERT,
UPDATE, DELETE, TRUNCATE and LOCK (select for update).
select
sb.sid sid_blocker
,sb.inst_id inst_id_blocker
,sb.event
,substr(sb.program,1,25) program
,o.OBJECT_NAME
,CASE WHEN
o.object_type = 'TABLE' THEN
dbms_rowid.rowid_create ( 1, o.DATA_OBJECT_ID,
sb.ROW_WAIT_FILE#, sb.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW# )
ELSE
NULL
END rowid_bloocked
from gv$session s
,gv$session sb
,dba_objects o
where s.seconds_in_wait > 60
and s.BLOCKING_SESSION = sb.sid
and s.BLOCKING_INSTANCE = sb.INST_ID
and sb.ROW_WAIT_OBJ# = o.OBJECT_ID;
My purpose is to retrieve the sql_id related to the transaction line in
INSERT, UPDATE, DELETE ... in order to show it to the developer and
validate the application if need to add commit in the code, if does not
exists.
I asked Oracle (SR 3-12200129251 : Request for ID of SQL responsible for
transaction to be added to v$transaction) and they created an enhancement
request
Bug 24920354 : ADD SQL_ID COLUMN TO V$TRANSACTION OF THE SQL THAT STARTED THE TRANSACTION
But two years have passed. They did nothing about it.
This is what I am locking for.
Thanks and Regards
Eriovaldo
Em seg, 29 de out de 2018 às 05:51, Neil Chandler <
SELECT... FOR UPDATE;
I find this is a common cause of blocking and is easy to overlook as you
might miss the FOR UPDATE.
Table DDL can also lock the resource by dropping a TM lock on there.
Neil.
sent from my phone
Hi Eriovaldo!
Queries do not lock tables, unless the queries are distributed, as in
"query over a database link". For everything else, queries to not lock
tables.
Second, the fact that the sessions are idle is irrelevant. If I execute
"LOCK TABLE SCOTT.EMP IN EXCLUSIVE MODE" and go to lunch, my session will
be idle. It will also keep the lock until the transaction ends. Locks are
caused by 5 statements: INSERT, UPDATE, DELETE, TRUNCATE and LOCK. For the
purpose of this consideration, we will consider MERGE to be an update &
insert combination.
So, if you have a SID, you should be able to find out what objects does
the session have locked and in what mode. Sometimes, that can be a load of
fun. While I was a DBA, I worked with a developer who learned that /*+
APPEND */ hint for inserts "makes things go faster". So she has put the /*+
APPEND */ hint to each and every insert statement in her multi-user web
application. For some inexplicable reason, things did not go faster, but I
did have some fun.
* Idle sessions can hold locks, for a very long time. That was the
primary reason for inventing resource limits.
* Queries, except in very rare cases, DO NOT LOCK anything.
* V$LOCK will tell what objects are locked and in what mode. Unless
you are using 18.3 or newer, you cannot kill a statement (actually, not
true: you can kill the current statement by using kill -URG, but very few
people know that and even fewer do that).
* Once you figure out that an idle session is blocking you by holding
a lock, you kill it without further ado and ask the application developer
to fix the bug. There is absolutely no reason whatsoever for an idle
session to hold locks. Locks are means for preserving consistency. You want
to prevent someone else from modifying a critical resource until your
transaction is finished. You don't hold locks on resources until an upgrade
to a new Oracle version takes you apart. TRANSACTION != MARRIAGE.
* Just for completeness, the /*+ APPEND */ hint causes the insert to
allocate a batch of empty blocks below the high water mark and effectively
extend the table. That requires an exclusive lock on the table. And
exclusive locks can be bad for concurrency.
Hello,
I using Oracle 12c R2.
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
--
Mladen Gogala
Database Consultant
Tel: [phone]

[Mladen Gogala]

But why are you looking for that? If there is an active session blocking
your transaction, you'll have to wait until that session ends its
transaction. If the blocking session is idle, make note of the
application that started the session, terminate the session with
prejudice and a strong Austrian accent, and tell the developer who wrote
the the application to fix the darned thing. Applications should never
become idle while holding locks. Period.

Post by Eriovaldo Andrietta
My purpose is to retrieve the sql_id related to the transaction line
in the view v$transaction that is responsible for the execution of
the  : INSERT, UPDATE, DELETE ... in order to show it to the developer
and validate the application if need to add commit  in the code, if
does not exists.
I asked Oracle (SR 3-12200129251 : Request for ID of SQL responsible
for transaction to be added to v$transaction) and they created an
enhancement request
Bug 24920354 : ADD SQL_ID COLUMN TO V$TRANSACTION OF THE SQL THAT
STARTED THE TRANSACTION
But two years have passed. They did nothing about it.
This is what I am locking for.

[Andy Sayer]

“Oh yes, queries in the distributed queries do lock tables because it is
not possible to ensure that the query will return only the data
committed before the start of the query without a lock. You must have a
join with a remote table, but yes, it does happen.”

Maybe the last time you tested this was in a very old version which
suffered from this problem but this is simply not what happens now. Regular
MVRC works fine over a DB link.

Or maybe the remote database was a different RDBMS.

If you have a test case then I’d be very interested in seeing it.

[Mladen Gogala]

I tested with Oracle 11.2.0.4, the latest version that I administered.
There was a distributed transaction containing a join with a remote
table. Transaction failed because of the network glitch. Remote table
remained locked. At no point was any update done to the remote table.
The problem is that with remote tables there is something called "2PC"
or 2 phased commit. If "prepare for commit" issued, but not followed by
"commit",  the remote objects may remain locked. And that is your
scenario. This has to be resolved by using DBMS_TRANSACTION package.

Regards

Post by Andy Sayer
“Oh yes, queries in the distributed queries do lock tables because it is
not possible to ensure that the query will return only the data
committed before the start of the query without a lock.  You must have a
join with a remote table, but yes, it does happen.”
Maybe the last time you tested this was in a very old version which
suffered from this problem but this is simply not what happens now.
Regular MVRC works fine over a DB link.
Or maybe the remote database was a different RDBMS.
If you have a test case then I’d be very interested in seeing it.

[Eriovaldo Andrietta]

Hi Gogala,
This is exactly what I am doing.
I asked application to use the dbms_application_info to set data to be
enable to identify the application module, user and anothers things ...
Regards
Eriovaldo

Post by Mladen Gogala
But why are you looking for that? If there is an active session blocking
your transaction, you'll have to wait until that session ends its
transaction. If the blocking session is idle, make note of the application
that started the session, terminate the session with prejudice and a strong
Austrian accent, and tell the developer who wrote the the application to
fix the darned thing. Applications should never become idle while holding
locks. Period.
My purpose is to retrieve the sql_id related to the transaction line in
INSERT, UPDATE, DELETE ... in order to show it to the developer and
validate the application if need to add commit in the code, if does not
exists.
I asked Oracle (SR 3-12200129251 : Request for ID of SQL responsible for
transaction to be added to v$transaction) and they created an enhancement
request
Bug 24920354 : ADD SQL_ID COLUMN TO V$TRANSACTION OF THE SQL THAT STARTED
THE TRANSACTION
But two years have passed. They did nothing about it.
This is what I am locking for.
--
Mladen Gogala
Database Consultant
Tel: [phone]

[Mark W. Farnham]

“Applications should never become idle while holding locks. Period.”



While I agree with this, the reality in the field is that a plethora of multiple statement logical units of work that are submitted remotely from the user tier or middle tier are in fact transmitted as if connections never break.

Routinely this means the latency of transmission of the code, parsing, and execution of the 2nd through nth statements of an n component unit of work are potentially increasing the time locks are held far above the time that would occur if all the statements resided on the host before the transaction commenced.



When a transaction is interrupted by any sort of a network hiccup (most insidiously between a client machine and the middle tier with no loss of connectivity between a middle tier and the database apparent unless a timeout is configured) then the locks on the statements of the transaction thus far in play can be held for a very long time.



I also agree this should be fixed and not left to cause confusion and delay.



BUT, it may not be the developer who can control this behavior, and it may have to be escalated to the person or team who can cause a change in the topology of transactions in the infrastructure or architecture.



There are ways to slice the onion better, either with service deamons or the entire smart database (previously called thick) as per Toons and Bryn, Bryn and Toons.

But I think some developers are caught within their scope of control to be unable to do more than explain the problem to the team who decided on the infrastructure topology.



As for JL’s depressing recognition that transaction is different from statement so that the trivial single column value identifier won’t lead to the offender in the context of that enhancement request, I believe he is correct and that the problem is exacerbated by the remote submission of statements of a transaction one piece at a time. That would require holding all the sql_ids of a transaction and the statement body of each somewhere. Now that might also be trivial for transactions that are embodied as PL/SQL stored packages so they could at least tell you the chunk of code to look at.



//soapbox warning:



If you don’t have all the code and partial success alternatives in hand on the RDBMS server to generate an answer and complete a transaction, then you shouldn’t be starting the transaction.



This has been clear since at least 1990, early in the implementation of client server enterprise class applications.

It seems it is too difficult a concept to master for flavor of the month UI tool builders. One toolkit that facilitates doing exactly that is Oracle’s PL/SQL. I continue to think they are the best game in town and I don’t miss an opportunity to remind Oracle that would be the best investment to gain market share in the cloud. Just remember the mythical man month and don’t overwhelm Bryn, et. al. as you add resources to the team.



off soapbox//



mwf



From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Mladen Gogala
Sent: Monday, October 29, 2018 9:27 AM
To: Eriovaldo Andrietta; ***@hotmail.com
Cc: ORACLE-L
Subject: Re: How to get the statment sql that caused the lock



But why are you looking for that? If there is an active session blocking your transaction, you'll have to wait until that session ends its transaction. If the blocking session is idle, make note of the application that started the session, terminate the session with prejudice and a strong Austrian accent, and tell the developer who wrote the the application to fix the darned thing. Applications should never become idle while holding locks. Period.

On 10/29/18 7:39 AM, Eriovaldo Andrietta wrote:

My purpose is to retrieve the sql_id related to the transaction line in the view v$transaction that is responsible for the execution of the : INSERT, UPDATE, DELETE ... in order to show it to the developer and validate the application if need to add commit in the code, if does not exists.

I received a message from a member of the group , like this :



I asked Oracle (SR 3-12200129251 : Request for ID of SQL responsible for transaction to be added to v$transaction) and they created an enhancement request

Bug 24920354 : ADD SQL_ID COLUMN TO V$TRANSACTION OF THE SQL THAT STARTED THE TRANSACTION



But two years have passed. They did nothing about it.



This is what I am locking for.

[Mladen Gogala]

YESSS! That is precisely the scenario I described as "distributed
queries causing locks".

Post by Mark W. Farnham
When a transaction is interrupted by any sort of a network hiccup
(most insidiously between a client machine and the middle tier with no
loss of connectivity between a middle tier and the database apparent
unless a timeout is configured) then the locks on the statements of
the transaction thus far in play can be held for a very long time.

[Mark W. Farnham]

Well, not exactly. I’m not talking about multiple DB servers or multiple databases. I’m talking about clients to middle tiers tossing and executing code at the database one statement at a time, starting a transaction that is not even distributed causing a lock of indeterminate duration.



I’m not arguing about when locks do to two phase commit can be stranded, which is another real case, but where at least the developer IS likely to be in control of the situation explicitly.







From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Mladen Gogala
Sent: Monday, October 29, 2018 6:22 PM
To: Mark W. Farnham; 'Eriovaldo Andrietta'; ***@hotmail.com
Cc: 'ORACLE-L'
Subject: Re: How to get the statment sql that caused the lock



YESSS! That is precisely the scenario I described as "distributed queries causing locks".

On 10/29/18 11:06 AM, Mark W. Farnham wrote:

When a transaction is interrupted by any sort of a network hiccup (most insidiously between a client machine and the middle tier with no loss of connectivity between a middle tier and the database apparent unless a timeout is configured) then the locks on the statements of the transaction thus far in play can be held for a very long time.

[Andrea Monti]

HI Eriovaldo
I do not think it's possible to "find the sql that couased the lock";
however, you may find the row which is currently locked and blocking:
select ROW_WAIT_OBJ# from v$session where sid = :BLOCKED_SID

is the object_id where the row is and
select dbms_rowid.rowid_create(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) as wanted_rowid from v$session where sid =
:BLOCKED_SID

is the rowid which is currently blocked and waited for
HTH

regards
Andrea


Il giorno dom 28 ott 2018 alle ore 00:50 Eriovaldo Andrietta <

Post by Eriovaldo Andrietta
Hello,
I using Oracle 12c R2.
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