Oracle数据库性能监控点.doc

CSR 性能测试实施方案 北京开发中心技术测试部 1 数据库性能监控关键点数据库性能监控关键点 V1 0V1 0 CSR 性能测试实施方案 北京开发中心技术测试部 2 修订状况修订状况 章节编号章节名称修订内容简述修订日期 修订前 版本号 修改人 CSR 性能测试实施方案 北京开发中心技术测试部 3 目录目录 数据库性能监控关键点数据库性能监控关键点 1 1 引言引言 5 2 监控工具监控工具 5 3 监控指标监控指标 6 3 1 调优需要遵循的基本概念 6 3 1 1 外部调整 6 3 1 2 Row re sequencing 以减少磁盘 I O 7 3 1 3 Oracle SQL 调整 8 3 1 4 调整 Oracle 排序 9 3 1 5 调整 Oracle 的竞争 10 3 2 需要关注的一些指标 10 3 2 1 参数文件的使用 pfile spfile 10 3 2 2 非默认参数的设置 10 3 2 3 控制文件及其状态 10 3 2 4 表空间及数据文件 11 3 2 5 重做日志文件信息 11 3 2 6 共享池命中率 12 3 2 7 DB buffer cache 命中率 12 3 2 8 磁盘排序 13 3 2 9 不使用临时文件的临时表空间 13 3 2 10 过多索引的非系统表 14 3 2 11 使用 system 表空间的用户 14 3 2 12 出现大量行链接 行迁移的表 15 3 2 13 没有分析或很久以前分析的表 15 3 2 14 死锁 16 3 2 15 首要的 5 个等待事件 Top 5 wait events 16 3 2 16 TOP SQL 监控 17 3 2 16 1 TOP GETS SQL 17 3 2 16 2 TOP READS SQL 17 3 2 16 3 TOP EXCUTIONS SQL 17 4 监控范围监控范围 18 4 1 监控对象 18 4 2 监控范围 18 4 3 不监控范围 18 5 监控方法及诊断步骤监控方法及诊断步骤 18 5 1 监控方法 18 5 2 诊断步骤 19 5 2 1 确定系统瓶颈 19 5 2 2 根据瓶颈所在进行相应的诊断分析 19 CSR 性能测试实施方案 北京开发中心技术测试部 4 5 2 3 基于等待事件的数据库监控诊断分析 20 5 2 4 常见的等待事件 21 6 安装步骤安装步骤 23 6 1 STATSPACK 工具 23 6 2 AWR 工具 24 6 3 SPOTLIGHT 工具 略 25 6 4 RDA 工具 25 CSR 性能测试实施方案 北京开发中心技术测试部 5 1 引言引言 本文档描述 Oracle 数据库性能的监控点 该文档的目的主要有 介绍监控工具 介绍监控内容 确定监控方法及诊断步骤 确定安装步骤 2 监控工具监控工具 为了避免因大量使用第三方工具进行监控 影响被测系统的各项指 标 应使用以下工具 STATSPACK 工具 STATSPACK 是 oracle 提供的用于诊断并优化系统 性能的工具 可以通过查看数据库的历史趋势和性能模式前瞻性地调整 数据库 是通过获取数据库当前状态的快照来进行工作 Automatic Workload Repository AWR 是是一个 Oracle10g 版本开 始提供的内置工具 是 STATSPACK 的替代工具 它采集与性能相关的统 计数据 快照由一个称为 MMON 的后台进程及其从进程自动地每小时采 集一次 为了节省空间 采集的数据在 7 天后自动清除 快照频率和 保留时间都可以由用户修改 SPOTLIGHT 工具 SPOTLIGHT 是 QUEST 公司出品的用于性能监控的 工具 可以非常形象和直观地对 Oracle 数据库的 CPU 内存 I O Data Buffer Size Shared Pool Size Redo Buffer 等参数进行 即时监控 并自动对不正常的参数以红色显示 但是此工具占用大量的 系统资源 不提倡使用 CSR 性能测试实施方案 北京开发中心技术测试部 6 RDA 工具 RDA 是 Remote Diagnostic Agent 的简称 是 oracle 用 来收集 分析数据库的工具 运行该工具不会改变系统的任何参数 RDA 收集的相关数据非常全面 可以简化我们日常监控 分析数据库的 工作 通过 SPOTLIGHT 工具实时对数据库的监控 STATSPACK AWR 工具 通过快照的形式获取的数据库系统信息 辅以 RDA 工具收集比较全面的 系统的相关数据 实现对数据库监控及分析诊断 3 监控指标监控指标 3 13 1 调优需要遵循的基本概念调优需要遵循的基本概念 Oracle 调优是一个复杂的主题 大多数调整工作就是在一定程度 上的折中 这是因为每个 Oracle Server 都受到 3 个关键资源的可利用 性所约束 CPU 磁盘 I O 以及内存 为了改善 Oracle 数据库的性能 有一些基本的概念是应该遵从的 3 1 13 1 1 外部调整外部调整 Oracle 并不是单独运行的 Oracle 数据库的性能和外部的环境有 很大的关系 这些外部的条件包括有 CPU CPU 资源的不足令查询变慢 当查询超过了 Oracle 服务 器的 CPU 性能时 数据库性能就受到 CPU 的限制 如果服务器的 CPU 已经超负荷运行 调整 Oracle 的内存和 I O 操作将只提供极小的好处 但是 即使在高档的多 CPU 服务器上 修改内存或设备配置也将对 CPU 利用率产生影响 磁盘 I O 内存中发生的 Oracle Server 活动越多 物理 I O 操 作次数将越低 但是通过超大化 Oracle 的内存结构来给可用内存施加 CSR 性能测试实施方案 北京开发中心技术测试部 7 太多的要求会导致操作系统页面调度和交换形式中发生额外的 I O 操作 内存 计算机内存对 Oracle 内存结构的可利用性是良好性能的 关键 管理好计算机内存非常重要 只有这样才能最大限度地发挥它的 作用 而又不浪费提供其他服务器进程更充分地使用的任何内存 在检查 Oracle 的外部环境时 有两个方面是需要注意的 1 当运行队列的数目超过服务器的 CPU 数量时 服务器的性能就 会受到 CPU 的限制 补救的方法是为服务器增加额外的 CPU 或者关闭 需要很多处理资源的组件 例如 Oracle Parallel Query 2 当内存分页时 内存容量已经不足 而内存页是与磁盘上的交 换区进行交互的 补救的方法是增加更多的内存 减少 Oracle SGA 的 大小 或者关闭 Oracle 的多线程服务器 可以使用各种标准的服务器工具来得到服务器的统计数据 例如 vmstat glance top 或 sar DBA 的目标是确保数据库服务器拥有足 够的 CPU 和内存资源来处理 Oracle 的请求 3 1 23 1 2 RowRow re sequencingre sequencing 以减少磁盘以减少磁盘 I OI O Oracle 调优最重要的目标是减少 I O I O 是响应时间的最大组 成部分 其中磁盘 I O 尤为重要 因为当 Oracle 由磁盘上的一个数 据文件得到一个数据块时 读的进程就必须等待物理 I O 操作完成 磁盘操作要比数据缓冲慢 10 000 倍 因此 如果可以令 I O 最小化 或者减少由于磁盘上的文件竞争而带来的瓶颈 就可以大大地改善 Oracle 数据库的性能 如果系统响应很慢 通过减少磁盘 I O 就可以有一个很快的改善 如果在一个事务中通过按一定的范围搜索 primary key 索引来访问表 那么重新以 CTAS 的方法组织表将是减少 I O 的首要策略 通过在物 CSR 性能测试实施方案 北京开发中心技术测试部 8 理上将行排序为和 primary key 索引一样的顺序 就可以加快获得数 据的速度 就象磁盘的负载平衡一样 行的重新排序也是很简单的 而且也很 快 通过与其它的 DBA 管理技巧一起使用 就可以在高 I O 的系统中 大大地减少响应的时间 在高容量的在线事务处理环境中 online transaction processing OLTP 数据是由一个 primary 索引得到的 重新排 序表格的行就可以令连续块的顺序和它们的 primary 索引一样 这样 就可以在索引驱动的表格查询中 减少物理 I O 并且改善响应时间 这个技巧仅在应用选择多行的时候有用 或者在使用索引范围搜索和应 用发出多个查询来得到连续的 key 时有效 对于随机的唯一 primary key 主键 的访问将不会由行重新排序中得到好处 3 1 33 1 3 OracleOracle SQLSQL 调整调整 Oracle SQL 调整是 Oracle 调整中最重要的领域之一 只要通过 一些简单的 SQL 调优规则就可以大幅度地提升 SQL 语句的性能 SQL 调优的目标是简单的 消除不必要的大表全表扫描 不必要的全表扫描导致大量不必要 的 I O 从而降低整个数据库的性能 可以根据查询返回的行数目来 评价 SQL 在一个有序的表中 如果查询返回少于 40 的行 或者在 一个无序的表中 返回少于 7 的行 那么这个查询都可以调整为使用 一个索引来代替全表搜索 对于不必要的全表扫描来说 最常见的调优 方法是增加索引 可以在表中加入标准的 B 树索引 也可以加入 bitmap 和基于函数的索引 要决定是否消除一个全表扫描 可以仔细 检查索引扫描的 I O 开销和全表扫描的开销 它们的开销和数据块的 读取和可能的并行执行有关 并将两者作对比 在某些特殊情况下 一 CSR 性能测试实施方案 北京开发中心技术测试部 9 些不必要的全表扫描的消除可以通过强制使用一个 index 来达到 只 需要在 SQL 语句中加入一个索引的提示就可以了 在全表扫描是一个最快的访问方法时 将小表的全表扫描放到缓 存中 应该确保有一个专门的数据缓冲用作行缓冲 小表可以被强制为 放到 KEEP 池中缓冲 确保最优的索引使用 对于改善查询的速度 这是特别重要的 有时 Oracle 可以选择多个索引来进行查询 必须检查每个索引并且确 保 Oracle 使用正确的索引 它还包括 bitmap 和基于函数的索引的使 用 确保最优的 JOIN 操作 有些查询使用 NESTED LOOP join 快一 些 有些则是 HASH join 快一些 另外一些则是 SORT MERGE join 更 快 需要根据具体的情况 确定正确的操作 3 1 43 1 4 调整调整 OracleOracle 排序排序 排序对于 Oracle 性能也是有很大影响的 排序是 SQL 语法中一 个小的方面 但很重要 在 Oracle 的调整中 它常常被忽略 当使用 create index ORDER BY 或者 GROUP BY 的语句时 Oracle 数据 库将会自动执行排序的操作 通常 在使用 order by 或 group by 的 sql 语句时 Oracle 会进行排序的操作 当在 Oracle 分配的内存空间 内无法完成排序操作时 就会产生磁盘排序 磁盘排序的开销是很大的 有几个方面的原因 首先 和内存排序相比较 它们特别慢 而且磁盘 排序会消耗临时表空间中的资源 Oracle 还必须分配缓冲池块来保持 临时表空间中的块 无论什么时候 内存排序都比磁盘排序好 磁盘排 序将会令任务变慢 并且会影响 Oracle 实例的当前任务的执行 其次 过多的磁盘排序将会令 free buffer waits 的值变高 从而令其它任 务的数据块由缓冲中移走 CSR 性能测试实施方案 北京开发中心技术测试部 10 3 1 53 1 5 调整调整 OracleOracle 的竞争的竞争 表和索引的参数设置对于 UPDATE 和 INSERT 的性能有很大的影响 3 23 2 需要关注的一些指标需要关注的一些指标 3 2 13 2 1 参数文件的使用 参数文件的使用 Pfile SpfilePfile Spfile 在 Oracle 9i 以前 Oracle 使用 Pfile 存储初始化参数设置 这些参数在实例启动时被读取 任何修改需要重起实例才能生效 使 用 Spfile 你可以使用 ALTER SYSTEM 或者 ALTER SESSION 来动态修改 那些可动态修改的参数 所有更改可以立即生效 你可以选择使更改只 应用于当前实例还是同时应用到 Spfile 这就使得所有对 Spfile 的 修改都可以在命令行完成 并且大大减少了人为错误的发生 3 2 23 2 2 非默认参数的设置非默认参数的设置 Oracle 数据库在安装的时候 会根据用户选择的数据库类型设置初 始化参数 这些参数中的一部分是需要根据具体的业务进行调整的 但 是就是这部分参数也是最容易引起数据库性能问题的 如果设置不当 将会对数据库的性能产生不良的影响 3 2 33 2 3 控制文件及其状态控制文件及其状态 每一个 Oracle 数据库都有一个控制文件 控制文件是一个小型的 二进制文件 可以记录数据库的物理结构 包含以下的内容 数据库名 称 相关数据文件和联机重做日志文件的名称和位置 数据库创建的时 CSR 性能测试实施方案 北京开发中心技术测试部 11 标 当前日志的序号 检验点信息 由此可知控制文件的重要性 为了 安全 防止控制文件的丢失 Oracle 要求复用控制文件 监控控制文件及其状态 能够准确把握数据库当前的状态 3 2 43 2 4 表空间及数据文件表空间及数据文件 表空间及数据文件的分布直接影响系统中硬盘的 I O 是否均衡 监 控表空间及数据文件的分布 能够得到影响 I O 均衡的具体因素 以便 以后进行调整 监控表空间及数据文件 可以把握当前的数据文件的使用情况 结 合数据量的增长幅度 及时的对表空间及数据文件进行扩容或调整 避 免因空间不足导致数据库出现问题 3 2 53 2 5 重做日志文件信息重做日志文件信息 重做日志文件 redo log file 对于 Oracle 数据库至关重要 它 们是数据库的事务日志 重做日志文件的主要目的是 万一实例或介质 失败 重做日志文件就能派上用场 或者可以作为一种维护备用数据库 standby database 的方法来完成故障恢复 如果数据库所在主机掉 电 导致实例失败 Oracle 会使用在线重做日志将系统恢复到掉电前的 那个时刻 如果包含数据文件的磁盘驱动器出现了永久性故障 Oracle 会使用归档重做日志以及在线重做日志 将磁盘驱动器的备份恢复到适 当的时间点 监控日志文件信息 能够得到日志文件的具体情况 避免因日志文 件失效 导致不可避免的损失 CSR 性能测试实施方案 北京开发中心技术测试部 12 3 2 63 2 6 共享池命中率共享池命中率 SGA 中的共享池主要由库缓存 Library Cache 字典缓存 Dictionary Cache 用于并行执行消息的缓冲以及控制结构组成 Shared Pool 的大小由参数 SHARED POOL SIZE 决定 库缓存 Library Cache 主要是用于缓存 sql 语句的解析树和查 询计划 以及解析 编译过的 PL SQL 程序单元 存储过程 函数 包 匿名 PL SQL 块和触发器 的程序单元和运行程序单元的会话所指定的 程序单元的参数值 当第二次执行 sql 或 pl sql 程序单元时 将直接 使用已被缓存的解析树和执行计划或编译过的程序单元 将大大的降低 系统的消耗 提高性能 字典缓存 Dictionary Cache 主要用于缓存数据字典的信息 数 据字典是有关于数据库的参考信息 数据库的结构信息和数据库中的用 户信息的一组表和视图的集合 如我们常用到的 V 视图 DBA 视图都属 于数据字典 在 SQL 语句解析的过程中 Oracle 可以非常迅速的访问 如果需要的话 这些数据字典 因为字典缓存总是优先于库缓存 所以监控共享池的命中率主要体 现在库缓存的命中率上 一般要求命中率应达到 99 以上 否则 应考 虑进行调整 3 2 73 2 7 DBDB bufferbuffer cachecache 命中率命中率 Buffer Cache 是 SGA 区中专门用于存放从数据文件中读取的的数据 块拷贝的区域 Oracle 进程如果发现需要访问的数据块已经在 Buffer Cache 中 就直接读写内存中的相应区域 而无需读取数据文件 从而 大大提高性能 CSR 性能测试实施方案 北京开发中心技术测试部 13 DB Buffer Cache 命中率是指读取的时候 在内存中直接得到所需 的数据的比率 数据块在数据缓冲区中得命中率 通常应在 90 以上 如果不能达到 应该考虑进行调整 3 2 83 2 8 磁盘排序磁盘排序 排序对于 Oracle 的性能有很大的影响 排序是 SQL 语法中一个小 的方面 但很重要 在 Oracle 的调整中 它常常被忽略 当使用 CREATE INDEX ORDER BY 或 GROUP BY 的语句时 Oracle 数据库将会 自动执行排序的操作 通常 在使用 ORDER BY 或 GROUP BY 语句的情况 下 Oracle 会进行排序的操作 当产生了一个需要排序的操作时 Oracle 会根据初始化参数 Sort area size 在内存中分配一块空间 用 于进行排序操作 当排序所需要的空间超过初始化参数 Sort area size 所限定的空间后 将会在 TEMP 表空间中分页进行磁盘 排序 磁盘排序要比内存排序大概慢 14 000 倍 不幸的是 对于所有 的 Session 用做排序的内存量都必须是一样的 我们不能为需要更 大排序的操作分配额外的排序区域 因此 监控磁盘排序 disk sorts 占所有排序操作的百分比 在不造成太大浪费的前提下 应将磁盘排序应控制在 5 以下 3 2 93 2 9 不使用临时文件的临时表空间不使用临时文件的临时表空间 Oracle 中的临时数据文件 Temporary data files 即临时文件 temp files 是一种特殊类型的数据文件 Oracle 使用临时文件来存 储大规模排序操作和散列操作的中间结果 如果 RAM 中没有足够的空间 还会用临时文件存储全局临时表数据 或结果集数据 Oracle 以一种特殊的方式处理临时文件 一般而言 你对对象所做 的每一个修改都会存储在重做日志中 这些事务日志会在以后某个时间 CSR 性能测试实施方案 北京开发中心技术测试部 14 重放以 重做事务 例如 失败后进行恢复时就可能需要 重做事务 临时文件不包括在这个重放过程内 对临时文件并不生成 redo 日志 不过可以生成 undo 日志 由于 UNDO 总是受 redo 的 保护 因此 这就会生成使用临时表的 redo 日志 确认临时表空间都要使用临时文件 否则得不到临时文件的任何好 处 3 2 103 2 10 过多索引的非系统表过多索引的非系统表 索引是建立在表的一列或多个列上的辅助对象 目的是加快访问表 中的数据 提高系统的性能 但是对于 DML 语句 insert update delete 因为要对索引进行维护 所以会降低性 能 尤其是频繁进行插入和更新的表 索引越多 系统 CPU I O 负担 就越重 因此 如果确认表上的索引过多 一般建议一个表上的索引数量不 要超过 5 个 需要和有关人员提议 合并或删除不必要的索引 以提 高性能 3 2 113 2 11 使用使用 systemsystem 表空间的用户表空间的用户 Oracle 表空间 tablespace 是数据库的逻辑划分 每个数据库至少 有一个表空间 叫做系统表空间 system 表空间 用于主要用于存放数 据字典信息 SYSTTE 回滚段以及存储过程 包 数据库触发器的定义 当然也可以容纳用户数据 当创建用户时如果不指定缺省的表空间时 系统会默认的将 system 表空间设定为默认表空间 其直接后果就是所 有属于该用户的表都将保存在 system 表空间 由于用户数据的增加 会大量占用 system 表空间 导致系统性能下降甚至出现 system 表空间 无法扩展的错误 CSR 性能测试实施方案 北京开发中心技术测试部 15 3 2 123 2 12 出现大量行链接出现大量行链接 行迁移的表行迁移的表 在第一次插入数据时 一个 block 不能存放一行记录的情况下 行 链接产生 Oracle 将使用链接一个或者多个在这个段中空闲的 block 存 储这一行记录 当一行记录初始插入的时候是可以存储在一个 block 中 的 由于更新操作导致行长增加了 而 block 的自由空间已经完全满了 这个时候就产生了行迁移 当发生了行迁移或者行链接 对这行数据操作的性能就会降低 因 为 Oracle 必须要扫描更多的 block 来获得这行的信息 这样就大大地 增加了系统的压力 应对发生行连接 行迁移的表的参数进行调整 并 对这些数据进行处理 3 2 133 2 13 没有分析或很久以前分析的表没有分析或很久以前分析的表 Oracle 的优化器有两种 10g 以前 基于规则的优化器 RBO 和基于成本的优化器 CBO 从性能的角度来看优化器是最重要的部 分 它性能的高低直接关系到数据库性能的好坏 从 Oracle8i 开始 Oracle 就建议使用 CBO 优化器 Oracle 把一个代价引擎 Cost Engine 集成到数据库内核中 用来 估计每个执行计划需要的代价 该代价将每个执行计划所耗费的资源进 行量化 从而 CBO 可以根据这个代价选择出最优 的执行计划 一个查 询耗费的资源可以被分成 3 个基本组成部分 I O 代价 CPU 代价 network 代价 在使用 CBO 时 需要有表和索引的统计数据 分析数据 作为基础数据 有了这些数据 CBO 才能为各个执行计划计算出相对准 确的代价 从而使 CBO 选择最佳的执行计划 所以定期的对表 索引进 行分析是绝对必要的 这样才能使统计数据反映数据库中的真实情况 否则就会使 CBO 选择较差的执行计划 影响数据库的性能 CSR 性能测试实施方案 北京开发中心技术测试部 16 如果采用了 CBO 优化器 而没有对表和索引进行分析 没有统计数 据 则 Oracle 使用缺省的统计数据 使用的缺省值肯定与系统的实际 统计值不一致 这可能会导致优化器选择错误的执行计划 影响数据库 的性能 3 2 143 2 14 死锁死锁 当两个事务需要一组有冲突的锁 而不能将事务继续下去的话 就 出现死锁 如事务 1 在表 A 行记录 3 中有一排它锁 并等待事务 2 在表 A 中记录 4 中排它锁的释放 而事务 2 在表 A 记录行 4 中有一排它锁 并等待事务 1 在表 A 中记录 3 中排它锁的释放 事务 1 与事务 2 彼此 等待 因此就造成了死锁 Oracle 中的死锁会自动处理 确认系统中是否有死锁现象的发生 死锁一般是因拙劣的事务设计 而产生 需要调整事务设计 10 1 149 1710 1 149 17 3 2 153 2 15 首要的首要的 5 5 个等待事件个等待事件 Top Top 5 5 waitwait events events Oracle 等待事件是衡量 oracle 运行状况的重要依据及指示 主要 有空闲等待事件和非空闲等待事件 设定初始化参数 TIMED STATISTICS TRUE 那么等待事件按等待的时间排序 如果设定为 TIMED STATISTICS FALSE 那么事件按等待的数量排序 空闲等待事件是 Oracle 正等待某种工作 在诊断和优化数据库时 候 不用过多注意这部分事件 非空闲等待事件专门针对 Oracle 的活 动 指数据库任务或应用程序运行过程中发生的等待 这些等待事件是 我们在调整数据库应该关注的 CSR 性能测试实施方案 北京开发中心技术测试部 17 通过 Statspack 报告中的 top 5 wait events 可以确定当前系统中 的最严重的等待事件 确定分析的方向 以便对系统性能进行有针对性 地分析 3 2 163 2 16 TOPTOP SQLSQL 监控监控 应用程序的执行最终将归结为数据库中的 SQL 语句执行 因此 SQL 语句的执行效率最终决定了 Oracle 数据库的性能 在某种意义上来说 监控 top sql 对 top sql 进行调优 有时会大大的提高系统的性能 3 2 16 13 2 16 1 TOPTOP GETSGETS SQLSQL 通过 buffer get 对 Sql 进行排序 即通过它执行了多少个逻辑 I O 来排序 这类 Sql 进行了大量的 block 的读 需要检查该 Sql 是否用到 了索引 或者说表上是否存在合理的索引 对于必须全表扫描的大表可 以考虑 recycle buffer 对于频繁进行全表扫描的小表可以考虑 keep buffer 3 2 16 23 2 16 2 TOPTOP READSREADS SQLSQL 通过物理读对 Sql 进行排序 这类 Sql 引起大部分进行读取活动的 SQL 即物理 I O 可能因为数据缓冲区太小 也可能是过多的全表扫描 需要考察索引是否合理 是否用到索引 3 2 16 33 2 16 3 TOPTOP EXCUTIONSEXCUTIONS SQLSQL 对于频繁执行的 Sql 语句 应注意跟踪 这类 Sql 是需要重点关注 的 也许这些 Sql 本身一次执行并没有消耗大量的时间或者空间 但由 于频繁的执行对系统影响极大 所以只要有优化的可能到要对这些 Sql 进行优化 为了隔离某些频繁执行的查询 以观察是否有某些更改逻辑 的方法以避免必须如此频繁的执行这些查询 这可能是很有用的 或许 CSR 性能测试实施方案 北京开发中心技术测试部 18 一个查询正在一个循环的内部执行 而且它可能在循环的外部执行一次 可以设计简单的算法更改以减少必须执行这个查询的次数 即使它运行 的飞快 任何被执行几百万次的操作都将开始耗尽大量的时间 4 监控范围监控范围 4 14 1 监控对象监控对象 系统内的数据库服务器 4 24 2 监控范围监控范围 4 34 3 不监控范围不监控范围 5 监控方法及诊断步骤监控方法及诊断步骤 5 15 1 监控方法监控方法 在单一的应用环境或业务相对简单的系统下 系统性能问题 瓶颈 所在往往是不言自明 解决问题的前提 定位问题是比较容易解决的 但在一个复杂的应用环境下 各应用系统对系统资源往往是一种共 享和竞争的关系 而且应用系统之间也可能存在着共生或制约的关系 资源利益的均衡往往是此消彼长 而这种环境下的应用系统一旦出现资 源竞争 系统的瓶颈往往难以断定 由于 Oracle 提供的监控工具 Statspack 并不能获取全面分析性能 问题所需要的所有信息 所以需要扩展其收集服务器的统计信息 需要 CSR 性能测试实施方案 北京开发中心技术测试部 19 结合操作系统的一些命令 如 vmstat top iostat 等命令监控系统 I O CPU 的繁忙程度 通过系统一级的监控结合数据库监控工具对数据 库性能的监控 得到当前系统的性能瓶颈 再有针对性的通过当时的 Statspack 报告以及 RDA 报告对系统进行诊断 5 25 2 诊断步骤诊断步骤 影响 Oracle 数据库性能的服务器资源主要有三个 1 内存 2 磁盘 I O 3 CPU 所有的性能问题最后都会表现在这三个方面上 5 2 15 2 1 确定系统瓶颈确定系统瓶颈 通过操作系统的 vmstat top iostat 等命令结合数据库监控工具 对数据库性能的监控确定系统瓶颈 CPU 瓶颈 CPU 利用率接近 100 表示系统正在接近满负荷工作 正在执行和等待 CPU 资源的任务个数超过了 CPU 数目 就会出现 CPU 瓶 颈了 内存瓶颈 数据库服务器只有有限的内存 出现内存争用现象是 Oracle 常见的问题 当内存的需求大于内存的数量 服务器就会启动虚 拟内存机制 这就会出现虚拟内存的页导出和页导入现象 当出现页导 入操作就表明服务器需要更多的内存了 I O 瓶颈 磁盘的 busy 程度很高 而且平均等待时间大于平均服 务时间 此时出现 I O 瓶颈 CSR 性能测试实施方案 北京开发中心技术测试部 20 5 2 25 2 2 根据瓶颈所在进行相应的诊断分析根据瓶颈所在进行相应的诊断分析 所有诊断的第一步都要先检查外部环境 如果外部坏境出现瓶颈 那么再多的 Oracle 调整都是没有帮助的 在确保外部资源在理论上够 用的前提下 根据出现的瓶颈所在 对数据库进行调优 CPU 瓶颈 当出现 CPU 瓶颈时 最直接的处理方法是通过查找占用 CPU 资源最多的进程 找到该进程执行的 SQL 进行调整 也可以在收 集到的信息中查找 TOP EXECUTIONS SQL 这类 sql 是频繁执行的 如果 这类 SQL 出现大量占用 CPU 资源的情况 将会导致系统出现 CPU 瓶颈 内存瓶颈 当出现内存瓶颈时 通常的处理方法是将划给 Oracle 使用的内存确定不超过系统内存的 1 2 为系统添加内存 打全补丁 防止内存泄露 在收集到的信息中查找 TOP GETS SQL 这类 SQL 是通过 buffer cache 对 SQL 进行排序的 有可能出现大量占用内存的现象 I O 瓶颈 当出现 I O 瓶颈时 通常首先需要检查 Oracle 系统全 局区 SGA 确定 SHARED POOL SIZE LARGE POOL SIZE DB CACHE SIZE 等参数的值设置 的是否合理 对这些参数的值进行修改后 磁盘 I O 操作将会有所下降 执行效率会提高 其次 在收集到的信息中查找 TOP READS SQL 这类 SQL 是根据物理读排序得到的 通常是由于全表扫描引起大量的 I O 操 作 通过添加合适的索引对这部分 SQL 进行优化 通常可以用索引扫描 来代替对大表操作的全表扫描 最后 通过改造表来减少磁盘 I O 操作 可以利用不同的 block size 把表有选择性的放到表空间 操作表行数 据按照主索引顺序等等 5 2 35 2 3 基于等待事件的数据库监控诊断分析基于等待事件的数据库监控诊断分析 监控数据库所得到的信息中有一类对数据库性能有着很重要的作用 那就是 Top 等待事件 Oracle 的等待事件是衡量 Oracle 运行状况的重 CSR 性能测试实施方案 北京开发中心技术测试部 21 要依据及指标 在 Oracle9i 中大约有 360 个等待事件 10g 更是高达 800 多个等待事件 主要有两种类别的等待事件 即空闲 idle 等待事 件和非空闲 non idle 等待事件 空闲事件指 Oracle 正等待某种工作 不用过多注意这部分事件 非空闲等待事件专门针对 Oracle 的活动 指数据库任务或应用运 行过程中发生的等待 这些等待事件是调整数据库的时候应该关注与研 究的 5 2 45 2 4 常见的等待事件常见的等待事件 db file scattered read DB 文件分散读取 这种情况通常显示与全表扫描相关的等待 当数据库进行全表扫描 时 基于性能的考虑 数据会分散 scattered 读入 Buffer Cache 如 果这个等待事件比较显著 可能说明对于某些全表扫描的表 没有创建 索引或者没有创建合适的索引 需要检查这些数据表已确定是否进行 了正确的设置 然而这个等待事件不一定意味着性能低下 在某些条件 下 Oracle 会主动使用全表扫描来替换索引扫描以提高性能 这和访问 的数据量有关 在 CBO 下 Oracle 会进行更为智能的选择 在 RBO 下 Oracle 更倾向于使用索引 因为全表扫描被置于 LRU Least Recently Used 最近最少适用 列 表的冷端 cold end 对于频繁访问的较小的数据表 可以选择把他们 Cache 到内存中 以避免反复读取 当这个等待事件比较显著时 可以结合动态性能视图来进行诊断 可能很多是全表扫描操作 db file sequential read DB 文件顺序读取 CSR 性能测试实施方案 北京开发中心技术测试部 22 这一事件通常显示与单个数据块相关的读取操作 如索引读取 如 果这个等待事件比较显著 可能表示在多表连接中 表的连接顺序存在 问题 可能没有正确的使用驱动表 或者可能说明不加选择地进行索引 在大多数情况下 通过索引可以更为快速的获取记录 所以对于一 个编码规范 调整良好的数据库 这个等待很大是很正常的 但是在很 多情况下 使用索引并不是最佳的选择 比如读取较大表中大量的数据 全表扫描可能会明显快于索引扫描 对于这样的查询应该尽量避免使用 索引扫描 Free Buffer 释放缓冲区 这个等待事件表明系统正在等待内存中的可用空间 这说明当前 Buffer 中已经没有 Free 的内存空间 如果应用设计良好 SQL 书写规 范 充分绑定变量 那这种等待可能说明 Buffer Cache 设置的偏小 你可能需要增大 DB BUFFER CACHE Free Buffer 等待可能说明 DBWR 的写出速度不够 或者磁盘存在严 重的竞争 可以需要考虑增加检查点 使用更多的 DBWR 进程 或者增 加物理磁盘的数量 分散负载 平衡 I O latch free latch 释放 latch 是一种低级排队机制 用于保护 SGA 中共享内存结构 latch 就像是一种快速地被获取和释放的内存锁 用于防止共享内存结 构被多个用户同时访问 大多数 latch 问题都与没有很好的使用绑定变量 library cache latch 重作生成问题 redo allocation latch 缓冲存储竞争问题 cache buffers LRU chain 以及 buffer cache 中的存在 热点 块 cache buffers chain 相关 CSR 性能测试实施方案 北京开发中心技术测试部 23 Log Buffer Space 日志缓冲空间 当你将日志缓冲 log buffer 产生重做日志的速度比 LGWR 的写出 速度快 或者是当日志切换 log switch 太慢时 就会发生这种等待 这个等待出现时 通常表明 Redo log buffer 过小 为解决这个问题 可以考虑增大日志文件的大小 或者增加日志缓冲器的大小 另外一个可能的原因是磁盘 I O 存在瓶颈 可以考虑使用写入速度 更快的磁盘 在允许的条件下设置可以考虑使用裸设备来存放日志文件 提高写入效率 在一般的系统中 最低的标准是 不要把日志文件和数 据文件存放在一起 因为通常日志文件只写不读 分离存放可以获得性 能提升 Log File Switch 日志文件切换 当这个等待出现时 表示所有的提交 commit 的请求都需要等待 日志文件切换 的完成 通常是因为日志组循环写满以后 第一个日志归 档尚未完成 出现该等待 出现该等待 可能表示 I O 存在问题 为解 决该问题 需要考虑增加额外的 DBWR 或者增加日志组或日志文件大小 根据 Top 5 wait 事件可以确定系统当前的运行状况 针对等待事 件 在收集的信息中找到对应的部分进行处理 6 安装步骤安装步骤 6 16 1 STATSPACK