Oracle Блокировки

Выявление проблем связанных с блокировками

select /*+ ordered */
       b.username,
       b.serial#,
       d.id1,
       a.sql_text
from   v$lock d,
       v$session b,
       v$sqltext a
where  b.lockwait=d.kaddr
  and  a.address=b.sql_address
  and  a.hash_value=b.sql_hash_value


Выявление пользователя, который поставил блокировку

select /*+ ordered */
       a.serial#,       
       a.sid,
       a.username,
       b.id1,
       c.sql_text
from   v$lock b,
       v$session a,
       v$sqltext c
where  b.id1 in (select /*+ ordered */
                        distinct e.id1
                        from v$lock e,
                             v$session d
                        where d.lockwait=e.kaddr)
and a.sid=b.sid
and c.hash_value=a.sql_hash_value
and b.request=0


Кто держит temporary table

select session_id, a.object_id, oracle_username as usr, os_user_name as Os_Usr, 
        decode(Locked_mode,1,'No Lock', 
                2,'Row Share', 
                3,'Row Exclusive', 
                4,'Share', 
                5,'Share Row Exclusive', 
                6,'Exclusive','NONE') as Locked_mode, 
owner, object_name, data_object_id as Data_Id, object_type, temporary
 from V$LOCKED_OBJECT o, all_objects a where o.object_id = a.object_id


Кто лочит сессию sid=18

SELECT /*+ RULE */
lk.SID, se.username, se.osuser, se.machine,
DECODE (lk.TYPE,
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
lk.TYPE
) lock_type,
DECODE (lk.lmode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lk.lmode)
) mode_held,
DECODE (lk.request,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lk.request)
) mode_requested,
TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2) lock_id2, ob.owner,
ob.object_type, ob.object_name, lk.BLOCK, se.lockwait
FROM v$lock lk, dba_objects ob, v$session se
WHERE lk.TYPE IN ('TM', 'UL')
AND lk.SID = se.SID
AND lk.id1 = ob.object_id
AND (lk.SID = '18');


Просмотр блокировок


SELECT /*+ rule */
bs.username "Blocking User", ws.username "Waiting User", bs.sid "SID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#",
ws.serial# "WSerial#",
DECODE (
wk.TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'USER Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL USER LOCK',
'DX', 'Distributed Xaction',
'CF', 'Control FILE',
'IS', 'Instance State',
'FS', 'FILE SET',
'IR', 'Instance Recovery',
'ST', 'Disk SPACE Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'LOG START OR Switch',
'RW', 'ROW Wait',
'SQ', 'Sequence Number',
'TE', 'Extend TABLE',
'TT', 'Temp TABLE',
wk.TYPE
) lock_type,
DECODE (
hk.lmode,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (hk.lmode)
) mode_held,
DECODE (
wk.request,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (wk.request)
) mode_requested,
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
DECODE (
hk.block,
0, 'NOT Blocking', /* Not blocking any other processes */
1, 'Blocking', /* This lock blocks other processes */
2, 'Global', /* This lock is global, so we can't tell */
TO_CHAR (hk.block)
)
blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.sid = bs.sid(+)
AND wk.sid = ws.sid(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> 'SYSTEM')
AND (bs.username <> 'SYS')
ORDER BY 1;


Поиск блокировок

