Home » RDBMS Server » Performance Tuning » How to find out who is the locking SQL statement? (Oracle Database 11g Release 11.2.0.1.0 - 64bit Production)
How to find out who is the locking SQL statement? [message #559576] Wed, 04 July 2012 07:12 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi all,

I have spent some time investigating the issue of locking rows of session "a" by a SQL statement,
execute by session "b".

Yet, could not find a complete answer to the question:
who is the SQL locking my session?

I've made the following testcase:


first, create a table:

SQL>  CREATE TABLE "ANDREY"."TEST"
  2    (    "ACOL" VARCHAR2(5)
  3    ) ;

Table created.



Then, inserted data into the table:

SQL> insert into "ANDREY"."TEST" values('Tom');

1 row created.

SQL> insert into "ANDREY"."TEST" values('John');

1 row created.

SQL> insert into "ANDREY"."TEST" values('Dana');

1 row created.

SQL> insert into "ANDREY"."TEST" values('Jenny');

1 row created.

SQL> commit;

Commit complete.


Then, connected via two different sessions:

first session:

SQL>  conn andrey@connstr
Enter password:
Connected.
SQL>
SQL> select userenv('sid') usersid from dual;

   USERSID
----------
        96


second session:

SQL> conn andrey@connstr
Enter password:
Connected.
SQL>
SQL>
SQL> select userenv('sid') usersid from dual;

   USERSID
----------
       102



then, from the session of first session:

SQL> --first session
SQL>
SQL> select * from test for update;

ACOL
-----
Tom
John
Dana
Jenny

SQL>


From the second session ,
we try to do the same and get blocked and hung:

SQL> --second session

SQL> select * from test for update;



Then, in the first session(the one blocking the rows of the table),
I continue working, executing all kinds of other statements:

SQL> --first session
SQL>
SQL> select * from dual;

D
-
X

SQL> select * from dual where 1=1;

D
-
X

SQL>



Now,
The DBA is reported that there is a locking problem,
caused by two batch processes(the two sessions).

He tries to query dba_waiters,
to identify blocking session and blocked session
(I had some hard time with formatting this in SQL_PLUS,
so i used pl\sql developer):

WAITING_SESSION	HOLDING_SESSION	LOCK_TYPE	MODE_HELD	MODE_REQUESTED	LOCK_ID1	LOCK_ID2
102	        96	        Transaction	Exclusive       Exclusive	983064	        923524



So He sees that the culprit is session 96,
causing vital process of session 102 to hang.

He wants to understand *why* this is happening,
who is the locking SQL statement.

so he executes:

SQL> select sql_id,prev_sql_id
  2  from v$session
  3  where sid=96;

SQL_ID        PREV_SQL_ID
------------- -------------
90ucj2wnvm8sy dgkru4k5fhqr1




He checks those two sql id's in v$sql_area and v$sql,
to see what is the locking SQL, to give the developers
the exact SQL statement causing the hanging of 102:

SQL> select sql_text from v$sqlarea where sql_id in ('90ucj2wnvm8sy', 'dgkru4k5fhqr1');

SQL_TEXT
--------------------------------------------------------------------------------
select WAITING_SESSION wt_ssn, HOLDING_SESSION hld_ssn, LOCK_TYPE  lck_typ, MODE
_HELD  mod_hld, MODE_REQUESTED  mod_rqs, LOCK_ID1  lck_id1, LOCK_ID2  lck_id2 fr
om dba_waiters

select sql_id,prev_sql_id from v$session where sid=96

SQL> select sql_text from v$sql where sql_id in ('90ucj2wnvm8sy', 'dgkru4k5fhqr1');

SQL_TEXT
--------------------------------------------------------------------------------
select WAITING_SESSION wt_ssn, HOLDING_SESSION hld_ssn, LOCK_TYPE  lck_typ, MODE
_HELD  mod_hld, MODE_REQUESTED  mod_rqs, LOCK_ID1  lck_id1, LOCK_ID2  lck_id2 fr
om dba_waiters

select sql_id,prev_sql_id from v$session where sid=96

SQL>



As you see, non of this is not the query that is causing session 102 to hang.

How can the DBA find the query causing the session to hang?

Thanks & Regards,
Andrey

[Updated on: Wed, 04 July 2012 07:14]

Report message to a moderator

Re: How to find out who is the locking SQL statement? [message #559578 is a reply to message #559576] Wed, 04 July 2012 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot know that.

Regards
Michel
Re: How to find out who is the locking SQL statement? [message #559579 is a reply to message #559576] Wed, 04 July 2012 07:18 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
He can't. Oracle doesn't store that information. You'll just have to look through the code and work it out the hard way.
If you know it's a batch process it shouldn't be that difficult.
Re: How to find out who is the locking SQL statement? [message #559581 is a reply to message #559579] Wed, 04 July 2012 07:28 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
>>>>>He can't. Oracle doesn't store that information.
>>>>>You'll just have to look through the code and work it out the hard way.

Are you sure?
It is currently (even now as we speak) blocking session 102 from doing anything..
I bet I am not the only one that faced this...


>>>>>If you know it's a batch process it shouldn't be that difficult

Well, it shouldn't be that difficult if you just teleport this problem away to the developers,
However, without the SQL causing the lock - they have little detail of the problem,
and it can get pretty difficult.



Kind of disappointing that there is absolutely no option of knowing this detail.

Thank you for your answers.

Regards,
Andrey

[Updated on: Wed, 04 July 2012 07:30]

Report message to a moderator

Re: How to find out who is the locking SQL statement? [message #559584 is a reply to message #559581] Wed, 04 July 2012 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Are you sure?


Yes.

Regards
Michel
Re: How to find out who is the locking SQL statement? [message #559585 is a reply to message #559584] Wed, 04 July 2012 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Imagine, I execute 1000 DML on a table, then I take my vacation, 7 days, without commiting before.
Now you, you connect 2 days after I left and try to update one of the rows I modified during my 478th DML.
Do you see the problem?

Regards
Michel
Re: How to find out who is the locking SQL statement? [message #559587 is a reply to message #559585] Wed, 04 July 2012 08:58 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
This is a discipline issue, and in that case, I will simply kill your session.
However, this is not the case here.

The case here is that developers have to know the query causing the locking to investigate
*a constant issue*, making the customer annoyed and causing him downtime of the application,
Not to mention that the software provider looks bad.

If an event like wait caused to a session is recorded -
I see a good point in retaining the sql_id and it's text for some time(maybe half a day, day),
so we can retro-actively find the locking SQL.

storing 1000 DML's somewhere, even on disk, to retrieve when needed after a couple of hours,
seems like a reasonable need and request.

Regards,
Andrey



Re: How to find out who is the locking SQL statement? [message #559588 is a reply to message #559587] Wed, 04 July 2012 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
If SQL below returns any rows, then you have additional clues to persue.

SELECT Decode(request, 0, 'Holder: ', 
                       'Waiter: ') 
       ||vl.sid sess, 
       status, 
       id1, 
       id2, 
       lmode, 
       request, 
       vl.TYPE 
FROM   v$lock vl, 
       v$session vs 
WHERE  ( id1, id2, vl.TYPE ) IN (SELECT id1, 
                                        id2, 
                                        TYPE 
                                 FROM   v$lock 
                                 WHERE  request > 0) 
       AND vl.sid = vs.sid 
ORDER  BY id1, 
          request 

/ 
Re: How to find out who is the locking SQL statement? [message #559589 is a reply to message #559587] Wed, 04 July 2012 09:26 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You could ask your programmers to name their transactions. Use SET TRANSACTION NAME.... at the start of each transaction to give each one a unique name, which is visible in V$TRANSACTION.NAME. Then when another session gets blocked, you'll be able to identify which transaction it is, and (so long as the transaction doesn't have zillions of statements) it will be pretty easy to identify the problem statement.
Re: How to find out who is the locking SQL statement? [message #559590 is a reply to message #559589] Wed, 04 July 2012 09:33 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi John,
Tried that and received an error.

SQL> set transaction name some_name;
set transaction name some_name
                     *
ERROR at line 1:
ORA-00922: missing or invalid option


Regards,
Andrey
Re: How to find out who is the locking SQL statement? [message #559591 is a reply to message #559590] Wed, 04 July 2012 09:34 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
RTFM.
Re: How to find out who is the locking SQL statement? [message #559593 is a reply to message #559587] Wed, 04 July 2012 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is a discipline issue, and in that case, I will simply kill your session.
However, this is not the case here.


It was just a coarse example to make you understand why it is not log anywhere.
It could be a batch that last one day.
It could be thousand connections making hundred dml per second.
Do you think client will wait you log hundred thousand statements on disk?
And what happen if the disk is full? Does the statement fail? An uncceptable reason of failure for an application.
And so on...

John gives you one solution, you could also use dbms_application_info to set client_info, module and action that you will retrieve in v$session (without speaking about longops you can record).

Regards
Michel
Re: How to find out who is the locking SQL statement? [message #559597 is a reply to message #559593] Wed, 04 July 2012 11:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT s.username, 
       s.sid, 
       s.serial#, 
       s.osuser, 
       k.ctime, 
       o.object_name                                      object, 
       k.kaddr, 
       Decode(l.locked_mode, 1, 'No Lock', 
                             2, 'Row Share', 
                             3, 'Row Exclusive', 
                             4, 'Shared Table', 
                             5, 'Shared Row Exclusive', 
                             6, 'Exclusive')              locked_mode, 
       Decode(k.TYPE, 'BL', 'Buffer Cache Management (PCM lock)', 
                      'CF', 'Controlfile Transaction', 
                      'CI', 'Cross Instance Call', 
                      'CU', 'Bind Enqueue', 
                      'DF', 'Data File', 
                      'DL', 'Direct Loader', 
                      'DM', 'Database Mount', 
                      'DR', 'Distributed Recovery', 
                      'DX', 'Distributed Transaction', 
                      'FS', 'File Set', 
                      'IN', 'Instance Number', 
                      'IR', 'Instance Recovery', 
                      'IS', 'Instance State', 
                      'IV', 'Library Cache Invalidation', 
                      'JQ', 'Job Queue', 
                      'KK', 'Redo Log Kick', 
                      'LA', 'Library Cache Lock', 
                      'LB', 'Library Cache Lock', 
                      'LC', 'Library Cache Lock', 
                      'LD', 'Library Cache Lock', 
                      'LE', 'Library Cache Lock', 
                      'LF', 'Library Cache Lock', 
                      'LG', 'Library Cache Lock', 
                      'LH', 'Library Cache Lock', 
                      'LI', 'Library Cache Lock', 
                      'LJ', 'Library Cache Lock', 
                      'LK', 'Library Cache Lock', 
                      'LL', 'Library Cache Lock', 
                      'LM', 'Library Cache Lock', 
                      'LN', 'Library Cache Lock', 
                      'LO', 'Library Cache Lock', 
                      'LP', 'Library Cache Lock', 
                      'MM', 'Mount Definition', 
                      'MR', 'Media Recovery', 
                      'NA', 'Library Cache Pin', 
                      'NB', 'Library Cache Pin', 
                      'NC', 'Library Cache Pin', 
                      'ND', 'Library Cache Pin', 
                      'NE', 'Library Cache Pin', 
                      'NF', 'Library Cache Pin', 
                      'NG', 'Library Cache Pin', 
                      'NH', 'Library Cache Pin', 
                      'NI', 'Library Cache Pin', 
                      'NJ', 'Library Cache Pin', 
                      'NK', 'Library Cache Pin', 
                      'NL', 'Library Cache Pin', 
                      'NM', 'Library Cache Pin', 
                      'NN', 'Library Cache Pin', 
                      'NO', 'Library Cache Pin', 
                      'NP', 'Library Cache Pin', 
                      'NQ', 'Library Cache Pin', 
                      'NR', 'Library Cache Pin', 
                      'NS', 'Library Cache Pin', 
                      'NT', 'Library Cache Pin', 
                      'NU', 'Library Cache Pin', 
                      'NV', 'Library Cache Pin', 
                      'NW', 'Library Cache Pin', 
                      'NX', 'Library Cache Pin', 
                      'NY', 'Library Cache Pin', 
                      'NZ', 'Library Cache Pin', 
                      'PF', 'Password File', 
                      'PI', 'Parallel Slaves', 
                      'PR', 'Process Startup', 
                      'PS', 'Parallel slave Synchronization', 
                      'QA', 'Row Cache Lock', 
                      'QB', 'Row Cache Lock', 
                      'QC', 'Row Cache Lock', 
                      'QD', 'Row Cache Lock', 
                      'QE', 'Row Cache Lock', 
                      'QF', 'Row Cache Lock', 
                      'QG', 'Row Cache Lock', 
                      'QH', 'Row Cache Lock', 
                      'QI', 'Row Cache Lock', 
                      'QJ', 'Row Cache Lock', 
                      'QK', 'Row Cache Lock', 
                      'QL', 'Row Cache Lock', 
                      'QM', 'Row Cache Lock', 
                      'QN', 'Row Cache Lock', 
                      'QO', 'Row Cache Lock', 
                      'QP', 'Row Cache Lock', 
                      'QQ', 'Row Cache Lock', 
                      'QR', 'Row Cache Lock', 
                      'QS', 'Row Cache Lock', 
                      'QT', 'Row Cache Lock', 
                      'QU', 'Row Cache Lock', 
                      'QV', 'Row Cache Lock', 
                      'QW', 'Row Cache Lock', 
                      'QX', 'Row Cache Lock', 
                      'QY', 'Row Cache Lock', 
                      'QZ', 'Row Cache Lock', 
                      'RT', 'Redo Thread', 
                      'SC', 'System Commit number', 
                      'SM', 'SMON synchronization', 
                      'SN', 'Sequence Number', 
                      'SQ', 'Sequence Enqueue', 
                      'SR', 'Synchronous Replication', 
                      'SS', 'Sort Segment', 
                      'ST', 'Space Management Transaction', 
                      'SV', 'Sequence Number Value', 
                      'TA', 'Transaction Recovery', 
                      'TM', 'DML Enqueue', 
                      'TS', 'Table Space (or Temporary Segment)', 
                      'TT', 'Temporary Table', 
                      'TX', 'Transaction', 
                      'UL', 'User-defined Locks', 
                      'UN', 'User Name', 
                      'US', 'Undo segment Serialization', 
                      'WL', 'Writing redo Log', 
                      'XA', 'Instance Attribute Lock', 
                      'XI', 'Instance Registration Lock') TYPE 
FROM   v$session s, 
       sys.v_$_lock c, 
       sys.v_$locked_object l, 
       dba_objects o, 
       sys.v_$lock k, 
       v$_lock v 
WHERE  o.object_id = l.object_id 
       AND l.session_id = s.sid 
       AND k.sid = s.sid 
       AND s.saddr = c.saddr 
       AND k.kaddr = c.kaddr 
       AND k.kaddr = v.kaddr 
       AND v.saddr = s.saddr 
       AND k.lmode = l.locked_mode 
       AND k.lmode = c.lmode 
       AND k.request = c.request 
ORDER  BY object; 

09:48:43 131  

USERNAME			      SID    SERIAL#
------------------------------ ---------- ----------
OSUSER				    CTIME
------------------------------ ----------
OBJECT
--------------------------------------------------------------------------------
KADDR		 LOCKED_MODE	      TYPE
---------------- -------------------- ----------------------------------
USER1				       14	 127
bcm				     1908
TEST
00000000BCC50EF8 Row Exclusive


09:48:48 SQL> 

Re: How to find out who is the locking SQL statement? [message #559599 is a reply to message #559591] Wed, 04 July 2012 13:16 Go to previous message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
John Watson wrote on Wed, 04 July 2012 17:34
RTFM.


Read The Fishing Manual?! Why would i read fishing manuals to handle Oracle issues?! Shocked Cool




Thanks everyone for the help,
I will try those methods and post about the results.

Regards,
Andrey

Previous Topic: Investigating sudden Temp Tablespace growth from AWR?
Next Topic: Query running slow
Goto Forum:
  


Current Time: Thu Mar 28 17:23:08 CDT 2024