Re: How to get the statment sql that caused the lock
Date: Sun, 28 Oct 2018 22:41:41 -0400
Message-ID: <ac29ccfb-5ef5-e155-3082-9316b2a7d52b_at_gmail.com>
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.
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.
> 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: (347) 321-1217 -- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 29 2018 - 03:41:41 CET