select 
nvl(S.USERNAME,'Internal') username, 
        L.SID, 
        nvl(S.TERMINAL,'None') terminal, 
        decode(command, 
0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab, 
decode(command, 
0,'BACKGROUND', 
1,'Create Table', 
2,'INSERT', 
3,'SELECT', 
4,'CREATE CLUSTER', 
5,'ALTER CLUSTER', 
6,'UPDATE', 
7,'DELETE', 
8,'DROP', 
9,'CREATE INDEX', 
10,'DROP INDEX', 
11,'ALTER INDEX', 
12,'DROP TABLE', 
13,'CREATE SEQUENCE', 
14,'ALTER SEQUENCE', 
15,'ALTER TABLE', 
16,'DROP SEQUENCE', 
17,'GRANT', 
18,'REVOKE', 
19,'CREATE SYNONYM', 
20,'DROP SYNONYM', 
21,'CREATE VIEW', 
22,'DROP VIEW', 
23,'VALIDATE INDEX', 
24,'CREATE PROCEDURE', 
25,'ALTER PROCEDURE', 
26,'LOCK TABLE', 
27,'NO OPERATION', 
28,'RENAME', 
29,'COMMENT', 
30,'AUDIT', 
31,'NOAUDIT', 
32,'CREATE EXTERNAL DATABASE', 
33,'DROP EXTERNAL DATABASE', 
34,'CREATE DATABASE', 
35,'ALTER DATABASE', 
36,'CREATE ROLLBACK SEGMENT', 
37,'ALTER ROLLBACK SEGMENT', 
38,'DROP ROLLBACK SEGMENT', 
39,'CREATE TABLESPACE', 
40,'ALTER TABLESPACE', 
41,'DROP TABLESPACE', 
42,'ALTER SESSION', 
43,'ALTER USER', 
44,'COMMIT', 
45,'ROLLBACK', 
46,'SAVEPOINT', 
47,'PL/SQL EXECUTE', 
48,'SET TRANSACTION', 
49,'ALTER SYSTEM SWITCH LOG', 
50,'EXPLAIN', 
51,'CREATE USER', 
52,'CREATE ROLE', 
53,'DROP USER', 
54,'DROP ROLE', 
55,'SET ROLE', 
56,'CREATE SCHEMA', 
57,'CREATE CONTROL FILE', 
58,'ALTER TRACING', 
59,'CREATE TRIGGER', 
60,'ALTER TRIGGER', 
61,'DROP TRIGGER', 
62,'ANALYZE TABLE', 
63,'ANALYZE INDEX', 
64,'ANALYZE CLUSTER', 
65,'CREATE PROFILE', 
66,'DROP PROFILE', 
67,'ALTER PROFILE', 
68,'DROP PROCEDURE', 
69,'DROP PROCEDURE',
70,'ALTER RESOURCE COST', 
71,'CREATE SNAPSHOT LOG', 
72,'ALTER SNAPSHOT LOG', 
73,'DROP SNAPSHOT LOG', 
74,'CREATE SNAPSHOT', 
75,'ALTER SNAPSHOT', 
76,'DROP SNAPSHOT', 
79,'ALTER ROLE',
85,'TRUNCATE TABLE', 
86,'TRUNCATE CLUSTER', 
87,'-', 
88,'ALTER VIEW', 
89,'-', 
90,'-', 
91,'CREATE FUNCTION', 
92,'ALTER FUNCTION', 
93,'DROP FUNCTION', 
94,'CREATE PACKAGE', 
95,'ALTER PACKAGE', 
96,'DROP PACKAGE', 
97,'CREATE PACKAGE BODY', 
98,'ALTER PACKAGE BODY', 
99,'DROP PACKAGE BODY', 
command||' - ???') COMMAND, 
        decode(L.LMODE,1,'No Lock', 
                2,'Row Share', 
                3,'Row Exclusive', 
                4,'Share', 
                5,'Share Row Exclusive', 
                6,'Exclusive','NONE') lmode, 
        decode(L.REQUEST,1,'No Lock', 
                2,'Row Share', 
                3,'Row Exclusive', 
                4,'Share', 
                5,'Share Row Exclusive', 
                6,'Exclusive','NONE') request, 
l.id1||'-'||l.id2 Laddr, 
l.type||' - '|| 
decode(l.type, 
'BL','Buffer hash table instance lock', 
'CF',' Control file schema global enqueue lock', 
'CI','Cross-instance function invocation instance lock',
'CS','Control file schema global enqueue lock', 
'CU','Cursor bind lock',
'DF','Data file instance lock', 
'DL','Direct loader parallel index create',
'DM','Mount/startup db primary/secondary instance lock', 
'DR','Distributed recovery process lock', 
'DX','Distributed transaction entry lock', 
'FI','SGA open-file information lock', 
'FS','File set lock', 
'HW','Space management operations on a specific segment lock',
'IN','Instance number lock',
'IR','Instance recovery serialization global enqueue lock', 
'IS','Instance state lock',
'IV','Library cache invalidation instance lock', 
'JQ','Job queue lock',
'KK','Thread kick lock',
'MB','Master buffer hash table instance lock', 
'MM','Mount definition gloabal enqueue lock', 
'MR','Media recovery lock', 
'PF','Password file lock',
'PI','Parallel operation lock',
'PR','Process startup lock',
'PS','Parallel operation lock',
'RE','USE_ROW_ENQUEUE enforcement lock', 
'RT','Redo thread global enqueue lock', 
'RW','Row wait enqueue lock', 
'SC','System commit number instance lock', 
'SH','System commit number high water mark enqueue lock', 
'SM','SMON lock',
'SN','Sequence number instance lock', 
'SQ','Sequence number enqueue lock', 
'SS','Sort segment lock',
'ST','Space transaction enqueue lock', 
'SV','Sequence number value lock', 
'TA','Generic enqueue lock', 
'TD','DDL enqueue lock', 
'TE','Extend-segment enqueue lock', 
'TM','DML enqueue lock', 
'TT','Temporary table enqueue lock', 
'TX','Transaction enqueue lock', 
'UL','User supplied lock', 
'UN','User name lock', 
'US','Undo segment DDL lock',
'WL','Being-written redo log instance lock', 
'WS','Write-atomic-log-switch global enqueue lock', 
'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)', 
'New block allocation enqueue lock (ID2=1)'), 
'LA','Library cache lock instance lock (A=namespace)', 
'LB','Library cache lock instance lock (B=namespace)', 
'LC','Library cache lock instance lock (C=namespace)', 
'LD','Library cache lock instance lock (D=namespace)', 
'LE','Library cache lock instance lock (E=namespace)', 
'LF','Library cache lock instance lock (F=namespace)', 
'LG','Library cache lock instance lock (G=namespace)', 
'LH','Library cache lock instance lock (H=namespace)', 
'LI','Library cache lock instance lock (I=namespace)', 
'LJ','Library cache lock instance lock (J=namespace)', 
'LK','Library cache lock instance lock (K=namespace)', 
'LL','Library cache lock instance lock (L=namespace)', 
'LM','Library cache lock instance lock (M=namespace)', 
'LN','Library cache lock instance lock (N=namespace)', 
'LO','Library cache lock instance lock (O=namespace)', 
'LP','Library cache lock instance lock (P=namespace)', 
'LS','Log start/log switch enqueue lock', 
'PA','Library cache pin instance lock (A=namespace)', 
'PB','Library cache pin instance lock (B=namespace)', 
'PC','Library cache pin instance lock (C=namespace)', 
'PD','Library cache pin instance lock (D=namespace)', 
'PE','Library cache pin instance lock (E=namespace)', 
'PF','Library cache pin instance lock (F=namespace)', 
'PG','Library cache pin instance lock (G=namespace)', 
'PH','Library cache pin instance lock (H=namespace)', 
'PI','Library cache pin instance lock (I=namespace)', 
'PJ','Library cache pin instance lock (J=namespace)', 
'PL','Library cache pin instance lock (K=namespace)', 
'PK','Library cache pin instance lock (L=namespace)', 
'PM','Library cache pin instance lock (M=namespace)', 
'PN','Library cache pin instance lock (N=namespace)', 
'PO','Library cache pin instance lock (O=namespace)', 
'PP','Library cache pin instance lock (P=namespace)', 
'PQ','Library cache pin instance lock (Q=namespace)', 
'PR','Library cache pin instance lock (R=namespace)', 
'PS','Library cache pin instance lock (S=namespace)', 
'PT','Library cache pin instance lock (T=namespace)', 
'PU','Library cache pin instance lock (U=namespace)', 
'PV','Library cache pin instance lock (V=namespace)', 
'PW','Library cache pin instance lock (W=namespace)', 
'PX','Library cache pin instance lock (X=namespace)', 
'PY','Library cache pin instance lock (Y=namespace)', 
'PZ','Library cache pin instance lock (Z=namespace)', 
'QA','Row cache instance lock (A=cache)', 
'QB','Row cache instance lock (B=cache)', 
'QC','Row cache instance lock (C=cache)', 
'QD','Row cache instance lock (D=cache)', 
'QE','Row cache instance lock (E=cache)', 
'QF','Row cache instance lock (F=cache)', 
'QG','Row cache instance lock (G=cache)', 
'QH','Row cache instance lock (H=cache)', 
'QI','Row cache instance lock (I=cache)', 
'QJ','Row cache instance lock (J=cache)', 
'QL','Row cache instance lock (K=cache)', 
'QK','Row cache instance lock (L=cache)', 
'QM','Row cache instance lock (M=cache)', 
'QN','Row cache instance lock (N=cache)', 
'QO','Row cache instance lock (O=cache)', 
'QP','Row cache instance lock (P=cache)', 
'QQ','Row cache instance lock (Q=cache)', 
'QR','Row cache instance lock (R=cache)', 
'QS','Row cache instance lock (S=cache)', 
'QT','Row cache instance lock (T=cache)', 
'QU','Row cache instance lock (U=cache)', 
'QV','Row cache instance lock (V=cache)', 
'QW','Row cache instance lock (W=cache)', 
'QX','Row cache instance lock (X=cache)', 
'QY','Row cache instance lock (Y=cache)', 
'QZ','Row cache instance lock (Z=cache)','????') Lockt 
from    V$LOCK L,  
        V$SESSION S, 
        SYS.USER$ U1, 
        SYS.OBJ$ T1 
