General | Dictionary Objects |
ALL_WM_LOCKED_TABLES | GV | DBA_BLOCKERS | GV | DBA_DDL_LOCKS | USER_WM_LOCKED_TABLES | DBA_DML_LOCKS | V_ | DBA_LOCK | V_ | DBA_LOCK_INTERNAL | V_ | DBMS_LOCK | V_ | DBMS_LOCK_ALLOCATED (table) | V_ | GV | V_ | GV | V_ | GV | V_ | GV | V_ | GV | V_ | GV | WM | GV | WM | GV | WM |
| | Lock Definitions | Descriptions |
Name | Description | sub-share | Can be used on an aggregate object to indicate that share locks are being aquired on sub-parts of the object. | sub-exclusive | Can be used on an aggregate object to indicate that exclusive locks are being aquired on sub-parts of the object. | share-sub-exclusive | Indicates that the entire aggregate object has a share lock, but some of the sub-parts may additionally have exclusive locks. |
| | 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" 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.sid, TRUNC(id1/power(2,16)) rbs, BITAND(id1,TO_NUMBER('ffff','xxxx'))+0 slot, id2 seq, lmode, request FROM gv, gv WHERE gv.type = 'TX' AND gv.sid = gv.sid AND gv.username = 'SCOTT';
SELECT XIDUSN, XIDSLOT, XIDSQN FROM gv; |
|
| rollback; |
| | Lock Table | Table Locking | LOCK TABLE IN MODE [NOWAIT | WAIT ]; | LOCK TABLE uwclass.servers IN exclusive MODE WAIT 120; | | 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 a, gv p, gv s, gv 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; | Active Locks | 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 gv s, sys.gv c, sys.gv l, dba_objects o, sys.gv k, gv 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; | List Locks | set wrap off col lock_type format a12 col mode_held format a10 col mode_requested format a10 col blocking_others format a20 col 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 v, dba_objects d, gv l, gv 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 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 WHERE (id1, id2, type) IN ( SELECT id1, id2, type FROM gv 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 p1, gv p2, gv s1, gv 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; |
|