enq: sq – contention 等待事件

 

ENQUEUE SQ的使用

 

当用户的SQL触发ORACLE需要填充或初始化SEQUENCE CACHE序列缓存时需要申请该SQ Enqueue 以便保护sequence的高水位,SEQUENCE CACHE以KGL sequence object形式存在,其通过查询row cache字典缓存数据中的元数据而获得填充,例如一个SELECT会递归地访问字典表。

 

减少该ENQUEUE SQ的争用

 

  • shared pool空间的不足可能直接导致该SQ ENQUEUE的争用,因为shared pool的空间不足可能导致sequence元数据被频繁刷出共享池。
  • 用户可以尝试使用dbms_shared_pool.keep()来保持该sequence保存在shared pool中
  • 可以为SEQUENCE增加 cache参数,由此减少填充该KGL对象的次数,则同样的申请ENQUEUE的次数会减少

 

ID1/ID2

ID1 代表对应的sequence的object_id , ID2总是0

 

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库优化团队成员帮您恢复!

 

诗檀软件专业数据库优化团队

 

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

Script:Diagnostic Oracle Locks

以下脚本可以用于诊断Oracle实例中的锁情况(Lock Status):


REM SCRIPT: FULLY DECODED LOCKING

set echo off
set lines 200
set pagesize 66
break on Kill on sid on  username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column locking heading 'Lock Held/Lock Requested' format a40
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username  format a10  heading "Username"
column terminal heading Term format a6
column obj format a30 heading "Table/Sequence Name"
column owner format a9
column LAddr heading "ID1 - ID2" format a18
column Lockt heading "Lock Type" format a40
column command heading "Command" format a25
column sid format 990

