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 <neil_chandler_at_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 <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.
>
> - 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: (347) 321-1217
>
>

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