Performace Tuning : LOCKING
Sometimes a problem that appears to be a performance issue could actually be due to locking.
Particularly if high occurrences of the following wait events are seen in the AWR or SQL Trace / TKPROF :
- enq: TX - row lock contention
- enq: TX - allocate ITL entry
- enq: TM – contention
- enq: UL - contention
2) TM (Table Locks)
This lock is acquired by a transaction when using DML (or SELECT …. FOR UPDATE) or by the LOCK TABLE statement.
DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction.
The wait for this lock is "enq: TM – contention".
3)UL (User Lock)
These are user specified locks.
They can be requested and released using the DBMS_LOCK package procedures REQUEST and RELEASE.
The wait for this lock is "enq: UL - contention".
Identifying where locks are being held
The following will identify the locks that have been held for the longest time along with the blocking/blocked session:
SELECT
lr.sid blocker_sid,
ld.sid blocked_sid,
lr.type, -- Type will typically be TM, TX, UL. Otherwise it is system type.
lt.name,
lr.id1, -- The value contained in these varies. For TM ID1 contains the object_id, for TX it is the Rollback entry
lr.id2, -- The value contained in these varies. For TX it is the Transaction entry.
decode(lr.lmode,
0,'none(0)',
1,'null(NULL)(1)',
2,'row-S(SS)(2)',
3,'row-S(SX)(3)',
4,'Share(S)(4)',
5,'S/Row-X(SSX)(5)',
6,'exclusive(X)(6)',
LTRIM(TO_CHAR(lr.lmode,'990'))) lock_hold_mode,
decode(lr.request,
0,'none(0)',
1,'null(NULL)(1)',
2,'row-S(SS)(2)',
3,'row-S(SX)(3)',
4,'Share(S)(4)',
5,'S/Row-X(SSX)(5)',
6,'exclusive(X)(6)',
LTRIM(TO_CHAR(lr.request,'990'))) lock_request_mode,
lr.ctime time_blocker_held,
ld.ctime time_blocked_waiting
FROM v$lock lr,
v$lock ld,
v$lock_type lt
WHERE lt.type (+) = lr.type
AND ld.id1 = lr.id1 -- rollback entries match
AND ld.id2 = lr.id2 -- transaction entries match
AND lr.block = 1 -- blocker
AND ld.block = 0 -- blocked
ORDER BY lr.ctime DESC; -----------------------------------------------------------------------------------
The following will give the chains of locks - with the longest first:
The key blocking sessions should be able to be identified from this.
Note that if there are occurrences of blocker sessions blocking multiple blocked sessions then parts of the chains will be reported multiple times (there will be one row for each combination of chain starts and ends).
SELECT * FROM (SELECT level chainlevel, blocked_sid, CONNECT_BY_ROOT blocker_sid ultimate_blocker_sid, sys_connect_by_path(blocker_sid,'/')|| '/' || blocked_sid blocking_path, time_blocked_waiting FROM (SELECT lr.sid blocker_sid, ld.sid blocked_sid, lr.id1, lr.id2, ld.ctime time_blocked_waiting FROM v$lock lr, v$lock ld WHERE ld.id1 = lr.id1 AND ld.id2 = lr.id2 AND lr.block = 1 AND ld.block = 0) linked_locks CONNECT BY NOCYCLE PRIOR blocked_sid = blocker_sid) chains -- blocked end of chain is not a blocker session WHERE NOT EXISTS (SELECT 'exists' FROM v$lock lr, v$lock ld WHERE ld.id1 = lr.id1 AND ld.id2 = lr.id2 AND lr.block = 1 AND ld.block = 0 AND lr.sid = chains.blocked_sid) -- blocker end of chain is not a blocked session AND NOT EXISTS (SELECT 'exists' FROM v$lock lr, v$lock ld WHERE ld.id1 = lr.id1 AND ld.id2 = lr.id2 AND lr.block = 1 AND ld.block = 0 AND ld.sid = chains.ultimate_blocker_sid) ORDER BY chainlevel DESC;-----------------------------------------------------------The following will report any cycles/deadlocks:
However, deadlock detection should prevent these from occurring.
SELECT * FROM (SELECT DECODE(CONNECT_BY_ISCYCLE,1,'YES','NO') is_a_cycle, level, blocked_sid, CONNECT_BY_ROOT blocker_sid ultimate_blocker_sid, sys_connect_by_path(blocker_sid,'/')|| '/' || blocked_sid blocking_path, time_blocked_waiting FROM (SELECT lr.sid blocker_sid, ld.sid blocked_sid, lr.id1, lr.id2, ld.ctime time_blocked_waiting FROM v$lock lr, v$lock ld WHERE ld.id1 = lr.id1 AND ld.id2 = lr.id2 AND lr.block = 1 AND ld.block = 0) linked_locks CONNECT BY NOCYCLE PRIOR blocked_sid = blocker_sid) WHERE is_a_cycle = 'YES';------------------------------------------------------------------------------------To get more information for any session ids (sid), with the longest running locks first :SELECT l.sid, l.block, DECODE(l.block,1,'Blocker','Blocked'), s.blocking_session, l.type, lt.name, l.id1, l.id2, decode(l.lmode, 0,'none(0)', 1,'null(NULL)(1)', 2,'row-S(SS)(2)', 3,'row-S(SX)(3)', 4,'Share(S)(4)', 5,'S/Row-X(SSX)(5)', 6,'exclusive(X)(6)', LTRIM(TO_CHAR(l.lmode,'990')) ) lock_hold_mode, decode(l.request, 0,'none(0)', 1,'null(NULL)(1)', 2,'row-S(SS)(2)', 3,'row-S(SX)(3)', 4,'Share(S)(4)', 5,'S/Row-X(SSX)(5)', 6,'exclusive(X)(6)', LTRIM(TO_CHAR(l.request, '990')) ) lock_request_mode, l.ctime time_held_s, s.seconds_in_wait, s.wait_class, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2, s.p2raw, s.p3text, s.p3, s.p3raw, s.serial#, s.program, s.client_identifier, s.terminal, s.command, ct.command_name, s.service_name, s.module, s.action, s.username, s.machine, DECODE(l.type, 'TM', o.object_name) object_name, s.sql_id, st.sql_text, -- just the first 64 characters of the SQL text st.hash_value, s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row# FROM v$session s, v$lock l, v$sqltext st, v$lock_type lt, v$sqlcommand ct, dba_objects o WHERE s.sid (+) = l.sid AND ( (l.request != 0) -- either requesting a lock OR (l.lmode != 0 -- or holding a lock AND EXISTS (SELECT 'exists' FROM v$lock ld WHERE ld.request != 0 AND ld.id1 = l.id1 AND ld.id2 = l.id2) -- and blocking a blocked session ) ) AND st.sql_id (+) = s.sql_id AND st.piece (+) = 0 AND o.object_id (+) = l.id1 AND lt.type (+) = l.type AND ct.command_type (+) = s.command -- AND l.sid = <session id> ORDER BY l.ctime DESC;--------------------------------------------------------------The object and rowid for a particular blocked session can be determined by using the following SQL:
This is primarily for row locks (TX), but it may also show some relevant information for TM locks, depending on the mode the table was locked and the activity taking place on the locked session.
SELECT o.object_name, s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#, dbms_rowid.rowid_create( 0, s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#) restricted_rowid, dbms_rowid.rowid_create( 1, s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#) extended_rowid FROM v$session s, dba_objects o WHERE s.row_wait_obj# = o.object_id (+) AND s.sid = <sid>;-------------------------------------------------------------The rowid could be used to query the contents of the locked row on the object (table).
The following SQL can be run for particular sql ids to get more SQL text:
SELECT sql_text -- First 1000 characters FROM v$sql WHERE sql_id = <sql_id>;orSELECT sql_text FROM v$sqltext WHERE sql_id = <sql_id> ORDER BY piece;------------------------------------------------------------5 Identifying where locks were held
The information on the views v$lock, v$session is only for current sessions only.
However, it is possible that the Active Session History (ASH) may have captured information on locks held for more than a few seconds.
The following query will give information on all sessions from the Active Session History (on DBA_ACTIVE_SESS_HISTORY) that were blocked due to a Row, Table or User lock.
A similar query using view V$ACTIVE_SESSION_HISTORY could be used to check the very recent history (not yet flushed to DBA_ACTIVE_SESS_HISTORY).
SELECT s.session_id, s.blocking_session, s.session_serial#, s.sql_id, s.wait_class, s.event, s.p1text, s.p1, s.p2text, s.p2, s.p3text, s.p3, o.object_type, o.object_name, s.current_obj#, s.current_file#, s.current_block#, s.current_row#, s.program, s.module, s.action, s.client_id, s.machine, COUNT(*)*10 approx_wait_secs, -- note that if 10 seconds is reported then it could be a lot less MIN(s.sample_time) start_sample_time, MAX(s.sample_time) end_sample_time FROM dba_active_sess_history s, dba_hist_seg_stat_obj o WHERE s.dbid = <db_id> AND s.instance_number = <inst_num> AND s.snap_id BETWEEN <begin_snap> and <end_snap> AND o.dbid (+) = s.dbid AND o.obj# (+) = s.current_obj# AND s.blocking_session IS NOT NULL AND s.event IN ('enq: TX - row lock contention' ,'enq: TM - contention' ,'enq: UL - contention' ,'enq: TX - allocate ITL entry') GROUP BY s.session_id, s.blocking_session, s.session_serial#, s.sql_id, s.wait_class, s.event, s.p1text, s.p1, s.p2text, s.p2, s.p3text, s.p3, o.object_type, o.object_name, s.current_obj#, s.current_file#, s.current_block#, s.current_row#, s.program, s.module, s.action, s.client_id, s.machineORDER BY COUNT(*) DESC;-----------------------------------------------------------------------------------------------The following query will give information on chains of locks (due to table, row or user locks) that have occurred for more than a few seconds, with the longest chains first :SELECT level, sample_time, session_id blocked_sid, CONNECT_BY_ROOT blocking_session ultimate_blocker_sid, sys_connect_by_path(blocking_session,'/')|| '/' || session_id blocking_path FROM (-- Blocked Sessions SELECT s.session_id, s.blocking_session, s.sample_time FROM dba_active_sess_history s WHERE s.dbid = <db_id> AND s.instance_number = <inst_num> AND s.snap_id BETWEEN <begin_snap> and <end_snap> AND s.blocking_session IS NOT NULL AND s.event IN ('enq: TX - row lock contention' ,'enq: TM - contention' ,'enq: UL - contention' ,'enq: TX - allocate ITL entry') UNION -- Blocking Sessions SELECT s.session_id, s.blocking_session, s.sample_time FROM dba_active_sess_history s WHERE s.dbid = <db_id> AND s.instance_number <inst_num> AND s.snap_id BETWEEN <begin_snap> and <end_snap> AND s.blocking_session IS NULL AND s.event IN ('enq: TX - row lock contention' ,'enq: TM - contention' ,'enq: UL - contention' ,'enq: TX - allocate ITL entry') AND EXISTS (SELECT 'exists' FROM DBA_HIST_ACTIVE_SESS_HISTORY bs WHERE bs.dbid = <db_id> AND bs.instance_number = <inst_num> AND bs.snap_id BETWEEN <begin_snap> and <end_snap> AND bs.blocking_session = s.session_id AND bs.sample_time = s.sample_time AND bs.blocking_session IS NOT NULL AND bs.event IN ('enq: TX - row lock contention' ,'enq: TM - contention' ,'enq: UL - contention' ,'enq: TX - allocate ITL entry') ) ) CONNECT BY NOCYCLE PRIOR session_id = blocking_session AND PRIOR sample_time = sample_time ORDER BY level DESC, blocked_sid, sample_time;----------------------------------------------------------------6 "PL/SQL lock timer" waits
In this case there is no lock or interaction between sessions.
A session simply sleeps for a pre-determined length of time specified in a call to the USER_LOCK.SLEEP or DBMS_LOCK.SLEEP procedures.
However it can appear that a session is locked or there is a performance issue.
The details of sessions with "PL/SQL lock timer" waits can be queried using the following SQL:
SELECT s.sid, s.seconds_in_wait, s.wait_class, s.event, s.p1text, s.p1, s.p1raw, s.serial#, s.program, s.client_identifier, s.terminal, s.command, ct.command_name, s.service_name, s.module, s.action, s.username, s.machine, s.sql_id, st.sql_text, -- just the first 64 characters of the SQL text st.hash_value FROM v$session s, v$sqltext st, v$sqlcommand ct WHERE st.sql_id (+) = s.sql_id AND st.piece (+) = 0 AND ct.command_type (+) = s.command AND s.event = 'PL/SQL lock timer' ORDER BY s.seconds_in_wait DESC;
No comments:
Post a Comment