select
    nvl(s.username,'Internal') username,
    l.sid,
    nvl(s.terminal,'None') terminal,
    decode(l.type,'TM',u.name||'.'||substr(t.name,1,20),
           'DL',u.name||'.'||substr(t.name,1,20),
           'SQ',u.name||'.'||substr(t.name,1,20),'None') obj,
    decode(command,
        0,'None',
        1,'CREATE TABLE',
        2,'INSERT',
        3,'SELECT',
        4,'CREATE CLUSTER',
        5,'ALTER CLUSTER',
        6,'UPDATE',
        7,'DELETE',
        8,'DROP CLUSTER',
        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 DATABASE LINK',
        33,'DROP DATABASE LINK',
        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,'-',
        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',
        77,'CREATE TYPE',
        78,'DROP TYPE',
        79,'ALTER ROLE',
        80,'ALTER TYPE',
        81,'CREATE TYPE BODY',
        82,'ALTER TYPE BODY',
        83,'DROP TYPE BODY',
        84,'DROP LIBRARY',
        85,'TRUNCATE TABLE',
        86,'TRUNCATE CLUSTER',
        87,'CREATE BITMAP FILE',
        88,'ALTER VIEW',
        89,'DROP BITMAP FILE',
        90,'SET CONSTRAINTS',
        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',
        'CF',' Control file schema global enqueue',
        'CI','Cross-instance function invocation instance',
        'CU','Cursor bind',
        'DF','Data file instance',
        'DL','Direct loader parallel index create',
        'DM','Mount/startup db primary/secondary instance',
        'DR','Distributed recovery process',
        'DX','Distributed transaction entry',
        'FS','File set',
        'HW','Space management operations on a specific segment',
        'IN','Instance number',
        'IR','Instance recovery serialization global enqueue',
        'IS','Instance state',
        'IV','Library cache invalidation instance',
        'JQ','Job queue',
        'KK','Thread kick',
        'LA','Library cache lock instance (A=namespace)',
        'LB','Library cache lock instance (B=namespace)',
        'LC','Library cache lock instance (C=namespace)',
        'LD','Library cache lock instance (D=namespace)',
        'LE','Library cache instance lock (E=namespace)',
        'LF','Library cache instance lock (F=namespace)',
        'LG','Library cache instance lock (G=namespace)',
        'LH','Library cache instance lock (H=namespace)',
        'LI','Library cache instance lock (I=namespace)',
        'LJ','Library cache instance lock (J=namespace)',
        'LK','Library cache instance lock (K=namespace)',
        'LL','Library cache instance lock (L=namespace)',
        'LM','Library cache instance lock (M=namespace)',
        'LN','Library cache instance lock (N=namespace)',
        'LO','Library cache instance lock (O=namespace)',
        'LP','Library cache instance lock (P=namespace)',
        'MM','Mount definition gloabal enqueue',
        'MR','Media recovery',
        'NA','Library cache pin instance (A=namespace)',
        'NB','Library cache pin instance (B=namespace)',
        'NC','Library cache pin instance (C=namespace)',
        'ND','Library cache pin instance (D=namespace)',
        'NE','Library cache pin instance (E=namespace)',
        'NF','Library cache pin instance (F=namespace)',
        'NG','Library cache pin instance (G=namespace)',
        'NH','Library cache pin instance (H=namespace)',
        'NI','Library cache pin instance (I=namespace)',
        'NJ','Library cache pin instance (J=namespace)',
        'NL','Library cache pin instance (K=namespace)',
        'NK','Library cache pin instance (L=namespace)',
        'NM','Library cache pin instance (M=namespace)',
        'NN','Library cache pin instance (N=namespace)',
        'NO','Library cache pin instance (O=namespace)',
        'NP','Library cache pin instance (P=namespace)',
        'NQ','Library cache pin instance (Q=namespace)',
        'NR','Library cache pin instance (R=namespace)',
        'NS','Library cache pin instance (S=namespace)',
        'NT','Library cache pin instance (T=namespace)',
        'NU','Library cache pin instance (U=namespace)',
        'NV','Library cache pin instance (V=namespace)',
        'NW','Library cache pin instance (W=namespace)',
        'NX','Library cache pin instance (X=namespace)',
        'NY','Library cache pin instance (Y=namespace)',
        'NZ','Library cache pin instance (Z=namespace)',
        'PF','Password file',
        'PI','Parallel operation',
        'PR','Process startup',
        'PS','Parallel operation',
        'QA','Row cache instance (A=cache)',
        'QB','Row cache instance (B=cache)',
        'QC','Row cache instance (C=cache)',
        'QD','Row cache instance (D=cache)',
        'QE','Row cache instance (E=cache)',
        'QF','Row cache instance (F=cache)',
        'QG','Row cache instance (G=cache)',
        'QH','Row cache instance (H=cache)',
        'QI','Row cache instance (I=cache)',
        'QJ','Row cache instance (J=cache)',
        'QL','Row cache instance (K=cache)',
        'QK','Row cache instance (L=cache)',
        'QM','Row cache instance (M=cache)',
        'QN','Row cache instance (N=cache)',
        'QO','Row cache instance (O=cache)',
        'QP','Row cache instance (P=cache)',
        'QQ','Row cache instance (Q=cache)',
        'QR','Row cache instance (R=cache)',
        'QS','Row cache instance (S=cache)',
        'QT','Row cache instance (T=cache)',
        'QU','Row cache instance (U=cache)',
        'QV','Row cache instance (V=cache)',
        'QW','Row cache instance (W=cache)',
        'QX','Row cache instance (X=cache)',
        'QY','Row cache instance (Y=cache)',
        'QZ','Row cache instance (Z=cache)',
        'RT','Redo thread global enqueue',
        'SC','System commit number instance',
        'SM','SMON',
        'SN','Sequence number instance',
        'SQ','Sequence number enqueue',
        'SS','Sort segment',
        'ST','Space transaction enqueue',
        'SV','Sequence number value',
        'TA','Generic enqueue',
        'TM','DML enqueue',
        'TS',decode(l.id2,0,'Temporary segment enqueue',
                    'New block allocation enqueue lock'),
        'TT','Temporary table enqueue',
        'TX','Transaction enqueue',
        'UL','User supplied',
        'UN','User name',
        'US','Undo segment DDL',
        'WL','Being-written redo log instance', '????') Lockt
