| General
|
| Dictionary Objects |
| ALL_WM_LOCKED_TABLES |
GV$LOCK_TYPE |
| DBA_BLOCKERS |
GV$_LOCK |
| DBA_DDL_LOCKS |
USER_WM_LOCKED_TABLES |
| DBA_DML_LOCKS |
V_$DLM_ALL_LOCKS |
| DBA_LOCK |
V_$DLM_LOCKS |
| DBA_LOCK_INTERNAL |
V_$GLOBAL_BLOCKED_LOCKS |
| DBMS_LOCK |
V_$LOCK |
| DBMS_LOCK_ALLOCATED
(table) |
V_$LOCKED_OBJECT |
| GV$DLM_ALL_LOCKS |
V_$LOCKS_WITH_COLLISIONS |
| GV$DLM_LOCKS |
V_$LOCK_ACTIVITY |
| GV$GLOBAL_BLOCKED_LOCKS |
V_$LOCK_ELEMENT |
| GV$LOCK |
V_$LOCK_TYPE |
| GV$LOCKED_OBJECT |
V_$_LOCK |
| GV$LOCKS_WITH_COLLISIONS |
WM$ALL_LOCKS_VIEW |
| GV$LOCK_ACTIVITY |
WM$LOCKROWS_INFO |
| GV$LOCK_ELEMENT |
WM$LOCKROWS_INFO_IDX |
|
| |
| Lock Escalation |
| How to make lock escalation look good |
Subject: Re: How to make lock escalation look good...
Date: Wed, 21 May 2003 14:03:35 GMT
From: "Jim Kennedy" <[email protected]>
Newsgroups: comp.databases.oracle.server
I've worked with DB2 and it requires throwing most transactions out the
window. Worse than that dynamic sql MUST be followed immediately by a
commit or no one else can do one. (their query tool does this for you) DB2
does NOT do dynamic sql; it does static sql. What it does with "dynamic"
sql is create a plan and bind it in then run it - turning dynamic to static
and that puts a lock on the plan table until you commit. So anyone else
running dynamic sql can't until you commit. (plan table is a source of
serialization for the entire system). When I worked with it (DB2 on a
mainframe) we had to make sure all DML had a commit immediately after it. |
| |
| Lock Demo |
Locking Demo |
| Session 1 |
Session 2 |
| conn / as sysdba |
conn scott/tiger |
|
UPDATE emp
SET deptno=deptno+10
WHERE deptno < 40; |
SELECT username, gv$lock.sid,
TRUNC(id1/power(2,16)) rbs,
BITAND(id1,TO_NUMBER('ffff','xxxx'))+0 slot, id2 seq,
lmode, request
FROM gv$lock, gv$session
WHERE gv$lock.type = 'TX'
AND gv$lock.sid = gv$session.sid
AND gv$session.username = 'SCOTT';
SELECT XIDUSN, XIDSLOT, XIDSQN
FROM gv$transaction; |
|
|
rollback; |
|
| |
| Lock Related Queries |
Active Table Locks |
SELECT SUBSTR(a.object,1,25) TABLENAME,
SUBSTR(s.username,1,15) USERNAME,
SUBSTR(p.pid,1,5) PID,
SUBSTR(p.spid,1,10) SYSTEM_ID,
DECODE(l.type,
'RT','Redo Log Buffer',
'TD','Dictionary',
'TM','DML',
'TS','Temp Segments',
'TX','Transaction',
'UL','User',
'RW','Row Wait',
l.type) LOCK_TYPE
FROM gv$access a, gv$process p, gv$session s, gv$lock l
WHERE s.sid = a.sid
AND s.paddr = p.addr
AND l.sid = p.pid
GROUP BY a.object, s.username, p.pid, l.type, p.spid
ORDER BY a.object, s.username; |
List Locks |
set wrap off
column lock_type format a12
column mode_held format a10
column mode_requested format a10
column blocking_others format a20
column username format a10
SELECT session_id,lock_type, mode_held, mode_requested, blocking_others, lock_id1
FROM dba_lock l
WHERE lock_type NOT IN ('Media Recovery', 'Redo Thread'); |
Locked Objects |
SELECT oracle_username USERNAME, owner OBJECT_OWNER,
object_name, object_type, s.osuser,
DECODE(l.block,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global') STATUS,
DECODE(v.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', TO_CHAR(lmode)
) MODE_HELD
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
and v.session_id = s.sid
ORDER BY oracle_username, session_id; |
| Locked Objects |
SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id; |
| Locked Objects |
SELECT DECODE(request,0,'Holder: ','Waiter:') || sid sess, id1, id2, lmode, request, type
FROM gv$lock
WHERE (id1, id2, type) IN (
SELECT id1, id2, type FROM gv$lock WHERE request>0)
ORDER BY id1, request; |
Objects that have been lock for 2 minutes or more |
SELECT SUBSTR(TO_CHAR(w.session_id),1,5) WSID, p1.spid WPID,
SUBSTR(s1.username,1,12) "WAITING User",
SUBSTR(s1.osuser,1,8) "OS User",
SUBSTR(s1.program,1,20) "WAITING Program",
s1.client_info "WAITING Client",
SUBSTR(TO_CHAR(h.session_id),1,5) HSID, p2.spid HPID,
SUBSTR(s2.username,1,12) "HOLDING User",
SUBSTR(s2.osuser,1,8) "OS User",
SUBSTR(s2.program,1,20) "HOLDING Program",
s2.client_info "HOLDING Client",
o.object_name "HOLDING Object"
FROM gv$process p1, gv$process p2, gv$session s1,
gv$session s2, dba_locks w, dba_locks h, dba_objects o
WHERE w.last_convert > 120
AND h.mode_held != 'None'
AND h.mode_held != 'Null'
AND w.mode_requested != 'None'
AND s1.row_wait_obj# = o.object_id
AND w.lock_type(+) = h.lock_type
AND w.lock_id1(+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
ORDER BY w.last_convert desc; |
|