where   L.SID = S.SID  
and     T1.OBJ#  = decode(L.ID2,0,L.ID1,1)  
and     U1.USER# = T1.OWNER# 
and     S.TYPE != 'BACKGROUND' 
order by 1,2,5


Выйти на сессии, которые держат pin на объекте, 
на котором ждем library cache pin

select s.sid, s.serial#, s.sql_hash_value as x_hash
from v$session s, x$kglpn x
where x.kglpnuse = s.saddr
and x.kglpnhdl = v$session_wait.P1raw;

-- выйти на LCO
select *
from x$kglob
where kglhdadr = v$session_wait.p1raw


Скрипт:

deadlock.sh
#!/bin/bash
. ${HOME}/bin/_conf/.env

# Check Parameters:
if [ $1 ]
then
  oracle_sid=`echo ${1} | tr "[A-Z]" "[a-z]"`
else
  echo "Error! Call `basename $0` With 1-st parameter as "
  exit
fi

# Variables:
export ORACLE_SID=${oracle_sid}
DBA_EMAIL="$MAIL_DBA $MAIL_APP"
loc_lock_tree_list=${TMP_DIR}/${ORACLE_SID}_lock_tree.lst
loc_host_name=`hostname`



#----- Lock Tree
sqlplus -s / << EOF > ${loc_lock_tree_list}
set linesize 900
set pagesize 92
set echo off
set head off
set feed off
set tab off