from    V$LOCK l,
        V$SESSION s,
        SYS.USER$ u,
        SYS.OBJ$ t
where   l.sid = s.sid
and     t.obj#  = decode(l.type,'TM',l.id1,'DL',l.id1,'SQ',l.id1,1)
and     u.user# = t.owner#
and     s.type != 'BACKGROUND'
order by 1,2,5 ;

select /*+ rule */ s.username, s.sid, s.serial#, l.type "LOCK TYPE", l.id1||'-'||l.id2 id1_id2,
       decode(l.lmode,0,'NONE',
                      1,'NULL',
                      2,'  RS',
                      3,'  RX',
                      4,'   S',
                      5,' SRX',
                      6,'   X',
                        '   ?') HELD,
       decode(l.request,0,'     NONE',
                        1,'     NULL',
                        2,'       RS',
                        3,'       RX',
                        4,'        S',
                        5,'      SRX',
                        6,'        X',
                          '        ?') REQUESTED
   from v$lock l,v$session s
   where l.sid = s.sid
   and s.username like upper('%%')
   order by id1_id2, s.sid, l.type;


  col gtxid form a50
  select 
   s.ksusenum SID
   ,r.ksqrsidt TYPE
   ,r.ksqrsid1 ID1
   ,r.ksqrsid2 ID2
   ,l.lmode lmode
   ,l.request request
   ,l.ctime ctime
   ,t.ktcxbstm tran_start_time
   ,g.K2GTIFMT||'-'||g.K2GTITID_EXT||'-'||g.K2GTIBID XID
  from 
   v$_lock l
   ,x$ksuse s
   ,x$ksqrs r 
   ,x$k2gte g
   ,x$ktcxb t
  where 
   l.saddr=s.addr(+) 
   and l.raddr=r.addr 
   and r.ksqrsidt ='TX'
   and l.laddr = t.ktcxbxba(+)
   and l.laddr = g.k2gtdxcb(+)
  ;


oradebug setmypid;
oradebug ulimite;   
oradebug dump hanganalyze 3;
oradebug dump systemstate 10;

REM check lock script

--- begin [lockchk9.sql] ---
define spoolfile = &1
spool &spoolfile
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set timed_statistics = true;
alter session set max_dump_file_size = UNLIMITED;
set feedback on
set term on
set wrap on
set trimspool on
set pagesize 1000
set linesize 2000
set numwidth 10
set echo on
select to_char(sysdate) start_time from dual;
alter session set events 'immediate trace name systemstate level 10';
alter session set events 'immediate trace name hanganalyze level 3';
column host_name format a20 tru
select instance_name, host_name, version, status, startup_time from v$instance;
select * from v$session;
select * from v$process;
select * from v$bgprocess;
select * from v$lock;
select * from v$locked_object;
select * from v$session_wait;
select * from v$latch;
select * from v$latchholder;
select * from v$rowcache;
/* For MTS */
select * from v$dispatcher;
select * from v$shared_server;
select * from v$circuit;
select * from v$queue;
select * from v$dispatcher_rate;
set echo off
Prompt;
Prompt Output file name is:;
define spoolfile
Prompt;
Prompt ALERT.LOG and TRACE FILES are located in:;
column host_name format a12 tru
column name format a20 tru
column value format a60 tru
select distinct i.host_name, p.name, p.value from v$instance i, v$parameter p
 where p.name like '%_dump_dest'
   and p.name != 'core_dump_dest';
select to_char(sysdate) end_time from dual;
spool off
exit
--- end [lockchk9.sql] ---

lockchk10.sql:

