en锁定oracle,enquence等待事件

1.排队是应用于/不相容时如何共享相同的资源。排队是事务的，由应用程序初始化。

2.事件参数(9i环境，)

事件编号：15

事件名：enqueue

参数一：name|mode(不同的锁类型，id1与id2有不同的含义)

参数二：id1

参数三：id2

注：ORACLE9i所有排队的等待都归于enqueue中，ORACLE10g开始，将所有排队按类型分成独立的事件，所以很容量分辨出ID1和ID2的含义。

3.受到排队锁影响的数据库资源，我们称之为"排队资源"。ORACLE使用内部数组结构来处理排队资源，可以通过x$ksqrs(内核服务排队资源)或v$resource视图来查看。

select s.ADDR as "资源对象地址", s.TYPE, s.ID1, s.ID2 from v$resource s

>>初始化参数enqueue_resources控制被锁968。一般默认值是足够用的。

>>并行DML操作比串行DML操作使用更多的锁。

>>排队资源超过初始化参数enqueue_resources的设置值，则会发生"ORA-00052"错误。

>>查询系统资源的使用情况：

SELECT s.resource_name, s.current_utilization AS "当前使用数",s.max_utilization AS "系统最大使用数",s.initial_allocation AS "系统初始化参数分配数",s.limit_valueFROM v$resource_limit sWHERE s.resource_name IN ('enqueue_resources', 'enqueue_locks', 'dml_locks','processes', 'processes')

4.排队锁使用单独的数组而不是排队资源数组来管理排队锁，通过查询x$ksqeq(内核服务排队对象)或v$enqueue_lock视图来看到这种结构。

>> v$resource_limit视图可查看排队锁资源的总体使用情况。查看'enqueue_locks'行的对应列。

>> v$equeue_lock视图(除TX和TM锁)

SELECT s.addr,s.kaddr,s.sid,s.TYPE,s.id1,s.id2,s.lmode,s.request,s.ctime,s.BLOCKFROM v$enqueue_lock s

ADDR：锁状态对象地址

KADDR：锁地址

LMODE：锁模式(0-NONE 1-NULL

2-RS 3-RX 4-S 5-SRX 6-X )

>> v$lock显示了所有的锁。

5.各种排队锁类型下，ID1和ID2的含义：

排队锁类型

ID1

ID2

TX

回滚段号和槽号。可以在v$transaction视图的XIDUSN和XIDSLOT列中看到这两个值。

序号。可以在v$transaction视图的XIDSQN列中看到值。

TM

对象ID。可以在dba_objects.object_id中找到该值

总为0。

TS

表空间号。在以在TS$TS#中找到该值。

相对的数据库块地址(DBA)

JQ

总为0。

工作号。

MR

数据文件ID。ORACLE对每个数据文件(包括临时文件)采用一个MR排队。

总为0。

RT

重做线程号。

总为0。

6.从equeue等待事件中，解码排队类型及模式：

SELECT s.sid,s.event,s.p1,s.p1raw,chr(bitand(s.p1,

-16777216) / 16777215) ||chr(bitand(s.p1, 16711680)

/ 65535) AS "TYPE", MOD(s.p1, 16) AS "MODE" FROM v$session_wait s WHERE s.event = 'enqueue'

7.表级TM锁模式的相容性：

模式(LMODE)

(T2)

-

S

X

RS

RX

SRX

(T1)

-

YES

YES

YES

YES

YES

YES

S

YES

YES

NO

YES

NO

NO

X

YES

NO

NO

NO

NO

NO

RS

YES

YES

NO

YES

YES*

YES*

RX

YES

NO

NO

YES*

YES*

NO

SRX

YES

NO

NO

YES*

NO

NO

LMODE

SQL

-

SELECT .. FROM ...

RS

SELECT * FROM ...

FOR UPDATE

LOCK TABLE

TABLE_NAME IN ROW SHARE MODE

RX

INSERT..

UPDATE..

DELETE..

LOCK TABLE

TABLE_NAME IN ROW EXCLUSIVE MODE

S

LOCK TABLE

TABLE_NAME IN SHARE MODE

SRX

LOCK TABLE

TABLE_NAME IN SHARE ROW EXCLUSIVE MODE

X

LOCK TABLE

TALBE_NAME IN EXCLUSIVE MODE

8.ORACLE9i中v$enqueue_stat视图中保留实例级别的各种队列锁的请求数量和等待时间。

9.排队体系结构：

组成：enqueue hash chains锁存器、排队散列表、排队散列链、排队资源

>>组成形式：

