Performance Diagnostic and Tuning Best Practices: What DBAs Must Know about Managing Performance

Size: px

Start display at page:

Download "Performance Diagnostic and Tuning Best Practices: What DBAs Must Know about Managing Performance"

赫谈
7 years ago
Views:

1 < 在此处插入图片 > 利用 Oracle Enterprise Manager 11g 进行数据库诊断与调优

2 以下内容旨在概述产品的总体发展方向该内容仅供参考, 不可纳入任何合同该内容不构成提供任何材料代码或功能的承诺, 并且不应该作为制定购买决策的依据此处所述有关 Oracle 产品的任何特性或功能的开发发布以及相应的日程安排均由 Oracle 自行决定

3 议题历史回顾主要概念主要基础架构组件利用 Oracle Enterprise Manager 11g 的 Diagnostic Pack 和 Tuning Pack 进行性能调优问答

4 历史回顾 < 在此处插入图片 >

5 Oracle 调优方法史前 (v5) 调试代码中世纪 (v6) 计数器 / 比率 BSTAT/ESTAT SQL*Trace 文艺复兴时期 (v7) 引入等待事件工具从计数器到计时器的转移 STATSPACK 现代 (v10) DB 时间调优使用数据库中的基本时间概念进行全面的调优多个范围界定级别一直运行, 非侵入性内置于基础架构中 : 工具 ASH AWR ADDM EM

6 主要概念 < 在此处插入图片 >

7 数据库时间 (DB 时间 ) 前台会话进行数据库调用的总时间包括 CPU 时间 IO 时间和非空闲等待时间 DB 时间 <> 响应时间对 Oracle 性能分析的新量度数据库时间是用户进程进行数据库调用 ( 此时进程处于活动运转或活动等待状态 ) 所花费的总时间

8 基本概念活动会话 = 当前正在花费时间进行数据库调用的会话数据库时间 (DB 时间 ) = 会话在所有数据库调用上花费的总时间会话的平均活动性 (% 活动性 ) = 活动时间与总时钟时间的比率浏览图书阅读一本书的评论添加到购物车结账 = 花在数据库上的时间时间

9 多个会话 DB 时间 = 所有会话的 DB 时间总和平均活动会话数 = 所有会话的平均活动性的总和 = 在 t 时刻有 2 个活动会话总数据库时间时钟 ( 经过的 ) 时间用户 1 用户 2 用户 3 = 花在数据库上的时间 t 时间用户 n

10 EM 性能页面随时间推移按等待类别统计的活动会话着色区域 = DB 时间总计 Click on the big stuff (CBS 方法 )

11 主要基础架构组件 < 在此处插入图片 >

12 Automatic Workload Repository (AWR) 内置于数据库中的负载和性能统计信息库自动捕获负载数据默认情况下每小时保存 8 天的数据或手动保存数据驻留于 SYSAUX 表空间中自动管理空间需求在夜间根据保留间隔自动清除过时数据保存不同类型的数据 : 基本统计信息, 如物理读取次数 SQL 统计信息, 如磁盘读取次数 ( 每个 sql 语句 ) 量度, 如每秒物理读取次数活动会话历史记录 (ASH)

13 活动会话历史记录 (ASH) 每秒对活动会话进行抽样, 抽样到内存中 (v$active_session_history) 直接访问内核结构每次快照使用十分之一的样本刷新 AWR 捕获的数据包括 : SID SQL ID 程序模块操作等待事件编号对象文件块实际等待时间 ( 如果捕获时处于等待状态 ) 对瞬时问题启用有针对性的性能分析

14 活动会话历史记录 (ASH) 查询 Khalid Husaini 的小说浏览和阅读评论将商品添加到购物车使用一键式模块结账 DB 时间时间 SID 模块 SQL ID 省 / 市 / 自治区事件 7:38: 按作者查找书籍 qa324jffritcf 等待 db 文件顺序读取 7:42: 获取评论 id aferv5desfzs5 CPU 7:50: 添加到购物车 hk32pekfcbdfr 等待缓冲区繁忙等待 7:52: 一键式 abngldf95f4de 等待日志文件同步

15 < 在此处插入图片 > 利用 Oracle Enterprise Manager 11g 的 Diagnostic Pack 和 Tuning Pack 进行性能调优

16 基本性能调优过程捕获性能和负载数据分析系统统计信息等待信息 SQL 统计信息等当前哪些类型的操作占用了最多的时间? 哪些资源造成瓶颈问题? 根本原因是什么? 如何纠正问题? 问题解决如果确定存在多个问题, 哪个问题最为严重? 哪种解决方案可使我获益最大? AWR ADDM ADDM

