ORACLE性能AWR报告的使用和分析

ORACLE 性能诊断 AWR 报告的使用和分析 为满足业务的运行要求 高性能要求是目前 IT 系统普遍面临的最棘手问题 尤其是客户面对着 目前越来越庞大系统和数据 系统整合 数据大集中似乎成了趋势 针对系统性能优化的诊断和分 析 数据库方向又是其中的重要一环 本文将针对 ORACLE 中常用的性能诊断工具 AWR 报告 进 行分析说明 一 一 ORACLEORACLE 性能诊断工具性能诊断工具 ORACLE 数据库的性能的诊断工具有很多种 在 9i 之前主要通过手工进行采集分析 例如使用 动态视图和 Statspack 报告来获取数据库性能状态信息 10g 以后 ORACLE 数据库的性能诊断和改 进建议越来越自动化 不过能够熟悉并掌握 ORACLE 的相关性能诊断工具的使用 仍对性能问题的 准确和有效处理提供有利的帮助 以下是 ORACLE 中常用的一些分析工具 动态性能视图 动态性能视图是 ORACLE 中最常用 也是最简单的一种工具 无论何种性能问题 都能在 动态性能视图中找到线索 不过仅 10g 中动态性能视图就高达几百个 每个视图都包括很多诊 断信息 想在众多的视图中找到问题的根源 也是一件费力的事情 一般常用的动态性能视图 有 v session v session wait v process v sql v lock v latch v sysstat v system event v sgastat Statspack 报告 statspack 是 Oracle 9i 之前使用的一个数据库收集工具 收集了数据库全面信息 包括 负载概览 前五个等待事件 高速缓存的大小 共享池中 SQL 语句 表空间和文件 I O 库高 速缓存 SGA 统计等 AWR 和 ADDM 报告 AWR 是 10g 以后提供的一个新工具 Oracle 建议用户用这个取代 Statspack 它采集与 性能相关的统计数据 并从那些统计数据中导出性能量度 以跟踪潜在的问题 并自动生成 ADDM 自动数据库诊断监控 报告 为用户提供数据库性能诊断分析建议 SQL 执行计划和建议 数据库中 SQL 的执行效率可能是对系统影响最大的一个因素 利用 ORACLE 执行计划的分 析 可以准确知道 SQL 执行的代价 并提供多个方面的调整建议 来进行 SQL 代码的优化分析 二 二 生成生成 AWRAWR 报告报告 以下 本文将针对 oracle10g 后提供的常用性能分析报告 AWR 依此来描述和分析数据库的 性能点和优化建议 AWR 由 ORACLE 自动产生 默认 30 分钟采集一次 保留 5 天的记录 但是也可以通过 DBMS WORKLOAD REPOSITORY 包来手工创建 删除和修改 使用脚本 awrrpt sql 或 awrrpti sql 来查看 AWR 报告 这两个脚本都在目录 ORACLE HOME rdbms admin 中 报告可 以保存为文本文件或 HTML 文件 生成 AWR 报告的步骤如下 sqlplus sys sys 127 0 0 1 scmis as sysdba SQL c oracle product 10 2 4 db 1 RDBMS ADMIN awrrpt sql 输入 report type 的值 html 注 确定报告的格式 输入 num days 的值 1 注 选择快照的天数 输入 begin snap 的值 425 注 起始快照 输入 end snap 的值 427 注 结束快照 输入 report name 的值 d scmis awr 2011 10 29 html 注 报告生成的名称和位置 三 三 AWRAWR 报告分析报告分析 AWR 报告头记录了数据库名称和起始快照时间 报告头中主要分析 Elapsed 总时间 和 DB Time DB 消耗的时间 不包括后台进行的消耗时间 如果 DB Time Elapsed 比值较大 说明 数据库系统压力较大 例如下图中系统包括 16CPU 2 8 核 每个 cpu 耗时 26 7min 负载为 26 7 60 03 44 5 说明数据库服务器存在较大的负荷 即 427 44 60 3 16 100 44 5 1 sessions 表示采集是实例连接的会话数 这个数可以让我们了解数据库并发用户的大概情况 如果是新接手的数据库 对 判断数据库的类型可以做参考 2 Cursors Session 平均每个会话卡开的游标数 3 DB Time 4 这个数值比较重要 它表示用户操作花费的时间 包括 cpu 和等待事件 有时候 DB Time 会比 Elapsed 时间要长 因为 AWR 是一个数据的合集 比如说 1 分钟内一个用户等待 10 秒钟 那么 10 个用户是 300 秒 5 分钟 cpu 的时间也是一样一分钟之内 一个 cpu 处理 30 秒 那么 4 个 cpu 就是 1 2 分钟 8 个就是 2 4 分钟 这些都以累计的方式记录在 awr 报告当中的 AWR 报告总览包括了五个部分 缓存尺寸 Cache Sizes 负载性能 Load Profile 数 据库效率 Instance Efficiency Percentages 共享池统计 Shared Pool Statistics TOP5 事件 Top 5 Timed Events 这五个部分也就是整个报告核心 记录了数据库系统的关键性能参 数和状况 1 缓存尺寸 缓存尺寸 Cache Sizes 主要记录总的缓存大小 Buffer Cache 和 SGA 缓存尺寸 Shared Pool Size SGA 是 ORACLE 中非常重要的内存共享区 对系统内的所有进程都是共享的 当多个用户同时连接到一个例程时 所有的用户进程 服务进程都可以共享使用这个 SGA 区 Shared pool 可以分为库缓存 library cache 和数据字典缓存 dictionary cache Library cache 存放了最近执行的 SQL 语句 存储 过程 函数 解析树以及执行计划等 而 dictionary cache 则存放了在执行 SQL 语句过程中 所 参照的数据字典的信息 包括 SQL 语句所涉及的表名 表的列 权限信息等 2 负载性能 负载性能 Load Profile 这个部分记录了数据库负载情况 绝对值的分析意义不大 需要与之前的基线数据比较才具有 更多的意义 单个的报告数据只说明应用的负载情况 绝大多数据并没有一个所谓 正确 的值 其中重要的几个对于 Logons 大于每秒 1 2 个 表明可能有争用问题 对于 Hard parses 大于每 秒 100 parses 大于每秒 300 表明硬解析太多 SQL 重用率不高 需要解决 SQL 代码变量绑定 问题 并调整共享池参数 调整 cursor sharing 参数 对于 Sorts 大于每秒 100 表明排序过多 需要减少 SQL 代码中排序操作 或调整排序空间 Logons Logons show how many users are logged onto the database per second 这个表里应该注重 1 logical reads 和 physical reads 同时也可以得到平均每个逻辑读导致多少物理读 即 19 1 37410 4 0 05 平均 每个事务产生了 9040 68 个逻辑读 这个数字应该越小越好 2 parses 和 hard parses 从表中可以看到 cpu 平均每秒进行了 81 24 个解析 超过 100 个应该注意 每秒进行 5 39 超过 10 个应该注意 次硬解析 即 cpu 每秒要处理 5 39 个全新的 sql 3 数据库效率 数据库效率 Instance Efficiency Percentages 记录了 Oracle 关键指标的内存命中率及数据库实例其它操作的效率 这个部分反应了数据库中 最重要指标的命中率 缓冲区未等待率 buffer nowait 指在缓冲区中获取 buffer 的未等待比率 该指标的值应接近 100 如果该值较低 则可能要增大 buffer cache 不应该低 于 99 redo 缓冲区未等待率 redo nowait 指在 redo 缓冲区获取 buffer 的未等待比率 该指标的值应接近 100 如果该值较低 则有 2 种可能的情况 1 online redo log 没有足够的空间 2 log 切换速度较慢 缓冲区命中率 buffer hit 指数据块在数据缓冲区中的命中率 该指标的值通常应在 90 以上 不应该低于 99 否则 需要调整 如果持续 小于 90 可能要加大 db cache size 但有时 缓存命中率低并不意味着 cache 设置小了 可能是潜在的全表扫描降低了缓存命中率 内存排序率 in memory sort 指排序操作在内存中进行的比率 该指标的值应接近 100 如果指标的值较低 则表示出现了大量排序时的磁盘 i o 操作 可考虑加大 sort area size 参数的值 共享区命中率 library hit 该指标主要代表 sql 在共享区的命中率 该指标的值通常 应在 95 以上 否则需要考虑加大共享池 修改 shared pool size 参数值 绑定变 量 修改 cursor sharing 等参数 软解析的百分比 soft parse 该指标是指 oracle 对 sql 的解析过程中 软解析所占 的百分比 该指标的值通常应在 95 以上 如果低于 80 那么就可能 sql 基本没被重用 sql 没有绑定变量 需要考虑绑定变量 闩锁命中率 latch hit 指获得 latch 的次数与请求 latch 的次数的比率 该指标的值应接近 100 如果低于 99 需要分析闩锁竞争 明确是应用锁 数 据字典锁 内存控制锁的哪一种 通过进一步分析 Latch Statistics 章节或动态性能 视图 v session wait v latch v latch children sql 语句执行与解析的比率 execute to parse 指 sql 语句执行与解析的比率 该指 标的值应尽可能到高 如果过低 可以考虑设置 session cached cursors 参数 Non Parse CPU 说明花费在十几工作的时间和花费在解析上的时间的对比 execute to parse 说明 sql 语句执行与解析的比率 4 共享池统计 共享池统计 Shared Pool Statistics 记录了在采集点时刻 共享池 share pool 内存被使用的比例 这个指标的值应保持在 75 90 如果这个值太低 就浪费内存 如果太高 会使共享池外部的组件老化 如果 sql 语句 被再次执行 则就会发生硬分析 其中执行次数大于 1 的 sql 比率 SQL with executions 1 如果此值太小 说明需要在应用中更多使用绑定变量 避免过多 SQL 解析 Memory Usage 说明在 shared pool 中 被使用的部分占 shared pool 总尺寸的百 分比 这个值应保持适中 如 85 如果太高 则会引起 shared pool 中的对象被刷 出内存 从而导致 sql 语句的硬解析增加 太低则浪费内存 SQL with executions 1 执行次数大于 1 次的 sql 占总 sql 数的百分比 越大越好 Memory for SQL w exec 1 在 shared pool 中执行次数大于 1 次的 sql 语句所消耗 的内存占 shared pool 的百分比 5 TOP5 事件 事件 Top 5 Timed Events 这个部分也是 AWR 报告中非常重要的部分 从这里可以看出等待时间在前五位的是什么事件 基本上就可以判断出性能瓶颈在什么地方 通常 在没有问题的数据库中 CPU time 总是列在第 一个 其他几类重要影响性能的事件分析如下 缓冲区忙 buffer busy 当一个会话想要访问缓存中的某个块 而这个块正在被其它会 话使用时 将会产生该等待事件 这时候 其它会话可能正在从数据文件向缓存中的这 个块写入信息 或正在对这个块进行修改 出现这个等待事件的频度不应大于 1 如 果这个等待事件比较显著 则需要根据等待事件发生在缓存中的哪一块 如字段头部 回退段头部块 回退段非头部块 数据块 索引块等 采取相应的优化方法 文件分散读取 db file scattered read 该等待事件通常与全表扫描有关 因为全表扫 描是被放入内存中进行的进行的 通常情况下它不可能被放入连续的缓冲区中 所以就 散布在缓冲区的缓存中 如果这个等待事件比较显著 可能说明对于某些全表扫描的表 没有创建索引或没有创建合适的索引 尽管在特定条件下执行全表扫描可能比索引扫描 更有效 但如果出现这种等待时 最好检查一下这些全表扫描是否必要 文件顺序读取 db file sequential read 该等待事件通常与单个数据块相关的读取操作 有关 如果这个等待事件比较显著 可能表示在多表连接中 表的连接顺序存在问题 或者可能不合适地使用了索引 对于大量事务处理 调整良好的系统 这一数值大多是 很正常的 但在某些情况下 它可能暗示着系统中存在问题 应检查索引扫描 以保证 每个扫描都是必要的 并检查多表连接的连接顺序 另外 db cache size 也是这些等 待出现频率的决定因素 队列 enqueue 队列是一种保护共享资源的锁定机制 该锁定机制保护共享资源 如 记录中的数据 以避免两个人在同一时间更新同一数据 如果 enqueue 等待事件比较 显著 则需要根据 enqueue 等待类型 采取相应的优化方法 闩锁释放 latch free latch 是一种低级排队机制 它们被准确地称为相互排斥机制 用 于保护系统全局区域 sga 中共享内存结构 该等待事件意味着进程正在等待其他进程已 持有的 latch 对于常见的 latch 等待通常的解决方法 1 share pool latch 在 oltp 应用中应该更多的使用绑定变量以减少该 latch 的等待 2 library cache latch 同样 的需要通过优化 sql 语句使用绑定变量减少该 latch 的等待 日志文件同步 log file sync 这个等待事件是指当一个会话完成一个事务 提交或者回 滚数据 时 必须等待 lgwr 进程将会话的 redo 信息从日志缓冲区写到日志文件后 才 能继续执行下去 这个等待事件的时间过长 可能是因为 commit 太频繁或者 lgwr 进 程一次写日志的时间太长 可能是因为一次 log io size 太大 可调整 log io size wait for a undo record 数据库恢复 read by other session READ BY OTHERS SESSIONS 的根本原因就是因为你某条 SQL 做了大量 block 的扫描 我 猜想那条 SQL 至少要 50 万个逻辑读 除了解决 SQL 问题 基本没有别的办法 6 Time Model Statistics Statistic NameTime s of DB Time sql execute elapsed time85 698 4999 38 DB CPU26 832 0231 12 parse time elapsed369 050 43 hard parse elapsed time324 190 38 failed parse elapsed time109 550 13 sequence load elapsed time62 490 07 PL SQL execution elapsed time17 780 02 hard parse sharing criteria elapsed time7 540 01 PL SQL compilation elapsed time1 420 00 connection management call elapsed time0 490 00 hard parse bind mismatch elapsed time0 130 00 repeated bind elapsed time0 120 00 DB time86 229 43 background elapsed time1 211 05 background cpu time46 42 Sql execute elapsed time 数据库执行 SQL 总时间 parse time elapsed 解释 SQL 总时间 hard parse elapsed time 硬解释 SQL 的总时间 PL SQL execution elapsed time pl sql 执行时间 DB CPU 用户占用 CPU 的总时间 failed parse elapsed time 遇到 SQL 解释时间 7 SQL 统计 统计 SQL Statistics AWR 报告中还有一块对性能影响最大的指标 TOP SQL 统计 本节按各种资源分别列出对资 源消耗最严重的 SQL 语句 并显示它们所占统计期内全部资源的比例 提供给我们调优依据 SQL ordered by Elapsed Time 记录了执行总和时间的 SQL 记录的是监控范围 内该 SQL 的执行时间总和 需要综合分析 CPU 时间 CPU Time 和执行次数 Executions 才能得到单个 SQL 的代价 单次执行开销较大的 SQL 属于重点优化之 列 SQL ordered by CPU Time 记录了执行占 CPU 时间总和时间最长的 SQL 再 CPU 消耗较大的系统中 重点优化此类 SQL SQL ordered by Gets 记录了执行占总 buffer gets 逻辑 IO 的 SQL 查找总的缓 冲区获取比较高的 SQL 并根据平均每次执行缓冲区获取的数量判断优化的余地有多大 优化这些 SQL 有助于减少 CPU 开销以及数据缓冲池相关的闩锁竞争 SQL ordered by Reads 记录了执行占总磁盘物理读 物理 IO 的 SQL 查找总的物理 读比较高的 SQL 并根据平均每次执行物理读的数量判断优化的余地有多大 优化这些 SQL 有助于减少 I O 开销和 CPU 开销 SQL ordered by Executions 记录了按照 SQL 的执行次数排序的 SQL 执行次数多 的 SQL 也是需要重点优化 使 sql 语句中的子操作执行次数尽量少 SQL ordered by Parse Calls 记录了解析次数排序的 SQL 避免出现硬解析 采用 使用绑定变量等方式 SQL ordered by Sharable Memory 记录了 SQL 占用 library cache 的大小的 SQL SQL ordered by Version Count 记录了 SQL 的打开子游标的 SQL SQL ordered by Cluster Wait Time 记录了集群的等待时间的 SQL 8 IO Stats Tablespace IO Stats TablespaceReadsAv Reads sAv Rd ms Av Blks RdWritesAv Writes sBuffer WaitsAv Buf Wt ms SYSAUX9 55304 071 6519 729000 00 UNDOTBS7 87903 211 008 2520205 50 SYSTEM2 49604 741 624 469000 00 USERS36403 081 574000 00 TEMP3403 2412 3525000 00 TEST24047 501 004000 00 1 可以找到具有频繁读写活动的表空间或数据文件 如果临时表空间的写入数量最高 说明排序太 多太大 2 从 AVG BLKS RD 列可以看出 哪些表空间上经历了最多的全表扫描 如果值大于 1 则应该将 该值与初始化参数 db file multiblock read count 的值进行比较 如果他们越接近 说明在该表空间 上进行的大部分是全表扫描 3 检查 AV RD MS 该列表明 I O 读的时间 值应该小于 20ms 如果过大应该检查是否将读写很频 繁的文件放在了同一个磁盘上 9 Segment Statistics 1 Segments by Logical Reads 或 Segments by Physical Reads 可以找到逻辑读或物理读比较大的对象 并查找原因 是否可以通过创建新索引 或采用分区表等来降低对象的逻辑读以及物理读 2 Segments by Row Lock Waits 通过这个报表可以找到获得行级锁最严重的对象 需要跟开发人 员探讨解决方法 3 Segments by ITL Waits 这个报表可以标明获得 ITL 等待最严重的对象 如果发现了 ITL 等待很 严重的对象 则应该将对象的 initrans 参数设置为并发操作该对象的进程个数 4 Segments by Buffer Busy Waits 获得 buffer busy waits 最严重的对象 在同一时刻只有一个进程能 够访问同一个数据块 其它进程必须等待 解决的关键是优化那些扫描了过多数据块的 sql 语句 减 少他们要扫描的数据块 如果已经优化了 sql 语句 则可以考虑增大 pctfree 的值 从而减少一个数 据块中能够包含的数据行数 从而将对象的数据行分部到更多的数据块里去 10 Instance Activity Statistics 实例活动统计数据实例活动统计数据 1 比较在内存中和磁盘中的排序量 如果磁盘排序太高就需要增加 PGA AGGREGATE TARGET 或者旧版本中增 大 SORT AREA SIZE 2 如果磁盘的读操作较高 表明可能执行了全表扫描 如果目前存在大量的较大的对较大表的全表扫描 就应当评估最常用的查询并通过增加索引来提高效率 大量的非一致性读操作意味着使用了过多的索引 或者使用了非选择性索引 3 如果脏读缓冲区数量高于所请求的空闲缓冲区的数量 超过 5 那么说明 DB CACHE SIZE 太小 或者 没有建立足够多的检查点 如果叶节点的分裂数量很高可以考虑重建已增长或已经碎化的索引 4 consistent gets 没有使用 select for update 子句的查询在缓冲中访问的数据块数量 这个数量加上 DB BLOCK GETS 统计信息的值就是逻辑读操作总数 5 DB BLOCK GETS 使用了 INSERT UPDATE DELETE OR SELECT FOR UPDATE 语句在缓存中访问的数据块数量 6 PHYSICAL READS 没有从缓存中度取得数据量 可以从磁盘 操作系统缓存或者磁盘缓存中读取 以满足 SELECT SELECT FOR UPDATE INSERT UPDATE DELETE 语句 7 LOGICAL READS CONSISTENT GETS DB BLOCK GETS 8 缓存命中率 HIT RATIO LOGICAL READS PHYSICAL READS LOGICAL READS 100 9 CONSISTENT GETS DB BLOCK GETS PHYSICAL READS CONSISTENT GETS DB BLOCK GETS 100 缓存命中率应该高于 95 否则需要增加 DB CACHE SIZE 10 DIRTY BUFFERS INSPECTED 从 LRU 列表中清除掉的脏读 经过修改的 数据缓冲区的数量 如果此值 超过 0 可以考虑增加 DB WR 进程 11 ENQUEUE TIMEOUTS 请求入列的次数 锁定 以及所请求的特定队列不可用的次数 如果这个统计信息 大于 0 就需要调查锁定问题 12 FREE BUFFER INSPECTED 由于是脏读数据 被固定或者正忙等原因儿跳过的缓冲区数量 如果数量很大 的话就说明缓冲区缓存太小了 13