做网站的技术难点,伪装的福祉 wordpress,dooor网站,网站建设项目需求分析流程图1.db file scattered read
含义#xff1a;当一个SQL语句需要从数据文件中读取多块非连续的数据块时#xff0c;就会发生db file scattered read等待事件。这意味着数据分散在不同的位置#xff0c;数据库需要进行多次I/O操作来收集所需的信息。场景#xff1a;这种事件常…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报告或ASHActive 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_MICROFROM GV$SESSION AWHERE (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_MICROFROM GV$SESSION Awhere a.BLOCKING_SESSION is not nullorder by BLOCKING_SESSION nulls first;--最近30分钟内ASH采样到的等待事件排名
select *from (select inst_id,rank() over(partition by inst_id order by cnt desc) rk,event,cntfrom (select ash.INST_ID, ash.EVENT, count(*) CNTfrom gv$active_session_history ashwhere ash.SAMPLE_TIME sysdate - 1 / 24 / 60 * 30and event is not nullgroup 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_rkfrom (select inst_id,event,sql_id,sql_cnt,rank() over(partition by event order by sql_cnt desc) sql_rkfrom (select ash.INST_ID, ash.EVENT, ash.SQL_ID, count(*) sql_cntfrom gv$active_session_history ashwhere to_char(ash.SAMPLE_TIME, YYYY-MM-DD HH24:MI:SS) 2024-06-20 14:00:00AND to_char(ash.SAMPLE_TIME, YYYY-MM-DD HH24:MI:SS) 2024-06-20 16:00:00AND EVENT IS NOT NULLGROUP BY ASH.INST_ID, ASH.EVENT, ASH.SQL_ID)) A,(SELECT INST_ID, EVENT, CNTFROM (SELECT INST_ID,rank() over(partition by INST_ID order by cnt desc) rk,EVENT,CNTFROM (SELECT ASH.INST_ID, ASH.EVENT, COUNT(*) CNTFROM GV$ACTIVE_SESSION_HISTORY ASHwhere to_char(ash.SAMPLE_TIME,YYYY-MM-DD HH24:MI:SS) 2024-06-20 14:00:00AND to_char(ash.SAMPLE_TIME,YYYY-MM-DD HH24:MI:SS) 2024-06-20 16:00:00AND EVENT IS NOT NULLGROUP BY ASH.INST_ID, ASH.EVENT))WHERE RK 3) BWHERE A.INST_ID B.INST_IDAND A.EVENT B.EVENTAND A.SQL_RK 5ORDER 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_idfrom (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_idfrom dba_hist_sqlstat a, dba_hist_snapshot bwhere sql_id xxxxxxxxxand a.snap_id b.snap_idand a.instance_number b.instance_number--and b.instance_number1and a.executions_delta 0and b.end_interval_time sysdate - 7)order by snap_id
