非空闲的等待事件(二)

非空闲的等待事件(一) http://blog.chinaunix.net/u3/107027/showart_2146592.html

 

 

八、direct path read(USER I/0类)

 

1. 与直接读取相关联的等待事件。当ORACLE将数据块直接读入会话的PGA（进程全局区）中，同时绕过SGA（系统全局区）。PGA中的数据并不和其他的会话共享。即表明，读入的这部分数据该会话独自使用，不放于共享的SGA中。
2. 在排序操作(order by/group by/union/distinct/rollup/合并连接)时，由于PGA中的SORT_AREA_SIZE空间不足，造成需要使用临时表空间来保存中间结果，当从临时表空间读入排序结果时，产生direct path read等待事件。
3. 使用HASH连接的SQL语句，将不适合位于内存中的散列分区刷新到临时表空间中。为了查明匹配SQL谓词的行，临时表空间中的散列分区被读回到内存中(目的是为了查明匹配SQL谓词的行)，ORALCE会话在direct path read等待事件上等待。
4. 使用并行扫描的SQL语句也会影响系统范围的direct path read等待事件。在并行执行过程中，direct path read等待事件与从属查询有关，而与父查询无关，运行父查询的会话基本上会在PX Deq:Execute Reply上等待，从属查询会产生direct path read等待事件。
5. 直接读取可能按照同步或异步的方式执行，取决于平台和初始化参数disk_asynch_io参数的值。使用异步I/O时，系统范围的等待的事件的统计可能不准确，会造成误导作用。
6. 该事件一般不可能显示为主要的瓶颈，但它实际上也许是就是祸首。由于ORACLE统计等待时间的方式会造成统计的时间量不准确(如：从属查询产生的时间无法进行统计)，所以对该事件不应该使用v$session_event视图中的total_wait或time_waited进行估计，应该使用v$sesstat视图中的直接读取操作次数(physical reads direct)进行判断：

select a.NAME,
       b.SID,
       b.VALUE,
       round((sysdate - c.LOGON_TIME) * 24) hours_connected
  from v$statname a, v$sesstat b, v$session c
 where b.SID = c.SID
   and a.STATISTIC# = b.STATISTIC#
   and b.VALUE > 0
   and a.NAME = 'physical reads direct'
 order by b.VALUE

7. 由direct path read事件产生的原因，我们需要判断该事件正在读取什么段(如：散列段、排序段、一般性的数据文件)，由此可判断产生该事件的原因是什么，可使用以下语句进行查询：

SELECT a.event,
       a.sid,
       c.sql_hash_value hash_vale,
       decode(d.ktssosegt,
              1,
              'SORT',
              2,
              'HASH',
              3,
              'DATA',
              4,
              'INDEX',
              5,
              'LOB_DATA',
              6,
              'LOB_INDEX',
              NULL) AS segment_type,
       b.tablespace_name,
       b.file_name
  FROM v$session_wait a, dba_data_files b, v$session c, x$ktsso d
 WHERE c.saddr = d.ktssoses(+)
   AND c.serial# = d.ktssosno(+)
   AND d.inst_id(+) = userenv('instance')
   AND a.sid = c.sid
   AND a.p1 = b.file_id
   AND a.event = 'direct path read'
UNION ALL
SELECT a.event,
       a.sid,
       d.sql_hash_value hash_value,
       decode(e.ktssosegt,
              1,
              'SORT',
              2,
              'HASH',
              3,
              'DATA',
              4,
              'INDEX',
              5,
              'LOB_DATA',
              6,
              'LOB_INDEX',
              NULL) AS segment_type,
       b.tablespace_name,
       b.file_name
  FROM v$session_wait a,
       dba_temp_files b,
       v$parameter    c,
       v$session      d,
       x$ktsso        e
 WHERE d.saddr = e.ktssoses(+)
   AND d.serial# = e.ktssosno(+)
   AND e.inst_id(+) = userenv('instance')
   AND a.sid = d.sid
   AND b.file_id = a.p1 - c.VALUE
   AND c.NAME = 'db_files'
   AND a.event = 'direct path read'   