17 利用 ADDM 自动进行系统级分析高负载 SQL Automatic Workload Repository 中的快照数据库中的自诊断引擎自动诊断引擎 IO/CPU 问题 RAC 问题 Automatic Database Diagnostic Monitor (ADDM) 自动提供数据库级性能诊断数据库中的自诊断引擎将所有组件集成到一起识别 RAC 提供影响和收益分析, 无问题区域即需即用主动运行, 在需要时可被动运行可用于进行实时分析和历史分析 SQL Advisor 系统资源建议网络 + 数据库配置建议

19 手动性能分析 CBS 方法 ( 即 Click on the Big Stuff 方法 ) 显示所选等待类别的 ASH 样本回答是谁因为做了什么而造成该问题谁正在使系统速度变慢? 他做了什么操作?

23 使用 ASH 进行有针对性的性能分析 ASH 使我们可对瞬时问题进行有针对性的性能分析可以按时间 SID SQL ID 等待类别服务模块操作客户端 ID 来筛选 ASH 报告帮助回答诸如以下的问题 : 此 SQL 是否已随时间流逝而变慢? 性能范围 * 时间

24 使用 AWR 基准和报告进行性能对比分析性能实际基准自动生成和管理参考基准支持通过 AWR 报告进行两个时段的性能对比使我们能轻松进行负载差异分析和性能诊断移动即时生成的时窗基准通过适应性阈值支持主动监视基准对以下方面的性能调优很关键 : 设置警报阈值监视性能对比性能差异时间

通过 SQL Advisor 进行指导性 SQL 调优自动 SQL 调优打包的应用程序 + SQL 配置文件打包的应用程序高负载 SQL 自动调整优化程序自动 SQL 分析可定制的应用程序 + SQL 建议可定制的应用程序 Access Advisor 可定制的应用程序 + 索引 MV 分区 SQL

25 通过 SQL Advisor 进行指导性 SQL 调优自动 SQL 调优打包的应用程序 + SQL 配置文件打包的应用程序高负载 SQL 自动调整优化程序自动 SQL 分析可定制的应用程序 + SQL 建议可定制的应用程序 Access Advisor 可定制的应用程序 + 索引 MV 分区 SQL Tuning Advisor 和 SQL Access Advisor 自动进行 SQL 和应用程序调优提供的建议带来的性能改进建议包括 : 新索引分区 MV 统计信息更新 SQL 重构和 SQL 监测 SQL 分析无需更改应用程序代码即可透明地对 SQL 进行调优支持打包的应用程序调优已调优的 SQL

26 SQL Tuning Advisor 自动调整优化程序 (ATO) 统计分析 SQL Tuning Advisor SQL 调优建议收集缺少或陈旧的统计信息 SQL 监测生成 SQL 配置文件访问路径分析添加缺少的索引 DBA SQL 结构分析修改 SQL 构造

27 SQL 配置文件是什么? 包含 ATO 针对一个 SQL 语句收集的辅助信息自定义优化程序设置基于过去的执行历史 ( 如 first_rows 对比 all_rows) 补充缺少的或陈旧的统计信息补救优化程序评估中的错误由于数据偏差和相关性复杂的筛选和联接而出现评估错误无需对 SQL 代码进行任何更改非常适用于打包的应用程序持久性 : 关闭或升级后仍有效可在数据库之间移动 (10.2)

28 SQL 监测流 SQL 监测提交优化程序生成 SQL Tuning Advisor ( 调优模式 ) SQL 配置文件之后数据库用户提交优化程序 ( 正常模式 ) 输出经过调优的计划

29 Oracle Database 10g 中的 SQL 调优端到端的工作流实施评估建议负载 DBA DBA 产生建议一小时 DBA AWR ADDM SQL 调优候选方案调用 Advisor SQL Tuning Advisor 一个好的端到端的解决方案, 但需要人工干预

30 Oracle Database 11g 中的自动 SQL 调优夜间打包的应用程序 SQL 配置文件自动捕获高负载 SQL 自动 SQL 调优自定义应用程序 SQL 分析完全自动化的 SQL 调优自动捕获高负载 SQL 通过生成 SQL 配置文件自动调优 SQL 而无需更改应用程序自动实施极大改进的 SQL 计划 ( 可选 ) 自动报告分析结果在维护时段自动运行自动实施报告人工实施已调优的 SQL