>> enqueu hash chains锁存器数目等于CPU_COUNT。这个锁存的数量可以通过初始化参数_enqueue_hash_chain_latches来进行调整。

>>排队散列表的默认长度来源于SESSIONS参数，可通过_ENQUEUE_HASH参数调整。

>>排队资源根据其资源类型和标识符被散列到相应的排队散列表，放置于排队散列链中。

>>如果调整过并发排队资源数的初始化参数enqueue_resources，则需要调整_enqueue_hash初始化参数。因为散列表的长度由SESSIONS参数来决定。

>>缺省排队散列表的长度=((sessions-10)*2)+55

>>使用下列命令将排队结构转储到一个跟踪文件：

alter session set events 'immediate trace name equeues level 3'

10.模式6中的TX enqueue等待(ORACLE10g enq:TX-row lock contention)

>> v$lock视图中的TYPE=TX并且REQUEST=6。

>>发生在一个事务尝试更新或删除当前被另一个事务锁定的行时发生该等待事件。

>>查询"谁是阻塞者以及是否存在相同资源的多个等待者"

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   FROM v$lock b, v$enqueue_lock c, v$session a  WHERE a.sid = b.sidAND b.id1

= c.id1(+)AND b.id2 = c.id2(+)   AND c.TYPE(+) = 'TX'AND b.TYPE = 'TX'AND b.BLOCK = 1  ORDER BY time_held, time_waited

说明：

ORACLE要花费很多时间分析多表连接，以决定表的连接顺序。如果SQL语句的连接包括七个以上的表时，因为ORALCE必须评估所有连接的可能，有时会花费30分钟的分析时间。在仅仅八个表的情况下就有4000种可能的顺序。而使用ORDERED标签可以直接给出一个正确的查询连接顺序。实际上就是指定一个驱动表，驱动表的行通常很少。

示例：

--强制使用nested loop

join和4路并行查询select /*+ ordered

use_nl(bonus) parallel(e, 4) */

e.ename,  hiredate, b.comm

from emp e, bonus b

where e.ename = b.ename

注：在这个查询中有个非常高明的hint-ORDERED，使SQL分析时间大大缩短。

>>查询enqueue事件等待的资源：

SELECT c.sid

waiter_sid, a.object_name, a.object_type  FROM dba_objects a, v$session b, v$session_wait c WHERE (a.object_id = b.row_wait_obj# OR a.data_object_id

= b.row_wait_obj#)   AND b.sid = c.sid   AND chr(bitand(c.p1, -16777216) / 16777215) ||

chr(bitand(c.p1, 16711680) / 65535) = 'TX'   AND c.event = 'enqueue'

11.模式4中的TX enqueue等待

>>原因一：ITL不足

1) ITL是"感兴趣的事务列表"的缩写，这是一个数据块的事务槽(SLOT)，槽的初始数据由INITRANS子句定义，并且由MAXTRANS子句限制。默认情况下，表所包含的每个数据块中有1个ITL，索引有2个ITL。每个ITL占据24个字节，以USN.SLOT#.WRAP#包含事务ID。

2)在可以操作数据前，每个DML事务需要在块中获取它自己的ITL空间，当某个块中所有可用的ITL都在使用中，并且PCTFREE中没有空间让ORACLE动态分配一个新的ITL槽时，ITL争用就会发生，在这种情况下，会话将持续等待，直到一个事务被提交或回滚。

3)可以使用alter system dump datafile block命令来查看ITL的使用情况，ITL的--U-标识该ITL正在使用。

4)查询系统所有对象的ITL等待状态，可以使用查询

SELECT s.owner, s.object_name,  s.subobject_name,

s.object_type, s.tablespace_name, s.VALUE,s.statistic_name FROM v$segment_statistics s WHERE s.statistic_name = 'ITL waits'AND s.VALUE > 0 ORDER by VALUE DESC

>>原因二：唯一键实施

>>原因三：位映射索引条目

12.ST enqueue等待

>>每个数据库只有一个ST锁。

>>修改UET$(用户范围表)和FET$(空闲范围表)的数据库操作需要ST锁，这包括删除、截取、结合等动作。

>> ST锁争用表明有多个活动会话在字典管理的表空间中执行动态磁盘空间分配或解除分配。不是使用TEMPORARY子句创建的临时表空间和经历大范围分配和解除分配的字典管理的表空间是ST锁争用的主要原因。

>>减少ST锁争用的方法：

1)使用本地管理表空间，在ORACLE9i中所有表空间均可本地化管理。

2)使用CREATE TEMPORARY TABLESPACE TEMPFILE...命令创建所有临时表空间。

3)

13.模式3的TM equeue等待