--- begin [lockchk10.sql] ---
define spoolfile = &1
spool &spoolfile
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set timed_statistics = true;
alter session set max_dump_file_size = UNLIMITED;
set feedback on
set term on
set wrap on
set trimspool on
set pagesize 1000
set linesize 2000
set numwidth 10
set echo on
select to_char(sysdate) start_time from dual;
alter session set events 'immediate trace name systemstate level 266';
alter session set events 'immediate trace name hanganalyze level 3';
column host_name format a20 tru
select instance_name, host_name, version, status, startup_time from v$instance;
select * from v$session;
select * from v$process;
select * from v$bgprocess;
select * from v$lock;
select * from v$locked_object;
select * from v$session_wait;
select * from v$latch;
select * from v$latchholder;
select * from v$rowcache;
/* FOR MTS */
select * from v$dispatcher;
select * from v$shared_server;
select * from v$circuit;
select * from v$queue;
select * from v$dispatcher_rate;
set echo off
Prompt;
Prompt Output file name is:;
define spoolfile
Prompt;
Prompt ALERT.LOG and TRACE FILES are located in:;
column host_name format a12 tru
column name format a20 tru
column value format a60 tru
select distinct i.host_name, p.name, p.value from v$instance i, v$parameter p
 where p.name like '%_dump_dest'
   and p.name != 'core_dump_dest';
select to_char(sysdate) end_time from dual;
spool off
exit
--- end [lockchk10.sql] ---

lockchk10win.sql

--- begin [lockchk10win.sql] ---
define spoolfile = &1
spool &spoolfile
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set timed_statistics = true;
alter session set max_dump_file_size = UNLIMITED;
set feedback on
set term on
set wrap on
set trimspool on
set pagesize 1000
set linesize 2000
set numwidth 10
set echo on
select to_char(sysdate) start_time from dual;
alter session set events 'immediate trace name systemstate level 10';
alter session set events 'immediate trace name hanganalyze level 3';
column host_name format a20 tru
select instance_name, host_name, version, status, startup_time from v$instance;
select * from v$session;
select * from v$process;
select * from v$bgprocess;
select * from v$lock;
select * from v$locked_object;
select * from v$session_wait;
select * from v$latch;
select * from v$latchholder;
select * from v$rowcache;
/* FOR MTS */
select * from v$dispatcher;
select * from v$shared_server;
select * from v$circuit;
select * from v$queue;
select * from v$dispatcher_rate;
set echo off
Prompt;
Prompt Output file name is:;
define spoolfile
Prompt;
Prompt ALERT.LOG and TRACE FILES are located in:;
column host_name format a12 tru
column name format a20 tru
column value format a60 tru
select distinct i.host_name, p.name, p.value from v$instance i, v$parameter p
 where p.name like '%_dump_dest'
   and p.name != 'core_dump_dest';
select to_char(sysdate) end_time from dual;
spool off
exit
--- end [lockchk10win.sql] ---

enqueue lock wait等待事件

Enqueues are sophisticated locks for managing access to shared resources like tables, rows, jobs, and redo threads. An enqueue can be requested in different levels/mode: null, row share, row exclusive, share, share row exclusive or exclusive. This wait event indicates a wait for a lock that is held by another session (or sessions) in an incompatible mode to the requested mode.

Isolating contention:

Once an enqueue resource contention problem has been identified with Ignite, one can quickly isolate the SQL and sessions that are suffering. Often, the SQL is a good clue to what objects have locking contention.

During a period of time when locking is typically a problem, use the following query to find out what session is requesting a lock, the type and mode of the requested lock and the session that is blocking.

SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;

Solutions

There are many types of locks in Oracle and each has a unique contention remedy. The following are the most common sources of contention:

TX     Transaction Lock

Generally due to application or table setup issues. See Metalink Note:62354.1 for example scenarios which can cause TX lock waits.

TM     DML enqueue

Generally due to application issues, particularly if foreign key constraints have not been indexed. The following two articles describe referential integrity issues related to TM locking:

Example TM locks During Referential Integrity Enforcement — Metalink Note:38373.1

TM locks and Foreign Key Constraints — Metalink Note:33453.1

ST     Space management enqueue

Usually caused by too much space management occurring (Eg: small extent sizes, lots of sorting etc..) See Metalink Note:33567.1 for more information about the ST enqueue.

enq: US – contention等待事件

This event indicates the session is currently waiting on the Undo Segments and is often related to using system managed undo with an auto tuned undo retention period. In 10g, Oracle added the _undo_autotune parameter which allows the database instance to extend and override “UNDO_RETENTION” settings. This is especically true when the datafiles for the Undo Tablespace are set to automatically extend. However, there are limitations and issues with this feature discussed in more detail in the Solutions section below.

