Oracle Wait Interface
Oracle等待事件的种类包括I/O locks latches enqueues background process activities network latencies memory等可以通过V$SYSTEM_WAIT_CLASS查询到
使用之前必须设置TIMED_STATISTICS=TRUE
OWI的关键组件
视图
V$EVENT_NAME包含定义的所有等待事件
V$SESSION_WAIT显示每个会话当前正在等待的时间和资源的详细信息这是一个实时视图
在G中v$session_wait已经合并到了v$session 视图中虽然仍包含v$session_wait
V$SESSION_EVENT显示当前连接的会话的等待事件的聚集
V$SYSTEM_EVENT显示所有会话遇到的所有等待事件的聚集
Oracle G新增的关键组件包括
V$SESTEM_WAIT_CLASS
V$SESSION_WAIT_CLASS
V$SESSION_WAIT_HISTORY
V$EVENT_HISTOGRAM
V$ACTIVE_SESSION_HISTORY
跟蹤
Trace event —扩充的SQL跟蹤
如果无法交互性的监控事件可以通过纪录等待事件到跟蹤文件进行诊断性能问题
可以在实例（Event= tace name contex forever level 永远不要这么做）/会话级别（Alter session set events trace name context forever level ）启用
运行要跟蹤的SQL
Alter session set events trace name context off
？？？以下过程在可能没有
也可以使用dbms_supportstart_trace进行跟蹤
跟蹤其他会话Exec dbms_supportstart_trace_in_session（sid=>xxx serial#=>xxx waits=>true binds=>true）
结束跟蹤stop_trace_in_session
跟蹤文件的位置在user_dump_dest 目录下
可以使用tracefile_identifer命名跟蹤文件名Alter session set tracefile_identifier= Tracefilesql
然后使用tkprof工具进行格式化输出最近Metalink上有一个新的工具TRCA更加适合分析跟蹤文件
OWI的限制
没有CPU 统计
非端到端的可见性
没有历史数据
由于当前计算机速度的飞增一些计算可能不精确
常见的等待事件
Buffer busy waitsg中更改为read by other session参数中P代表块所在的文件号P代表实际的块号P在i中代表reason for the waitg中代表v$waitstat中的CLASS列等待时间为s或cs
Control file parallel write参数中P代表块驻留的绝对文件号P代表总块数P代表I/O请求数量等待时间为完成所有I/O请求的实际延迟
Db file parallel read当一个进程从一个/多个数据文件读取多个不连续的块或数据库恢复时有些数据库块需要恢复时会发生这种事件参数P代表需要读取的文件数P读取的总块数P代表I/O请求的总数量等待时间为完成所有I/O请求的实际延迟
Db file parallel write当DBWR以批模式些藏块到数据文件时会发生参数中P代表需要写入的文件数P代表要写的总块数P（+）以百分之一秒为单位代表超时的值等待时间无超时
Db file scattered read通常在全表扫描时发生参数P代表开始读取的块的文件号P代表开始读取的块号P代表读取的块数等待时间无超时
Db file sequential read当从索引回滚段表（ROWID）数据文件头一些临时段读取时会发生该事件参数P代表开始读取的块的文件号P代表开始读取的块号P通常为但是临时段则大于等待时间无超时
Db file single write当Oracle更新数据文件头时发生该事件通常在检查点期间如果数据库有大量数据文件可能会注意到这种情况参数P代表写入的文件号P代表开始写入的块P通常为等待时间无超时
Direct path read当oracle直接读取数据库块到会话PGA而不是SGA的缓沖缓存时时发生直接路径读取通常在访问临时段时使用参数P读取的所在的绝对文件号P代表开始读取的块号P则为读取的块数等待时间无超时
Direct path write与Direct path read相反Oracle从PGA写缓沖到数据文件通常用来写到临时段直接数据加载或并行DML操作
Enqueue是一种Oracle用来串行化访问数据库资源的共享内存结构进程为了得到该enqueue锁必须等待在队列中用来串行访问资源的各种enqueue包括ST空间管理SQ序列号TX事务参数P代表正在等待的进程请求的enqueue名和模式P代表请求的锁的资源标识符IDP代表请求的锁的资源标识符ID等待时间依赖于enqueue名Oracle最多可以等待三秒或直到enqueue资源可用
Free buffer waits当会话无法在数据库缓沖缓存中找到空闲缓沖读入块或者建立一致性读时将发生该事件这会唤醒DBWR释放髒缓沖参数中P代表文件号P代表需要读入到缓存中的块号P（i未使用）G显示缓沖缓存中LRU列表的id等待时间Oracle最多等待s然后重试
Latch free当进程等待一个当前被其他进程占用的latch时会发生这种事件进程要得到latch不必等待在队列中最常见的latch包括cache buffer chains library cache和shared pool参数P代表latch的地址P代表latch号（v$latchnamelatch#）P代表尝试的次数等待时间指数增长G中latch由其自身的等待事件
Library cache pin / library cache lock当会话试图将一个对象钉在库缓存中以更改或测试它时会发生该事件必须得到一个钉确保对象不会被改变P代表库对象地址P代表加载锁的地址P为模式+名字空间（来自v$db_object_cache）等待时间PMON为S其他进程为S
Log buffer space当进程必须等待日志缓沖中的空间可用时发生未使用参数等待时间S如果必须等待日志切换则为S
Log file parallel write会话等待LGWR写日志缓沖块到重做组成员时发生参数P代表要写入的日志文件数P代表要写入的OS块数P代表I/O请求数量等待时间实际的延迟
Log file sequential read当进程等待块从在线重做日志文件读取时会发生该事件参数P代表重做日志文件的相对序列号P代表开始读取的块号P代表读取的OS块数等待时间实际的延迟
Log file switch （archiving needed）指示ARCH 跟不上LGWR
Log file switch （checkpoint incomplete）文件归档前检查点必须完成指示重做日志文件可能太小
Log file sync参数P代表需要同步的日志缓沖中的缓沖的数量等待时间S
SQL*Net message from/to client如果很大可能指示网络有问题P显示网络驱动器的类型P代表字节数P未使用等待时间实际的延迟
跟蹤CPU和其他统计V$SESSTAT / V$SYSSTAT其中包含了
– CPU used by this session
– CPU used when call started
– Recursive CPU usage
– Parse time CPU
– Session logical reads
– Physical reads
– Physical writes
等待事件根本原因分析
需要收集等待事件统计历史
Trace 会有很大的负载但是可以得到最小粒度的性能数据
Statspack不能得到会话级别的数据
使用BEFORE LOGOFF TRIGGER收集历史数据—低负载（如果会话被KILL或挂起则没有任何数据）建立表并保持大约天的数据可以归档这些数据进行长期比较将等待事件和产生该事件的SQL语句（V$SQLTEXT）保存起来
推断常用等待事件找到以及修复？
诊断IO相关的等待事件
在任何系统中I/O操作都是最慢的活动
与I/O相关的最常见的事件
– Db file scattered read
– Direct path read
– Direct path write
– Log file parallel write
– Db file parallel write
– Controlfile parallel write
– Db file sequential read
Db file sequential read
Oracle进程想要的块当前不在SGA中查看V$SESSION_EVENT的TIME_WAITED和AVERAGE_WAIT列从系统范围的事件来看时这通常是TOP
大量的等待时间通常是由于应用程序问题当前应该小于cs
如果db files sequential reads的对象是索引应用程序可能执行了大量的索引读考虑使用全表扫描是否合适？？？检查聚簇因子检查两个初始化参数（optimizer_index_cost_adj和optimizer_index_caching）的设置
如果db files sequential reads的对象是表—记住索引读后的rowid访问是顺序的检查V$SYSTEM_EVENT的average_wait其为TOP 并不指示系统有问题如果没有出现在top 中则表明其他等待有问题需要解决如果AVGERAGE_WAIT特别大—检查磁盘热点G使用ASM平衡负载
db file scattered read
Oracle会话使用db_file_multiblock_read_count从磁盘读取块到SGA多块I/O请求通常与全表扫描和索引快速全扫描相关过高的该事件值通常是应用程序的问题需要使用更多的单块读（索引扫描）代替多块读
如果全表扫描时恰当的考虑实施多块读减少等待时间如果应用程序开始高效运行突然开始产生db file scattered read waits查看是否有索引被删除或无效
可以使数据库倾向于全表扫描的优化器参数包括db_file_multiblock_read_counthash_area_sizeoptimizer_index_cost_adj通常表很久未分析也会导致该等待事件增加检查表的last_analyzed如果表有大量链接行也会产生该问题
Direct path read waits
通常是磁盘排序太多检查V$STATNAMEv$sesstat决定内存排序的参数包括sort_area_sizepga_aggregate_target目标是尽可能的减少排序使用UNION ALL代替UNION哈希连接代替排序接合嵌套循环代替哈希连接
Direct path write waits
直接写来自SORT CTAS HASH INDEX 以及运行在并行模式的sqlldr直接路径写的调整方法和直接路径读一样首先调整对直接路径读和写影响最大SQL语句
Db file parallel write
该事件属于DBWR进程该事件如果很大意味着I/O问题没有该事件的用户会话可能显示free buffer waitwrite complete wait检查系统是否支持asynch_io（HP仅在RAW上支持）如果是则使用它否则考虑使用多DBWRs
Log file parallel write
该事件属于LGWR这是一个系统范围的事件用户会话可能指示log file sync查看v$system_event的time_waited和average_wait列如果average is > cs则系统可能正在经历较低的吞吐量该事件的修复同Db file parallel write不幸的是不能使用多个LGWRs检查日志文件所在的位置是否有沖突对某些操作使用NOLOGGING
Control file parallel write
该事件通常是大量日志切换的征兆增加日志文件的大小
诊断锁相关的等待事件
锁与闩的区别是闩的唯一目的是探测排他访问内存结构闩保护内存对象锁的目的是当资源在兼容模式时允许多个进程共享资源当资源在不兼容模式时强制排它访问锁保护数据库对象一共有种模式null row share row exclusive share row share exclusive以及exclusive
Latch Free wait
查看V$SYSTEM_EVENT表的TOTAL_WAITS列闩可以通过V$LATCH监控在高并发的系统中闩沖突时很常见的应该被预计种最常见的闩等待为shared pool library cache cache buffers chains cache buffers lru chain row cache objects
共享池和库缓存闩等待通常是由于大量的硬解析在使用文本常量的应用程序中这是很频繁的可以通过使用绑定变量设置CURSER_SHARING为FORCE解决通过查看V$SQLAREAPARSE_CALLS识别SQL语句
缓存缓沖链闩等待是由于低效的SQL语句引起的当应用程序打开多个并行会话执行相同的语句查询相同的结果集时可以得到该沖突另一个原因是热块在缓存缓沖中可以查看V$SESSION_WAIT的PRAW 列检查是否有热块在缓沖缓存中可以通过EXP/IMP带热块的表增加PCTFREE的值进行扩展考虑减小具有许多热块的表的块（i+）
缓存缓沖LRU链闩等待是由于有大量的缓存缓沖活动重复扫描大量低选择性的索引或者执行全表扫描的语句通常是主要原因只能通过调整SQL
行缓存对象闩保护数据字典唯一方法是增加共享池的尺寸
enqueue wait
enqueue是应用于数据库资源的锁它们由应用程序请求初始化查询V$ENQUEUE_STAT查看各种信息
最常见的enqueue等待是TX模式号为是一种行级锁通过V$LOCK确定阻塞者另一种模式号为通常是主键强制或等待数据块中的一个ITL槽查看V$SEGMENT_STATISTICS决定ITL等待的值统计名为ITL waits通过使用较高的INITTRANS和PCTFREE重建对象修复该问题
对于唯一键强制问题会在多个用户同时向表插入相同的键时发生方法是找出为什么应用程序允许用户同时尝试插入重复的键
另一种常见的enqueue是等待ST Enqueue每个数据库只有一个ST锁方法是更改UET$和FET$请求该锁使用本地管理表空间修复使用TEMPFILE重建所有临时表空间修复如果不能从字典管理表空间更改增加所有高速增长的段的next extent分区尺寸同时允许预分配分区
另一种常见Enqueue是等待TM Enqueue模式号为未索引外键是这种锁沖突的主要原因如果应用程序显示使用LOCK TABLE语句也会发生这种锁见V$SQLAREA
Buffer busy waits
通常是由于多个会话尝试读取块到内存中或者尝试将块钉在内存中时会发生可以通过尝试降低级别或并行性或增加对象的FREELISTS/FREELIST GROUPS也可以尝试重建表使用较小的块或增加PCTFREE的值减少表中的行数
如果主要等待是段头检查NEXT分区的大小并确保PCTFREE和PCTUSED之间的gap不会太大如果主要等待是撤销段头可能是由于有太多的小的回滚段考虑使用系统管理撤销
诊断延迟相关的等待事件
Log file sync
当会话等待LGWR写出缓沖时会发生这种情况主要有三个原因高提交率—应用程序相关I/O子系统太慢—考虑RAWRAID 光纤通道过大的日志缓沖同时参数PROCESSES过大可能也会造成该事件等待增加
Log buffer space
如果由于空间不足或LGWR进程不够快会话等待拷贝重做条目到日志缓沖时会发生这种情况如果是日志缓沖太小则考虑增加它如果是I/O子系统太慢—考虑使用NOLOGGING或升级硬件
Free buffer
当DBWR正在从SGA写出藏块时会发生该等待
主要原因有编写得很差的SQLDBWRs不够较慢的I/O延迟块清除较小的缓沖缓存
延迟的块清除第一个扫描刚被加载的表的进程将会进行该工作全表扫描刚加载的表可以最小化该问题或者分析这些数据也可以解决
缓沖缓存太小通常不是问题在增加它之前考虑增加DBWR
Write complete
是前台进程等待DBWR写出块的征兆检查db file parallel writes事件的情况
Log file switch completion
当重做日志太小并且事务产生大量的重做日志条目时会发生这种事件可以通过创建更大的日志文件或可以通过创建更多的重组日志组完成
还有一些g新增的等待事件和RAC环境下的等待事件
Dumps和Traces
主要是ORA和核心ORA事件
作为一个DBA必须熟悉ORADEBUG可以通过oradebug dumplist和oradebug help得到帮助该工具没有文档
遇到块中断时需要dump数据文件和块
alter system dump datafile <file#> block <block#>
控制文件也可以被dump当跟蹤恢复相关的问题和SCN同步问题时可能会有用
alter session set events immediate trace name controlf level
或
oradebug sitmypid
oradebug ulimit
oradebug dump controlf
共享池有问题时通常需要DUMP堆
alter sessions set events immediate trace name heapdump level <level>
或
oradebug sitmypid
oradebug ulimit
oradebug dump heapdump <level>
库缓存DUMP会给出库缓存中关于对象的详细信息
alter sessions set events immediate trace name library_cache level
或
oradebug sitmypid
oradebug ulimit
oradebug dump library_cache
当诊断内存中断或死锁错误时可以使用DUMP进程状态
alter sessions set events immediate trace name processstate level <level>
oradebug sitmypid
oradebug ulimit
oradebug dump processstate<level>
当诊断数据库挂起条件时可以DUMP系统状态
alter sessions set events immediate trace name systemstate level <level>
或
oradebug sitmypid
oradebug ulimit
oradebug dump systemstate <level>
