oracle 等待事件

韶博雅

已于 2024-06-21 11:57:23 修改

阅读量269

点赞数 2

文章标签： oracle 数据库

于 2024-06-21 11:25:00 首次发布

本文链接：https://blog.csdn.net/weixin_39735909/article/details/139854896

版权

1.db file scattered read

含义：当一个SQL语句需要从数据文件中读取多块非连续的数据块时，就会发生db file scattered read等待事件。这意味着数据分散在不同的位置，数据库需要进行多次I/O操作来收集所需的信息。

场景：这种事件常见于全表扫描或者索引快速扫描（index fast full scan）操作，因为这些操作通常不按照数据块的物理顺序进行访问。
性能影响：由于涉及多个分散的I/O操作，与顺序读相比，散列读可能会有更高的I/O开销，特别是在I/O子系统响应时间较长的情况下。

2.db file sequential read

含义：当数据库以连续的顺序从数据文件中读取数据块时，会发生db file sequential read等待事件。这意味着数据块是按顺序排列的，可以连续读取，减少了磁头移动的时间。

场景：这种事件通常发生在通过索引访问表数据（如索引范围扫描）或者直接通过ROWID访问单行数据时，因为这些操作往往能够预测到下一个要读取的数据块位置。

性能影响：顺序读相对高效，因为它利用了磁盘I/O的局部性原理，减少了寻道时间和旋转延迟。在I/O子系统性能良好的情况下，顺序读的速度通常比散列读快。

3.enq: TX - row lock contention

ENQ：代表“Enqueue”，是Oracle中用于实现资源锁定和队列管理的一个系统，确保在多用户环境中对共享资源的访问能够有序进行。

TX：指代Transaction，说明这个等待事件与数据库事务处理中的锁有关。

row lock contention：意味着有多个事务尝试同时修改或锁定同一行数据。在Oracle中，为了维护数据的一致性和完整性，当一个事务正在更新某一行时，会为此行加上排他锁（X锁），阻止其他事务同时修改该行。如果此时有其他事务也尝试修改这行数据，它就必须等待当前持有锁的事务完成并释放锁。

这种等待通常表示数据库中存在一定程度的并发冲突，特别是在高并发事务处理的应用场景下更为常见。过多的行锁争用可能导致事务响应时间延长，影响数据库的整体性能。

解决和优化方法：
优化SQL查询：减少不必要的数据更新操作，尽量使用批量处理来减少锁的粒度和持续时间。
使用绑定变量：避免因SQL语句的硬解析导致的不必要的锁争用。
分析并调整事务设计：确保事务尽可能短小，减少持有锁的时间。
考虑乐观锁或悲观锁策略：根据业务需求选择合适的锁策略，乐观锁可以在一定程度上减少直接的锁争用。
数据库参数调整：比如调整锁相关参数，如TX_LOCK_TIMEOUT等，来管理锁等待行为。

4.buffer busy waits

表示数据库进程在尝试访问一个数据缓冲区（buffer）时，该缓冲区正被另一个进程占用，因此当前进程必须等待直到该缓冲区变为可用。这个等待事件通常与高度并发的数据库活动相关，特别是在以下几种情况中较为常见：

块争用：当多个会话试图同时访问同一个数据库块（例如，表块、索引块）时，如果这些块还没有被加载到内存中的数据缓冲区缓存里，或者已经被加载但正被其他会话锁定，就会产生buffer busy waits。

热块争用：在高并发事务处理场景下，某些数据块（如索引叶节点、热门表的特定块）可能被频繁访问，导致这些块成为“热块”。多个会话同时尝试修改这些热块时，就会引起争用。

长时间运行的查询或事务：如果某个查询或事务长时间持有对缓冲区的锁定，也会导致其他需要访问相同缓冲区的会话等待。

缓冲池大小不足：如果数据缓冲区缓存（Buffer Cache）的大小不足以满足当前的工作负载，频繁的缓冲区替换会导致更多的等待事件。

解决和优化策略：

增加数据缓冲区缓存大小：根据系统监控和性能分析结果，适当增加DB_CACHE_SIZE参数值，以增大数据缓冲区缓存，减少缓冲区争用。