SELECT
  ' Blocking_user    : ' || bs.username || chr(13)||chr(10)||
  ' DB_user          : ' || bs.username || chr(13)||chr(10)||
  ' Waiting_user     : ' || ws.username || chr(13)||chr(10)||
  ' Blocking_SID     : ' || bs.SID      || chr(13)||chr(10)||
  ' Blocking_Serial# : ' || bs.serial#  || chr(13)||chr(10)||
  ' Waiting_SID      : ' || ws.SID      || chr(13)||chr(10)||
  ' Blocking_App     : ' || bs.program  || chr(13)||chr(10)||
  ' Waiting_App      : ' || ws.program  || chr(13)||chr(10)||
  ' Blocking_machine : ' || bs.machine  || chr(13)||chr(10)||
  ' Waiting_machine  : ' || ws.machine  || chr(13)||chr(10)||
  ' Blocking_OS_user : ' || bs.osuser   || chr(13)||chr(10)||
  ' Waiting_OS_user  : ' || ws.osuser   || chr(13)||chr(10)||
  ' lock_type        : ' ||
    DECODE (
      wk.TYPE,
      'MR', 'Media Recovery',
      'RT', 'Redo Thread',
      'UN', 'USER Name',
      'TX', 'Transaction',
      'TM', 'DML',
      'UL', 'PL/SQL USER LOCK',
      'DX', 'Distributed Xaction',
      'CF', 'Control FILE',
      'IS', 'Instance State',
      'FS', 'FILE SET',
      'IR', 'Instance Recovery',
      'ST', 'Disk SPACE Transaction',
      'TS', 'Temp Segment',
      'IV', 'Library Cache Invalidation',
      'LS', 'LOG START OR Switch',
      'RW', 'ROW Wait',
      'SQ', 'Sequence Number',
      'TE', 'Extend TABLE',
      'TT', 'Temp TABLE',
      wk.TYPE) || chr(13)||chr(10)||
  ' Mode_held        : ' ||
    DECODE (
      hk.lmode,
      0, 'None',
      1, 'NULL',
      2, 'ROW-S (SS)',
      3, 'ROW-X (SX)',
      4, 'SHARE',
      5, 'S/ROW-X (SSX)',
      6, 'EXCLUSIVE',
      TO_CHAR (hk.lmode)) ||chr(13)||chr(10)||
  ' Mode_req         : ' ||
    DECODE (
      wk.request,
      0, 'None',
      1, 'NULL',
      2, 'ROW-S (SS)',
      3, 'ROW-X (SX)',
      4, 'SHARE',
      5, 'S/ROW-X (SSX)',
      6, 'EXCLUSIVE',
      TO_CHAR (wk.request)) ||chr(13)||chr(10)||
  ' Id1              : ' || TO_CHAR (hk.id1) || chr(13)||chr(10)||
  ' Id2              : ' || TO_CHAR (hk.id2)