Here are more details about Automatic Tuning of Undo.

Solutions

Review Bug 7291739 which affected Oracle 10.2.0.4 and is fixed in 10.2.0.4.4 / 10.2.0.5 and 11.2. It is caused by runaway queries inflating the undo retention time. This fix allows users to specify a high threshold for undo retention via the “_highthreshold_undoretention” parameter. The tuned value of undo retention will then not exceed the set value.

Ensure the UNDO tablespace has enough space and/or can autoextend to accomodate the undo settings.

If all else fails or you cannot install the latest patchset, disable automatic undo tuning by setting “_undo_autotune” = false.

enq: TX – row lock/index contention、allocate ITL等待事件

SQL> select name from v$event_name where name like ‘%TX%’;

NAME
—————————————————————-
enq: TX – row lock contention
enq: TX – allocate ITL entry
enq: TX – index contention
enq: TX – contention

 

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

enqueue TX事务锁 transaction enqueue,顾名思义这个队列锁用来保护事务信息。

 

当进程修改某块中的一行数据,则Oracle必须将该事务信息与被改变的这一行联系起来,做法是在块中的row piece的lk上标记ITL位,而实际的ITL记录了这个事务相关的回滚段号USN,以便能够定位其撤销链。

这样做的目的有几个:

  1. 允许用户手动rollback或者因为dead transaction的发生而后台(PMON or SMON 取决于_cleanup_rollback_entries)回滚该事务。
  2. 在块中留下Undo线索,才能让查询者Queryer顺藤摸瓜去构造这个块在事务发生前的镜像块

 

绝大多数情况下 ,TX enqueue的申请、获得模式总是排他的exclusive的即mode=6/request=6的(v$LOCK),这通常意味着我们的DML操作所感兴趣的数据行正被其他事务锁定着, 但这并不绝对。

 

有场景中我们会以共享模式Share mode=4/request=4(V$LOCK)去申请、获得TX enqueue锁; 一种场景是进程仅仅先需要申请到数据块(data block包括table/index)上的 ITL(interested transaction lists)事务槽。 以share mode等待(request=4)TX enqueue 的一种可能就是块上无法再扩展本进程所需要的一个ITL了,要么这个块上的ITL 超过MAXTRANS(255)ITL的上限值了,要么是这个块没有更多的空间来容纳更多一个ITL了,(一个ITL 24字节)。

 

若争用contention缘起于排他的exclusive TX队列申请,则表明有并发事务试图锁住同一行数据。 大多数情况下这是由于应用引起的。

实际上log file sync(其实是写redo慢)也会造成buffer busy wait。

 

若争用contention缘起于共享shared TX 队列申请,则有可能是一个或多个数据块无法扩展事务表所导致的。在这种情况下可以考虑增加更大的INITTRANS来解决问题:

对于该问题建议做10046 level 8+ buffer cache dump配合V$SESSION fileid/blockid/objno来诊断问题。

 

从版本9iR2开始可以通过动态性能视图的V$SEGMENT_STATISTICS来判断对象是否经历ITL 等待 ,但是实例参数 STATISTICS_LEVEL必须设置为typical 以上,可以参考statspack中segment by ITL的环节,当然AWR里也有类似的信息:

select owner, object_name from v$segment_statistics where
statistic_name = ‘ITL waits’ and value > 0;

 

TX锁的 ID1/ID2解释

ID1 是用来存放 USN==> Undo Segment Number以及相关的undo segment transaction slot,通过以下转换获得:

(undo_segment_number << 16) + slot

ID2是事务槽位序列号 transaction slot sequence number 或曰wrap number。

 

 

 

另外一些以共享模式share mode mode=4/request=4(V$LOCK)去申请、获得TX enqueue锁的场景:

 

  1. 等待块上的ITL slot
  2. 由于主键、唯一索引约束而等待
  3. 由于位图索引bitmap index而等待
  4. 因为2 phase commit,2段提交而等待

 

其他的一些官方调优手册中也提出了一些可能引起TX V$LOCK. lmode/request =4的shared lock contention的情况:

  • 当2个session试图向一个unique index插入同一个索引键值时,后插入的那个session如此等待,知道先插入的session commit/rollback,则后一个session对应的ORA-0001或插入成功。
  • 当一个session因为bitmap index fragment而等待。位图索引bitmap index是一些索引键值加上ROWID的范围。位图索引中的每一条记录可以映射为实际表上的多条记录。 若2个session试图更新的行映射到同样的位图索引记录,则后者的session需要在前者commit/rollback之前一直等request=4的TX lock。
  • 当等待一个PREPARED transaction时也会出现mode 4的TX
  • 还有就是使用dbms_repair时也可能看到mode 4的TX

 

一些相关的BUG

 

Bug 3159414 documents another case where we might see a TX request in mode 4 when constraints are involved.
Bug 5849679 documents an undetected deadlock case with RAC.

 

 

Occurs when attempting to access a row that is locked in exclusive mode. more…

Solutions

1) Waits due to row locked by an active transaction

See if certain jobs can be run at other times; Investigate application code problems such as commits occurring later than necessary or unnecessary repetitive updates.

2) Waits due to Unique or Primary Key Constraint enforcement

A possible, but risky solution is to drop the constraint and possibly check constraints at a later point (perhaps via a batch job).

3) Waits due to Insufficient ‘ITL’ slots in the Block(INITRANS and MAXTRANS)

Increase the INITRANS and MAXTRANS (9i and below) settings

4) Waits due to rows being covered by the same BITMAP index fragment

Rebuild index

Compressed Indexes

Expanded Definition

A TX lock is acquired exclusive (X) when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. This ensures a read consistent image of the block before the transaction started. It is used mainly as a queuing mechanism so that other sessions can wait for the transaction to complete.

enq: TT – contention等待事件

TT 队列锁在官方文档中介绍为TT, Temporary Table,但是实际在版本8i之后该队列锁更多参与在表空间管理事务中。 也可以称enqueue TT为tablespace lock。

作用

该enqueue TT队列锁用以在各种类型的表空间操作执行过程中避免出现死锁dead lock。 该enqueue lock的 ID2表明正在执行的操作种类,ID1表明该操作对应的表空间号V$Tablespace.TS#。

ID1/ ID2的含义

id1是tablespace number V$Tablespace.TS#,ID2表明执行中的操作类型。以下是操作类型对应代码;

 

0- 用以避免在drop tablespace和创建rollback segment之间发生死锁

1- 用以串行化在给定表空间上创建数据文件

2- 用以避免在TSPITR tablespace point in time recovery期间发生其他类型操作

4- 用以在创建tablespace时锁住该tablespace id

8- 用以避免在ALTER TABLESPACE期间发生死锁

16- 即16进制的0x10,用以同步 分配和回收盘区,allocation and deallocation of extents.

32+ 用在增加数据文件add datafile和创建表空间时,其中ID2为 32(十进制的)+相对文件号relative file number

 

 

最常见的TT enqueue争用是 TT-00000x-0000010  即allocation and deallocation of extents.

如果10g以后遇到类似问题 , 建议先了解一下extent management、segment management(ASSM、MSSM?)管理方式等,是否打开了回收站recyclebin功能,以及该表空间上回收站对象的extent数量。

 

enqueue TT相关的一些BUG列表如下:

 
Bug 8313598 – ORA-60 on TT enqueue from DBMS_SPACE.ISDATAFILEDROPPABLE_NAME [ID 8313598.8]
Bug 4732503 – Self-deadlock on TT enqueue [ID 4732503.8]
Bug 6809093 – Hang due to TT enqueue waits on an UNDO tablespace [ID 6809093.8]
Bug 14055559 System hang due to TT enqueue contention with BIGFILE tablespace resize
Bug 3833893 SMON may hold the TT enqueue preventing alter of TEMPORARY TABLESPACE
Sessions Hang Due to Self Deadlock on TT Enqueue [ID 948668.1]
Bug 3397983 – Sessions may hang waiting for TT enqueue [ID 3397983.8]
Bug 3467364 – Adding datafiles serializes on the TT enqueue [ID 3467364.8]
Bug 8332021 – Cannot add a datafiles when sessions reporting ORA-1653 / TT enqueue contention between datafile addition and other segment extension operations [ID 8332021.8]
Bug 2272671 – DEADLOCK possible on CREATE INDEX ONLINE (KGL lock v. TT enqueue) [ID 2272671.8]
Bug 6762619 : ENQUEUE “TT” REMAINS WHEN ACCESSING DBA_FREE_SPACE
Bug 6858962 : TT AND US ENQUEUE DEADLOCK
Bug 9439759 : TT ENQUEUE IS NOT RELEASED WHEN CANCEL QUERY
Bug 6265482 : TT ENQUEUE REMAINS AFTER CANCEL “SELECT * FROM DBA_EXTENTS”
Bug 7217723 : ORA-00060 DEADLOCK DETECTED DUE TO TT AND US ENQUEUE
Bug 4732503 : SELF-DEADLOCK TT ENQUEUE ON 9.2.0.7 SIMILAR TO 3425298
Bug 9403168 : WAIT FOR TT-ENQUEUE FOR INSERT TO BASCIFILES DURING ADDING DATAFILE TO SAME TBS
Bug 9451566 : DEADLOCK ON TT-ENQUEUE
Bug 3833893 : SMON GETS “TT ENQUEUE” OF TEMP IF DATEFILE OF TEMP DOESN’T EXIST.
Bug 9948775 : SELF-DEADLOCK ON TT ENQUEUE
 

 

The TT portion of the wait event name stands for Temporary Table and typically indicates issues with the temporary tablespace or related storage. Look for other process that are manipulating temporary tablespaces or tempfiles during the time when these waits were noticed.

Solutions

If an ALTER DATABASE TEMPFILE DROP hangs due to a TT enqueue that is retained by the SMON process in Oracle 9.2.0.6 or lower, Oracle bug 3833893 is responsible. This problem has been solved in Oracle 9.2.0.7. For more information, see Note 867681.

Longer waits for the TT enqueue are generally caused by Oracle bugs. If you experience problems with this enqueue type, you should contact Oracle Support for further assistance.

enq: TM – contention等待事件

介绍

TM enqueue ,这里的TM 是指Table Manipulation,最常见的enqueue 之一, enq: TM – contention 最常见的enqueue 并发争用等待之一。

 

使用

TM 锁在下列场景中被申请:

  1.  在OPS(早期的RAC)中LGWR会以ID1=0 &  ID2=0去申请该队列锁来检查 DML_LOCKS 在所有实例中是全0还是全非0
  2. 当一个单表或分区 需要做不同的表/分区操作时,ORACLE需要协调这些操作,所以需要申请该队列锁。包括:
  3. 启用参考约束 referential constraints
  4. 修改约束从DIASABLE NOVALIDATE 到DISABLE VALIDATE
  5. 重建IOT
  6. 创建视图或者修改ALTER视图时可能需要申请该队列锁
  7. 分析表统计信息或validate structure时
  8. 一些PDML并行DML操作
  9. 所有可能调用kkdllk()函数的操作
  10. 太多太多了。。。

 

减少ENQ: TM – Contention

 

我们可以通过ID1作为object_id来确认大家争用的到底是什么对象,之后我们可以确定具体如何减少这些争用,具体到SQL。

 

ID1 ID2的含义

ID1 要么是0(LGWR)  要么是object_number, 即DBA_OBJECTS.OBJECT_ID,对应锁住对象的对象号。Id2 is 0 for a normal table / partition lock and 1 for a partition-wait lock.

 

 

 

 

 

 

 

Waits on this event typically occur because an index is missing on the column(s) containing a foreign key constraint. In this case Oracle is forced to acquire a TM lock on the child table during DELETE, INSERT and UPDATE statements. However, there are other cases where this can occur, e.g. a LOCK TABLE command is being used.

Solutions

Review all foreign key constraints to ensure corresponding indexes are in place. Script displays the problem table in the Objects tab for the SQL statement. Also review the Blockers tab to see what the blocker is doing. The following script will show all unindexed columns from foreign key constraints for a specific user and it can also be customized to include only the one table :

SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = ‘R’
AND c.owner = upper(‘&&owner’) and cc.owner = upper(‘&&owner’)
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM user_indexes i, user_ind_columns ic
WHERE i.index_name = ic.index_name
AND c.owner = upper(‘&&owner’) and cc.owner = upper(‘&&owner’)
)
ORDER BY table_name, column_position;

 

 

1. Bug 4153376 documents a change in the mode that this enqueue takes out for SELECT FOR
UPDATE statements.
2. Bug 5849679 documents an undetected deadlock case with RAC.
3. Bug 6053354 documents a possible area of contention if using indexes that reside in a read-only
tablespace.

Identify ksusetxn DID:An Deadlock ID

我们在查看10704 event trace(Print out information about what enqueues are being obtained)或deadlock detected trace死锁检测跟踪日志时,总是会看到名为”DID”的名词,影响”DID”这个名词被正确理解的一个原因是你很难通过search engine正确找到相关的正确解释(被误解)。

那么DID到底是什么东西呢?我们来看一下trace中的DID:

=====================10704 enqueue trace========================

ksqgtl *** CU-913f5a28-00000000 mode=6 flags=0x10010 timeout=300 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
        ksqlkdid: 0002-001E-00000026

*** 2011-05-09 23:44:15.210
*** ksudidTrace: ksqgtl
        ksusesdi:   0002-001E-00000025
        ksusetxn:   0002-001E-00000026
ksqgtl: RETURNS 0

*** 2011-05-09 23:44:15.212
ksqrcl: CU,913f5a28,0
ksqrcl: returns 0

*** 2011-05-09 23:44:15.212
ksqgtl *** TM-00013abc-00000000 mode=6 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x97227118, ktcdix=2147483647, topxcb=0x97227118
        ktcipt(topxcb)=0x0

*** 2011-05-09 23:44:15.212
ksucti: init txn DID from session DID
ksqgtl:
        ksqlkdid: 0002-001E-00000026

*** 2011-05-09 23:44:15.212
*** ksudidTrace: ksqgtl
        ktcmydid(): 0002-001E-00000026
        ksusesdi:   0002-001E-00000025
        ksusetxn:   0002-001E-00000026

===========================global enqueue deadlock detected==========================

Global Wait-For-Graph(WFG) at ddTS[0.1] :
BLOCKED 0xda9da740 5 wq 2 cvtops x1 TX 0x110001.0x8c(ext 0x5,0x0)[34000-0001-0000006E] inst 1
BLOCKER 0x99a2bf50 5 wq 1 cvtops x28 TX 0x110001.0x8c(ext 0x5,0x0)[1E000-0002-0000001D] inst 2
BLOCKED 0x99448ad8 5 wq 2 cvtops x1 TX 0x9000d.0x7b9(ext 0x2,0x0)[1E000-0002-0000001D] inst 2
BLOCKER 0xda983008 5 wq 1 cvtops x28 TX 0x9000d.0x7b9(ext 0x2,0x0)[34000-0001-0000006E] inst 1

DID即Deadlock id,在Oracle死锁检测时充当一个进程的identifier.可以看到lmd RAC后台进程的global enqueue deadlock detected日志中的DID与单实例死锁检测日志或10704事件跟踪日志中的DID在格式上存在区别,但他们的内容是一样的,我们以单实例中的DID为例:

002-001E-00000026

002:instance number,实例号

001E: 十进制的30,ORACLE PID,ORACLE进程号

0026:十进制的38,类似于v$session.serial#,ORACLE PID的一个串行值,复用ORACLE PID后会递增

global enqueue deadlock detected日志中的DID也是类似的,仅仅是把instance number和oracle pid的顺序颠倒了,同时pid要从高位读起。

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569