Oracle10g性能优化


Oracle Database 10g:性能优化 电子演示 D19165CN10 版本 1.0 2006 年 9 月 D46716 ® 版权所有 © 2006,Oracle。保留所有权利。 免责声明 本文档包含专有权信息,并受版权法和其它知识产权法的保护。您可以复制和打印本文档供在 Oracle 培训课程中单独使用。不得以任何方式修改或变更本文档。除了在依照版权法中制定的“合理使用” 范围内使用本文档外,在未经 Oracle 明确授权的情况下,您不得以全部或部分的形式使用、共享、 下载、上载、复制、打印、显示、展示、再版、发布、许可、张贴、传播或散布本文档。 本文档中包含的信息如有更改,恕不另行通知。如果您在本文档中发现任何问题,请书面通知: Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA. 不能保证 本文档中没有错误。 有限权利声明 如果将本文档交付给美国政府或代表美国政府使用本文档的任何人,请适用下列通知中的规定: 美国政府权利 美国政府使用、修改、再版、发行、展示、显示或公开这些培训资料的权利必须遵守 Oracle 许可协议 和(或)美国政府合同法中适用的条款。 商标声明 Oracle、JD Edwards、PeopleSoft 和 Siebel 是 Oracle 公司和(或)其分公司的注册商标。其它名称 可能是其各自拥有者的商标。 作者 James Spiller Jean-Francois Verrier 技 术撰稿 者和 审稿者 Mirza Ahmad Christian Bauwens Tom Best Maria Billings Harald Van Breederode Mary Bryksa Peter Caldwell Immanuel Chan Edward Choi Patrick Cullen Andy Fortunak Joel Goodman Bert Van Gorkom Scott Gossett Connie Green Laurie Holdren Sanjeev Joglekar Pete Jones Donna Keesling Teria Kidd 编辑 Atanu Raychaudhuri Nita Pavitran 制图 员 James Hans 出版 商 Giri Venugopal Vishal Kohli Subodh Kumar Pierre Labrousse Herve Lejuene Stefan Lindblad Roderick Manalac Louise Morin Howard Ostrow Jagadeesh Papajah Gopi Ramamoorthy Joerg Reinschmidt Rick Shavwer Ira Singer Janet Stern Bransislav Valny Christian Weinfurtner Lester Wells James Womack Anthony Woodell Fang Wu 版权所有 © 2006,Oracle。保留所有权利。 简介 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课程后,应能完成下列工作: • 使用适合于可用工具的 Oracle 数据库优化方法 • 利用数据库 Advisor 主动优化 Oracle 数据库 • 使用基于自动工作量资料档案库的工具优化数据库 • 使用 Statspack 报表优化数据库 • 诊断和优化常见的数据库性能问题 • 使用与 Enterprise Manager 性能有关的页监视 Oracle 数据库 版权所有 © 2006,Oracle。保留所有权利。 课程安排 自动管理共享内存10 3 优化缓冲区高速缓存93 优化共享池82 被动式优化72 使用自动工作量资料档案库62 使用 Statspack 51 度量、预警和基线41 统计信息和等待事件31 性能优化:概览21 简介11 主题课程日程 版权所有 © 2006,Oracle。保留所有权利。 课程安排 性能优化:小结15 4 优化块空间使用情况14 4 优化 PGA 和临时空间13 4 优化 I/O 12 4 检查点和重做优化11 3 主题课程日程 版权所有 © 2006,Oracle。保留所有权利。 优化问题 • 谁进行优化? • 对什么进行优化? • 如何进行优化? 版权所有 © 2006,Oracle。保留所有权利。 谁进行优化 参与优化的人员包括: • 数据库管理员 • 应用程序结构设计师 • 应用程序设计者 • 应用程序开发人员 • 系统管理员 版权所有 © 2006,Oracle。保留所有权利。 整体上对什么进行优化 性能优化范围: • 应用程序: – 编写得不好的 SQL – 序列化的资源 – 很差的会话管理 • 实例优化: – 内存 – 数据库结构 – 实例配置 • 操作系统: – I/O – 交换 – 参数 本课程的重点 版权所有 © 2006,Oracle。保留所有权利。 实例中的优化对象 实例优化范围: • 内存: – 内存不足 – 内存分配不佳 • I/O: – 带宽不足 – 磁盘空间分配不佳 – 数据库配置不佳 • 实例配置: – 不适合的实例参数 – 恢复配置和可用性配置不佳 版权所有 © 2006,Oracle。保留所有权利。 如何进行优化 用于优化的过程与工具相关: • 基本工具: – 动态性能视图 – 统计信息 – 度量 – Enterprise Manager 页 • AWR 或 Statspack • 自动数据库诊断监视程序 (ADDM) • DBA 脚本 版权所有 © 2006,Oracle。保留所有权利。 传统性能优化方法的难题 数据收集 数据分析 解决方案实施 重放工作量 了解/关联原始统计信息 按影响确定 解决方案的优先级 版权所有 © 2006,Oracle。保留所有权利。 性能监视解决方案 快照 内存中 统计信息 AWR SGA 60 mn ADDM 结果 MMON DBA 快照 Statspack 前台 自动 ASH ADDM 预警 版权所有 © 2006,Oracle。保留所有权利。 使用软件包的功能 使用软件包进行监视和优化 不使用软件包进行监视和优化 • SQL 跟踪 • Statspack • 系统统计信息 • 等待模型 • 时间模型 • OS 统计信息 • 度量 • 服务统计信息 • 直方图 • 优化程序统计信息 • SQL 统计信息 版权所有 © 2006,Oracle。保留所有权利。 备选优化方案 启用游标共享 通过观察 SQL 是否包含文字确定硬分析问题 检查所访问的对象并复查 SQL 通过复查许多具有相同散列计划的语句的 V$SQL 来 确定 SQL 查看已用分析时间 > CPU 分析时间,并且硬分析数 大于正常值 复查 V$SYSSTAT 查看共享池和库高速缓存闩锁上的等待事件 观察闩锁争用 ADDM 建议使用 CURSOR_SHARING查看等待事件 复查 ADDM 建议检查系统利用率 使用 ADDM不使用 ADDM 版权所有 © 2006,Oracle。保留所有权利。 优化方法 优化步骤: • 自上而下进行优化: – 在优化应用程序代码之前先优化设计。 – 在优化实例之前先优化代码。 • 对可以带来最大潜在好处的方面进行优化: – 确定最长的等待时间。 – 确定最大的服务次数。 • 达到目标时停止优化。 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 确定优化工具 • 利用优化方法 版权所有 © 2006,Oracle。保留所有权利。 性能优化:概览 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成下列工作: • 制定恰当的优化目标 • 应用优化方法 • 平衡性能和安全性 • 确定常见的优化问题 • 使用 Oracle Support 记录性能服务请求 版权所有 © 2006,Oracle。保留所有权利。 常规优化会话 优化会话的过程都是相同的: 1. 定义问题并陈述目标。 2. 收集当前统计信息。 3. 考虑一些常见的性能错误。 4. 制定试用解决方案。 5. 实施并度量更改。 6. 决定:“该解决方案是否达到目标?” – 否?转到步骤 3 并重复相关过程。 – 是?创建新基线。 版权所有 © 2006,Oracle。保留所有权利。 定义问题 发现和定义问题: • 倾听用户的反馈 • 检查预警日志和跟踪文件以发现错误 • 检查参数文件中的所有诊断设置或不适合的参数设置 • 检查内存、I/O 和 CPU 的使用情况。确定资源使用情 况异常的进程 • 确定并优化大量占用 CPU 或 I/O 的 SQL 语句 • 收集实例和操作系统 (OS) 的统计信息 版权所有 © 2006,Oracle。保留所有权利。 设置优先级 选择影响最大的问题: • 通过完成工作(CPU 时间或服务时间)与等待工作 所用时间(等待时间)的对比来分析系统性能。 • 确定占用时间最长的组件。 • 细化以优化该组件(如果适合)。 版权所有 © 2006,Oracle。保留所有权利。 优化方法:设置优先级示例 Time Model System Stats DB/Inst: ORCL Snaps: 1-11 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) % of DB time --------------------------- --------- --------- sql execute elapsed time 467.0 77.1 DB CPU 414.2 68.4 parse time elapsed 200.5 33.1 hard parse elapsed time 109.0 18.0 … DB time 605.8 版权所有 © 2006,Oracle。保留所有权利。 常见的优化问题 最常见的优化问题包括: • SQL 语句 • 会话管理 • 共享池大小调整和争用 • 缓冲区高速缓存大小调整和争用 • 数据块争用 • 重做日志和重做缓冲区优化 • 还原优化 • I/O 问题 • 锁定问题 版权所有 © 2006,Oracle。保留所有权利。 ADDM 优化会话 ADDM 优化会话与常规优化会话的过程相同,但是组合了 下列步骤: 1. 查看 ADDM 报表。 A. 收集当前的统计信息;与以前的统计信息集进行比较。 B. 与性能问题知识库进行比较。 C. 定义问题并提供建议。 2. 复查建议。 D. 制定试用解决方案。 3. 实施建议。 E. 实施并度量更改。 4. 复查下一个 ADDM 报表。 F. 决定:“该解决方案是否达到目标?” 版权所有 © 2006,Oracle。保留所有权利。 有效的优化目标 有效的优化目标包括: • 具体 • 可量化 • 可实现 版权所有 © 2006,Oracle。保留所有权利。 优化目标 优化目标包括: • 尽可能缩短响应时间 • 增大吞吐量 • 提高负载能力 • 缩短恢复时间 版权所有 © 2006,Oracle。保留所有权利。 数据库时间 数据库时间 = 数据库等待时间 + 数据库 CPU 时间 版权所有 © 2006,Oracle。保留所有权利。 CPU 时间和等待时间优化范围 可伸缩的 应用程序 可能需要进行 SQL 优化 需要进行 实例/RAC 优化 CPU 时间 等待时间 添加 CPU/节点 没有任何作用 数据库时间 = 数据库 CPU 时间 + 数据库等待时间 版权所有 © 2006,Oracle。保留所有权利。 优化活动周期阶段 应用程序活动周期可以分为不同的阶段: • 应用程序的设计和开发 • 测试:数据库配置 • 部署:在现有数据库中添加新的应用程序 • 生产:故障排除和优化 版权所有 © 2006,Oracle。保留所有权利。 活动周期期间的优化步骤 1. 优化设计。 2. 优化应用程序。 3. 优化内存。 4. 优化 I/O。 5. 优化争用。 6. 优化操作系统。 版权所有 © 2006,Oracle。保留所有权利。 应用程序的设计和开发 即使在设计和开发阶段,也可以通过构建和优化测试案例 来优化应用程序。 • 根据主要功能检查规范化。 • 根据访问时间检查数据结构。 • 查看发生进程序列化的点。 • 优化主要报表。 • 优化大批量进程。 版权所有 © 2006,Oracle。保留所有权利。 测试:数据库配置 测试阶段允许在更深的层次进行优化: • 检查物理布局。 • 监视资源争用。 – 内存利用率 – 锁定 – 磁盘热点 • 测试资源耗尽。 版权所有 © 2006,Oracle。保留所有权利。 部署 部署: • 部署新的应用程序和数据库 – 获取基线。 – 监视增长和性能。 • 在现有数据库中部署新应用程序 – 获取部署前的基线。 – 获取部署后的基线。 – 比较基线。 版权所有 © 2006,Oracle。保留所有权利。 生产 优化是被动式的。您需要了解: 发生了哪些变化? 基线在哪里? 版权所有 © 2006,Oracle。保留所有权利。 收集基线统计信息集 基线统计信息集用于: • 提供系统在设置的界限内运行时收集的一组统计信息 • 将基线统计信息与当前统计信息进行比较 • 创建与系统已发生的改变相关的前提 版权所有 © 2006,Oracle。保留所有权利。 性能与安全性的折衷 影响性能的因素包括: • 多控制文件 • 组中有多个重做日志成员 • 频繁的检查点检查 • 备份数据文件 • 执行存档 • 块校验和 • 并发用户和事务数 版权所有 © 2006,Oracle。保留所有权利。 记录性能服务请求 记录性能服务请求: • 问题针对整个实例还是只针对查询? • 确定根本原因。 • 提供 Statspack 或 AWR 报表和 OS 统计信息。 • 提供远程诊断代理 (RDA) 报表。 • 提供 SQL_TRACE 报表。 版权所有 © 2006,Oracle。保留所有权利。 RDA 报表 版权所有 © 2006,Oracle。保留所有权利。 监视和优化工具:概览 预警日志 跟踪文件 性能视图 优化程序统计信息 SQL 统计信息 基础统计信息 直方图 度量 服务统计信息 ASH tkprof trcsess 系统统计信息 会话统计信息 等待模型 时间模型 预警 ASH 报表 服务 版权所有 © 2006,Oracle。保留所有权利。 监视和优化工具:概览 Statspack AWR EM 性能页 度量基线 EM 策略 ADDM Advisor 直接 SGA 监视程序 挂起分析程序 基线 AWR 基线 比较期间 服务 版权所有 © 2006,Oracle。保留所有权利。 练习概览:制定优化目标 本练习包含以下主题: • 将问题定义转换为目标 • 使目标成为可量化的目标 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 制定适合的优化目标 • 确定适合不同开发阶段的优化方法 • 平衡性能和安全性的折中关系 • 确定常见的优化问题 • 使用 Oracle Support 记录性能服务请求 版权所有 © 2006,Oracle。保留所有权利。 统计数据和等待事件 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成下列工作: • 确定对优化有用的动态性能视图 • 确定预警日志文件的关键优化组件 • 确定用户跟踪文件的关键优化组件 • 使用动态性能视图查看统计数据和等待事件 版权所有 © 2006,Oracle。保留所有权利。 性能优化数据 收集的数据类型: • 累计统计数据: – 包含时间信息的等待事件 – 时间模型 • 度量:统计率 • 抽样统计数据:活动会话历史记录 – 按会话显示统计数据 – 按 SQL 显示统计数据 – 按服务显示统计数据 – 其他维 版权所有 © 2006,Oracle。保留所有权利。 性能优化工具 可用的工具: • 基本工具: – Enterprise Manager 页 – 预警日志 – 跟踪文件 – 动态性能视图和表 • 插件: – Statspack • 选件: – 诊断包 – 优化包 版权所有 © 2006,Oracle。保留所有权利。 访问数据库主页 http://主机名:1158/em 版权所有 © 2006,Oracle。保留所有权利。 EM 性能页 版权所有 © 2006,Oracle。保留所有权利。 查看预警日志 Database Home page > Related Links region > Alert Log Content( “数据库”主页 >“相关链接”区域 > 预警日志 内容) 版权所有 © 2006,Oracle。保留所有权利。 使用预警日志信息帮助进行优化 预警日志文件包含的下列信息,可用于帮助优化数据库: • 不完整的检查点 • 执行归档的时间 • 实例恢复的开始时间和完成时间 • 死锁错误和超时错误 • 检查点的开始时间和结束时间 版权所有 © 2006,Oracle。保留所有权利。 用户跟踪文件 • 可以在会话级别或实例级别启用或禁用服务器进程跟踪。 • 用户跟踪文件包含该会话中跟踪的 SQL 语句的统计 信息。 • 用户跟踪文件是按每个服务器进程创建的。 • 用户跟踪文件还可通过下列方式创建: – 执行一次 BACKUP CONTROL FILE TO TRACE 命令 – 进程错误 版权所有 © 2006,Oracle。保留所有权利。 后台进程跟踪文件 • Oracle 数据库服务器可将任何后台进程检测到的错误 的有关信息转储到跟踪文件中。 • Oracle 技术支持人员可以使用这些跟踪文件进行诊断 和故障排除。 • 这些文件通常不包含优化信息。 版权所有 © 2006,Oracle。保留所有权利。 动态性能视图 通过动态性能视图可以访问实例中 不断变化的状态和条件的有关信息 会话数据 等待事件 内存分配 运行 SQL UNDO 使用率 打开的游标 重做日志使用率 等等 Oracle 实例 版权所有 © 2006,Oracle。保留所有权利。 动态性能视图:用法示例 SQL> SELECT sql_text, executions 2 FROM v$sqlstats 3 WHERE cpu_time > 200000; SQL> SELECT * FROM v$session 2 WHERE machine = 'EDRSR9P1' and 3 logon_time > SYSDATE - 1; SQL> SELECT sid, ctime 2 FROM v$lock WHERE block > 0; a b c 版权所有 © 2006,Oracle。保留所有权利。 动态性能视图:注意事项 • 这些视图属于 SYS。 • 不同的时间使用不同的视图: – 实例已启动。 – 数据库已装载。 – 数据库已打开。 • 可以通过查询 V$FIXED_TABLE 查看所有视图名称。 • 这些视图通常称为“v-dollar 视图”。 • 因为数据是动态的,所以不能保证这些视图上读取数据 的一致性。 版权所有 © 2006,Oracle。保留所有权利。 统计级别 STATISTICS_LEVEL BASIC TYPICAL ALL 建议的默认值 用于人工 SQL 诊断的 其他统计数据 禁用所有 自优化功能 V$STATISTICS_LEVEL 版权所有 © 2006,Oracle。保留所有权利。 统计数据和等待事件 统计数据 等待事件 报表 动态性能视图 版权所有 © 2006,Oracle。保留所有权利。 系统统计数据类 高速缓存 调试 RAC SQL 重做 用户 入队 OS V$SYSSTAT V$SESSTATV$STATNAME 系统统计 信息类 版权所有 © 2006,Oracle。保留所有权利。 显示系统范围内的统计数据 V$SYSSTAT • STATISTIC# • NAME • CLASS • VALUE V$SGASTAT • POOL • NAME • BYTES V$EVENT_NAME • EVENT NUMBER • NAME • PARAMETER1 • PARAMETER2 • PARAMETER3 V$SYSTEM_EVENT • EVENT • TOTAL_WAITS • TOTAL_TIMEOUTS • TIME_WAITED • AVERAGE_WAIT 版权所有 © 2006,Oracle。保留所有权利。 显示与会话有关的统计数据 V$STATNAME • STATISTIC# • NAME • CLASS V$SESSTAT • SID • STATISTIC# • VALUE V$SESSION_WAIT • SID • SEQ# • EVENT • P1/2/3 • P1/2/3 TEXT • P1/2/3 RAW • WAIT TIME • SECONDS_IN_WAIT • STATE V$SESSION • SID • SERIAL# • USERNAME • OSUSER V$SESSION_EVENT • SID • EVENT • TOTAL_WAITS • TOTAL_TIMEOUTS • TIME_WAITED • AVERAGE_WAIT • MAX_WAIT V$EVENT_NAME • EVENT# • NAME • PARAMETER1 • PARAMETER2 • PARAMETER3 版权所有 © 2006,Oracle。保留所有权利。 显示与服务有关的统计数据 V$SERVICE_STATS • SERVICE_NAME_HASH • SERVICE_NAME • STAT_ID • STAT_NAME • VALUE V$SESSION_WAIT_CLASS • SERVICE_NAME • SERVICE_NAME_HASH • WAIT_CLASS_ID • WAIT_CLASS# • WAIT_CLASS • TOTAL_WAITS • TIME_WAITED V$SERVICES • SERVICE_ID • NAME • NAME_HASH • NETWORK_NAME • CREATION_DATE V$SERVICE_EVENT • SERVICE_NAME • SERVICE_NAME_HASH • EVENT • EVENT_ID • TOTAL_WAITS • TOTAL_TIMEOUTS • TIME_WAITED • AVERAGE_WAIT • MAX_WAIT • TIME_WAITED_MICRO 版权所有 © 2006,Oracle。保留所有权利。 等待事件 • 等待事件集合提供由于不同原因不得不等待或必须等待 的会话的有关信息 • 这些事件在 V$EVENT_NAME 视图中列出,该视图包含 以下列: – EVENT# – NAME – PARAMETER1 – PARAMETER2 – PARAMETER3 版权所有 © 2006,Oracle。保留所有权利。 公共等待事件 过度提交、I/O日志文件同步 日志缓冲区 I/O日志缓冲区空间 闩锁库高速缓存等待 锁排队等待 (enq:) I/O、SQL 优化DB 文件分散读取,DB 文件 序列读取 缓冲区高速缓存、DBWR、I/O空闲缓冲区等待 缓冲区高速缓存、DBWR缓冲区忙等待 区域等待事件 版权所有 © 2006,Oracle。保留所有权利。 等待类V$SESSION_WAIT_CLASS V$SERVICE_WAIT_CLASS V$SYSTEM_WAIT_CLASS V$EVENT_NAME调度程序 集群 配置 提交 应用程序 管理 网络 用户 I/O 并发 系统 I/O 其他 空闲 后台进程 I/O 前台进程 I/O DBA命令 应很少见 不充分的数据库/实例配置 资源管理器 RAC 资源 用户应用 程序代码 不活动的会话 网络消息传递 日志文件同步 内部数据库资源 等待类 版权所有 © 2006,Oracle。保留所有权利。 使用 V$EVENT_NAME 视图 NAME PARAMETER1 PARAMETER2 PARAMETER3 ------------------------------- ---------- ---------- ---------- PL/SQL lock timer duration alter system set mts_dispatcher waited buffer busy waits file# block# id library cache pin handle addr pin address 0*mode+name log buffer space log file switch (checkpoint incomplete) transaction undo seg# wrap# count ... 286 rows selected. SQL> SELECT name, parameter1, parameter2, parameter3 2 FROM v$event_name; 版权所有 © 2006,Oracle。保留所有权利。 等待事件统计数据 这些视图包含会话级的等待事件统计数据: • V$SESSION_EVENT:按事件显示每个必须等待的会 话的会话等待事件 • V$SESSION_WAIT:按事件显示正在等待的当前活动 会话的会话等待事件 • V$SYSTEM_EVENT:某个事件的等待事件合计,所有 会话汇总在一起 版权所有 © 2006,Oracle。保留所有权利。 使用 V$SESSION_EVENT 视图 SID EVENT TOTAL_WAITS AVERAGE_WAIT ---- ---------------------------- ----------- ------------ 10 buffer busy waits 12 5 10 db file sequential read 129 0 10 file open 1 0 10 SQL*Net message to client 77 0 10 SQL*Net more data to client 2 0 10 SQL*Net message from client 76 0 SQL> SELECT sid, event, total_waits,average_wait 2 FROM v$session_event 3 WHERE sid=10; 版权所有 © 2006,Oracle。保留所有权利。 使用 V$SESSION_WAIT 视图 SID SEQ# EVENT WAIT STATE TIME --- - -- ---- -------------------------------- ------- 1 1284 pmon timer 0 WAITING 2 1697 rdbms ipc message 0 WAITING 3 183 rdbms ipc message 0 WAITING 4 4688 rdbms ipc message 0 WAITING 5 114 smon timer 0 WAITING 6 14 SQL*Net message from client -1 WAITED SHORT TIME SQL> SELECT sid, seq#, event, wait_time, state 2 FROM v$session_wait; 版权所有 © 2006,Oracle。保留所有权利。 使用 V$SYSTEM_EVENT 视图 EVENT TOTAL_ TOTAL_ TIME_ AVERAGE_ WAITS TIMEOUTS WAITED WAIT ----------------- ------ -------- ------ ---------- latch free 5 5 5 1 pmon timer 932 535 254430 272.993562 process startup 3 8 2.66666667 buffer busy waits 12 0 5 5 ... 34 rows selected. SQL> SELECT event, total_waits, total_timeouts, 2 time_waited, average_wait 3 FROM v$system_event; 版权所有 © 2006,Oracle。保留所有权利。 系统统计数据的精度 • 包含微秒定时的视图: – V$SESSION_WAIT、V$SYSTEM_EVENT、 V$SERVICE_EVENT、 V$SESSION_EVENT(TIME_WAITED_MICRO 列) – V$SQL、V$SQLAREA(CPU_TIME、ELAPSED_TIME 列) – V$LATCH、V$LATCH_PARENT、V$LATCH_CHILDREN (WAIT_TIME 列) – V$SQL_WORKAREA、V$SQL_WORKAREA_ACTIVE (ACTIVE_TIME 列) • 包含毫秒定时的视图: – V$ENQUEUE_STAT(CUM_WAIT_TIME 列) 版权所有 © 2006,Oracle。保留所有权利。 时间模型:概览 • 时间模型是一组统计数据,对在 Oracle 数据库内部所 用时间的位置提供了一个概览。 • 所有统计数据使用相同的维:时间。 • 统计数据可以通过下列视图访问: – V$SYS_TIME_MODEL – V$SESS_TIME_MODEL • 数据库时间代表用于数据库调用的总时间。 • 优化目标是缩短数据库时间。 • 使用数据库时间可以评价对数据库的任何实体的性能 影响。 数据库时间 Java SQL 分析 连 接 PLSQL 版权所有 © 2006,Oracle。保留所有权利。 时间模型统计数据的层次结构 失败分析(共享内存不足)所用时间 数据库时间 数据库 CPU 连接管理调用所用时间 序列装入所用时间 SQL 执行所用时间 分析所用时间 硬分析所用时间 硬分析(共享标准)所用时间 硬分析(绑定不匹配)所用时间 失败分析所用时间 PL/SQL 执行所用时间 入站 PL/SQL RPC 所用时间 PL/SQL 编译所用时间 Java 执行所用时间 重复绑定所用时间 后台所用时间 后台 CPU 时间 RMAN CPU 时间 (备份/还原) 版权所有 © 2006,Oracle。保留所有权利。 练习概览:使用基本工具 本练习包含以下主题: • 使用预警日志信息进行优化 • 查看系统统计数据 • 查看等待事件 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 确定可以在优化时使用的动态性能视图 • 确定预警日志文件的关键优化组件 • 确定用户跟踪文件的关键优化组件 • 使用动态性能视图查看统计数据和等待事件 版权所有 © 2006,Oracle。保留所有权利。 度量、预警及基线 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成下列工作: • 通过使用度量历史记录视图查看度量 • 创建度量阈值 • 查看预警 • 创建度量基线 • 启用自适应阈值 版权所有 © 2006,Oracle。保留所有权利。 度量、预警及基线 度量:累计统计数据中的更改率 预警:监视的度量超过阈值时生成的事件 基线:从“正常运行的数据库”中收集的用于进行性能比较 的数据 版权所有 © 2006,Oracle。保留所有权利。 基本统计数据的限制 30 分钟 30 分钟 V$SYSSTAT 物理读取数I/O I/O 时间 时间 低比率 高比率 统计数据一直保持增长 2M 2M 版权所有 © 2006,Oracle。保留所有权利。 典型增量工具 • 需要比较对应于两个时间点的统计数据。 • 下列工具可生成增量: – Statspack – AWR 报表 – 自定义脚本 版权所有 © 2006,Oracle。保留所有权利。 Oracle Database 10g 解决方案:度量 基本统计数据 1 重做生成 度量 1 度量 2 重做生成/Tx 客户机 1 客户机 2 客户机 3 客户机 4 V$METRICNAME V$SYSMETRIC V$SESSMETRIC V$FILEMETRIC V$EVENTMETRIC MMON 用户提交 用户回退 V$WAITCLASSMETRICV$SERVICEMETRIC 版权所有 © 2006,Oracle。保留所有权利。 度量的优点 分析差值 查询度量 没有度量 具有度量 DBA 版权所有 © 2006,Oracle。保留所有权利。 查看度量历史记录信息 • 在内存中: – V$SYSMETRIC_HISTORY – V$FILEMETRIC_HISTORY – V$WAITCLASSMETRIC_HISTORY – V$SERVICEMETRIC_HISTORY – V$SESSION_WAIT_HISTORY(最近 10 个事件) • 在磁盘上: – DBA_HIST_SYSMETRIC_SUMMARY – DBA_HIST_SYSMETRIC_HISTORY(仅预警) – DBA_HIST_SESSMETRIC_HISTORY – DBA_HIST_SYSTEM_EVENT(累计) – DBA_HIST_FILEMETRIC_HISTORY(仅预警) – DBA_HIST_FILESTATXS(累计) – DBA_HIST_WAITCLASSMET_HISTORY(预警) 版权所有 © 2006,Oracle。保留所有权利。 查看度量的详细信息 版权所有 © 2006,Oracle。保留所有权利。 统计数据直方图 30 分钟 V$SYSSTAT 物理读取数I/O 时间 高比率 1 2 4 8 16 32 64 128 256 512 … 222 等待的次数 时段 V$FILE_HISTOGRAM 是否为实际问题? 5500 版权所有 © 2006,Oracle。保留所有权利。 直方图视图 • V$EVENT_HISTOGRAM:用于如“DB 文件连续读取” 之类的每个事件 • V$FILE_HISTOGRAM:用于基于每个数据文件的单块 读取数 • V$TEMP_HISTOGRAM:用于基于每个临时文件的单块 读取数 版权所有 © 2006,Oracle。保留所有权利。 服务器生成的预警 服务器预警队列 度量超过阈值 AWR Oracle Enterprise Manager Oracle 实例 版权所有 © 2006,Oracle。保留所有权利。 DNT 使用模型 设置通知规则(寻呼、电子邮件) 更正问题 查看预警详细资料和建议 通过设置阈值启用预警 接收通知 验证问题是否已解决 版权所有 © 2006,Oracle。保留所有权利。 设置阈值 版权所有 © 2006,Oracle。保留所有权利。 创建和测试预警 1. 指定阈值。 2. 创建测试用例。 3. 检查预警。 2 1 3 版权所有 © 2006,Oracle。保留所有权利。 “度量”视图和“预警”视图 最新度量 度量历史记录 V$SYSMETRIC_HISTORY V$SYSMETRIC V$SERVICEMETRIC V$METRICNAME ... DBA_HIST_SYSMETRIC_HISTORY ... DBA_OUTSTANDING_ALERTS DBA_ALERT_HISTORY DBA_THRESHOLDS V$ALERT_TYPES ... 服务器预警 版权所有 © 2006,Oracle。保留所有权利。 用户定义的 SQL 度量 版权所有 © 2006,Oracle。保留所有权利。 练习概览:使用度量 本练习包含以下主题: • 使用度量历史记录视图查看度量 • 创建度量阈值 • 查看预警 • 清除预警 版权所有 © 2006,Oracle。保留所有权利。 使用度量基线 • 启用度量基线 • 创建度量基线 – 静态基线 – 移动窗口基线 • 查看基线统计数据 • 对基线设置阈值 – 固定阈值(最大百分比) – 自适应阈值 版权所有 © 2006,Oracle。保留所有权利。 启用度量基线 版权所有 © 2006,Oracle。保留所有权利。 创建静态度量基线 版权所有 © 2006,Oracle。保留所有权利。 创建静态度量基线 版权所有 © 2006,Oracle。保留所有权利。 时间分组 基线时段 分组的时间 白天和夜晚一天中的 每小时 工作日和周末一周中的 每天一天 一周 8am 11am 7am 7am7pm 7am 7am7pm 周一 周日 周一 周日 周一 周六和周日 按日 按周 版权所有 © 2006,Oracle。保留所有权利。 时间分组:注意事项 否数据是否充足? 一周中的每天分组依据 7 天移动窗口大小 是数据是否充足? 一周中的每天分组依据 14 天移动窗口大小 是数据是否充足? 工作日和周末分组依据 7 天移动窗口大小 周一 周二 周三 周四 周五 周六 周日 数据点 工作日 周末 周一 周二 周三 周四 周五 周六 周日 数据点 数据点 充足水平 1 周前 2 周前 版权所有 © 2006,Oracle。保留所有权利。 激活静态度量基线 版权所有 © 2006,Oracle。保留所有权利。 激活移动窗口度量基线 版权所有 © 2006,Oracle。保留所有权利。 查看度量基线统计数据 版权所有 © 2006,Oracle。保留所有权利。 设置自适应预警阈值 版权所有 © 2006,Oracle。保留所有权利。 Setting Adaptive Alert Thresholds 版权所有 © 2006,Oracle。保留所有权利。 度量基线和自适应阈值:注意事项 • 必须使用 Oracle Enterprise Manager 启用基线。 • 只能定义一个移动窗口基线。 • 可定义多个静态基线。 • 一次只能有一个基线处于活动状态。 • 自适应阈值需要一个活动基线。 版权所有 © 2006,Oracle。保留所有权利。 合格度量的分类 数据库时间(每秒厘秒数) 响应时间(每事务处理) 系统响应时间(厘秒) 性能度量 执行(每秒) 生成重做(每秒) 网络字节(每秒) 物理写入数(每秒) 物理读取数(每秒) 当前登录计数 用户调用(每秒) 事务处理数(每秒) 工作量大小度量 数据库块更改(每事务处理) 入队请求(每事务处理) 总分析数(每事务处理) 会话逻辑读取数(每事务处理) 工作量类型度量 版权所有 © 2006,Oracle。保留所有权利。 度量基线:规范化的视图 版权所有 © 2006,Oracle。保留所有权利。 配置规范化度量 版权所有 © 2006,Oracle。保留所有权利。 “自适应阈值”和“所有度量”页 版权所有 © 2006,Oracle。保留所有权利。 练习概览:使用基线 本练习包含以下主题: • 创建静态度量基线 • 查看基线数据 • 启用自适应阈值 • 查看与基线之间的偏差 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 通过使用度量历史记录视图查看度量 • 创建度量阈值 • 查看预警 • 创建度量基线 • 启用自适应阈值 版权所有 © 2006,Oracle。保留所有权利。 使用 Statspack 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成下列工作: • 安装 Statspack • 创建 Statspack 快照 • 生成 Statspack 报表 • 识别 Statspack 报表的主要部分 版权所有 © 2006,Oracle。保留所有权利。 Statspack 简介 • Statspack 是一组脚本,用于捕获和报告 Oracle 数据 库中的性能数据 • 使用 Statspack 脚本,您可以: – 通过获取“快照”捕获实例数据 – 将数据库中的快照数据存储在单独的方案中 – 创建两个快照之间的报表 – 将快照标记为基线信息 – 将报表用作性能优化方法的一部分 版权所有 © 2006,Oracle。保留所有权利。 Statspack 脚本 • 通过使用 spcreate.sql 安装 Statspack。 • 使用 statspack.snap 收集统计数据。 • 使用 spauto.sql 自动执行统计数据的收集。 • 通过使用 spreport.sql 或 sprepsql.sql 生成 报表。 • 使用 statspack.purge 或 sppurge.sql 清除 Statspack 数据。 • 使用 sptrunc.sql 截断所有 Statspack 表。 • 使用 spdrop.sql 删除 Statspack 资料档案库。 • 使用 spexp.par 导出 Statspack 资料档案库。 版权所有 © 2006,Oracle。保留所有权利。 安装 Statspack • 安装 Statspack: – 以交互方式提供所请求的参数 – 在会话中针对批处理模式定义参数 • • • • • spcreate.sql 脚本将创建要拥有 Statspack 资料档 案库和程序包的 PERFSTAT 用户 SQL> connect / as sysdba SQL> define default_tablespace='SYSAUX' SQL> define temporary_tablespace='TEMP' SQL> define perfstat_password='erg8oiw' SQL> @?/rdbms/admin/spcreate SQL> undefine perfstat_password 版权所有 © 2006,Oracle。保留所有权利。 捕获 Statspack 快照 • 捕获快照: • • • 使用函数确定快照编号: • • • • 自动进行快照收集: SQL> connect perfstat/erg8oiw SQL> execute statspack.snap; SQL> variable snap number; SQL> begin :snap := statspack.snap; end;/ SQL print snap SQL> connect perfstat/erg8oiw SQL> @spauto 版权所有 © 2006,Oracle。保留所有权利。 配置快照数据捕获 • 临时更改捕获变量: • • 更改默认值并获取快照: • • • 修改默认值而不获取快照: • • • 将会话详细资料包含在快照中: SQL> execute statspack.snap(i_snap_level=>6); SQL> execute statspack.snap - (i_snap_level=>10, i_modify_parameter=>'true'); SQL> execute statspack.modify_statspack_parameter – (i_snap_level=>10, i_buffer_gets_th=>10000,- i_disk_reads_th=>1000); SQL> execute statspack.snap(i_session_id=>3); 版权所有 © 2006,Oracle。保留所有权利。 Statspack 快照级别 父闩锁和子闩锁10 段级统计数据7 SQL 计划和 SQL 计划的使用情况6 SQL 语句5 一般性能0 捕获的统计数据级 => 默认情况下,AWR 捕获全部统计数据! 版权所有 © 2006,Oracle。保留所有权利。 Statspack 基线和清除 SQL> exec statspack.clear_baseline - 2 (to_date('13-DEC-2004 23:00','DD-MON-YYYY HH24:MI'),- 3 to_date('14-FEB-2005 02:00','DD-MON-YYYY HH24:MI')); SQL> exec statspack.make_baseline – 2 (to_date('31-AUG-2005 09:00','DD-MON-YYYY HH24:MI'),- 3 to_date('31-AUG-2005 12:00','DD-MON-YYYY HH24:MI')); SQL> exec statspack.statspack.make_baseline(2,4,false); SQL> exec statspack.make_baseline - 2 (i_begin_snap => 45, i_end_snap => 50); SQL> exec statspack.purge – 2 (i_begin_date=>to_date('01-JAN-2003','DD-MON-YYYY'),- 3 i_end_date =>to_date('02-JAN-2003','DD-MON-YYYY'),- 4 i_extended_purge=>TRUE); 版权所有 © 2006,Oracle。保留所有权利。 使用 Statspack 生成报告 SQL> connect perfstat/perfstat_password SQL> define begin_snap=1 SQL> define end_snap=2 SQL> define report_name=batch_run SQL> @?/rdbms/admin/spreport SQL> connect perfstat/perfstat_password SQL> @?/rdbms/admin/spreport SQL> connect perfstat/perfstat_password SQL> define begin_snap=39 SQL> define end_snap=40 SQL> define hash_value=1988538571 SQL> define report_name=batch_sql_run SQL> @sprepsql 版权所有 © 2006,Oracle。保留所有权利。 Statspack 注意事项 • 设置 STATISICS_LEVEL = TYPICAL。 • 设置 TIMED_STATISTICS = TRUE 以收集定时信息。 • 避免 Statspack 和 AWR 之间出现重叠。 • 选择合理的快照间隔。 • 报表输出中显示“#######”时调整间隔。 • 收集 PERFSTAT 方案的优化程序统计数据。 版权所有 © 2006,Oracle。保留所有权利。 Statspack 和 AWR 报表 Statspack 和 AWR 报表被设计为自上而下使用: • 前几页中包含以下部分: – 概要信息 – 加载概要文件(用于基线) – 实例效率(用于基线) – 前 5 个计时事件 – 时间模型 • 在以下页中显示详细数据: – SQL – IO – 等待统计数据 版权所有 © 2006,Oracle。保留所有权利。 • 从顶部的概要数据开始: – 前 5 个计时事件 – 等待事件和后台等待事件 – 等待事件直方图(仅存在于 Statspack 中) – 加载概要文件(用于基线) – 实例效率(用于基线) – 时间模型 • 细化到特定部分 – 由顶级等待事件表示 阅读 Statspack 或 AWR 报表 C O N F I R M 版权所有 © 2006,Oracle。保留所有权利。 Statspack/AWR 报表细化部分 CONT:块争用 CPU:CPU 占用 ENQ:入队 IO:IO 占用 LAT:闩锁争用 LC:库高速缓存 MEM:内存占用 PGAM:PGA 内存占用 RBS:回退段 UNDO:自动还原 SP:共享池 按读取数排序的 SQL IO 按分析调用数排序的 SQL LC 按获取数排序的 SQL CPU 按用时排序的 SQL IO 按 CPU 排序的 SQL CPU 虚拟内存分页MEM 实例 CPUCPU 主机 CPUCPU 共享池统计数据LC 部分细化 版权所有 © 2006,Oracle。保留所有权利。 报表细分示例 • 如果顶级计时事件与 I/O 等待数有关,请查看以下部分: – 按读取数排序的 SQL – 按用时排序的 SQL – 表空间 IO 统计数据 – 文件 IO 统计数据 – 文件 IO 直方图 • 如果顶级计时事件与 CPU 占用率有关,请查看以下部分: – 加载概要文件 – 时间模型 – 按 CPU 排序的 SQL – 按获取数排序的 SQL 版权所有 © 2006,Oracle。保留所有权利。 “加载概要文件”部分 • 允许查看应用程序的特性 • 可指向潜在问题: – 硬分析率很高 – I/O 率很高 – 登录率很高 • 如果有可比较的基线,则更有用 • 回答“哪些内容发生了更改?” – Txn/sec 更改表示工作量发生了更改 – 重做大小 /txn 表示事务处理组合发生了更改 – 物理读取数 /txn 表示 SQL 或计划发生了更改 版权所有 © 2006,Oracle。保留所有权利。 “时间模型”部分 • “时间模型”部分列出了一组统计数据,可以大致了解 各操作在 Oracle 数据库服务器中所花费的时间。 • 它是基于 V$SYS_TIME_MODEL 进行报告的。 • 度量任何数据库实体的性能影响,并将其细化。 版权所有 © 2006,Oracle。保留所有权利。 Statspack 和 AWR Statspack 方案 AWR 表 移植 使用 Statspack 方案的 旧应用程序代码 版权所有 © 2006,Oracle。保留所有权利。 练习概览:使用 Statspack 本练习包含以下主题: • 安装 Statspack • 创建 Statspack 快照 • 生成 Statspack 报表 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 安装 Statspack • 创建 Statspack 快照 • 生成 Statspack 报表 • 识别 Statspack 报表的主要部分 版权所有 © 2006,Oracle。保留所有权利。 使用自动工作量资料档案库 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成下列工作: • 创建和管理自动工作量资料档案库 (AWR) 快照 • 生成 AWR 报表 • 创建快照集和比较期间 • 生成 ADDM 报表 • 生成 ASH 报表 版权所有 © 2006,Oracle。保留所有权利。 自动工作量资料档案库:概览 SGA V$ DBA_* ADDM 自优化组件自优化组件 … 内部客户机 外部客户机 EM SQL*Plus … 有效内存中 统计信息收集 AWR 快照 MMON 版权所有 © 2006,Oracle。保留所有权利。 自动工作量资料档案库数据 • 基本统计信息: – SQL 和优化程序统计信息 – OS 统计信息 – 等待类 – 时间统计信息 • 度量 • 活动会话历史记录 • 指导结果 • 快照统计信息 • 数据库功能使用情况 V$SYSSTAT V$SQL V$SEGMENT_STATISTICS V$SYS_TIME_MODEL V$SYSMETRIC_HISTORY V$SYSTEM_WAIT_CLASS V$OSSTAT V$ACTIVE_SESSION_HISTORY DBA_ADVISOR_* DBA_HIST_* DBA_FEATURE_* DBA_HIGH_WATER_MARK_* DBA_TAB_STATS_HISTORY 版权所有 © 2006,Oracle。保留所有权利。 工作量资料档案库 SYSAUX SGA 内存中统计信息 6:00 a.m. 7:00 a.m. 8:00 a.m. 快照 1 快照 2 快照 3 快照 49:00 a.m. 9:30 a.m. ADDM 查找首要问题 MMON 版权所有 © 2006,Oracle。保留所有权利。 AWR 快照集 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE ( - start_snap_id IN NUMBER , end_snap_id IN NUMBER , baseline_name IN VARCHAR2); 过去的相关时段 版权所有 © 2006,Oracle。保留所有权利。 AWR 快照清除策略 SYSAUX 表空间 sys 方案 快照 快照 快照 快照 7 天 快照 MMON 每晚 60 分钟 版权所有 © 2006,Oracle。保留所有权利。 AWR 快照设置 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ( - retention IN NUMBER DEFAULT NULL, interval IN NUMBER DEFAULT NULL, topnsql IN NUMBER DEFAULT NULL); 版权所有 © 2006,Oracle。保留所有权利。 DNT 和 AWR 版权所有 © 2006,Oracle。保留所有权利。 在 EM 中生成 AWR 报表 版权所有 © 2006,Oracle。保留所有权利。 在 SQL*Plus 中生成 AWR 报表 版权所有 © 2006,Oracle。保留所有权利。 快照集和时段比较 版权所有 © 2006,Oracle。保留所有权利。 比较期间:优点 DBA ≠ DBA ≠ 版权所有 © 2006,Oracle。保留所有权利。 比较期间:常规信息 版权所有 © 2006,Oracle。保留所有权利。 比较期间:报表 版权所有 © 2006,Oracle。保留所有权利。 AWR 数据 指导结果 AWR SQL 统计信息基本统计信息 度量 ASH 版权所有 © 2006,Oracle。保留所有权利。 DBMS_WORKLOAD_REPOSITORY 程序包 用于修改快照设置的过程MODIFY_SNAPSHOT_SETTINGS 用于删除单个 AWR 基线的过程DROP_BASELINE 用于创建单个 AWR 基线的过程CREATE_BASELINE 用于删除一组快照的过程DROP_SNAPSHOT_RANGE 用于立即创建手动快照的过程CREATE_SNAPSHOT 说明过程名称 版权所有 © 2006,Oracle。保留所有权利。 ADDM 性能监视 快照 ADDM 内存中 统计信息 AWR SGA 60 分钟 ADDM 结果 MMON 版权所有 © 2006,Oracle。保留所有权利。 ADDM 和数据库时间 用户 1 用户 2 用户 n 连接 执行 提取 提取 提取 执行 广域网 应用程序服务器 局域网 Oracle 数据库 用户发送请求 用户获得响应 数据库时间 系统时钟时间 版权所有 © 2006,Oracle。保留所有权利。 数据库时间图和 ADDM 方法学 用户连接 根节点 SQL 优化 SQL 执行 缓冲区高速缓存 大小不足 I/O 能力 数据库锁 I/O 带宽不足 CPU 能力 维 1 维 2 故障现象 根本原因 版权所有 © 2006,Oracle。保留所有权利。 检测到的首要性能问题 Oracle Streams、AQ 和 RMAN 应用程序使用情况 配置问题 分析 I/O 问题 顶层 SQL PL/SQL、Java 时间 检查点原因 锁和 ITL 争用 RAC 服务问题 热块和对象 w/SQL 内存大小不足 登录/注销过多 未被 Statspack 检测到 ADDM 确定 首要问题 版权所有 © 2006,Oracle。保留所有权利。 DNT 和 ADDM 查找结果 版权所有 © 2006,Oracle。保留所有权利。 ADDM 分析结果 1 2 3 版权所有 © 2006,Oracle。保留所有权利。 ADDM 建议案 版权所有 © 2006,Oracle。保留所有权利。 DNT 和 ADDM 任务 版权所有 © 2006,Oracle。保留所有权利。 更改 ADDM 属性 1. 确保 STATISTICS_LEVEL 已设置为 TYPICAL 或 ALL。 2. I/O 性能的 ADDM 分析取决于 I/O 子系统的预期速度: a. 度量 I/O 子系统速度。 b. 设置预期速度。 SQL> exec DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(- 'ADDM', 'DBIO_EXPECTED', 8000); SELECT parameter_value, is_default FROM dba_advisor_def_parameters WHERE advisor_name = 'ADDM' AND parameter_name = 'DBIO_EXPECTED'; 版权所有 © 2006,Oracle。保留所有权利。 使用 SQL 检索 ADDM 报表 SELECT dbms_advisor.GET_TASK_REPORT(task_name) FROM dba_advisor_tasks WHERE task_id = ( SELECT max(t.task_id) FROM dba_advisor_tasks t, dba_advisor_log l WHERE t.task_id = l.task_id AND t.advisor_name = 'ADDM' AND l.status = 'COMPLETED'); SQL> @?/rdbms/admin/addmrpt … Enter value for begin_snap: 8 Enter value for end_snap: 10 … Enter value for report_name: Generating the ADDM report for this analysis ... 版权所有 © 2006,Oracle。保留所有权利。 活动会话历史记录:概览 • 存储数据库时间的历史记录 • 系统中的样本会话活动包括: – SQL 语句的 SQL 标识符 – 对象编号、文件编号和块编号 – 等待事件标识符和参数 – 会话标识符和会话序列号 – 模块和操作名称 – 会话的客户机标识符 – 服务散列标识符 – 阻塞会话 • 对于首次故障分析始终处于打开状态 • 无需重放工作量 版权所有 © 2006,Oracle。保留所有权利。 活动会话历史记录:技术 SGA 统计信息 V$SESSION ASH 最近历史记录 滚动 缓冲区 工作量资料档案库 1 秒 1 秒 1 秒 每 60 分钟 MMON MMNL 到达 66% V$ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SESSION_HISTORY(已分区) 不使用 SQL 直接路径插入 DBA_HIST_ACTIVE_SESSION_HISTORY 十分之一 查看器已解锁 版权所有 © 2006,Oracle。保留所有权利。 ASH 取样率:示例 会话 1 等待 I/O 等待锁 等待块 等待 I/O 等待 I/O 等待锁 等待 I/O 等待块 … … 会话 1 等待 I/O 会话 1 等待 I/O 会话 1 等待块 会话 2 活动 会话 3 活动 会话 n 活动 … 活动 非活动的 会话 1 秒 1 秒 ASH … V$ACTIVE_SESSION_HISTORY 时间 版权所有 © 2006,Oracle。保留所有权利。 访问 ASH 数据 • 转储到跟踪文件 • V$ACTIVE_SESSION_HISTORY • DBA_HIST_ACTIVE_SESS_HISTORY • ASH 报表 • EM 诊断程序包性能页 • ADDM 版权所有 © 2006,Oracle。保留所有权利。 将 ASH 转储到文件 生成的文件包含指定的最后分钟数内的 ASH 样本: SQL> oradebug setmypid SQL> oradebug dump ashdump 10 <<>> DBID, INSTANCE_NUMBER, SAMPLE_ID, SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#, USER_ID, SQL_ID, SQL_CHILD_NUMBER, SQL_PLAN_HASH_VALUE, SERVICE_HASH, SESSION_TYPE, SQL_OPCODE, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL# QC_SESSION_ID, QC_INSTANCE_ID, XID, CURRENT_OBJ#, CURRENT_FILE#, CURRENT_BLOCK#, EVENT_ID, SEQ#, P1, P2, P3, WAIT_TIME, TIME_WAITED, FORCE_MATCHING_SIGNATURE, PROGRAM, MODULE, ACTION, CLIENT_ID <<>> <<>> 1090770270,1,1317127,"08-26-2005 01:11:40.505471000", 162, 1, 0, "", 0, 0, 165959219, 2, 0, 4294967295, 0,0,0,,5129,1,11242,86156091,1664,0,0,0,0,64620,0,"oracle@edrsr14p1 (CJQ0)","","","",1090770270,1,1317116,"08-26-2005 01:11:29.505471000", 142,20,24, "0hbv80w9ypy0n",0,3304045827, 3427055676, 1,3,4294967291, 0,0,0,,8751, 3,2486, 1421975091,24975,1413697536,1,0,620,0,17258348159868772889,"emagent@edrsr14p1 (TNS V1- V3)","emagent@edrsr14p1 (TNS V1-V3)","","" … <<>> 版权所有 © 2006,Oracle。保留所有权利。 筛选 ASH • GROUP BY 和 COUNT – 非空闲已用时间的代理 – 实际花费时间的比例 • 可以分析任何时间切片 • 示例:返回上一分钟的大多数活动 SQL SELECT sql_id, count(*), round(count(*)/sum(count(*)) over (), 2) pctload FROM v$active_session_history WHERE sample_time > sysdate -1/24/60 and session_type <> 'BACKGROUND' GROUP BY sql_id ORDER BY count(*) desc; 版权所有 © 2006,Oracle。保留所有权利。 生成 ASH 报表 版权所有 © 2006,Oracle。保留所有权利。 ASH 报表脚本 SQL> define dbid = ''; SQL> define inst_num = ''; SQL> define report_type = 'html'; SQL> define begin_time = '09:00'; SQL> define duration = 480; SQL> define report_name = '/tmp/sql_ashrpt.txt'; SQL> define slot_width = ''; SQL> define target_session_id = ''; SQL> define target_sql_id = 'abcdefghij123'; SQL> define target_wait_class = ''; SQL> define target_service_hash = ''; SQL> define target_module_name = ''; SQL> define target_action_name = ''; SQL> define target_client_id = ''; SQL> @?/rdbms/admin/ashrpti 版权所有 © 2006,Oracle。保留所有权利。 ASH 报表:“常规信息”区域 V$ACTIVE_SESSION_HISTORY DBA_HIST_ACTIVE_SESSION_HISTORY 版权所有 © 2006,Oracle。保留所有权利。 ASH 报表结构 版权所有 © 2006,Oracle。保留所有权利。 ASH 报表:一段时间内的活动 版权所有 © 2006,Oracle。保留所有权利。 练习概览:使用基于 AWR 的工具 本练习包含以下主题: • 生成 AWR 报表 • 创建快照的保留集 • 生成 ADDM 报表 • 生成 ASH 报表 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 创建和管理 AWR 快照 • 生成 AWR 报表 • 创建快照集和比较期间 • 生成 ADDM 报表 • 生成 ASH 报表 版权所有 © 2006,Oracle。保留所有权利。 被动式优化 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成下列工作: • 使用 Oracle Enterprise Manager 页确定性能问题 • 消除操作系统问题 版权所有 © 2006,Oracle。保留所有权利。 问题出在何处 性能问题可以分为以下类别: • 操作系统 (OS) • 应用程序 • 实例 版权所有 © 2006,Oracle。保留所有权利。 OS 问题 操作系统问题: • 内存 • CPU • I/O(磁盘和网络) 操作系统 应用程序 实例 版权所有 © 2006,Oracle。保留所有权利。 数据库主页 版权所有 © 2006,Oracle。保留所有权利。 OS 统计信息和 Oracle Enterprise Manager 版权所有 © 2006,Oracle。保留所有权利。 OS 统计信息 在最后 60 秒内正在运行或处于就绪状态的进程数 说明统计信息名称 物理内存的字节总数PHYSICAL_MEMORY_BYTES 由于分页而产生的页调进/调出的数据字节数VM_PAGE_IN/OUT_BYTES Oracle 进程已处于就绪状态(正在等待 CPU 处理其当前活动 资源计划中的相应使用者组)的百分秒数 RSRC_MGR_CPU_WAIT_TIME 进程已处于就绪状态(正在等待调度)的百分秒数OS_CPU_WAIT_TIME 进程等待 I/O 完成的平均百分秒数IOWAIT_TIME 进程处于空闲/繁忙或其它状态的百分秒数IDLE/BUSY/USER/NICE/SYS_TIME 可用 CPU 或处理器数NUM_CPUS NUM_CPU_SOCKETS 可用 CPU 套接字数 NUM_CPU_CORES 可用 CPU 核心数 LOAD 版权所有 © 2006,Oracle。保留所有权利。 操作系统 应用程序 实例 应用程序问题 应用程序问题可以分为以下类别: • 资源串行化 • 关系设计 • 会话管理 • 游标管理 • 程序设计 版权所有 © 2006,Oracle。保留所有权利。 实例问题 实例问题可以分为以下类别: • 内存 – SGA – PGA • I/O – 物理 I/O – 数据布局 • 争用 – 锁 – 闩锁 – 串行化的资源 操作系统 应用程序 实例 版权所有 © 2006,Oracle。保留所有权利。 体系结构 Spfile 临时文件 数据文件 还原 闪回日志 重做 日志文件 归档 日志文件 控制文件 SYSTEM SYSAUX 更改 跟踪文件 口令文件 PMON SMON RECO MMON MMNL PSP0 MMAN DBWn LGWR CKPT CJQ0 S000 D000QMNC Qnnn FMON ARCnCTWR RVWR 固定 大小 共享池 流池 默认 缓冲区 高速缓存 保留 缓冲区 高速缓存 循环 缓冲区 高速缓存 nK 缓冲区 高速缓存 重做日志 缓冲区 ASH 缓冲区 排序 区池 全局 上下文池 SGA 闪回 缓冲区 实例 Java 池大型池 版权所有 © 2006,Oracle。保留所有权利。 性能管理方法 CPU/等待 SQL 会话 主机 Oracle CPU 瓶颈 内存瓶颈 使用 ASH 和 AWR 版权所有 © 2006,Oracle。保留所有权利。 被动式优化的性能页 主页 性能 正在等待的 活动会话 直方图SQL 详细资料 运行 ADDMASH 报表 版权所有 © 2006,Oracle。保留所有权利。 数据库主页 版权所有 © 2006,Oracle。保留所有权利。 数据库性能页 版权所有 © 2006,Oracle。保留所有权利。 活动会话等待页 版权所有 © 2006,Oracle。保留所有权利。 SQL 详细资料 版权所有 © 2006,Oracle。保留所有权利。 历史记录数据视图 版权所有 © 2006,Oracle。保留所有权利。 诊断挂起或速度非常慢的数据库 • 用于在数据库执行速度很慢或已挂起时进行问题分析的 新功能: – 直接访问 SGA 以进行性能监视(内存访问模式) V$SESSION V$SESSION_WAIT V$SYSTEM_EVENT V$SYSSTAT – 使用 Oracle Enterprise Manger 进行挂起分析 • 同时支持 Oracle9i 数据库 版权所有 © 2006,Oracle。保留所有权利。 使用内存访问模式 版权所有 © 2006,Oracle。保留所有权利。 使用挂起分析页 版权所有 © 2006,Oracle。保留所有权利。 练习概览:被动式优化 本练习包含以下主题: • 使用 Oracle Enterprise Manger 性能页确定或消除 OS 问题 • 使用 Oracle Enterprise Manger 性能页确定数据库 在应用程序负荷方面的性能问题 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 使用 Oracle Enterprise Manager 页确定性能问题 • 消除和确定操作系统问题 版权所有 © 2006,Oracle。保留所有权利。 优化共享池 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成下列工作: • 诊断共享池问题 • 调整共享池的大小 • 调整保留区域的大小 • 将对象保留在共享池中 版权所有 © 2006,Oracle。保留所有权利。 共享池体系结构 共享池的主要组件包括: • 库高速缓存 • 数据字典高速缓存 • 共享服务器会话的用户全局区 (UGA) 数据库缓冲 区高速缓存 重做日志 缓冲区 共享池 库高速缓存 数据字典高速缓存 共享区域 大型池 UGA 版权所有 © 2006,Oracle。保留所有权利。 共享池操作 可通过 LRU 算法来管理共享池 • 新对象需要内存分配 • 可重新创建的对象将在高速缓存中到期释放 • 对象由内存块构成 • 内存分配是一个整块 • 块是连续的 版权所有 © 2006,Oracle。保留所有权利。 库高速缓存 • 存储与游标关联的复杂对象元数据 • 存储可供用户共享的 SQL 语句和 PL/SQL 块 • 防止重新分析语句 版权所有 © 2006,Oracle。保留所有权利。 SQL 和 PL/SQL 存储 SQLAREA SELECT 语句 2 的 游标上下 文区域 SELECT 语句 1 的 游标上下 文区域 共享池 SELECT 语句 2 SELECT 语句 1 SELECT 语句 1 版权所有 © 2006,Oracle。保留所有权利。 游标的使用和分析 分析过程: 1. 查找并执行打开的游标。 2. 在会话高速缓存中查找关闭的游标。 3. 搜索散列链(软分析)。 4. 构建游标(硬分析)。 打开的游标 关闭的游标 会话内存 (UGA) 共享池 (SGA) 游标句柄 散列链 1 2 3 4 版权所有 © 2006,Oracle。保留所有权利。 重要的共享池闩锁 • latch: shared pool 保护共享池中的内存分配。 • latch: library cache 查找共享池中相匹配的 SQL。 版权所有 © 2006,Oracle。保留所有权利。 互斥 互相排斥的对象允许: • 在不产生损坏的情况下共享资源 • 对读取的共享访问 • 对更新的独占访问 • 每个对象都有自己的互斥 版权所有 © 2006,Oracle。保留所有权利。 互斥的优点 使用互斥可提高性能。互斥: • 更小更快 • 产生争用的可能性较小 • 可替换闩锁和 pin 版权所有 © 2006,Oracle。保留所有权利。 互斥视图和统计数据 • 互斥视图: – V$MUTEX_SLEEP – V$MUTEX_SLEEP_HISTORY • 互斥等待事件: – cursor:mutex X – cursor:mutex S – cursor:pin X – cursor:pin S – cursor:pin S wait on X 版权所有 © 2006,Oracle。保留所有权利。 互斥保护的操作 从 10.2.0.2 开始,互斥保护: • 在 V$SQLSTAT 中进行的选择 • 对子游标列表的搜索 版权所有 © 2006,Oracle。保留所有权利。 Statspack/AWR 指示器 Statspack 和 AWR 报表包含以下指示器: • 加载概要文件 • 实例效率 • 顶级等待事件 • 时间模型 版权所有 © 2006,Oracle。保留所有权利。 加载概要文件 Load Profile Per Second Per Transaction ~~~~~~~~~~~~ --------------- --------------- User calls: 4.29 21.78 Parses: 188.09 954.20 Hard parses: 80.00 405.85 % Blocks changed per Read: 0.18 Recursive Call %: 99.78 Rollback per transaction %: 9.76 Rows per Sort: 8.47 版权所有 © 2006,Oracle。保留所有权利。 实例效率 Instance Efficiency Percentages ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.87 In-memory Sort %: 100.00 Library Hit %: 87.90 Soft Parse %: 57.47 Execute to Parse %: 22.50 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 83.16 % Non-Parse CPU: 17.88 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 92.75 92.97 % SQL with executions>1: 72.62 72.28 % Memory for SQL w/exec>1: 92.35 92.17 版权所有 © 2006,Oracle。保留所有权利。 顶级等待 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------- ------------ ----------- ------ ------ CPU time 190 98.0 latch: library cache 76 1 19 .8 latch: shared pool 49 1 16 .4 db file sequential read 1,343 0 0 .2 log file parallel write 54 0 5 .1 ------------------------------------------------------- 版权所有 © 2006,Oracle。保留所有权利。 时间模型 Time Model System Stats -> Ordered by % of DB time desc, Statistic name % of Statistic Time (s) DB time ----------------------------------- ---------- --------- sql execute elapsed time 399.5 95.6 DB CPU 269.3 64.4 parse time elapsed 126.8 30.3 hard parse elapsed time 111.9 26.8 PL/SQL compilation elapsed time 6.6 1.6 PL/SQL execution elapsed time 5.9 1.4 connection management call elapsed 4.9 1.2 failed parse elapsed time 4.0 1.0 hard parse (sharing criteria) elaps 2.3 .5 sequence load elapsed time 0.5 .1 hard parse (bind mismatch) elapsed 0.2 .0 repeated bind elapsed time 0.1 .0 DB time 417.9 版权所有 © 2006,Oracle。保留所有权利。 库高速缓存活动 Library Cache Activity DB/Inst: ORCL/orcl Snaps: 61-71 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations ---------- --------- ------ -------------- ------ ------ -------- BODY 33 27.3 450 14.0 46 0 CLUSTER 49 0.0 200 3.0 6 0 INDEX 5 100.0 35,300 0.0 0 0 SQL AREA 18,880 96.9 89,904 36.5 496 0 版权所有 © 2006,Oracle。保留所有权利。 术语 • 获取数:(分析)查找名称空间对象的次数 • pin 数:(执行)读取或执行名称空间对象的次数 • 重新加载数:(分析)在执行步骤上未命中库高速缓存, 从而导致隐式重新分析 SQL 语句的次数 • 无效数:DDL 修改对象从而导致所有相关对象都被标记 为无效的次数 版权所有 © 2006,Oracle。保留所有权利。 用于优化共享池的诊断工具 影响组件的参数: SHARED_POOL_SIZE、OPEN_CURSORS SESSION_CACHED_CURSORS、CURSOR_SPACE_FOR_TIME CURSOR_SHARING、SHARED_POOL_RESERVED_SIZE V$SGASTAT V$SGAINFO V$LIBRARYCACHE V$LIBRARY_CACHE_MEMORY V$SHARED_POOL_RESERVED V$SQLSTATS V$SQL V$SQLAREA V$SQLTEXT V$DB_OBJECT_CACHE sp_m_n.lst report.txt 数据字典 高速缓存 共享池 库高速缓存 共享 SQL 和 PL/SQL 视图 版权所有 © 2006,Oracle。保留所有权利。 库高速缓存重新加载数 • 重新加载数应低于 pin 数的 1%: • 如果重新加载数与 pin 数之比高于 1%,则增加 SHARED_POOL_SIZE 参数的值。 执行 PROC1 —> 第 4 个 pin,无重新加载 执行 PROC1 —> 第 1 个 pin,1 次加载 执行 PROC1 —> 第 2 个 pin,无重新加载 执行 PROC1 —> 第 3 个 pin,无重新加载 四个 pin, 无重新加载 SQL> SELECT SUM(pins) "Executions", 2 SUM(reloads) "Cache Misses", 3 SUM(reloads)/SUM(pins) 4 FROM V$LIBRARYCACHE; 版权所有 © 2006,Oracle。保留所有权利。 无效数 名称空间的对象被标记为无效从而导致重新加载的次数: SQL> SELECT COUNT(*) FROM hr.employees; SQL> SELECT namespace,pins,reloads, 2 invalidations 3 FROM V$LIBRARYCACHE; SQL> execute DBMS_STATS.GATHER_TABLE_STATS - > ('HR','EMPLOYEES'); SQL> SELECT COUNT(*) FROM hr.employees; SQL> SELECT namespace,pins,reloads, 2 invalidations 3 FROM V$LIBRARYCACHE; 版权所有 © 2006,Oracle。保留所有权利。 避免硬分析 在 OLTP 系统中,可以通过将硬分析保持为最低来减少未 命中数: • 确保用户可以共享语句。 • 通过分配足够的空间,防止频繁使用的语句到期释放。 • 避免会导致重新分析的无效。 版权所有 © 2006,Oracle。保留所有权利。 游标是否处于共享状态 • 检查 V$LIBRARYCACHE 中的 GETHITRATIO: • 确定可以共享的语句: SQL> SELECT plan_hash_value, count(*) 2 FROM V$SQL 3 GROUP BY plan_hash_value ORDER BY 2 DESC SQL> SELECT sql_text, executions 2 FROM V$SQLAREA 3 WHERE plan_hash_value = NNNNNNNNN SQL> SELECT gethitratio 2 FROM V$LIBRARYCACHE 3 WHERE namespace = 'SQL AREA'; 版权所有 © 2006,Oracle。保留所有权利。 共享游标 CURSOR_SHARING 的值包括: • EXACT • SIMILAR • FORCE 版权所有 © 2006,Oracle。保留所有权利。 避免软分析 • 减少软分析可以降低库高速缓存闩锁的争用。 • 通过以下措施可将软分析保持为最低: – 设置 SESSION_CACHED_CURSORS – 在应用程序预编译器中设置 HOLD_CURSOR – 设置 CURSOR_SPACE_FOR_TIME 版权所有 © 2006,Oracle。保留所有权利。 避免碎片 可通过以下措施避免碎片: • 升级到 10.2.0.x • 保留频繁需要使用的大对象 • 保留空间以供大对象使用 • 消除大型匿名 PL/SQL 块 • 允许使用大型池 版权所有 © 2006,Oracle。保留所有权利。 调整共享池大小 • 使用自动共享内存管理。 • 使用共享池指导,在数据具有可操作的历史记录时, 确认使用其它诊断。 • 如果没有任何历史记录,则使用 40% 的 SGA 大小来 开始。根据需要进行监视和调整。 • 有可用空闲内存时,请不要增加此大小。 SQL> SELECT * FROM V$SGASTAT 2 WHERE NAME = 'free memory' 3 AND POOL = 'shared pool'; 版权所有 © 2006,Oracle。保留所有权利。 共享池指导 SQL> SELECT shared_pool_size_for_estimate AS 2 pool_size, estd_lc_size, 3 estd_lc_time_saved 4 FROM V$SHARED_POOL_ADVICE; POOL_SIZE ESTD_LC_SIZE ESTD_LC_TIME_SAVED ---------- ------------ ------------------ 32 8 7868 40 15 7868 48 17 7868 56 17 7868 64 17 7868 72 17 7868 80 17 7868 88 17 7868 版权所有 © 2006,Oracle。保留所有权利。 共享池指导 版权所有 © 2006,Oracle。保留所有权利。 较大的内存要求 • 满足对较大的连续内存的请求。 • 在共享池中保留连续内存。 SHARED_POOL_SIZE SHARED_POOL_RESERVED_SIZE 数据字典 高速缓存 共享池 库高速缓存 共享 SQL 和 PL/SQL V$SHARED_POOL_RESERVED 保留区域 版权所有 © 2006,Oracle。保留所有权利。 优化共享池保留空间 或 并且 并且 降低 SHARED_POOL_ RESERVED_SIZE SHARED_POOL_RESERVE D_SIZE 的 Free_memory => 50% =0 增加 SHARED_POOL_ SIZE SHARED_POOL_RESERVE D_SIZE 的 Free_memory => 50% >0 并正在增加 增加 SHARED_POOL_ RESERVED_SIZE Request_misses > 0>0 并正在增加 操作如果 REQUEST_FAILURES 版权所有 © 2006,Oracle。保留所有权利。 保留大对象 • 查找那些未保留在库高速缓存中的 PL/SQL 对象: • 将大型程序包固定在库高速缓存中: SQL> EXECUTE DBMS_SHARED_POOL.KEEP(‘package_name’); SQL> SELECT * FROM V$DB_OBJECT_CACHE 2 WHERE sharable_mem > 10000 3 AND (type='PACKAGE' OR type='PACKAGE BODY' OR 4 type='FUNCTION' OR type='PROCEDURE') 5 AND kept='NO'; 版权所有 © 2006,Oracle。保留所有权利。 数据字典高速缓存 数据字典高速缓存保存数据字典行的行图像。 数据字典高速缓存 数据字典 版权所有 © 2006,Oracle。保留所有权利。 Statspack/AWR 报表输出: 如果有太多的高速缓存未命中,则应增加 SHARED_POOL_SIZE 参数。 字典高速缓存未命中 Get Pct Final Cache Requests Miss Usage ---------------- -------- ------ ------- dc_histogram_data 5,587 26.8 411 dc_histogram_defs 92,501 3.7 899 dc_object_grants 240 55.0 26 dc_object_ids 959,293 0.1 289 dc_objects 19,385 5.7 314 版权所有 © 2006,Oracle。保留所有权利。 UGA 和 Oracle 共享服务器 共享池 PGA 堆栈 空间 游标 状态 用户会 话数据 UGA 专用服务器配置 共享池 或 大型池 堆栈 空间 PGA 用户会话 数据 游标 状态 UGA V$STATNAME V$SESSTAT V$MYSTAT OPEN_CURSORS SESSION_CACHED_CURSORS 共享服务器配置 版权所有 © 2006,Oracle。保留所有权利。 共享池 库高速缓存 数据字典高速缓存 UGA 数据库缓冲 区高速缓存 重做日志 缓冲区 大型池 大型池 • 可以配置为 SGA 中的单独内存区,用于以下各项的内存: – I/O 服务器进程:DBWR_IO_SLAVES – 备份和还原操作 – 共享服务器的会话内存 – 并行查询消息传送 • 用于避免由收缩共享 SQL 高速缓存导致的性能开销 • 可通过 LARGE_POOL_SIZE 参数调整大小 版权所有 © 2006,Oracle。保留所有权利。 优化大型池 • 大型池有一个参数 LARGE_POOL_SIZE。 • V$SGASTAT 可显示已用内存和可用内存。 SELECT * FROM V$SGASTAT WHERE pool = 'large pool'; POOL NAME BYTES ------------ -------------------------- ---------- large pool free memory 2814112 large pool session heap 1380192 版权所有 © 2006,Oracle。保留所有权利。 练习概览:优化共享池 本练习包含以下主题: • 优化硬分析工作量 • 优化软分析工作量 • 将对象保留在共享池中 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 诊断共享池问题 • 调整共享池的大小 • 调整保留区域的大小 • 将对象保留在共享池中 版权所有 © 2006,Oracle。保留所有权利。 优化缓冲区高速缓存 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成下列工作: • 描述缓冲区高速缓存体系结构 • 调整缓冲区高速缓存大小 • 解决与缓冲区高速缓存相关的常见性能问题 • 使用常见诊断指标提出可能的解决方案 版权所有 © 2006,Oracle。保留所有权利。 Oracle 数据库体系结构 Spfile 临时文件 数据文件 还原 闪回日志 重做日志 文件 归档日志 文件 控制文件 SYSTEM SYSAUX 更改跟踪 文件 口令文件 SGA 实例 PMON SMON RECO MMON MMNL PSP0 MMAN DBWn LGWR CKPT CJQ0 S000 D000QMNC Qnnn FMON ARCnCTWR RVWR 固定 大小 大型池 Java 池 流池 nK 缓冲区 高速缓存 重做日志 缓冲区 排序 区池 全局 上下 文池 闪回 缓冲区 循环 缓冲区 高速缓存 保留 缓冲区 高速缓存 默认 缓冲区 高速缓存 共享池 ASH 缓冲区 版权所有 © 2006,Oracle。保留所有权利。 缓冲区高速缓存:重要内容 • 可扩展的体系结构: – 多版本并发控制 – 基于专有 LRU 的替换策略 – 高速缓存合并 • 增量检查点机制 • 指导 • 用于 I/O 密集型操作的专用池 版权所有 © 2006,Oracle。保留所有权利。 数据库缓冲区 SGA DB 缓冲区高速缓存 DBWn 服务器 版权所有 © 2006,Oracle。保留所有权利。 用于查找的缓冲区散列表 高速缓存 缓冲区链 闩锁 散列存 储桶表 缓冲区 散列链 CR 克隆 缓冲区头 A B C C E F 缓冲区 高速缓存 CCE A B F 版权所有 © 2006,Oracle。保留所有权利。 工作集 默认 保留 循环 nK 缓冲区 高速缓存 C A B A C 工作集 1 工作集 2 自动优化 … F E F B D E F 缓冲区 高速缓存 LRU CKPTs LRU CKPTs C A D D 版权所有 © 2006,Oracle。保留所有权利。 优化目标和技术 • 建议使用自动共享内存管理 (ASMM) • 优化目标: – 服务器找到内存中的数据 – 缓冲区高速缓存上没有等待 • 诊断度量: – 等待事件 – V$DB_CACHE_ADVICE 视图和高速缓存命中率 • 优化技术: – 如果使用 ASMM,则应查看 ADDM 建议 – 减少 SQL 语句所需的块数 – 增加缓冲区高速缓存大小,使用多个缓冲池 版权所有 © 2006,Oracle。保留所有权利。 故障现象 表示存在缓冲区高速缓存问题的故障现象: • 闩锁:高速缓存缓冲区链 • 闩锁:高速缓存缓冲区 LRU 链 • 缓冲区忙等待 • 读取等待 • 可用缓冲区等待 • 高速缓存命中率 版权所有 © 2006,Oracle。保留所有权利。 高速缓存缓冲区链闩锁争用 此闩锁的争用表明: • 多个进程正在尝试访问同一个“热”块 • 块替换过多 版权所有 © 2006,Oracle。保留所有权利。 查找热段 • 高速缓存缓冲区链闩锁争用的特点: – 在同一闩锁下对一个或多个块进行多次访问 – 块大小越大,争用越明显 • 要查找热段,请执行以下语句: SQL> SELECT * FROM ( SELECT owner, object_name, 2 object_type, statistic_name, sum(value) 3 FROM V$SEGMENT_STATISTICS 4 GROUP BY owner, object_name, object_type, 5 statistic_name 6 ORDER BY SUM(value) DESC) 7 WHERE ROWNUM < 10; 版权所有 © 2006,Oracle。保留所有权利。 缓冲区忙等待 • 缓冲区高速缓存中应用程序级的缓冲区争用 • 识别缓冲区忙等待争用: SELECT object_name, value FROM V$SEGMENT_STATISTICS WHERE statistic_name 'buffer busy waits' AND value > 20000; SELECT class, count FROM V$WAITSTAT WHERE count>0 ORDER BY count DESC; 版权所有 © 2006,Oracle。保留所有权利。 计算缓冲区高速缓存命中率 SELECT name, value FROM v$sysstat WHERE name IN ('db block gets from cache', 'consistent gets from cache', 'physical reads cache'); PHYSICAL READS = 'physical reads cache') LOGICAL READS = ('consistent gets from cache' + 'db block gets from cache') HIT RATIO = 1 - PHYSICAL READS/LOGICAL READS 版权所有 © 2006,Oracle。保留所有权利。 缓冲区高速缓存命中率并不是性能的绝对指标 • 没有得到适当优化的数据库仍可能具有 99% 或更高的 命中率。 • 在确定性能是否得到了适当优化时命中率仅是应考察的 一部分。 • 不能通过命中率确定数据库是否得到了最佳优化。 • 可使用等待界面检查导致瓶颈的原因: – V$SESSION_WAIT – V$SESSION_EVENT – V$SYSTEM_EVENT • 优化 SQL 语句。 版权所有 © 2006,Oracle。保留所有权利。 解释缓冲区高速缓存命中率 • 命中率会受到数据访问方法的影响: – 全表扫描 – 相同表的重复扫描 – 使用随机访问的大型表 – 数据或应用程序设计 • 如果出现了下列情况,则应进行调查以确定是否需要 增加高速缓存的大小: – 命中率很低 – 为了避免全表扫描,已对应用程序进行了优化 版权所有 © 2006,Oracle。保留所有权利。 读取等待 • 执行到缓冲区高速缓存的磁盘读取的等待事件列表: – db file sequential read – db file parallel read – db file scattered read • 如果读取的等待时间很长: – 优化执行最多磁盘读取的 SQL 语句,方法是按 DISK_READS 和 BUFFER_GETS 排序 V$SQL – 如果需要,增加缓冲区高速缓存 – 减少由检查点导致的写入 – 增加磁盘容量 版权所有 © 2006,Oracle。保留所有权利。 可用缓冲区等待 K B J A C D LRU 列表 服务器 服务器 服务器 写入 缓冲区状态 灰 可用 DBWn 版权所有 © 2006,Oracle。保留所有权利。 解决方案 根据故障现象应用缓冲区高速缓存解决方案: • 适当调整缓冲区高速缓存大小 • 高速缓存对象 • 使用保留池或循环池 • 增加 DBWn 的写入速度 • 使用专用 I/O 池 版权所有 © 2006,Oracle。保留所有权利。 调整缓冲区高速缓存大小 缓冲区高速缓存大小会影响几个优化诊断。如果高速缓存 太小,则会发生下列情况: • 块替换会导致额外读取 • 将灰块移至磁盘会导致额外写入 • 缓冲区高速缓存 LRU 链出现争用 版权所有 © 2006,Oracle。保留所有权利。 缓冲区高速缓存大小参数 • 为循环缓冲池、保留缓冲池和默认缓冲池设置主要块 大小:DB_BLOCK_SIZE • 设置缓冲池的大小: – DB_CACHE_SIZE – DB_KEEP_CACHE_SIZE – DB_RECYCLE_CACHE_SIZE • 表示用于缓冲区高速缓存的所有内存 • 是使用缓冲区高速缓存功能时所必需的参数: – 动态增加/减少 – 缓冲区高速缓存建议 – 多个块大小 版权所有 © 2006,Oracle。保留所有权利。 动态缓冲区高速缓存建议参数 • 缓冲区高速缓存建议功能可以针对使用了不同高速缓存 大小的预测行为启用和禁用统计数据采集。 • 使用这些统计数据提供的信息可针对给定工作量以最佳 方式调整缓冲区高速缓存大小。 • 缓冲区高速缓存建议是通过 DB_CACHE_ADVICE 初始 化参数启用的: – 此参数是动态的,可以使用 ALTER SYSTEM 进行更改。 – 允许使用三个值:OFF、ON 和 READY。 版权所有 © 2006,Oracle。保留所有权利。 支持缓冲区高速缓存建议的视图 • V$DB_CACHE_SIZE 视图中包含缓冲区高速缓存建议 信息。 • 该视图包括不同的行,这些行可估计当前高速缓存大小 的 10% 和 200% 之间的高速缓存大小的物理读取数。 • 这些行还可计算物理读取系数,该系数是估计读取数与 实际读取数之比。 • 对所有缓冲池进行了模拟。 版权所有 © 2006,Oracle。保留所有权利。 使用 V$DB_CACHE_ADVICE 视图 SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads FROM V$DB_CACHE_ADVICE WHERE name = 'DEFAULT' AND advice_status = 'ON' AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size'); Estd Phys Estd Phys Cache Size (MB) Buffers Read Factor Reads ---------------- ------------ ----------- ------------ (10%) 30 3,802 18.70 192,317,943 … 243 30,416 1.33 13,720,149 273 34,218 1.13 11,583,180 (Current) 304 38,020 1.00 10,282,475 334 41,822 .93 9,515,878 … 577 72,238 .67 6,895,122 (200%) 608 76,040 .66 6,739,731 版权所有 © 2006,Oracle。保留所有权利。 与 EM 一起使用缓冲区高速缓存建议 版权所有 © 2006,Oracle。保留所有权利。 对表进行高速缓存 • 通过下列方式可以在全表扫描期间启用高速缓存: – 使用 CACHE 子句创建表 – 使用 CACHE 子句变更表 – 在查询中使用 CACHE 提示 • 对表进行高速缓存会将块置于 LRU 列表的 MRU 端。 • 准则:不要使缓冲区高速缓存过度拥挤。 • 使用保留池。 版权所有 © 2006,Oracle。保留所有权利。 多个缓冲池 • 有三个缓冲池: – 默认:SYS 和非标记表或索引 – 保留:热对象 – 循环:很少访问 • 对使用已知访问路径的小而简单的方案非常有用 默认 保留 循环 自动优化 版权所有 © 2006,Oracle。保留所有权利。 启用多个缓冲池 • 使用 BUFFER_POOL 子句。 • 此子句对表、集群和索引有效。 • 变更后,缓冲池可用于将来的读取。 • 对象可以具有多个缓冲池。 CREATE INDEX cust_idx … STORAGE (BUFFER_POOL KEEP …); ALTER TABLE customer STORAGE (BUFFER_POOL RECYCLE); ALTER INDEX cust_name_idx STORAGE (BUFFER_POOL KEEP); 版权所有 © 2006,Oracle。保留所有权利。 计算多个池的命中率 SQL> SELECT name, 1 - (physical_reads / 2 (db_block_gets + consistent_gets)) "HIT_RATIO" 3 FROM V$BUFFER_POOL_STATISTICS 4 WHERE db_block_gets + consistent_gets > 0; NAME HIT_RATIO ------------------ ---------- KEEP .983520845 RECYCLE .503866235 DEFAULT .790350047 版权所有 © 2006,Oracle。保留所有权利。 多个块大小 • 允许非标准块大小的缓冲区高速缓存 • 参数:DB_nK_CACHE_SIZE {n = 2, 4, 8, 16, 32} • CREATE TABLESPACE 存储子句的 BLOCKSIZE 属性 • 用于可移动表空间 默认 保留 循环 16 KB 高速缓存 集 1 集 2 集 3 集 4 集 5 集 6 集 7 集 8 版权所有 © 2006,Oracle。保留所有权利。 多个数据库写进程 • 多个数据库写进程用于增加写入吞吐量,对于大型 SMP 系统非常有用。 • 缓冲区高速缓存在数据库写进程之间按照工作集进行分区。 • 每个 DBWn 进程会扫描自己的分配的集。 • 数据库写操作的数量可以通过 DB_WRITER_PROCESSES 手动进行控制。 集 1 集 3 集 2 集 4 DBW0 DBW1 版权所有 © 2006,Oracle。保留所有权利。 多个 I/O 从进程 • 当不能有效地支持异步 I/O 时,就会允许 DBW0 并行写入 • DBW0 收集缓冲区批 • 使用 I/O 从进程按循环方式对批进 行排队 • 等待所有从进程完成操作 • 无法将多个 DBWn 和多个 I/O 从进程组合在一起 • 可通过以下参数进行控制: – DBWR_IO_SLAVES – DISK_ASYNCH_IO 集 1 集 2 集 3 集 4 i000 i002 DBW0 i001 版权所有 © 2006,Oracle。保留所有权利。 使用多个写进程或 I/O 从进程 要减少可用缓冲区等待,请执行下列操作: • 首先执行异步 I/O • 在数据库写进程所消耗的 CPU 达到 100% 时使用多个 写进程 • 在系统不支持异步 I/O 时使用 I/O 从进程 版权所有 © 2006,Oracle。保留所有权利。 用于 I/O 密集型操作的专用池 • 用于符合以下条件的操作: – 不需要高速缓存 – 需要高效的 I/O • 用于大型异步 I/O • 需要全局检查点以确保数据一致 • 仅用于直接路径 I/O: – 并行直接插入 – SQL*Loader 直接路径 版权所有 © 2006,Oracle。保留所有权利。 自动优化的多块读取 • DB_FILE_MULTIBLOCK_READ_COUNT 是自动优化的 参数 • 简化最佳值的确定过程 • 最佳 I/O 大小与平台相关: – 不能超过高速缓存的 10% 预取被限制为 64 KB – 防止高速缓存被填满 • 未设置或设置为零时会自动启用 版权所有 © 2006,Oracle。保留所有权利。 极大缓冲区高速缓存更快速的实例启动 • 在初始化了缓冲区高速缓存的 10% 时,数据库即 为可用 • 剩余 90% 由 CKPT 在后台进行格式化 • 利用动态缓冲区高速缓存基础结构 • 对于非常大的缓冲区高速缓存尤其有用 版权所有 © 2006,Oracle。保留所有权利。 刷新缓冲区高速缓存 (仅用于测试) ALTER SYSTEM FLUSH BUFFER_CACHE; 版权所有 © 2006,Oracle。保留所有权利。 练习:概览 本练习包含以下主题: • 使用 DB 缓冲区高速缓存指导调整缓冲区高速缓存 的大小 • 使用保留池 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 描述缓冲区高速缓存体系结构 • 调整缓冲区高速缓存大小 • 解决与缓冲区高速缓存相关的常见性能问题 • 使用常见诊断指标提出可能的解决方案 版权所有 © 2006,Oracle。保留所有权利。 自动管理共享内存 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成下列工作: • 启用 Oracle Enterprise Manager (EM) 内存参数 • 设置自动优化的内存参数 • 使用手动优化的 SGA 参数覆盖最小大小 • 使用 SGA Advisor 设置 SGA_TARGET 版权所有 © 2006,Oracle。保留所有权利。 Oracle 数据库体系结构 Spfile 临时文件 数据文件 还原 闪回日志 重做 日志文件 归档 日志文件 控制文件 SYSTEM SYSAUX 更改 跟踪文件 口令文件 PMON SMON RECO MMON MMNL PSP0 MMAN DBWn LGWR CKPT CJQ0 S000 D000QMNC Qnnn FMON ARCnCTWR RVWR 固定 大小 大型池 Java 池 流池 默认 缓冲区 高速缓存 保留 缓冲区 高速缓存 循环 缓冲区 高速缓存 nK 缓冲区 高速缓存 重做日志 缓冲区 ASH 缓冲区 排序 区池 全局 上下文池 SGA 闪回 缓冲区 实例 共享池 版权所有 © 2006,Oracle。保留所有权利。 动态 SGA 功能 • 实施基础结构,以使服务器可以在不关闭实例的情况下 更改其 SGA 配置 • SGA 大小由 SGA_MAX_SIZE 进行限制: – 用于在实例启动时保留虚拟内存地址空间 – 无法动态地进行更改 • 允许动态地调整某些 SGA 组件的大小 SELECT bytes FROM V$SGAINFO WHERE name = 'Free SGA Memory Available'; 版权所有 © 2006,Oracle。保留所有权利。 区组 • SGA 内存以相邻的内存块(称为区组)为单位进行分配。 • 区组大小取决于估计的总 SGA。如果估计的 SGA 大小: – 小于或等于 1 GB,则区组大小为 4 MB – 大于 1 GB,则区组大小为 16 MB SELECT bytes FROM V$SGAINFO WHERE name = 'Granule Size'; 版权所有 © 2006,Oracle。保留所有权利。 内存指导 • 缓冲区高速缓存建议(在 9i R1 中引入): – V$DB_CACHE_ADVICE – 预测不同高速缓存大小的物理读取数 • 共享池建议(在 9i R2 中): – V$SHARED_POOL_ADVICE – 预测不同大小的共享池可以节省的分析时间 • Java 池建议(在 9i R2 中): – V$JAVA_POOL_ADVICE – 预测不同大小的 Java 池可以节省的 Java 类加载时间 • 流池建议 (10g R2) – V$STREAMS_POOL_ADVICE – 预测不同大小的溢出和未溢出活动 版权所有 © 2006,Oracle。保留所有权利。 在组件中手动添加区组 • 使用 ALTER SYSTEM 命令动态地增加为组件分配的 内存。 • 只有存在满足请求的足够可用区组时,才能成功地增 加组件的内存使用量。 • 不会自动地从另一个组件释放内存区组来满足此增加 请求。 • 可以减小组件大小,但是只有组件未使用释放的区组 时才能实现此操作。 版权所有 © 2006,Oracle。保留所有权利。 增加 SGA 组件的大小 SQL> show parameter NAME TYPE VALUE ------------- ----------- ----------------------- sga_max_size big integer 200M shared_pool_size big integer 84M db_cache_size big integer 92M SQL> alter system set shared_pool_size=120M; alter system set shared_pool_size=120M * ERROR at line 1: ORA-02097: parameter cannot be modified … ORA-04033: Insufficient memory to grow pool SQL> alter system set db_cache_size=50M; System altered. SQL> alter system set shared_pool_size=120M; System altered. 版权所有 © 2006,Oracle。保留所有权利。 自动管理共享内存:概览 • 使用动态 SGA 和内存指导来自动适应工作量的变化 • 最大化内存使用率 • 帮助避免内存不足错误 • 消除了在使用 SPFILE 时的重新学习过程 缓冲区高速缓存 大型池 共享池 Java 池 缓冲区高速缓存 大型池 共享池 Java 池 联机用户 批处理作业 流池 流池 版权所有 © 2006,Oracle。保留所有权利。 SGA 调整大小参数:概览 • 使用 ASMM,可以自动调整五个重要 SGA 组件的大小。 • 不会自动优化非默认缓冲区池。 • 日志缓冲区不是动态组件,但是具有很合适的默认值。 SHARED_POOL_SIZE DB_CACHE_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE STREAMS_POOL_SIZE 自动优化的参数 手动动态参数 手动静态参数 DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE DB_nK_CACHE_SIZE SGA_TARGET LOG_BUFFER_SIZE SGA_MAX_SIZE 版权所有 © 2006,Oracle。保留所有权利。 自动管理共享内存的优点 DB_CACHE_SIZE SHARED_POOL_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE STREAMS_POOL_SIZE 总 SGA 大小 SGA_TARGET 版权所有 © 2006,Oracle。保留所有权利。 动态 SGA 传递模式 • ASMM IMMEDIATE 传递模式: – 内存不足 (ORA-04031) 错误 – 可以使用部分区组 • ASMM DEFERRED 传递模式: – 在后台透明地执行 – 可以使用部分区组 • MANUAL 传递模式: – 与 ALTER SYSTEM 命令一起使用 – 调整大小必须使用全部区组 版权所有 © 2006,Oracle。保留所有权利。 内存中介体系结构 不同时段 的统计增量 MMON 捕获的统计信 息的循环 SGA 缓冲区 内存 中介 策略 模块 在共享池中 添加两个区组 输出:调整大小请求 不同组件 优点/缺点的折衷 MMAN 传递内存 调整 队列大小 MMAN MMON 版权所有 © 2006,Oracle。保留所有权利。 手动调整动态 SGA 参数的大小 • 对于自动优化的参数: – 如果新值大于当前大小,则手动调整大小将立即调整组件 的大小 – 如果新值小于当前大小,则手动调整大小将更改最小大小 • 调整手动优化的参数的大小会影响 SGA 的可优化部分 版权所有 © 2006,Oracle。保留所有权利。 自动优化的 SGA 参数的行为 • 未设置 SGA_TARGET 或将其设置为零时: – 明确设置了自动优化的参数 – 注:SHARED_POOL_SIZE 包含内部启动开销 与以前的版本相比,值可能需要增加 • 设置了 SGA_TARGET 时: – 自动优化的参数的默认值为零 – 非零值为下限 – 当前值 (MB) 显示为: SELECT component, current_size/1024/1024 FROM V$SGA_DYNAMIC_COMPONENTS; 版权所有 © 2006,Oracle。保留所有权利。 手动优化的 SGA 参数的行为 • 手动优化的组件包括: – KEEP 和 RECYCLE 缓冲区高速缓存 – 非默认块大小高速缓存 – LOG_BUFFER • 手动优化的组件由用户指定。 • 手动优化的组件包含在 SGA_TARGET 中,可以精确地 控制 SGA 大小。 版权所有 © 2006,Oracle。保留所有权利。 使用 V$PARAMETER 视图 SGA_TARGET = 8G SELECT name, value, isdefault FROM V$PARAMETER WHERE name LIKE '%size'; DB_CACHE_SIZE = 0 JAVA_POOL_SIZE = 0 LARGE_POOL_SIZE = 0 SHARED_POOL_SIZE = 0 STREAMS_POOL_SIZE = 0 版权所有 © 2006,Oracle。保留所有权利。 调整 SGA_TARGET 的大小 • SGA_TARGET 初始化参数: – 是动态参数 – 最大可以增加到 SGA_MAX_SIZE – 可以缩小此参数,直到所有组件都达到最小 – 更改只影响自动调整大小的组件 • 包括 SGA 中的所有部分: – 固定 SGA 和其它内部分配 – 自动调整大小的 SGA 组件 – 手动 SGA 组件 • 允许 Oracle 服务器精确地调整总共享内存分配的大小 版权所有 © 2006,Oracle。保留所有权利。 禁用自动管理共享内存 • 将 SGA_TARGET 设置为零会禁用自动优化。 • 将自动优化的参数设置为其当前大小。 • SGA 大小整体上不受影响。 SGA 大小 = 8 GB 参数: SGA_TARGET = 8G SHARED_POOL_SIZE=1G 参数: SGA_TARGET = 0 DB_CACHE_SIZE = 4G SHARED_POOL_SIZE = 1.5G LARGE_POOL_SIZE = 512M JAVA_POOL_SIZE = 512M STREAMS_POOL_SIZE = 512M SGA 大小 = 8 GB 原始值 SGA_TARGET=0 版权所有 © 2006,Oracle。保留所有权利。 配置 ASMM 版权所有 © 2006,Oracle。保留所有权利。 SGA Advisor 版权所有 © 2006,Oracle。保留所有权利。 监视 ASMM 监视自动管理共享内存,并使用下列视图检查所做的调整大小 决策: • V$SGA_CURRENT_RESIZE_OPS:有关正在进行的调整 大小 SGA 操作的信息 • V$SGA_RESIZE_OPS:包含最近 800 个 SGA 调整大小 请求的循环历史记录缓冲区 • V$SGA_DYNAMIC_COMPONENTS:所有内存组件的当前 状态 • V$SGA_DYNAMIC_FREE_MEMORY:有关以后的调整大小 操作可用的 SGA 内存的信息 版权所有 © 2006,Oracle。保留所有权利。 练习:概览 本练习包含以下主题: • 启用自动管理共享内存 • 根据工作量的变化调整内存 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 启用 EM 内存参数 • 设置自动优化的内存参数 • 设置手动优化的 SGA 参数 • 使用 SGA Advisor 设置 SGA_TARGET 版权所有 © 2006,Oracle。保留所有权利。 检查点和重做优化 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成下列工作: • 诊断检查点问题和重做问题 • 实现快速启动 MTTR 目标 • 监视快速启动 MTTR 目标的性能影响 • 优化重做链 • 调整重做日志文件的大小 • 调整重做日志缓冲区的大小 版权所有 © 2006,Oracle。保留所有权利。 检查点和重做 • 检查点: – 将更改的数据传送到磁盘 – 使缓冲区空间可供更多的数据块使用 – 控制平均恢复时间 (MTTR) • 重做: – 恢复不在磁盘上的已提交的数据 – 提供用于回退操作的未提交的数据 – 为完全恢复提供源数据 版权所有 © 2006,Oracle。保留所有权利。 Oracle 数据库体系结构 Spfile 临时文件 数据文件 还原 闪回日志 重做 日志文件 归档 日志文件 控制文件 SYSTEM SYSAUX 更改 跟踪文件 口令文件 PMON SMON RECO MMON MMNL PSP0 MMAN DBWn LGWR CKPT CJQ0 S000 D000QMNC Qnnn FMON ARCnCTWR RVWR 固定 大小 大型池 Java 池 流池 默认 缓冲区 高速缓存 保留 缓冲区 高速缓存 循环 缓冲区 高速缓存 nK 缓冲区 高速缓存 重做日志 缓冲区 ASH 缓冲区 排序 区池 全局 上下文池 SGA 闪回 缓冲区 实例 共享池 版权所有 © 2006,Oracle。保留所有权利。 检查点体系结构 检查点体系结构提供: • 检查点位置:在重做日志中开始恢复的起始位置 • 检查点目标:在重做日志中计算的检查点应处的位置 • 估计的平均恢复时间 • 高性能增量检查点 • 需要时提供完全检查点 版权所有 © 2006,Oracle。保留所有权利。 数据库写进程 (DBWn) DBWn 在发生下列事件之一时执行 写入: • 检查点 • 灰缓冲区阈值 • 无可用的缓冲区 • 超时 • RAC Ping 请求 • 表空间 OFFLINE • 表空间 READ ONLY • 表 DROP 或 TRUNCATE • 表空间 BEGIN BACKUP SGA 数据库 缓冲区高速缓存 数据文件 数据库 写进程 (DBWn) 背景信息 版权所有 © 2006,Oracle。保留所有权利。 检查点 (CKPT) 进程 负责: • 在检查点向 DBWn 发出信号 • 使用检查点信息更新数据文件标题 • 使用检查点信息更新控制文件 数据库 写进程 (DBWn) 检查点 (CKPT) SGA 数据库 缓冲区高速缓存 版权所有 © 2006,Oracle。保留所有权利。 重做体系结构 重做的设计旨在使对性能产生的影响达到最小 • 服务器进程写入重做日志缓冲区: – 循环缓冲区 – 内存间写入 – 小型快速写入 • LGWR 将日志缓冲区数据块写入日志文件: – 循环文件 – 内存到磁盘的写入 – 完整数据块(如果可能) • ARCn 将日志文件复制到归档日志文件: – 磁盘间写入 – 可以启动多个归档进程 版权所有 © 2006,Oracle。保留所有权利。 重做日志缓冲区 数据库 缓冲区 高速缓存 重做日志 缓冲区 共享池 库高速缓存 数据字典高速缓存 用户全局区 SQL> UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=736; 服务器进程 LGWR 控制文件 ARCn 归档的日志文件 重做日志文件 数据文件 版权所有 © 2006,Oracle。保留所有权利。 重做日志文件和日志写进程 重做日志文件: • 记录对数据库进行的更改 • 应多路复用以避免文件丢失 日志写进程的写入时间为: • 提交时 • 三分之一已满时 • 每隔 3 秒 • 在 DBWn 写入之前组 3 日志写进程 (LGWR) 组 1 组 2 重做日志缓冲区 SGA 重做日志文件 版权所有 © 2006,Oracle。保留所有权利。 归档进程 (ARCn) • 是可选的后台进程 • 为数据库设置 ARCHIVELOG 模式时会自 动归档联机重做日志文件 • 保留对数据库所做的所有 更改的记录 日志写进程 (LGWR) 归档进程 (ARCn) 重做日志缓冲区 SGA 版权所有 © 2006,Oracle。保留所有权利。 增量检查点 c1 c2 c3 c4 c5 重做流 检查点队列 目标 RBA b3 b4 t2 t1 FAST_START_MTTR_TARGET=T t1 + t2 < T b1 b2 增量检查点 重做线程尾标检查点位置 版权所有 © 2006,Oracle。保留所有权利。 增量检查点和日志文件大小 • 最大检查点延迟为: 90%*(SUM(log_sizei) – MAX(log_sizei)) • 检查点延迟用于防止日志切换受到阻止。 • 多个小型日志文件可能会造成过多的检查点写入。 当前尾标 目标检查点 9,000 个 数据块 日志文件 1: 10,000 个数据块 日志文件 2: 10,000 个数据块 版权所有 © 2006,Oracle。保留所有权利。 调整检查点速率 检查点速率由下列参数中最激进的参数决定: • FAST_START_MTTR_TARGET 参数(仅用于企业版) • 最小重做日志文件的大小 • LOG_CHECKPOINT_TIMEOUT 参数(设置后将覆盖 FAST_START_MTTR_TARGET) • LOG_CHECKPOINT_INTERVAL 参数(设置后将覆盖 FAST_START_MTTR_TARGET) 版权所有 © 2006,Oracle。保留所有权利。 重做日志文件大小指导 • 此指导确定联机重做 日志的最佳大小: – 检查点写入次数不会 超 FAST_START_ MTTR_TARGET 产生 的写入次数 • 必须设置 FAST_START_MTTR_TARGET 此列显示被视为最小值的重做日志文件大小 (MB)说明 OPTIMAL_LOGFILE_SIZE列名称 V$INSTANCE_RECOVERY视图名称 版权所有 © 2006,Oracle。保留所有权利。 检查点速率的影响 SELECT c.value-nc.value FROM V$SYSSTAT c, V$SYSSTAT nc WHERE c.name = 'physical writes' AND nc.name = 'physical writes non checkpoint'; Statistic Total -------------------------- ------------------ physical writes 47,308 physical writes non checkpoint 44,674 从 V$ 视图: 从 Statspack 报表: 版权所有 © 2006,Oracle。保留所有权利。 自动检查点优化 • 不再需要连续的手动优化操作。 • 自动检查点优化是最尽力的检查点,没有很高的开销。 • 可以利用未使用的带宽来缩短平均恢复时间。 • 未明确地将 FAST_START_MTTR_TARGET 设置为零时, 就会启用自动检查点优化。 版权所有 © 2006,Oracle。保留所有权利。 ADDM 报表:检查点 版权所有 © 2006,Oracle。保留所有权利。 ADDM 报表:重做日志 版权所有 © 2006,Oracle。保留所有权利。 Statspack 和 AWR 报表 检查点和重做将显示某些故障现象: • 显示日志切换尚未完成的预警日志 • 由检查点过多导致的 I/O 故障现象 • 每个小时的日志切换次数 > 4 • 顶级计时事件中的 log file 和 latch:redo Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------- ------- ----------- ------ ------ CPU time 551 56.8 log file parallel write 3,899 201 52 20.7 log file sync 823 58 71 6.0 latch: redo copy 635 44 70 4.6 latch: redo allocation 1,109 42 38 4.4 版权所有 © 2006,Oracle。保留所有权利。 检查参数 Parameter Name Begin value ---------------------------- ----------- fast_start_mttr_target 25 查看检查点参数值是否合理: SQL> SELECT mttr_target_for_estimate, 2> estd_total_ios, estd_total_io_factor 3> FROM V$MTTR_TARGET_ADVICE ORDER BY 1 MTTR_TARGET_FOR_ESTIMATE ESTD_TOTAL_IOS ESTD_TOTAL_IO_FACTOR ------------------------ -------------- -------------------- 20 2436690 1.0739 22 2330674 1.0272 25 2268973 1 37 2204817 .9717 62 2181841 .9616 使用 V$MTTR_TARGET_ADVICE 获得最佳值: 版权所有 © 2006,Oracle。保留所有权利。 检查重做日志大小 查看重做日志文件的当前大小: 检查预警日志的日志切换速率: 版权所有 © 2006,Oracle。保留所有权利。 重做日志链优化 重做优化从速度最慢的部分开始。 • 减少生成的重做数量。 • 检查归档事件记录(需要等待归档)。 • 检查重做日志文件大小和日志切换速率。 • 检查检查点参数。 • 查找日志空间请求。 • 重做缓冲区分配重试次数的值应接近 0,并且小于重做 条目的 1%。 版权所有 © 2006,Oracle。保留所有权利。 减少重做操作 在重做日志中避免记录批量操作的方式如下: • 不进行归档的直接路径装入不会生成重做日志条目。 • 进行归档的直接路径装入可以使用 NOLOGGING 模式。 • 直接装入 INSERT 可以使用 NOLOGGING 模式。 • 某些 SQL 语句可以使用 NOLOGGING 模式。 版权所有 © 2006,Oracle。保留所有权利。 提高归档性能 • 在临时增加工作量时分担归档工作: • • 使用 LOG_ARCHIVE_MAX_PROCESSES 增加归档 进程数。 • 多路复用重做日志文件,并添加更多的成员。 • 更改归档目标数: – LOG_ARCHIVE_DEST_n ALTER SYSTEM ARCHIVE LOG ALL TO 版权所有 © 2006,Oracle。保留所有权利。 诊断工具 V$ARCHIVE_DEST V$ARCHIVED_LOG V$ARCHIVE_PROCESSES LOG_ARCHIVE_DEST_n 归档的日志 LOG_ARCHIVE_DEST_STATE_n 版权所有 © 2006,Oracle。保留所有权利。 重做日志组和成员 LGWR 组 2 组 3组 1 磁盘 1 磁盘 2 成员 成员 成员 成员 成员 成员 版权所有 © 2006,Oracle。保留所有权利。 联机重做日志文件配置 • 调整重做日志文件的大小,以使争用降低到最低。 • 提供足够的组以避免等待。 • 将重做日志文件存储在独立的快速设备上。 • 使用下列视图监视重做日志文件的配置: – V$LOGFILE – V$LOG – V$LOG_HISTORY 版权所有 © 2006,Oracle。保留所有权利。 监视联机重做日志文件 I/O 版权所有 © 2006,Oracle。保留所有权利。 调整重做日志缓冲区的大小 • 重做日志缓冲区的大小由下列参数确定: • LOG_BUFFER 参数 • 固定区域区组中的剩余空间 默认值:2 MB 或 128 KB × CPU_COUNT 的值,采用 较大的值 版权所有 © 2006,Oracle。保留所有权利。 诊断重做日志缓冲区低效率的原因 归档的日志文件重做日志文件 SQL> UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=736; 服务器进程 LGRW ARCH 服务器进程 SQL> DELETE FROM employees 2 WHERE employee_id=7400; 版权所有 © 2006,Oracle。保留所有权利。 诊断日志缓冲区问题 V$SESSION_WAIT Log Buffer Space 事件 V$SYSSTAT 重做缓冲区分配重试次数 重做条目 重做日志缓冲区 版权所有 © 2006,Oracle。保留所有权利。 日志空间请求等待:进一步调查 日志空间请求等待可能是由以下原因引起的: • 重做日志文件上存在磁盘 I/O 争用。 • LGWR 等待 DBWn 完成所需重做日志文件的检查点。 • LGWR 等待 ARCn 完成所需重做日志文件的归档。 • 日志缓冲区太小。 版权所有 © 2006,Oracle。保留所有权利。 练习概览:诊断检查点问题和重做问题 本练习包含以下主题: • 诊断检查点问题和重做问题 • 调整日志文件的大小 • 调整检查点参数 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 诊断检查点问题和重做问题 • 实现快速启动 MTTR 目标 • 监视快速启动 MTTR 目标的性能影响 • 实现多个数据库写进程 • 优化重做链 • 调整重做日志文件的大小 • 调整重做日志缓冲区的大小 版权所有 © 2006,Oracle。保留所有权利。 优化 I/O 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成下列工作: • 诊断数据库 I/O 问题 • 说明条带化和镜像所有内容 (SAME) 概念 • 说明异步 I/O 的优点 • 选择适合的 I/O 解决方案 • 使用自动存储管理 (ASM) 优化 I/O 版权所有 © 2006,Oracle。保留所有权利。 I/O 体系结构 Oracle Database 10g 包括三种标准的存储方法: • 文件系统 • 裸分区 • 自动存储管理 (ASM) 版权所有 © 2006,Oracle。保留所有权利。 文件系统的特性 某些特性有利于数据库的使用: • 写入高速缓存功能 • 写确认 • 安全性 • 日记 • 高性能 版权所有 © 2006,Oracle。保留所有权利。 裸分区 裸分区一直被视为是一种高性能解决方案。 • 裸读取和裸写入不使用 OS 缓冲区高速缓存。 • 与文件系统 I/O 相比,裸读取和裸写入可以移动更大 的缓冲区。 • 使用裸分区会使管理变得复杂。 写入 读取 进程 磁盘文件 版权所有 © 2006,Oracle。保留所有权利。 I/O 模式 可以通过使用不同的系统调用,以多种方式将 I/O 写入磁盘: • 标准 I/O • 同步 I/O • 异步 I/O 缓冲区高速缓存 磁盘文件 写入 刷新 进程 版权所有 © 2006,Oracle。保留所有权利。 带宽与大小 I/O 性能取决于带宽。 • 磁盘数,而不是大小 • 控制器数 后台进程 磁盘控制器 版权所有 © 2006,Oracle。保留所有权利。 条带化和镜像所有内容 • 所有数据文件可以访问所有可用带宽 • 所有数据库文件都位于相同的逻辑设备上 • 最高的性能配置 版权所有 © 2006,Oracle。保留所有权利。 使用 RAID 廉价设备冗余阵列 (RAID) 级别: • 级别 0 – 已进行了条带化以提高性能 – 无冗余 • 级别 1 – 已进行了镜像以提高安全性 – 对性能几乎没有好处 • 级别 5 – 块级冗余(重建算法) – 提高了读取性能 – 增加了写入成本 版权所有 © 2006,Oracle。保留所有权利。 RAID 的成本与优点 将在性能和可靠性两方面来评估 RAID 成本。 • RAID 0: – 速度很快 – 丢失任何设备都会对阵列造成损害 • RAID 1: – 安全但是成本较高 – 在读取占比例较高的环境中略有优势 • RAID 5: – 速度很快 – 在丢失任何一个设备的情况下也可保证安全 – 写入成本可能很高 版权所有 © 2006,Oracle。保留所有权利。 是使用 RAID 1 还是 RAID 5 RAID 1(镜像) • Oracle 推荐使用 • 要求最为苛刻的应用程序 优点 • 最佳的冗余性 • 最佳的性能 • 恢复开销很低 缺点 • 要求的容量较大 RAID 5(奇偶校验) • DSS 和适中的 OLTP 优点 • 要求的容量较小 缺点 • 冗余性较差 • 性能较差 • 恢复开销很高 版权所有 © 2006,Oracle。保留所有权利。 诊断 I/O 问题指示器: • 顶级等待为读取和写入以及: – 缓冲区忙等待 – 写入完成等待 – DB 文件并行写入 – 入队等待 • “File I/O Statistics(文件 I/O 统计信息)”部分显示 了高比例的等待,以及在某些文件上高于平均值的平均 缓冲区等待时间。 注:在运行状态良好的系统上,顶级事件很可能是 CPU 时 间、DB 文件分散读取以及 DB 文件连续读取。 版权所有 © 2006,Oracle。保留所有权利。 数据库 I/O 优化 • 为数据库配置存储取决于许多变量: – 将哪些数据放在哪一磁盘上;供应商配置的逻辑单元 (LUN) 使这一变量变得复杂 – DB 应用程序负荷:OLTP、DSS、批量与联机 – 在可用选项之间进行折衷 – 正在进行的优化:工作量的变化 – 扩展或收缩数据库 版权所有 © 2006,Oracle。保留所有权利。 什么是自动存储管理 ASM: • 是一种可移植的高性能集群文件系统 • 管理 Oracle 数据库文件 • 将数据分布到各个磁盘以平衡负载 • 提供跨磁盘的集成镜像 • 解决许多存储管理问题 • 封装 SAME 方法 ASM 文件系统 卷管理器 操作系统 应用程序 数据库 版权所有 © 2006,Oracle。保留所有权利。 ASM:关键功能和优点 • 条带化文件(而不是逻辑卷) • 重新配置联机磁盘并以动态方式使其 重新平衡 • 可调整重新平衡速度 • 按文件提供冗余 • 只支持 Oracle 数据库文件 • 具有裸 I/O 的性能的数据库集群文件系统,在所有 存储平台上都是可用的 • 自动管理数据库文件 • 不再有热点:免除了手动进行 I/O 优化 . . ... . .. ............. . .. ............. . .. ........... .. . .... .. ............. . .. ............. . .. .......... ................ ................ ................................ ................ ................ 版权所有 © 2006,Oracle。保留所有权利。 每个数据库包含的磁盘组数 • 建议包含两个磁盘组: – 利用最大 LUN 数 – 互为备份 – 可以为 FRA(或内轨)使用 较低的性能 • 例外: – 不同的容量或性能特性需要更多的磁盘组 – ILM 存储层不同 数据 DG FRA DG ERP DB CRM DB HR DB 版权所有 © 2006,Oracle。保留所有权利。 数据库存储合并 • 跨多个数据库共享存储 – RAC 和单一实例可以使用同一个 ASM 实例 • 优点: – 简化了管理并使其更加集中 – 提高了存储使用率 – 改善了性能 工资表 总帐 工资表和总帐 10 ° 50 GB 10 ° 50 GB 10 ° 100 GB … … … 版权所有 © 2006,Oracle。保留所有权利。 可获得最佳可用性的 RAID 配置 A. ASM 镜像 B. 硬件 RAID 1(镜像) C. 硬件 RAID 5(奇偶校验保护) D. ASM 镜像和硬件 RAID 答案:根据业务需求和预算(成本、可用性、性能和使用率)而定 ASM 利用硬件 RAID 版权所有 © 2006,Oracle。保留所有权利。 ASM 镜像准则 • 是低成本存储的最佳选择 • 启用扩展集群解决方案 • 无硬件镜像 版权所有 © 2006,Oracle。保留所有权利。 ASM 条带化粒度 1MB AU 粗糙 精细 版权所有 © 2006,Oracle。保留所有权利。 最合适的条带类型 A. 仅 ASM 条带(无 RAID 0) B. RAID 0 和 ASM 条带 C. 使用 LVM D. 无条带 答案:A 和 B ASM 条带和 RAID 条带是互补的 版权所有 © 2006,Oracle。保留所有权利。 仅 ASM 条带 优点 • 为数据和 FRA 均匀分配驱动器 • 带宽更宽 • 允许较小的增量增长 (73 GB) • 无驱动器争用 数据 DG FRA DG 1 TB 2 TB16 个 73 GB LUN 32 个 73 GB LUN RAID 1 Oracle DB 大小:1 TB 存储配置: 8 个阵列,每个阵列 具有 12 个 73 GB 的磁盘 缺点 • 不能各磁盘之间达到很好的平衡 • LUN 大小限制为磁盘大小 版权所有 © 2006,Oracle。保留所有权利。 硬件 RAID 的条带化 LUN 优点 • 对于数据 DG 是最快速的区域 • 数据分配达到平衡 • 要管理的 LUN 较少,同时最大化了主轴数 缺点 • 较大的增量增长 • 存在数据和 FRA“争用” 数据 DG FRA DG 1 TB 2 TB4 个 250 GB LUN 4 个 500 GB LUN Oracle DB 大小:1 TB 存储配置: 8 个阵列,每个阵列具有 12 个 73 GB 的磁盘 RAID 0+1 版权所有 © 2006,Oracle。保留所有权利。 ASM 准则 • 尽可能使用外部 RAID 保护。 • 创建逻辑单元 (LUN) 时使用: – 磁盘驱动器的外半区,以实现最佳性能 – 较小的磁盘,较高的 rpm(例如 73 GB/15k rpm) • 使用具有相同性能特性的 LUN。 • 使用具有相同容量的 LUN。 • 最大化磁盘组中的主轴数。 版权所有 © 2006,Oracle。保留所有权利。 ASM 实例初始化参数 • ASM 实例需要静态内存。 • 对于大多数配置,使用默认 SGA 调整大小参数就足以 实现下列目的:在前五个磁盘组之后,使每个额外磁盘 组的共享池增加 500 KB。 INSTANCE_TYPE = ASM DB_UNIQUE_NAME = +ASM ASM_POWER_LIMIT = 1 ASM_DISKSTRING = '/dev/rdsk/*s2', '/dev/rdsk/c1*' ASM_DISKGROUPS = dgroupA, dgroupB PROCESSES = 25 + 15*<使用 ASM 进行存储的 DB 实例数> 版权所有 © 2006,Oracle。保留所有权利。 动态性能视图 存储系统 V$ASM_DISKGROUPV$ASM_CLIENT V$ASM_DISK V$ASM_FILE V$ASM_TEMPLATE V$ASM_ALIAS V$ASM_OPERATION 磁盘组 A 磁盘组 B 版权所有 © 2006,Oracle。保留所有权利。 使用 V$ASM_OPERATION 监视长时间运行的操作 用来终止操作的预计时间量(分钟)EST_MINUTES 每分钟移动的预计分配单元数EST_RATE 预计剩余分配单元数EST_WORK 到目前为止已移动的分配单元数SOFAR 为此操作分配的处理能力ACTUAL 为此操作请求的处理能力POWER 操作状态:WAIT 或 RUNSTATE 操作类型:REBALOPERATION 磁盘组GROUP_NUMBER 说明列 版权所有 © 2006,Oracle。保留所有权利。 ASM 实例性能诊断 SELECT event, total_waits t_wait, total_timeouts t_timeout, time_waited t_waittm, average_wait a_waittm, wait_class FROM V$SYSTEM_EVENT WHERE wait_class <> 'Idle' and time_waited > 0 ORDER BY 4 DESC; EVENT WAIT TOUT WAITT AVG CLASS ------------------------------ ------ ----- ----- ------- ------- ASM mount : wait for heartbeat 1 1 439 438.85 Admin… kfk: async disk IO 578 0 377 .65 SystI/O log write(odd) 7 3 296 42.33 Other rdbms ipc reply 37 1 259 7.01 Other log write(even) 8 2 197 24.58 Other SQL*Net message to client 139249 0 103 0 Network os thread startup 9 0 79 8.77 Conc… buffer write wait 1 0 60 60.31 Other DBFG waiting for reply 16 0 1 .04 Other 版权所有 © 2006,Oracle。保留所有权利。 ASM 性能页 版权所有 © 2006,Oracle。保留所有权利。 数据库实例参数更改 • 为 SHARED_POOL_SIZE 添加下列内容: • 使 PROCESSES 至少增加 16 SELECT d+l+t DB_SPACE FROM (SELECT SUM(bytes)/(1024*1024*1024) d FROM V$DATAFILE), (SELECT SUM(bytes)/(1024*1024*1024) l FROM V$LOGFILE a, V$LOG b WHERE a.group#=b.group#), (SELECT SUM(bytes)/(1024*1024*1024) t FROM V$TEMPFILE WHERE status='ONLINE'); (DB_SPACE/100+2)*#_External_Red OR (DB_SPACE/50+4)*#_Normal_Red OR (DB_SPACE/33+6)*#_High_Red 版权所有 © 2006,Oracle。保留所有权利。 ASM 磁盘元数据要求 • 对于空磁盘组: – 对于正常冗余和高冗余: 15 +(2*磁盘数)+(126 * ASM 实例数) – 对于外部冗余: 5+(2*磁盘数)+(42 * ASM 实例数) • 对于每个文件: – 高冗余:文件大小大于 20 MB 时增加 3 MB,加上每增 加 42 GB 增加 3 MB。 – 正常冗余:文件大小大于 30 MB 时增加 3 MB,加上每 增加 64 GB 增加 3 MB。 – 外部冗余:文件大小大于 60 MB 时增加 1 MB,加上每 增加 128 GB 增加 1 MB。 版权所有 © 2006,Oracle。保留所有权利。 ASM 可伸缩性 ASM 强制实施下列限制: • 63 个磁盘组 • 10,000 个 ASM 磁盘 • 每个 ASM 磁盘 4 PB • 40 EB 的存储空间 • 每个磁盘组 1 百万个文件 • 最大文件大小: – 外部冗余:35 TB – 正常冗余:5.8 TB – 高冗余:3.9 TB 版权所有 © 2006,Oracle。保留所有权利。 练习概览: 优化 I/O:演示 本练习包含以下主题: • 查看单磁盘系统上的故障现象和等待 • 查看使用 SAME 的多磁盘系统上的故障现象和等待 • 查看多磁盘 ASM 系统上的故障现象和等待 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 诊断数据库 I/O 问题 • 说明条带化和镜像所有内容 (SAME) 概念 • 说明异步 I/O 的优点 • 选择适合的 I/O 解决方案 • 使用自动存储管理 (ASM) 优化 I/O 版权所有 © 2006,Oracle。保留所有权利。 优化 PGA 和临时空间 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成下列工作: • 诊断 PGA 内存问题 • 调整 PGA 内存大小 • 诊断临时空间问题 • 指定临时表空间参数以进行高效操作 版权所有 © 2006,Oracle。保留所有权利。 SQL 内存使用情况 • 占用大量内存的 SQL 操作符: – 基于分类(分类、分组方式、汇总、窗口等等) – 散列联接 – 位图操作符(合并和反转) • 工作区概念: – 由占用大量内存的操作符分配的内存,用于处理其输入 数据 • 内存的性能影响: – 最佳:输入数据与工作区(高速缓存)相符 – 一遍执行:对输入数据额外执行一次 – 多遍执行:对输入数据额外执行多次 版权所有 © 2006,Oracle。保留所有权利。 性能影响 响应时间 内存 . . 一遍执行 最佳多遍执行 分类 响应时间 . .散列联接 M 一遍执行 M 最佳 M 一遍执行 M 最佳 一遍执行 最佳多遍执行 内存 版权所有 © 2006,Oracle。保留所有权利。 自动 PGA 内存 • 根据以下各项动态调整 SQL 内存分配: – 可用的 PGA 内存 – SQL 操作符的需要 – 系统工作量 • 提高可管理性: – 无需设置 *_AREA_SIZE 参数 – DBA 设置内存目标:PGA_AGGREGATE_TARGET • 提高性能: – PGA 内存实际上返回给 OS – 为操作分配内存,以最大化吞吐量 – 根据工作量变化动态调整内存大小,以最大化内存的 总体利用率 – 在执行期间,操作可以调整其内存使用量 版权所有 © 2006,Oracle。保留所有权利。 SQL 内存管理器 ... HJ GB HJ WP1 本地 SMM HJ GB WP5 服务器 1 服务器 n SGA 全局内存限制 全局 SMM (CKPT) 自动 PGA 目标总计 内存限制计算 每隔 3 秒WP2 本地 SMM WP3 WP4 V$SQL_WORKAREA_ACTIVE V$SQL_WORKAREA PGA 内存使用 情况统计数据 PGA_AGGREGATE_TARGET V$PROCESS V$PROCESS_MEMORY V$PGASTAT WP5 WP2 WP1 V$PGASTAT 自动目标计算WP3 WP4 版权所有 © 2006,Oracle。保留所有权利。 配置自动 PGA 内存 • PGA_AGGREGATE_TARGET: – 指定可用于实例的 PGA 内存的目标汇总量 – 可在实例级别以动态方式进行修改 – 示例:100,000 KB;2,500 MB;50 GB – 默认值:10 MB 或 SGA 大小的 20%(取两者中较大的值) • WORKAREA_SIZE_POLICY: – 可选 – 可在实例级别或会话级别以动态方式进行修改 – 对于特定会话,允许退回到静态 SQL 内存管理 版权所有 © 2006,Oracle。保留所有权利。 设置 PGA_AGGREGATE_TARGET 的初始值 • 为其它应用程序保留 20% 的可用内存。 • 为 Oracle 实例保留 80% 的内存。 • 对于 OLTP: • 对于 DSS: PGA_AGGREGATE_TARGET=(total_mem*80%)*20% PGA_AGGREGATE_TARGET=(total_mem*80%)*50% 版权所有 © 2006,Oracle。保留所有权利。 监视 SQL 内存使用情况 V$SQL_WORKAREA_ACTIVE V$SQL_WORKAREA V$PGASTAT V$SQL_PLANV$SQL V$SQL_WORKAREA_HISTOGRAMS V$PROCESS_MEMORY V$SYSSTATV$TEMPSEG_USAGE 版权所有 © 2006,Oracle。保留所有权利。 监视 SQL 内存使用情况:示例 SELECT sql_text, sum(onepass_executions) onepass_cnt, sum(multipasses_executions) mpass_cnt FROM V$SQL s, V$SQL_WORKAREA wa WHERE s.address = wa.address GROUP BY sql_text HAVING sum(onepass_executions+multipasses_executions)>0; SELECT TO_NUMBER(DECODE(sid, 65535, NULL, sid)) sid, operation_type OPERATION, TRUNC(expected_size/1024) ESIZE, TRUNC(actual_mem_used/1024) MEM, TRUNC(max_mem_used/1024) MAXMEM, number_passes PASS, TRUNC(tempseg_size/1024) TSIZE FROM V$SQL_WORKAREA_ACTIVE ORDER BY 1,2; 1 2 版权所有 © 2006,Oracle。保留所有权利。 优化 SQL 内存使用情况 • 通过以下视图确定 PGA_AGGREGATE_TARGET 的最佳值: – V$PGA_TARGET_ADVICE – V$PGA_TARGET_ADVICE_HISTOGRAM • 监视下列情况的 AWR 报表/Statspack 报表: – direct path read temp – direct path write temp 版权所有 © 2006,Oracle。保留所有权利。 PGA 目标建议统计数据 • V$PGA_TARGET_ADVICE 预测 V$PGASTAT 中显示的 高速缓存命中率将如何变化。 • 必须将 STATISTICS_LEVEL 至少设置为 TYPICAL。 V$PGA_TARGET_ADVICE 13% 25% 50% 75% 100% 120% 140% 160% 180% 200% 300% 400% PGA_TARGET_FACTORESTD_PGA_CACHE_HIT_PERCENTAGE 当前值过度分配区 有效值 ESTD_OVERALLOC_COUNT 0 10 20 30 40 50 60 70 80 90 100 版权所有 © 2006,Oracle。保留所有权利。 PGA 目标建议直方图 • V$PGA_TARGET_ADVICE_HISTOGRAM 预测 V$SQL_WORKAREA_HISTOGRAM 中显示的直方图将 如何变化 • 必须至少将 STATISTICS_LEVEL 设置为 TYPICAL 版权所有 © 2006,Oracle。保留所有权利。 自动 PGA 和 Oracle Enterprise Manager 版权所有 © 2006,Oracle。保留所有权利。 自动 PGA 和 AWR 报表 版权所有 © 2006,Oracle。保留所有权利。 临时表空间管理:概览 • 数据库生成的临时数据包括: – 位图合并 – 散列联接 – 位图索引创建 – 分类 – 临时 LOB – 全局临时表 • 在事务处理或会话期间保持数据。 • 空间管理操作的高并发性非常重要。 • 介质和实例恢复并不是必需的。 版权所有 © 2006,Oracle。保留所有权利。 临时表空间:最佳方法 使用本地管理的临时表空间: • 允许高并发性的空间管理。 – 在稳定状态下,所有空间元数据都缓存在 SGA 中。 – 操作已由 SGA 闩锁进行了序列化。 • 允许更快地对临时文件执行写入操作。临时块上生成的 重做不会写入磁盘。 • 使“只读”数据库成为可能。 版权所有 © 2006,Oracle。保留所有权利。 配置临时表空间 • 本地管理的临时表空间是统一区的表空间。 • 1 MB 到 10 MB 的区大小: – 对于 DSS 而言,OLAP 应用程序涉及庞大的工作区 – 大型临时 LOB 占主导地位 • 64 KB 或少于 1 MB 的 64 KB 的倍数: – 小型全局临时表占主导地位 – OLTP • 使用 V$TEMPSEG_USAGE 可监视空间使用情况及工作 量分配。 版权所有 © 2006,Oracle。保留所有权利。 临时表空间组:概览 • 对多个临时表空间进行分组 • 特征: – 至少有一个临时表空间 – 命名空间与表空间相同 – 在初次指定时隐式创建 – 没有显式删除 默认表空间示例 … 默认临时表空间组 TEMP 表空间 TEMP1 表空间 TEMPn 版权所有 © 2006,Oracle。保留所有权利。 临时表空间组:优点 使用户可以使用多个临时表空间: • 同一用户可处于多个会话中 • 一个特殊的并行操作 临时表空间组 TEMP 表空间 TEMP1 表空间 TEMP2 表空间 TEMP3 串行 并行HR HR 版权所有 © 2006,Oracle。保留所有权利。 创建临时表空间组 版权所有 © 2006,Oracle。保留所有权利。 维护临时表空间组 版权所有 © 2006,Oracle。保留所有权利。 数据字典更改 SELECT group_name, tablespace_name FROM DBA_TABLESPACE_GROUPS; 版权所有 © 2006,Oracle。保留所有权利。 监视临时表空间 • 使用 V$TEMPSEG_USAGE 可监视空间使用情况和工作 量分配: • • 使用 V$SORT_SEGMENT 可确定空间利用率百分比: SELECT session_num, username, segtype, blocks, tablespace FROM V$TEMPSEG_USAGE; SELECT (s.tot_used_blocks/f.total_blocks)*100 as pctused FROM (SELECT SUM(used_blocks) tot_used_blocks FROM V$SORT_SEGMENT WHERE tablespace_name='TEMP') s, (SELECT SUM(blocks) total_blocks FROM DBA_TEMP_FILES WHERE tablespace_name='TEMP') f; 版权所有 © 2006,Oracle。保留所有权利。 练习概览:优化 PGA 内存 本练习包含以下主题: • 启用自动 PGA 内存 • 优化 PGA_AGGREGATE_TARGET • 优化临时表空间性能 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 诊断 PGA 内存问题 • 调整 PGA 内存大小 • 诊断临时空间问题 • 指定临时表空间参数以进行高效操作 版权所有 © 2006,Oracle。保留所有权利。 优化块空间使用情况 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成下列工作: • 优化段空间管理 • 从字典管理的表空间进行转换 • 转换到自动段空间管理 • 优化块空间管理 • 诊断并纠正行迁移 • 诊断表碎片 • 对大文件表空间和小文件表空间的特征进行比较 版权所有 © 2006,Oracle。保留所有权利。 空间管理 可以在三个级别上对空间进行管理: • 使用文件(OS、ASM、裸分区)将磁盘空间分配给 表空间。 • 使用区将文件空间分配给表空间中的段。 • 使用块来组织数据对象中的空间。 版权所有 © 2006,Oracle。保留所有权利。 区管理 可以采用两种方式对区进行分配: • 字典管理 – 仅出于向后兼容性才支持此方式 – 在 EXT$ 和 FET$ 字典表中对区进行管理 – 递归 SQL • 本地管理 – 在文件头位图中对区进行管理 – 未在区操作上创建还原 – 文件头块中可能发生争用 版权所有 © 2006,Oracle。保留所有权利。 本地管理的区 • 创建本地管理的表空间: • • • • • • • 默认的区管理是本地管理。 SQL> CREATE TABLESPACE user_data_1 2 DATAFILE 3 ‘/oracle9i/oradata/db1/lm_1.dbf’ 4 SIZE 100M 5 EXTENT MANAGEMENT LOCAL 6 UNIFORM SIZE 2M; 版权所有 © 2006,Oracle。保留所有权利。 大型区的优缺点 • 优点: – 不太可能以动态方式进行扩展 – 提供少量性能优势 – 通过单个 I/O 操作,使服务器进程能够读取整个区映射 • 缺点: – 可能无法使用可用空间 – 可能包含未使用的空间 版权所有 © 2006,Oracle。保留所有权利。 将 SYSTEM 表空间迁移到本地管理的表空间 • 使用 DBMS_SPACE_ADMIN 程序包: • 本地管理的 SYSTEM 表空间限制由迁移过程强制执行。 • 仅当系统处于“限制”模式,而除 SYSTEM、UNDO 和 TEMP 之外的所有表空间均处于“只读”模式时,迁移 才是可能的。 • 在转换 SYSTEM 之前,请使用相同的过程转换其它字 典管理的表空间。 SQL> EXECUTE DBMS_SPACE_ADMIN. - 2 TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); 版权所有 © 2006,Oracle。保留所有权利。 表数据的存储过程 表空间 表 A 表 B 段段 行 列 表 块 行片段 区 版权所有 © 2006,Oracle。保留所有权利。 数据库块的结构 块头 可用空间 行数据 增长 版权所有 © 2006,Oracle。保留所有权利。 最大限度地减少块访问 通过以下方式可最大限度地减少块访问: • 使用较大的块大小 • 紧密压缩行 • 阻止行迁移 表空间 块 段 区 版权所有 © 2006,Oracle。保留所有权利。 DB_BLOCK_SIZE 参数 数据库块大小: • 由 DB_BLOCK_SIZE 参数进行定义 • 在创建数据库时进行设置 • 成为表空间和缓冲区高速缓存的默认块大小 • 是数据文件读取的最小 I/O 单元 • 默认情况下为 8 KB,在大多数平台上最高允许 32 KB • 不能轻易进行更改 • 应是操作系统 (OS) 块大小的整数倍 • 应小于或等于 OS I/O 大小 版权所有 © 2006,Oracle。保留所有权利。 较小的块大小:考虑事项 • 优点: – 减少块争用 – 适合于较小的行 – 适合于随机访问 • 缺点: – 空间开销相对较高 – 每块的行数较少 – 可能导致要读取的索引块增多 版权所有 © 2006,Oracle。保留所有权利。 较大的块大小:考虑事项 • 优点: – 空间开销较低 – 适合于顺序访问 – 适合于非常大的行 – 索引读取性能更好 • 缺点: – 块争用增加 – 在缓冲区高速缓存中使用较多的空间 版权所有 © 2006,Oracle。保留所有权利。 块分配 • 当 INSERT 或 UPDATE 操作需要更多空间时,必须 找到具有足够空间的块。 • 有两种方法: – 可用列表 – 自动段空间管理 (ASSM) 版权所有 © 2006,Oracle。保留所有权利。 可用列表 可用列表管理的空间的特征: • 段头块保存可用列表。 • 可以将块添加到可用列表或从可用列表中删除。 • 可以搜索可用列表以查找可用的块。 • 段头已被固定以便对可用列表进行搜索和更新。 版权所有 © 2006,Oracle。保留所有权利。 块空间管理 每个段都有用于控制块的内部空间使用情况的参数。对于表: • PCTFREE:为更新保留的空间数量。 • PCTUSED:在将块放置到可用列表之前,块中可用空间 的最低级别。 对于索引: • PCTFREE:创建索引时为新索引条目保留的空间数量。 • PCTUSED:对于索引始终为 0。 版权所有 © 2006,Oracle。保留所有权利。 使用可用列表的块空间管理 插入 插入 插入 1 3 2 4 插入 PCTFREE PCTUSED 版权所有 © 2006,Oracle。保留所有权利。 自动段空间管理 自动段空间管理 (ASSM) 的特征: • 使用位图块 (BMB) 管理空间。 • 多个进程搜索不同的 BMB。 • 使用块满标志位表示块的可用性。 • 块满程度用每个百分比( 25%、50%、75% 和 100%) 的块满标志位来表示。 版权所有 © 2006,Oracle。保留所有权利。 工作时的自动段空间管理 区 BMB BMB BMB BMB BMB 数据 BMB BMB BMB BMB BMB { 段 块 ……… … … … … … … … …BMB BMB 版权所有 © 2006,Oracle。保留所有权利。 使用 ASSM 的块空间管理 插入 插入 插入 1 3 2 4 插入 25% 50% 75% 版权所有 © 2006,Oracle。保留所有权利。 创建自动段空间管理段 • SEGMENT SPACE MANAGEMENT 是用于创建表空间的属 性,以后不能对该属性进行修改。 • 段空间管理是在表空间级别上声明的。 • 表空间必须是永久性的和本地管理的。 • 自动空间管理段是通过 AUTO 关键字指定的。 • 对于可用列表段,会使用默认值 MANUAL。 • 对于 ASSM,在创建表时将忽略 PCTUSED、FREELIST 和 FREELIST GROUPS。 版权所有 © 2006,Oracle。保留所有权利。 迁移和链接 索引 表 迁移 链接 版权所有 © 2006,Oracle。保留所有权利。 PCTFREE 和 PCTUSED 的准则 • PCTFREE – 默认值:10 – 如果没有 UPDATE 活动,则为零 – PCTFREE = 100 × UPD /(平均行长) • PCTUSED – 仅用于可用列表 – 默认值:40 – 如果删除多个行,则设置此参数 – PCTUSED = 100 - PCTFREE -(100 ×行数× 平均行长 / 块大小) 版权所有 © 2006,Oracle。保留所有权利。 检测迁移和链接 使用 ANALYZE 命令检测迁移和链接: 使用 Statspack/AWR 检测迁移和链接: Statistic Total Per transaction ... ------------------------- ----- --------------- ... table fetch continued row 495 .02 … SQL> ANALYZE TABLE oe.orders COMPUTE STATISTICS; Table Analyzed. SQL> SELECT num_rows, avg_row_len, chain_cnt 2 FROM DBA_TABLES 3 WHERE table_name='ORDERS'; NUM_ROWS AVG_ROW_LEN CHAIN_CNT ---------- ----------- ---------- 1171 67 83 版权所有 © 2006,Oracle。保留所有权利。 选择已迁移的行 SQL> ANALYZE TABLE oe.orders LIST CHAINED ROWS; Table analyzed. SQL> SELECT owner_name, table_name, head_rowid 2 FROM chained_rows 3 WHERE table_name = 'ORDERS'; OWNER_NAME TABLE_NAME HEAD_ROWID ---------- ---------- ------------------ SALES ORDER_HIST AAAAluAAHAAAAA1AAA SALES ORDER_HIST AAAAluAAHAAAAA1AAB ... 版权所有 © 2006,Oracle。保留所有权利。 消除已迁移的行 • 导出/导入: – 导出表 – 删除或截断表 – 导入表 • MOVE 表命令: – ALTER TABLE EMPLOYEES MOVE • 重新定义联机表 • 复制已迁移的行: – 使用 ANALYZE 查找迁移的行 – 将迁移的行复制到新表 – 从原始表删除迁移行 – 将行从新表复制到原始表 版权所有 © 2006,Oracle。保留所有权利。 收缩段:概览 HWM HWM 收缩操作 数据 未使用 的空间 数据 未使用 的空间 回收的空间 版权所有 © 2006,Oracle。保留所有权利。 收缩段:考虑事项 • 收缩操作是联机的就地操作。 • 它仅适用于位于 ASSM 表空间中的段。 • 候选段类型为: – 按堆组织的表和按索引组织的表 – 索引 – 分区和子分区 – 实体化的视图和实体化的视图日志 • 对索引进行维护。 • 不会触发触发器。 版权所有 © 2006,Oracle。保留所有权利。 通过使用 SQL 收缩段 ALTER … SHRINK SPACE [COMPACT][CASCADE] TABLE [OVERFLOW] INDEX MATERIALIZED VIEW MATERIALIZED VIEW LOG MODIFY PARTITION MODIFY SUBPARTITION MODIFY LOB ALTER TABLE employees SHRINK SPACE CASCADE; ALTER TABLE employees ENABLE ROW MOVEMENT; 1 2 ALTER TABLE employees MODIFY LOB(resume) (SHRINK SPACE);3 ALTER TABLE employees OVERFLOW SHRINK SPACE; 4 版权所有 © 2006,Oracle。保留所有权利。 段收缩:基本执行 HWM ALTER TABLE employees SHRINK SPACE COMPACT; HWM HWM ALTER TABLE employees SHRINK SPACE; 1 2 版权所有 © 2006,Oracle。保留所有权利。 段收缩:执行的考虑事项 • 仅在下列情况下使用压缩: – 为避免不必要的游标失效 – 在高峰时段 • 在压缩期间,可以执行 DML 操作和查询。 • 在调整 HWM 时,会阻止 DML 操作。 版权所有 © 2006,Oracle。保留所有权利。 使用 EM 来收缩段 版权所有 © 2006,Oracle。保留所有权利。 大文件表空间:概览 • 大文件表空间包含单个文件。 • 最大文件大小介于 8 TB 到 128 TB 之间。 • 表空间在逻辑上等同于数据文件。 数据库 表空间 小文件数据 文件 大文件 数据 文件 版权所有 © 2006,Oracle。保留所有权利。 大文件表空间:优点 • 显著增加存储容量 • 通过使表空间成为磁盘空间管理的主要单元,简化了 大型数据库的数据文件管理 BFT 1 … 8 EB40 亿个块 一对一映射 BFT n 版权所有 © 2006,Oracle。保留所有权利。 使用大文件表空间 • 仅为使用自动段空间管理的本地管理的表空间提供支持 • 与逻辑卷管理器或自动存储管理 (ASM) 一起使用 • OMF 可用于实现完全的数据文件透明性 800,00016 KB 400,0008 KB 200,0004 KB 100,0002 KB 建议的最大区数数据库块大小 版权所有 © 2006,Oracle。保留所有权利。 练习概览: 优化数据库空间利用率 本练习包含以下主题: • 从字典管理的表空间转换为本地管理的表空间 • 诊断可用列表争用 • 将段从手动段空间管理转换为自动段空间管理 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 优化段空间管理 • 从字典管理的表空间进行转换 • 转换到自动段空间管理 • 优化块空间管理 • 诊断并纠正行迁移 • 诊断表碎片 • 对大文件表空间和小文件表空间的特征进行比较 版权所有 © 2006,Oracle。保留所有权利。 性能优化:小结 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成下列工作: • 列出该课程中确定的最佳方案 • 总结性能优化方法 版权所有 © 2006,Oracle。保留所有权利。 对性能几乎没有影响的必要初始化参数 设置为可以指定给数据库的最大文件数DB_FILES 设置为 TYPICALSTATISTICS_LEVEL 设置为在不同的磁盘驱动器上至少包含两个文件, 以避免因控制文件丢失而出现故障 CONTROL_FILES 限制每个会话的最大游标数。该设置与应用程序相 关,建议设置为 500 OPEN_CURSORS 以 Internet 点标记表示的数据库位置DB_DOMAIN 数据库的名称。此名称应与 ORACLE_SID 环境变 量相匹配 DB_NAME 说明参数 版权所有 © 2006,Oracle。保留所有权利。 对性能有影响的重要初始化参数 实例要使用的还原表空间UNDO_TABLESPACE 建议使用 AUTO 模式UNDO_MANAGEMENT 必须用于共享服务器SESSIONS 该实例可以启动的最大进程数PROCESSES 自动 PGA 管理PGA_AGGREGATE_TARGET 自动调整大小的 SGA 组件SGA_TARGET 对于 OLTP 为 8192,对于 DSS 为更高的值DB_BLOCK_SIZE 用于利用新版本的最新改进COMPATIBLE 说明参数 版权所有 © 2006,Oracle。保留所有权利。 最初调整内存大小 最初估计的内存分配如下: • 保留可用内存的 20% 供其它应用程序使用。 • 保留 80% 的内存供 Oracle 实例使用。 • 对于 OLTP: • 对于 DSS: PGA_AGGREGATE_TARGET=(total_mem*80%)*20% PGA_AGGREGATE_TARGET=(total_mem*80%)*50% SGA_TARGET=(total_mem*80%)*80% SGA_TARGET=(total_mem*80%)*50% 版权所有 © 2006,Oracle。保留所有权利。 数据库高可用性:最佳方案 • 使用 SPFILE。 • 多路复用重做日志。 • 使用可恢复的空间分配。 • 至少创建两个控制文件。 • 启用闪回数据库。 • 启用块检查。 • 使用自动优化检查点。 • 将检查点记录到预警日志中。 • 使用数据库资源管理器。 • 使用自动还原管理。 • 使用自动段空间管理。 • 使用本地管理的表空间。 • 使用本地管理的临时表空间。 • 启用 ARCHIVELOG 模式并使用快速恢复区。 • 为 CONTROL_FILE_RECORD_KEEP_TIME 设置足够长的时间。 • 指定除 SYSTEM 和 SYSAUX 以外的默认永久表空间。 版权所有 © 2006,Oracle。保留所有权利。 还原表空间:最佳方案 • 使用自动还原管理。 • UNDO_RETENTION: – Oracle Database 10g 版本 1:根据闪回要求进行设置。 – Oracle Database 10g 版本 2:不设置此参数。 • 还原表空间大小: – 初始大小:很小并启用 AUTOEXTEND。 – 稳定状态:使用还原指导固定大小,为安全起见,将此 大小增加 20%。 版权所有 © 2006,Oracle。保留所有权利。 临时表空间:最佳方案 本地管理的临时表空间使用统一的区。区大小应为: • 1 MB 到 10 MB 的区大小: – 用于涉及大量工作区的 DSS 应用程序和 OLAP 应用程序 – 大型临时 LOB 占主导地位 • 64 KB 或 64 KB 的倍数,但小于 1 MB: – 小型全局临时表占主导地位 – OLTP 临时表空间组将寻址能力从 TB 数量级提高到 PB 数量级。 版权所有 © 2006,Oracle。保留所有权利。 常规表空间:最佳方案 • 使用本地管理的表空间以及自动分配区策略。 • 使用自动段空间管理 (ASSM)。 • 使用联机段压缩以避免内部碎片。 • 定期检查自动段指导的结果。 • 使用服务器生成的预警来监视表空间的空间使用情况。 • 区大小比段中的区数量更重要。 版权所有 © 2006,Oracle。保留所有权利。 内部碎片的考虑事项 • 观察是否存在下列情况: – 堆段的 PCTFREE 和 PCTUSED 选择不当 – LOB 段的 PCTVERSION 和 RETENTION 选择不当 – 段中的数据密度低 – 删除之后直接装入(无插入) – 表上的索引包含随机更新和删除操作,但是没有进一步 的插入 • 补救措施: – 联机段压缩 – 联机重新定义 – MOVE 操作 版权所有 © 2006,Oracle。保留所有权利。 块大小:优点和缺点 存在索引叶块争用 (OLTP)适合连续访问 在随机访问较小的行时, 会浪费高速缓存空间 开销较低:可以使用更多 的空间来存储数据 较大 适用于较大的行(链接)块争用有所减少 (OLTP) 开销相对较高(块头)行较小,并且可以进行大 量的随机访问 较小 缺点优点块大小 版权所有 © 2006,Oracle。保留所有权利。 调整重做日志文件的大小 • 重做日志文件的大小可能会影响性能。 • 重做日志文件越大,性能就越好。 • 通常,重做日志文件的大小范围在 100 MB 到几个 GB 之间。 • 最多每 20 分钟切换一次重做日志文件。 • 使用重做日志文件大小指导来正确地调整重做日志的 大小。 版权所有 © 2006,Oracle。保留所有权利。 自动收集统计数据 • STATISTICS_LEVEL = TYPICAL | ALL • 统计数据由预定义的 GATHER_STATS_JOB 作业进行 收集 • 此作业隐式确定下列内容: – 统计数据丢失或过期的数据库对象 – 收集有关这些对象的有效统计数据时所需的相应抽样 百分比 – 需要直方图以及这些直方图的大小的相应列 – 收集统计数据时的并行度 – 收集其统计数据的对象的优先级 版权所有 © 2006,Oracle。保留所有权利。 自动收集统计数据:考虑事项 • 在下列情况下,仍需要手动收集统计数据: – 执行批量操作之后 – 使用外部表时 – 收集系统统计数据时 – 收集固定对象的统计数据时 • 禁止自动收集易失表的统计数据: – 统计数据包含典型值时进行锁定 – 没有统计数据时的锁定意味着要进行动态抽样 版权所有 © 2006,Oracle。保留所有权利。 常见的等待事件 V$SYSSTAT 视图中的提交数 和回退数,磁盘数 I/O 速度慢,未批量 提交 过度提交,I/OLog file sync V$SYSSTAT 重做缓冲区分配 重试次数,磁盘 缓冲区小,I/O 速度慢日志缓冲区 I/Olog buffer space V$SQLAREA 分析调用, 子游标 SQL 分析/共享闩锁Library cache waits V$ENQUEUE_STAT取决于入队类型锁Enqueue waits (enq:) V$SQLAREA 磁盘读取数 V$FILESTAT 读取时间 SQL 优化不当,I/O 系统速度慢 I/O,SQL 优化db file scattered read、db file sequential read 使用 OS 统计数据时的写入时 间,缓冲区高速缓存统计数据 DBWR 速度慢缓冲区高速缓存, DBWR,I/O free buffer waits 出现问题时的 V$SESSION (块) 取决于缓冲区类型 PK 索引和序列 缓冲区高速缓存, DBWR buffer busy waits 检查可能的原因区域等待事件 版权所有 © 2006,Oracle。保留所有权利。 其它统计数据 使用自动还原功能,优 化工作量 为了保证读取一致性而 执行的回退数 Consistent changes 应对检查点、DBWR 或 存档进程活动进行优化, 增大日志文件 服务器进程 等待联机重做日志中的 空间的次数 Redo Log Space Requests 使用自动还原功能,优 化工作量 在一致读取模式下读取 的块数 Consistent gets 重新组织迁移的行或链接的行Table Fetch by Continued Row 建议的操作说明统计数据名称 版权所有 © 2006,Oracle。保留所有权利。 Oracle 系统中最常见的前 10 个错误 1. 连接管理不当 2. 游标和共享池使用不当 3. 不良 SQL 4. 使用了非标准初始化参数 5. 获取数据库 I/O 时出错 6. 重做日志设置问题 7. 缓冲区高速缓存中的数据块序列化 8. 长时间的全表扫描 9. 大量递归的 SQL 10. 部署错误和迁移错误 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 按照该课程中讲述的最佳方案创建初始数据库 • 总结性能优化方法 版权所有 © 2006,Oracle。保留所有权利。 服务 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成下列工作: • 配置并管理服务 • 通过客户机应用程序使用服务 • 通过数据库资源管理器使用服务 • 通过调度程序使用服务 • 设置服务的性能度量阈值 • 配置服务汇总和跟踪 版权所有 © 2006,Oracle。保留所有权利。 传统工作量分派 HR DW CRM BATCH 白天 HR DW CRM BATCH 假日 HR DW CRM BATCH 发薪日 版权所有 © 2006,Oracle。保留所有权利。 网格工作量分派 白天 假日发薪日 HRDW CRM BATCH 空闲 HR CRM 空闲 BATCH DW CRM BATCH HR 空闲 DW 版权所有 © 2006,Oracle。保留所有权利。 什么是服务 • 将执行同一种工作的会话分组的一种手段 • 提供单一系统映像,而不是提供多实例映像 • 一种常规管理任务,用于提供服务到实例的动态分配 • 实现连接高可用性的基础 • 提供新的性能优化维度 版权所有 © 2006,Oracle。保留所有权利。 RAC 中服务的高可用性 • 通过在一个或多个实例间分担负载,实现服务的连续 可用性。 • 在出现故障时,会增加可用的实例数。 • 首选实例: – 设置服务的初始基数 – 是最先启动服务的实例 • 首选实例出现故障时,将使用可用实例。 版权所有 © 2006,Oracle。保留所有权利。 RAC 的可能服务配置 AP RAC01 RAC02 RAC03 GL AP GL 活动/备用 AP RAC01 RAC02 RAC03 GL AP GL AP GL 活动/均衡 AP RAC01 RAC02 RAC03 GL AP GL AP GL 活动/非均衡 版权所有 © 2006,Oracle。保留所有权利。 服务属性 • 全局唯一名称 • 网络名称 • 负载平衡建议目标 • 分布式事务处理标记 • OCI 客户机和 ODP.NET 客户机的提前排队通知特性 • 故障转移特性 • 连接负载平衡算法 • 阈值 • 优先级 • 高可用性配置 版权所有 © 2006,Oracle。保留所有权利。 服务类型 • 应用程序服务 • 内部服务: – SYS$BACKGROUND – SYS$USERS – 不能删除或更改 • 每个数据库 64 项服务的限制: – 62 项应用程序服务 – 2 项内部服务 版权所有 © 2006,Oracle。保留所有权利。 创建服务 • 服务在数据字典中进行维护 • 使用 DBMS_SERVICE.CREATE 为单一实例数据库创建 服务 • 服务将根据 SERVICE_NAMES 初始化参数自动进行创建 • 使用下列工具在 RAC 中创建服务: – Database Configuration Assistant (DBCA) – SRVCTL – Oracle Enterprise Manager • 高可用性业务规则在 OCR 中进行维护,并通过 Oracle Clusterware 进行管理 版权所有 © 2006,Oracle。保留所有权利。 在单一实例环境中管理服务 • 创建新服务 • 启动服务 • 停止服务 • 删除服务 • 断开使用服务连接的会话 exec DBMS_SERVICE.CREATE_SERVICE('SERV1','SERV1.oracle.com'); exec DBMS_SERVICE.START_SERVICE('SERV1'); exec DBMS_SERVICE.STOP_SERVICE('SERV1'); exec DBMS_SERVICE.DELETE_SERVICE('SERV1'); exec DBMS_SERVICE.DISCONNECT_SESSION('SERV1'); 版权所有 © 2006,Oracle。保留所有权利。 一切转向服务 • 数据字典维护服务 • AWR 评估服务的性能 • 数据库资源管理器优先使用服务,而不是用户 • 使用服务运行作业调度程序、并行查询 (PQ) 和流队列 • RAC 保持服务在站点内可用 • Data Guard 中介和 RAC 保持主要服务在多个站点中可用 版权所有 © 2006,Oracle。保留所有权利。 通过客户机应用程序使用服务 ERP=(DESCRIPTION= (LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=TCP)(HOST=node-1vip)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=node-2vip)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=node-3vip)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=node-4vip)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=ERP))) url="jdbc:oracle:oci:@ERP" url="jdbc:oracle:thin:@(DESCRIPTION= (LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=TCP)(HOST=node-1vip)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=node-2vip)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=node-3vip)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=node-4vip)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=ERP)))" 版权所有 © 2006,Oracle。保留所有权利。 通过资源管理器使用服务 • 使用者组将基于会话服务自动分配给会话 • 按服务确定工作在一个实例内的优先级 AP 实例资源 BATCH 75% 25% AP BATCH 连接 版权所有 © 2006,Oracle。保留所有权利。 使用 EM 配置服务和资源管理器 版权所有 © 2006,Oracle。保留所有权利。 服务和资源管理器:示例 exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA; exec DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP( CONSUMER_GROUP => 'HIGH_PRIORITY', COMMENT => 'High priority consumer group'); exec DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'AP', CONSUMER_GROUP => 'HIGH_PRIORITY'); exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA; exec - DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(- GRANTEE_NAME => 'PUBLIC', CONSUMER_GROUP => 'HIGH_PRIORITY', GRANT_OPTION => FALSE); 版权所有 © 2006,Oracle。保留所有权利。 通过调度程序使用服务 • 服务与调度程序类相关联 • 调度程序作业具有服务关系: – 高可用性 – 负载平衡 数据库 作业 1 HOT_BATCH_CLASS HOT_BATCH_SERV 作业表 作业 2 HOT_BATCH_CLASS HOT_BATCH_SERV 作业 3 LOW_BATCH_CLASS LOW_BATCH_SERV HOT_BATCH_SERV 作业协调程序 作业从属进程 HOT_BATCH_SERV 作业协调程序 作业从属进程 LOW_BATCH_SERV 作业协调程序 作业从属进程 版权所有 © 2006,Oracle。保留所有权利。 使用 EM 配置服务和调度程序 版权所有 © 2006,Oracle。保留所有权利。 使用 EM 配置服务和调度程序 版权所有 © 2006,Oracle。保留所有权利。 服务和调度程序:示例 DBMS_SCHEDULER.CREATE_JOB_CLASS( JOB_CLASS_NAME => 'HOT_BATCH_CLASS', RESOURCE_CONSUMER_GROUP => NULL , SERVICE => 'HOT_BATCH_SERV' , LOGGING_LEVEL => DBMS_SCHEDULER.LOGGING_RUNS, LOG_HISTORY => 30, COMMENTS => 'P1 batch'); DBMS_SCHEDULER.CREATE_JOB( JOB_NAME => 'my_report_job', JOB_TYPE => 'stored_procedure', JOB_ACTION => 'my_name.my_proc();', NUMBER_OF_ARGUMENTS => 4, START_DATE => SYSDATE+1, REPEAT_INTERVAL => 5, END_DATE => SYSDATE+30, JOB_CLASS => 'HOT_BATCH_CLASS', ENABLED => TRUE, AUTO_DROP => false, COMMENTS => 'daily status'); 版权所有 © 2006,Oracle。保留所有权利。 通过并行操作使用服务 • 从属进程从协调程序中继承服务 • 可以在每个实例上执行从属进程 ERP 执行 协调程序 并行执行 服务器 共享磁盘 节点 4节点 1 节点 2 节点 3ERP ERP ERP ERP ERP ERP ERP ERP 版权所有 © 2006,Oracle。保留所有权利。 通过度量阈值使用服务 • 可以定义服务级阈值: – ELAPSED_TIME_PER_CALL – CPU_TIME_PER_CALL • 违反阈值时会触发服务器生成的预警 • 可以对生成的预警采取以下措施: – 更改优先级 – 重新定位服务 – 为服务添加实例 SELECT service_name, elapsedpercall, cpupercall FROM V$SERVICEMETRIC; 版权所有 © 2006,Oracle。保留所有权利。 使用 EM 更改服务阈值 版权所有 © 2006,Oracle。保留所有权利。 服务和度量阈值:示例 必须在支持服务的每个实例上设置阈值 exec DBMS_SERVER_ALERT.SET_THRESHOLD(- METRICS_ID => dbms_server_alert.elapsed_time_per_call, WARNING_OPERATOR => dbms_server_alert.operator_ge, WARNING_VALUE => '500000', CRITICAL_OPERATOR => dbms_server_alert.operator_ge, CRITICAL_VALUE => '750000', OBSERVATION_PERIOD => 15, CONSECUTIVE_OCCURRENCES => 3, INSTANCE_NAME => 'I0n', OBJECT_TYPE => dbms_server_alert.object_type_service, OBJECT_NAME => 'ERP'); 版权所有 © 2006,Oracle。保留所有权利。 服务汇总和跟踪 • 始终按服务来汇总统计数据,以评估用于性能优化的 工作量 • 可以在更详细的级别上汇总统计数据: – MODULE – ACTION – SERVICE_NAME、MODULE、ACTION 的组合 • 可以在不同的级别上进行跟踪: – SERVICE_NAMES – MODULE – ACTION – SERVICE_NAME、MODULE、ACTION 的组合 • 适用于使用共享会话的优化系统 版权所有 © 2006,Oracle。保留所有权利。 顶级服务性能页 版权所有 © 2006,Oracle。保留所有权利。 服务汇总配置 • 统计数据的自动服务汇总级别 • DBMS_MONITOR 用于粒度更细的服务汇总: – SERV_MOD_ACT_STAT_ENABLE – SERV_MOD_ACT_STAT_DISABLE • 其它可能的汇总级别: – SERVICE_NAME/MODULE – SERVICE_NAME/MODULE/ACTION • 跟踪服务、模块和操作: – SERV_MOD_ACT_TRACE_ENABLE – SERV_MOD_ACT_TRACE_DISABLE • 在实例重新启动时将保留数据库设置 版权所有 © 2006,Oracle。保留所有权利。 服务汇总:示例 • 收集服务和模块的统计数据: • 收集服务、模块和操作的统计数据: • 跟踪整个服务的所有会话: • 跟踪服务、模块和操作: exec DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(- 'AP', 'PAYMENTS'); exec DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(- 'AP', 'PAYMENTS', 'QUERY_DELINQUENT'); exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('AP'); exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(- 'AP', 'PAYMENTS', 'QUERY_DELINQUENT'); 版权所有 © 2006,Oracle。保留所有权利。 客户机标识符汇总和跟踪 • 收集客户机标识符的统计数据: • 查看收集的数据: • 禁用统计数据收集: • 跟踪客户机标识符: exec DBMS_MONITOR.CLIENT_ID_STAT_ENABLE('HR.HR'); SELECT * FROM V$CLIENT_STATS; exec DBMS_MONITOR.CLIENT_ID_STAT_DISABLE('HR.HR'); exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(- client_id => 'HR.HR',waits => TRUE, binds => FALSE); 版权所有 © 2006,Oracle。保留所有权利。 trcsess 实用程序 客户机 专用 服务器 跟踪 文件 客户机 共享 服务器 跟踪 文件 共享 服务器 跟踪 文件 共享 服务器 跟踪 文件 客户机 专用 服务器 跟踪 文件 TRCSESS 一个客户机 的跟踪文件TKPROF 报表 文件 TRCSESS CRM 服务 的跟踪文件 客户机 专用 服务器 跟踪 文件 CRM ERP CRM CRM ERP CRM 版权所有 © 2006,Oracle。保留所有权利。 服务性能视图 • 服务、模块和操作信息位于: – V$SESSION – V$ACTIVE_SESSION_HISTORY • 服务性能信息位于: – V$SERVICE_STATS – V$SERVICE_EVENT – V$SERVICE_WAIT_CLASS – V$SERVICEMETRIC – V$SERVICEMETRIC_HISTORY – V$SERV_MOD_ACT_STATS – DBA_ENABLED_AGGREGATIONS – DBA_ENABLED_TRACES • 服务的 28 项统计数据 版权所有 © 2006,Oracle。保留所有权利。 通用跟踪启用 • 对于数据库中的所有会话: • 对于特定会话: EXEC DBMS_MONITOR.DATABASE_TRACE_ENABLE(TRUE,TRUE); EXEC DBMS_MONITOR.DATABASE_TRACE_DISABLE(); EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27, serial_num => 60, waits => TRUE, binds => FALSE); EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 27, serial_num => 60); 版权所有 © 2006,Oracle。保留所有权利。 跟踪自己的会话 • 启用跟踪: • 禁用跟踪: EXEC DBMS_SESSION.SESSION_TRACE_DISABLE(); EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE); 版权所有 © 2006,Oracle。保留所有权利。 练习 C:概览 本练习包含以下主题: • 在单一实例环境中使用服务 • 在单一实例环境中跟踪服务 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何: • 配置并管理服务 • 通过客户机应用程序使用服务 • 通过数据库资源管理器使用服务 • 通过调度程序使用服务 • 设置服务的性能度量阈值 • 配置服务汇总和跟踪
还剩479页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 20 金币 [ 分享pdf获得金币 ] 20 人已下载

下载pdf

pdf贡献者

yangjueshe

贡献于2010-10-07

下载需要 20 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf