Re: How to get the statment sql that caused the lock
Date: Mon, 29 Oct 2018 08:49:47 +0000
Message-ID: <AM0PR10MB2450C34D199DBE5AA5E1F7B285F30_at_AM0PR10MB2450.EURPRD10.PROD.OUTLOOK.COM>
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.
On 29 Oct 2018, at 03:36, Mladen Gogala <gogala.mladen_at_gmail.com<mailto:gogala.mladen_at_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.
On 10/27/18 6:49 PM, Eriovaldo Andrietta wrote:
Hello,
I using Oracle 12c R2.
Query 1 :
and here , the query shows the blocker sid. These sessions are IDLE.
Query 2
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
,to_char(last_active_time,'dd-hh24:mi:ss') last_active
sent from my phone
I have this query that shows blocked sid:
select * from gv$lock where sid in
(select sid from gv$session where blocking_session is not null);
select * from gv$lock where sid in
(select blocking_session from gv$session where blocking_session is not null);
(
select sql_id
,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: (347) 321-1217
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 29 2018 - 09:49:47 CET