FROM
  v\$lock hk,
  v\$session bs,
  v\$lock wk,
  v\$session ws
WHERE hk.BLOCK = 1
  AND hk.lmode != 0
  AND hk.lmode != 1
  AND wk.request != 0
  AND wk.TYPE(+) = hk.TYPE
  AND wk.id1(+) = hk.id1
  AND wk.id2(+) = hk.id2
  AND hk.SID = bs.SID(+)
  AND wk.SID = ws.SID(+)
ORDER BY 1;

exit
EOF

if  [ -s ${loc_lock_tree_list} ]; then
  echo "Blocking lock exists"

#-- Addons:
echo ' '       >> ${loc_lock_tree_list}
echo 'Addons:' >> ${loc_lock_tree_list}
echo '=======' >> ${loc_lock_tree_list}

sqlplus -s / << EOF >> ${loc_lock_tree_list}
set linesize 900
set pagesize 92
set echo off
--set head off
set feed off
set tab off

select a.sid, a.serial#, a.username, s.sql_text
from v\$session a, v\$sqltext s
where a.sql_address=s.address
  and a.sql_hash_value=s.hash_value
  and (sid, serial#) in (
    SELECT bs.sid, bs.serial#
    FROM v\$lock hk, v\$session bs, v\$lock wk, v\$session ws, v\$process p
    WHERE hk.lmode != 0
      AND hk.lmode != 1
      AND wk.request != 0
      AND wk.TYPE(+) = hk.TYPE
      AND wk.id1(+) = hk.id1
      AND wk.id2(+) = hk.id2
      AND hk.sid = bs.sid(+)
      AND wk.sid = ws.sid(+)
      AND bs.paddr=p.addr
      AND (bs.username IS NOT NULL)
      AND (bs.username <> 'SYS')
  )
order by a.username, a.sid, s.piece;

exit
EOF

mailx -s "${loc_host_name}(${ORACLE_SID}): Warning! Blocking lock exists" "${DBA_EMAIL}" < ${loc_lock_tree_list}

else
  echo "No blocking lock"
fi