注：如果是从临时文件中读取排序段的会话，则表明SORT_AREA_SIZE或PGA_AGGREGATE_TARGET的设置是不是偏小。如果是从临时文件中读取HASH段的会话，则表明HASH_AREA_SIZE或PAG_AGGREGATE_TARGET的设置是不是偏小。

8. 当direct path read等待事件是由于并行查询造成的(读取的是一般的数据文件而非临时文件)，父SQL语句的HASHVALUE与子SQL语句的HASHVALUE不同，可以通过以下SQL查询产生子SQL语句的父SQL语句：

SELECT decode(a.qcserial#, NULL, 'PARENT', 'CHILD') stmt_level,
       a.sid,
       a.serial#,
       b.username,
       b.osuser,
       b.sql_hash_value,
       b.sql_address,
       a.degree,
       a.req_degree
  FROM v$px_session a, v$session b
 WHERE a.sid = b.sid
 ORDER BY a.qcsid, stmt_level DESC

9. 初始化参数db_file_direct_io_count用来设置直接读出和写入操作设置最大的IO缓冲区大小，因此能影响direct path read的性能，该参数在9i中被隐蔽，并改以字节数而不是块数为单位。

>> 使用10046第8层跟踪直接读取操作的ORACLE会话，其中P3参数表明块读取的数量。

>> 也可使用strace,truss追踪直接读取或直接写入操作的UNIX进程，从生成的TRACE文件可获得相应的直接IO大小。

>> 在第1层使用追踪事件10357，启动执行直接IO操作的会话的调试信息。

10. 大量的direct path read等待事件最可能是一个应用程序的问题。
11. 参数说明：

事件号：212

事件名：direct path read        

参数一：读取数据文件的绝对文件号码file number

参数二：起始块号first dba

参数三：要读取的块数block cnt

由参数P1与P2推得访问的数据对象：

select s.segment_name, s.partition_name 
  from dba_extents s 
 where  between s.block_id and (s.block_id + s.blocks -1) and s.file_id = 

注：

>> 1. 如果是Temp文件，则表示该会话正在读取它先前用direct path write操作所创建的临时段，查明使用的是什么类型的临时段，有助于了解会话所做的事情。

SELECT DISTINCT decode(t.ktssosegt,
                        1,'SORT',
                        2,'HASH',
                        3,'DATA',
                        4,'INDEX',
                        5,'LOB_DATA',
                        6,'LOB_INDEX',
                        'UNDEFINED')
  FROM sys.x$ktsso t
 WHERE t.inst_id = userenv('instance') AND
       t.kssoses = <当前session地址> AND
       t.ktssosno =

>> 2. 如果是数据文件，则可能是并行查询从属操作在工作，通过P1值确定数据文件的名称：

select s.NAME from v$datafile s where s.FILE# = 
union all
select a.name 
from v$tempfile a, v$parameter b
where b.NAME = 'db_files'
      and a.FILE# + b.VALUE = 

12. 等待时间：无超时

 

九、direct path write(USER I/0类)

 

1. 从PGA写入数据文件，一个会话可以发布多个写入请求和连续的处理。
2. 直接写入可以按同步或异步方式执行，取决于平台和DISK_ASYNC_IO参数的值。
3. 通常用于在数据加载（APPEND提示、CTAS－CREATE　TABLE　AS　SELECT）、并行DML操作时写入到临时段。
4. 在使用异步IO时，direct path write事件产生的等待时间不准确，所以通过v$sesstat视图来获得直接写入次数来评估该事件的影响情况：

SELECT a.NAME,
       b.sid,
       b.VALUE,
       round((SYSDATE - c.logon_time) * 24) hours_connected
  FROM v$statname a, v$sesstat b, v$session c
 WHERE a.statistic# = b.statistic#
   AND b.sid = c.sid
   AND b.VALUE > 0
   AND a.NAME = 'physical writes direct'

5. 参数说明：

　　事件号：213

　　事件名：direct path write        

　　参数一：要写入的绝对文件号file number，可发现所进行的操作性质(如：排序/并行操作)

　　参数二：起始块号first dba

　　参数三：块数block cnt，可发现直接写入IO的大小

由参数P1与P2推得访问的数据对象：

select s.segment_name, s.partition_name 
  from dba_extents s 
 where  between s.block_id and (s.block_id + s.blocks -1) and s.file_id = 

注：

>> 1. 如果是Temp文件，则表示该会话正在写入临时表空间，查明使用临时段的类型，有助于了解会话所做的事情。

SELECT DISTINCT decode(t.ktssosegt,
                        1,'SORT',
                        2,'HASH',
                        3,'DATA',
                        4,'INDEX',
                        5,'LOB_DATA',
                        6,'LOB_INDEX',
                        'UNDEFINED')
  FROM sys.x$ktsso t
 WHERE t.inst_id = userenv('instance') AND
       t.kssoses = <当前session地址> AND
       t.ktssosno =

>> 2. 如果是数据文件，则可能正在执行一项直接路径加载操作，通过P1值确定数据文件的名称：

select s.NAME from v$datafile s where s.FILE# = 
union all
select a.name 
from v$tempfile a, v$parameter b
where b.NAME = 'db_files'
      and a.FILE# + b.VALUE = 

6. 等待时间：无超

 

十、enqueue

1. 排队是应用于数据库对象、重做线程、后台工作的锁，用来控制多个并发会话在锁模式相容/不相容时如何共享相同的资源。排队是事务的，由应用程序初始化。
2. 事件参数(9i环境，10g中参数二、三有所变化)

事件编号：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_value
  FROM v$resource_limit s
 WHERE 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.BLOCK
FROM 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.sid
   AND 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等待

 

十一、free buffer waits (Configuration)

1. 在将一个块读入到缓冲存储器之前，Oracle进程必须发现并获得用于这个块的空闲缓冲区。如果找不到LRU列表上的空闲缓冲区，或缓冲区不可用，那么会话就会在free buffer waits事件上等待。
2. DBWR进程负责在LRU列表上建立清洁缓冲区。
3. ORACLE前台进程扫描LRU列表，要到预先定义的限度才停止扫描，这个限度通常是LRU列表的一定百分比，在ORACLE9i中，这个限度是40%，这个值在视图x$kvit中可以查看到：

select * from x$kvit where kvittag = 'kcbfsp'

KVITTAG	KVITDSC	KVITVAL
kcbfsp	Max percentage of LRU list foreground can scan for free	40

如果扫描LRU列表至该限度依旧未找到合适的空闲缓冲区，前台进程则向DBWR提交，DBWR建立清洁的缓冲区，在DBWR建立清洁缓冲区的工作中，ORACLE进程在free buffer waits事件上等待。

4. ORACLE保持对每个空闲缓冲区请求的计数。可以从v$sysstat视图中查询而得：

select * from v$sysstat s where s.NAME = 'free buffer requested'

5. ORACLE保持对空闲缓冲区请求失败的次数：

select * from v$system_event s where s.EVENT = 'free buffer waits'

注：字段total_waits则表明总等待次数。

6. 可以从v$sysstat视图中查询而得进程必须查看多少缓冲区以获得所要求的free buffer：

select * from v$sysstat s where s.NAME in ('free buffer inspected','free buffer requested')

注：如果inspected数量远远大于requested数量，则意味着进程需要扫描更多的LRU列表以获得可用的缓冲区。

7. 产生该等待事件的原因：

原因	说明
低效率的SQL语句

8. 参数说明：

事件号：137

事件名：free buffer waits        

参数一：ORACLE读取块的文件号file#

参数二：块号block#

参数三：LRU列表的set-id#

9. 等待时间：最多1秒，然后再次查找。