31 Oracle 11g 中的自动 SQL 调优实施 SQL 配置文件测试 SQL 配置文件负载产生建议选择候选 SQL 一周 SQL 调优候选方案这是自动的! AWR DBA 查看报告 / 控制该过程

32 SQL Access Advisor SQL Access Advisor 建议 B 树索引自动调整优化程序访问路径分析位图索引分区 ( 仅 11g) DBA MV 和 MV 日志

33 SQL Access Advisor 的特性为优化性能给出创建和 / 或丢弃哪些索引分区 ( 仅散列和间隔 ) 物化视图和物化视图日志的建议对整个负载情况进行分析而不只是对各个 SQL 语句进行单独的分析考虑了新访问结构对 DML 操作的影响考虑了存储创建和维护成本同时考虑了 : 索引解决方案物化视图解决方案两者的结合优化物化视图以实现 : 最大的查询重写使用快速刷新

34 自定义应用程序自动性能诊断 Oracle Enterprise Manager 11g 中的新增内容中间件数据库预打包的应用程序物理和虚拟服务器组合应用程序电子商务应用程序从应用程序到磁盘增强的 ADDM 新增 : 高亮显示影响性能的 SQL 结果通过全局 AWR 报告更轻松地进行 RAC 诊断向后兼容性 : 可以分析早期版本的快照活动会话历史记录 (ASH) 新增 : 备用数据库的 ASH: 便于对备用数据库进行性能分析 RAC 系统的全局 ASH 报告存储

35 性能诊断新增 : 活动报告用于脱机分析的交互式报告提供同级深入交互以便进行详细分析可以通过电子邮件发送给相应人员以进行分析不需要安装 Oracle Enterprise Manager

36 RAC 数据库可管理性 Oracle Enterprise Manager 11g 中的新增内容新增 : 对集群件的监视与管理新增 : 对通过策略管理的 RAC 数据库的网格即插即用支持添加和删除 RAC 节点 ADDM 显示 RAC 实例间的阻塞会话

37 高可用性 Oracle Enterprise Manager 11g 中的新增内容针对 RAC/HA 设置的配置级汇总视图 ( 内含图表 ) 对 MAA 实施提供建议新增 : 单实例最小停机时间自动化实现 MAA 显示全部配置中与可用性有关的事件新增 : 管理基于角色的备用数据库, 如将物理备用数据库转为主用 Data Guard

38 数据库云服务器 V2 支持新增 : 数据库云服务器支持数据库云服务器监视插件警报和量度从域级到单元磁盘的性能监视配置分析数据库云服务器的 SQL 监视支持含有数据库云服务器信息的 I/O 性能图表基于数据库云服务器 V2 提供数据库云服务器使用 SQL Performance Analyzer 评估数据库云服务器对 DSS 负载的性能提高目标 : 测量因使用数据库云服务器而提高的 I/O 互连字节数通过 SPA 比较报告显示性能提高或降低情况

39 SQL Tuning Advisor Oracle Enterprise Manager 11g 中的新增内容 SQL 监测统计分析访问路径分析 SQL 重构分析替代计划分析并行查询分析 SQL Tuning Advisor 收集缺少或陈旧的统计信息生成 SQL 配置文件添加缺少的访问结构修改 SQL 构造采用替代执行计划创建并行的 SQL 配置文件管理员自动调整优化程序全面的 SQL 调优建议 SQL Tuning Advisor 新增 : 使用实时和历史性能数据确定备选执行计划新增 : 建议合适的并行度以改善 SQL 性能

40 实时 SQL 监视深入了解 SQL 执行情况自动监视长时间运行的 SQL 即需即用, 不会影响性能监视每个 SQL 执行情况公开监视统计信息全局执行级别计划运行级别并行执行级别指导调优工作新增 : 作为活动报告可用于 Grid Control 11.1 中新增 : 增强以监视 PL/SQL

41 总结 < 在此处插入图片 >

42 使用 DBA 2.0 技术和管理更多的数据库更多的应用程序 :OLTP DW OCS AS 更多的用户更大的数据库更多的任务关键的应用程序变得更加主动更具战略性更加重要和更有价值获得更多的夜间睡眠时间! 更多的周末休息时间!

Slide 1

使用 Oracle 诊断和调整包进行性能管理 : 技术和技巧 Jagan R. Athreya Oracle 公司, 数据和存储管理部, 总监 The following is intended to outline our general product direction. It is intended for information purposes