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 |
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 #559581 is a reply to message #559579] |
Wed, 04 July 2012 07:28 |
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 #559587 is a reply to message #559585] |
Wed, 04 July 2012 08:58 |
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 |
|
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 |
John Watson
Messages: 8938 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 #559597 is a reply to message #559593] |
Wed, 04 July 2012 11:51 |
|
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>
|
|
|
|
Goto Forum:
Current Time: Thu Jul 04 19:32:23 CDT 2024
|