优化SQL查询：减少大表的全表扫描，优化查询逻辑，使用索引来减少需要访问的数据块数量。

调整数据库参数：比如调整DB_BLOCK_CHECKSUM（关闭校验和检查以减少CPU开销）、DB_BLOCK_LRU_LATCHES（增加LRU latch的数量以减少闩锁等待）等。

使用绑定变量：减少硬解析，以减少缓冲区争用的可能性。

分析争用热点：使用Oracle的AWR报告或ASH（Active Session History）数据来定位具体的争用对象，然后针对性地优化访问模式或调整索引策略。

考虑分区或分片：对于非常大的表或索引，可以考虑进行分区，以分散访问压力，减少对单一数据块的争用。

解决buffer busy waits问题对于提高数据库的并发处理能力和整体性能至关重要。

5.cursor: mutex X

游标的互斥（mutex）锁。在Oracle中，游标是用来处理SQL语句执行上下文的内部结构，包括解析、执行计划生成及结果集处理等信息。这个等待事件具体指的是：

Mutex锁：Mutex是一种轻量级的锁机制，用于保护共享资源免受并发访问的影响，确保同一时间只有一个会话可以修改该资源。在Oracle中，游标上的Mutex锁（特别是Mutex X，即独占型Mutex锁）用于控制对游标定义和状态的访问，尤其是当存在多个会话试图同时打开、关闭或修改同一个游标的状态时。

等待原因：当一个会话尝试获取一个游标的Mutex X锁，而该锁已被其他会话持有时，就会发生cursor: mutex X等待事件。这通常意味着有并发操作正在进行，比如多个会话试图同时执行DDL（数据定义语言）操作，或者在执行动态SQL时创建、修改或销毁相同的游标。

潜在场景：
动态SQL执行频繁，尤其是在PL/SQL代码块中，每次执行不同的SQL语句都可能创建新的游标。
应用程序设计不当，导致重复打开和关闭相同的游标。
数据库内部操作，如统计信息的自动收集、优化器的重优化等，也可能涉及游标的Mutex锁。

解决策略：
优化SQL和PL/SQL代码：减少动态SQL的使用，尽量使用绑定变量和静态SQL，以复用已存在的游标。
分析和调整应用逻辑：确保不会过度创建和关闭游标，特别是在循环中重复执行相似查询时。
调整数据库参数：虽然直接调整与Mutex相关的参数可能较难直接改善此问题，但合理配置资源管理、调整会话和进程相关参数可能间接帮助减少竞争。
监控和诊断：使用Oracle的等待事件、ASH报告和SQL监控工具来定位具体导致Mutex争用的SQL或PL/SQL代码段，进而针对性地优化。
减少cursor: mutex X等待事件的发生，可以提升数据库的并发处理能力，减少响应时间，从而提高整体性能。

6.direct path read

direct path read 是Oracle数据库中的一个等待事件，它表示数据库进程绕过传统的Buffer Cache（数据缓冲区缓存），直接从数据文件中读取大量数据到PGA（程序全局区）的直接路径操作中。这个等待事件通常与大容量数据加载操作相关，例如使用DIRECT方式的INSERT、CREATE TABLE AS SELECT、MERGE操作，以及数据泵（Data Pump）的导出导入等操作。以下是关于direct path read的几个关键点：

目的：为了提高大容量数据操作的效率，避免大量的数据读写操作影响到Buffer Cache的性能，以及减少对其他常规事务处理的干扰。直接路径读取跳过了数据缓存，减少了缓存污染和缓存争用的可能性。

适用场景：当需要快速加载大量数据到表中，并且这些数据之后会被频繁查询而不是立即更新时，直接路径读是一个很好的选择。它适用于数据仓库加载、批量数据处理等场景。

资源使用：直接路径读会消耗更多的I/O资源，因为它直接从磁盘读取数据。但是，由于省去了将数据先加载到Buffer Cache再从缓存写入磁盘的过程，总体上可以减少内存和CPU的使用，尤其是在数据量非常大时。

性能影响：虽然短期内可能因为直接的磁盘I/O操作而导致更高的I/O等待，但从整体数据加载任务的角度看，它可以显著提高数据加载的效率，尤其是在I/O子系统能够高效处理大量连续读取请求的情况下。

与直接路径写（Direct Path Write）的关系：直接路径读通常与直接路径写一起出现，构成了一种高效的批量数据处理模式，数据直接从数据文件读取后，再直接写入到目标表或文件中，整个过程尽量减少对Buffer Cache的依赖。

总之，direct path read是一个优化大容量数据读取操作的机制，特别适合于数据仓库加载和批量处理作业，旨在通过减少对Buffer Cache的依赖来提高数据处理的吞吐量和效率。

7.control file sequential read

表示数据库进程在顺序读取控制文件中的信息。控制文件是Oracle数据库的核心组成部分之一，存储了数据库的物理和逻辑结构信息，包括但不限于数据文件、重做日志文件的位置和状态、表空间信息、数据库名称和版本等元数据。

当以下操作发生时，可能会观察到control file sequential read等待事件：
数据库启动：数据库实例在启动时需要读取控制文件来验证和获取数据库的结构信息。
日志切换：重做日志文件切换时，数据库需要更新控制文件中的相关信息。
数据字典统计信息更新：某些维护操作，如自动段空间管理（ASSM）的位图块更新，可能需要读取控制文件。
备份和恢复操作：执行数据库备份或恢复操作时，需要读取控制文件来确认数据库的状态和需要处理的文件列表。
表空间或数据文件操作：添加、删除、重命名表空间或数据文件时，需要访问控制文件以更新其内容。
由于控制文件通常较小且存储在高速存储设备上，因此这类等待通常不会成为性能瓶颈。然而，如果控制文件频繁地被大量并发请求访问，或者存储设备响应缓慢，也可能导致性能问题。

优化建议：
确保控制文件位于快速的存储介质上，以减少I/O等待时间。
监控控制文件的访问模式，如果发现异常的高等待，检查是否有不合理的数据库活动或配置问题。
在一些极端场景下，考虑增加控制文件的副本数量，分布于不同的磁盘，以提供更好的容错能力和I/O平衡，但这通常不是出于性能考虑，而是为了提高可用性和可靠性。

--查询阻塞和被阻塞的session
SELECT a.INST_ID,
       a.sid,
       a.SERIAL#,
       a.USERNAME,
       a.SQL_ID,
       a.PROGRAM,
       a.EVENT,
       a.BLOCKING_SESSION,
       a.WAIT_TIME_MICRO
  FROM GV$SESSION A
 WHERE (A.INST_ID, a.SID) in
       (select b.BLOCKING_INSTANCE, b.BLOCKING_SESSION from gv$session b)
union all
select a.INST_ID,
       a.sid,
       a.SERIAL#,
       a.USERNAME,
       a.SQL_ID,
       a.PROGRAM,
       a.EVENT,
       a.BLOCKING_SESSION,
       a.WAIT_TIME_MICRO
  FROM GV$SESSION A
 where a.BLOCKING_SESSION is not null
 order by BLOCKING_SESSION nulls first;

--最近30分钟内ASH采样到的等待事件排名
select *
  from (select inst_id,
               rank() over(partition by inst_id order by cnt desc) rk,
               event,
               cnt
          from (select ash.INST_ID, ash.EVENT, count(*) CNT
                  from gv$active_session_history ash
                 where ash.SAMPLE_TIME > sysdate - 1 / 24 / 60 * 30
                   and event is not null
                 group by ash.INST_ID, ash.EVENT))
 where rk <= 10;

--某段事件内ASM采样到的等待事件排名
select a.inst_id, a.event, a.sql_id, a.sql_cnt, b.cnt event_cnt, sql_rk
  from (select inst_id,
               event,
               sql_id,
               sql_cnt,
               rank() over(partition by event order by sql_cnt desc) sql_rk
          from (select ash.INST_ID, ash.EVENT, ash.SQL_ID, count(*) sql_cnt
                  from gv$active_session_history ash
                 where to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') >
                       '2024-06-20 14:00:00'
                   AND to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') <
                       '2024-06-20 16:00:00'
                   AND EVENT IS NOT NULL
                 GROUP BY ASH.INST_ID, ASH.EVENT, ASH.SQL_ID)) A,
       (SELECT INST_ID, EVENT, CNT
          FROM (SELECT INST_ID,
                       rank() over(partition by INST_ID order by cnt desc) rk,
                       EVENT,
                       CNT
                  FROM (SELECT ASH.INST_ID, ASH.EVENT, COUNT(*) CNT
                          FROM GV$ACTIVE_SESSION_HISTORY ASH
                         where to_char(ash.SAMPLE_TIME,
                                       'YYYY-MM-DD HH24:MI:SS') >
                               '2024-06-20 14:00:00'
                           AND to_char(ash.SAMPLE_TIME,
                                       'YYYY-MM-DD HH24:MI:SS') <
                               '2024-06-20 16:00:00'
                           AND EVENT IS NOT NULL
                         GROUP BY ASH.INST_ID, ASH.EVENT))
         WHERE RK <= 3) B
 WHERE A.INST_ID = B.INST_ID
   AND A.EVENT = B.EVENT
   AND A.SQL_RK <= 5
 ORDER BY INST_ID, EVENT_CNT DESC, SQL_CNT DESC, SQL_RK;


--查看过去7天sql执行的hash_plan_id和资源消耗统计变化
select sql_id,
       endtime,
       plan_hash_value,
       nvl2(exed, exed, 0) exec_avg_times_ms,
       nvl2(ems, ems, 0) exec_once_time_ms,
       nvl2(cms, cms, 0) "avg_CCWAIT(ms)",
       nvl2(ams, ams, 0) "avg_APWAIT(ms)",
       nvl2(clms, clms, 0) "avg_CMWAIT(ms)",
       nvl2(ioms, ioms, 0) "AVG_IOWAIT(ms)",
       nvl2(ctms, ctms, 0) "AVG_CPU_WAIT(ms)",
       nvl2(bfgets, bfgets, 0) "AVG_buffer gets",
       nvl2(drd, drd, 0) "avg_disk reads",
       nvl2(sd, sd, 0) "avg_sort",
       nvl2(fet, fet, 0) "avg_fetch",
       nvl2(rpd, rpd, 0) "avg_rows process",
       snap_id
  from (select a.sql_Id,
               to_char(b.end_interval_time, 'yyyymmdd hh24:mi:ss') endtime,
               a.executions_delta exed,
               a.plan_hash_value,
               round(decode(a.executions_delta,
                            0,
                            0,
                            a.elapsed_time_delta / a.executions_delta) / 1000,
                     2) ems,
               round(decode(a.executions_delta,
                            0,
                            0,
                            a.ccwait_delta / a.executions_delta) / 1000,
                     2) cms,
               round(decode(a.executions_delta,
                            0,
                            0,
                            a.apwait_delta / a.executions_delta) / 1000,
                     2) ams,
               round(decode(a.executions_delta,
                            0,
                            0,
                            a.clwait_delta / a.executions_delta) / 1000,
                     2) clms,
               round(decode(a.executions_delta,
                            0,
                            0,
                            a.iowait_delta / a.executions_delta) / 1000,
                     2) ioms,
               round(decode(a.executions_delta,
                            0,
                            0,
                            a.cpu_time_delta / a.executions_delta) / 1000,
                     2) ctms,
               round(decode(a.executions_delta,
                            0,
                            0,
                            a.buffer_gets_delta / a.executions_delta),
                     2) bfgets,
               round(decode(a.executions_delta,
                            0,
                            0,
                            a.disk_reads_delta / a.executions_delta),
                     2) drd,
               round(decode(a.executions_delta,
                            0,
                            0,
                            a.sorts_delta / a.executions_delta),
                     2) sd,
               round(decode(a.executions_delta,
                            0,
                            0,
                            a.fetches_delta / a.executions_delta),
                     2) fet,
               round(decode(a.executions_delta,
                            0,
                            0,
                            a.rows_processed_delta / a.executions_delta),
                     4) rpd,
               b.snap_id
          from dba_hist_sqlstat a, dba_hist_snapshot b
         where sql_id = 'xxxxxxxxx'
           and a.snap_id = b.snap_id
           and a.instance_number = b.instance_number
              --and b.instance_number=1
           and a.executions_delta <> 0
           and b.end_interval_time > sysdate - 7)
 order by snap_id