SQ – Sequence Lock
logon/logoff problem
TX - mode 6 – application problem
Look at what application is doing
Find SQL
Look at locked data
TX - mode 4
probably ITL problem
find the object and SQL
HW – High Water
Look at object and SQL
use LMT, freelists, pre-allocate extents
ST - Space Transaction
only one per database
used for space allocations uet, fet
Find object
use LMT
UL - User Lock
find out what application is doing
SELECT /*+ ordered */
a.sid blocker_sid,
a.username blocker_username,
a.serial#,
a.logon_time,
b.type,
b.lmode mode_held,
b.ctime time_held,
c.sid waiter_sid,
c.request request_mode,
c.ctime time_waited,
a.sql_hash_value blocker_sql_hash_value,
d.sql_hash_value waiter_sql_hash_value
FROM v$lock b, v$enqueue_lock c, v$session a, v$session d
WHERE a.sid = b.sid
AND b.id1 = c.id1(+)
AND b.id2 = c.id2(+)
AND c.type(+) = 'TX'
AND b.type = 'TX'
AND b.block = 1
AND c.sid = d.sid
ORDER BY time_held, time_waited
logon/logoff problem
TX - mode 6 – application problem
Look at what application is doing
Find SQL
Look at locked data
TX - mode 4
probably ITL problem
find the object and SQL
HW – High Water
Look at object and SQL
use LMT, freelists, pre-allocate extents
ST - Space Transaction
only one per database
used for space allocations uet, fet
Find object
use LMT
UL - User Lock
find out what application is doing
SELECT /*+ ordered */
a.sid blocker_sid,
a.username blocker_username,
a.serial#,
a.logon_time,
b.type,
b.lmode mode_held,
b.ctime time_held,
c.sid waiter_sid,
c.request request_mode,
c.ctime time_waited,
a.sql_hash_value blocker_sql_hash_value,
d.sql_hash_value waiter_sql_hash_value
FROM v$lock b, v$enqueue_lock c, v$session a, v$session d
WHERE a.sid = b.sid
AND b.id1 = c.id1(+)
AND b.id2 = c.id2(+)
AND c.type(+) = 'TX'
AND b.type = 'TX'
AND b.block = 1
AND c.sid = d.sid
ORDER BY time_held, time_waited
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22818880/viewspace-2077110/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22818880/viewspace-2077110/