Oracle数据库性能优化精解.ppt

李轶楠Mail ora 600 13331192030技术服务人生 Oracle数据库性能优化精解 李轶楠Mail ora 600 13331192030技术服务人生 诊断工具中的七种武器 多情环 sqltuningadvisor sqlaccessadvisor 多情环似乎是一个情种 谁拥有它似乎都会产生感情 从而对许多江湖事看的很淡 在Oracle应用中 谁对性能影响最大 不言而喻 是SQL 准确地说是SQL语句的算法 可以说 80 以上的性能问题都可以通过调整SQL来解决或者缓解 拥有调优SQL性能的能力 基本上可以算作一个DBA高手咯 李轶楠Mail ora 600 13331192030技术服务人生 以前检查系统使用情况查看等待事件查看数据库分散读取上的等待事件通过以下方法识别SQL 难以操作 识别具有大量数据库分散读取等待事件的会话并跟踪它们 或者在OEM中查看最突出的会话获得解释计划检查被访问的对象 大小 基数 查看SQL统计信息和 或与对象统计信息相比较 v sql 难以操作 识别问题联系打包应用程序的供应商为供应商提供测试方案供应商提供补丁 升级安装在客户的下一个维护周期中的补丁 升级 Oracle10g查看ADDM建议根据链接来运行自动SQL调整接受来自SQL调整的SQL描述文件建议 李轶楠Mail ora 600 13331192030技术服务人生 执行计划 执行计划是一系列的优化器用来完成SQL操作的步骤和操作 李轶楠Mail ora 600 13331192030技术服务人生 曾经我们如何查看执行计划 通过下面的工具能够看到执行计划EXPLAINPLANV SQL PLANSQLTraceSQL PlusAUTOTRACE看到执行计划不是目的 优化与分析仍然靠DBA去努力 李轶楠Mail ora 600 13331192030技术服务人生 SQL调优建议 SQLTuning AccessAdvisors能够对系统中的SQL语句提供优化指导从多个不同的方向为SQL提供优化建议建议包括了 统计信息的重新收集 创建 删除索引 创建 删除物化视图 是否需要物化视图日志 SQL语句的书写以及固化执行计划的SQLProfiling通过存储在Oracle内部的SQLProfiling能够在不改变SQL代码的基础上强制执行计划 SQLProfile PackagedApps Indexes MVs Partitions Well tunedSQL CustomizableApps 李轶楠Mail ora 600 13331192030技术服务人生 SQLTuningAdvisorOverview AddMissingIndexes ModifySQLConstructs CreateaSQLProfile AutomaticTuningOptimizer SQLStructureAnalysis AccessPathAnalysis SQLProfiling StatisticsAnalysis GatherMissingorStaleStatistics DBA SQLTuningRecommendations SQLTuningAdvisor 李轶楠Mail ora 600 13331192030技术服务人生 SQLTuningUsageScenarios SQLTuningAdvisor ADDM High loadSQL CursorCache AWR SQLTuningSet STS User defined Filter Rank SQLSources ManualSelection AutomaticSelection AWR 李轶楠Mail ora 600 13331192030技术服务人生 SQLTuninginOracleDatabase10gEnd to EndWorkflow Workload AWR onehour Agoodend to endsolution butmanualinterventionisrequired 李轶楠Mail ora 600 13331192030技术服务人生 AutomaticSQLTuninginOracle11g It sAutomatic Workload 李轶楠Mail ora 600 13331192030技术服务人生 AutomaticSQLTuning 完全自动的SQL优化自动捕捉高负载的SQL自动创建SQLProfile 不改变SQL代码自动优化SQL不能完全取代DBA 代码的书写还是需要DBA来调整的 PackagedApps CustomApps AutomaticSQLTuning SQLProfiles Nightly Well tunedSQL Automaticimplement Manuallyimplement SQLAnalysis Report 李轶楠Mail ora 600 13331192030技术服务人生 SQL优化指导 李轶楠Mail ora 600 13331192030技术服务人生 顶级SQL 李轶楠Mail ora 600 13331192030技术服务人生 运行SQL优化指导 李轶楠Mail ora 600 13331192030技术服务人生 SQLAccessAdvisor 同时考虑索引解决方案物化视图解决方案两者的结合优化物化视图以获得最大化查询重写使用率快速刷新为快速刷新推荐物化视图日志将类似索引合并到单一索引 李轶楠Mail ora 600 13331192030技术服务人生 SQLAccessAdvisorOverview Partitions 11gonly MVandMVLogs Bit mapindexes AutomaticTuningOptimizer AccessPathAnalysis B treeindexes DBA Recommendations SQLAccessAdvisor 除了像在Oracle数据库10g中一样可以分析索引 物化视图等 Oracle数据库11g中的SQLAccessAdvisor还可以分析表和查询以提供可能的分区策略 这在设计最佳模式时可以提供很大帮助 李轶楠Mail ora 600 13331192030技术服务人生 诊断工具中的七种武器 离别钩 提示 hints Oracle很强大的工具 优化SQL的利器 能够强制SQL的执行算法 确保SQL按照我们希望的执行计划执行 钩 用的好伤人 用不好伤己 hints也如此 非高手者 非思路清晰者 且忌乱用 用不好的话 你会很受伤的 李轶楠Mail ora 600 13331192030技术服务人生 为什么要用hints 为什么有了CBO仍然考虑用hints RBO只看规则CBO代价计算仍然有缺陷不能考虑实际运行环境什么时候用hints首选用于测试执行计划其次可用于在需求确定时 固化执行计划常用的hints FIRST ROWS ALL ROWS RULEFULL tab INDEX tabindex NO INDEX tabindex USE NL tab USE MERGE tab USE HASH tab1tab2 它很锋利 小心 伤人 李轶楠Mail ora 600 13331192030技术服务人生 一些典型的hints 1与OptimizerMode优化模式相关 FIRST ROWS ALL ROWS 强制CBORULE 强制RBO2读取方式 CACHE tab 将数据强制保留在数据缓存中 不受LRU的影响NOCACHE tab 重新回到正常的LRU管理队列中FULL tab 强制全表扫描INDEX tabindex 强制走索引INDEX ASC tabindex 升序索引访问INDEX DESC tabindex 降序索引访问INDEX FFS tabindex 强制快速索引扫描NO INDEX tabindex 强制禁止某个索引的使用INDEX JOIN tabindex 强制索引联合查询INDEX COMBINE tabindex 强制使用位图索引INDEX SS tabindex 强制跳跃式索引扫描3表连接顺序 ORDERED 按照FROM中表名顺序连接LEADING 将选择的表作为连接驱动表 李轶楠Mail ora 600 13331192030技术服务人生 一些典型的hints 4连接 USE NL tab NO USE NL tab Usetable tab asthedrivingtableinaNestedLoopsjoin IfthedrivingrowsourceisacombinationoftablesnameoneofthetablesintheinnerjoinandtheNLshoulddriveofftheentirerow source DoesnotworkunlessaccompaniedbyanORDEREDhint USE MERGE tab NO USE MERGE tab Use tab asthedrivingtableinasort mergejoin DoesnotworkunlessaccompaniedbyanORDEREDhint USE HASH tab1tab2 NO USE HASH tab1tab2 Joineachspecifiedtablewithanotherrowsourcewithahashjoin tab1 isjoinedtopreviousrowsourceusingahashjoin 7 3 5并行查询选项 PARALLEL table NO PARALLEL table Useparalleldegree instancesasspecifiedPARALLEL INDEX table index degree instances Parallelrangescanforpartitionedindex6其他 APPEND OnlyvalidforINSERT SELECT AllowsINSERTtoworklikedirectloadortoperformparallelinsert NOAPPEND DonotuseINSERTAPPENDfunctionalityREWRITE v1 v2 在MV上启用查询重写NOREWRITE 在MV上禁用查询重写DYNAMIC SAMPLING tablelevel 动态采样 level可以从0 10 默认是4DRIVING SITE table 分布式数据库中用 分布式连接确定将结果集传输到本地还是远端 如果远端结果集大 本地结果集小 而关联的结果集也很小 可以选择远端表 意味着本地的小结果集在远端作关联 李轶楠Mail ora 600 13331192030技术服务人生 OptimizerHintSyntax hint commenttext hint commenttext SELECT INSERT DELETE UPDATE SELECT INSERT DELETE UPDATE 李轶楠Mail ora 600 13331192030技术服务人生 使用Hints的规则 提示直接放在第一个SQL关键字的后面 每条语句只允许一个提示区 但是可以包含多个提示 如果一个语句中使用了别名 那么提示中也必须是用别名 李轶楠Mail ora 600 13331192030技术服务人生 在SQL语句中使用提示 SQL CREATEindexgen idxoncustomers2 cust gender SQL SELECT INDEX customersgen idx 2cust last name cust street address 3cust postal code4FROMsh customers5WHEREUPPER cust gender M 李轶楠Mail ora 600 13331192030技术服务人生 OptimizerHintExample SQL update INDEX pPROD CATAGORY IDX 2productsp3setp prod min price 4 select5 pr prod list price 95 6fromproductspr7wherep prod id pr prod id 8wherep prod category Men 9andp prod status available onstock 10 李轶楠Mail ora 600 13331192030技术服务人生 诊断工具中的七种武器 拳头 没有武器就是有武器 有武器就是没有武器 最后一种武器 拳头 就是对整个体系的全面理解 无形的武器胜于有形的武器 就像太极 没有招数就是最好的招数 作为一个DBA 或者更高一些 作为一个架构管理员 能够理解整个业务系统 对数据库 存储 网络 系统 应用软件 业务流程都非常清楚 甚至于对使用者的使用习惯都非常清楚 优化就不再是什么高难度了 天地之大皆装于我胸中 万物皆为我之神兵 如果真有那么一天一切都在你的掌握之中 优化也许会变得非常easy 李轶楠Mail ora 600 13331192030技术服务人生 七种武器之外 除了介绍到的这七种武器 实际上做优化和诊断的还有很多很多利器 不是一定要 上榜 的才是好兵器只要管用 板砖也是好武器 何况有些板砖还很趁手 例如 DBMS XPLAN包SELECT FROMtable DBMS XPLAN DISPLAY SELECT FROMtable DBMS XPLAN DISPLAY CURSOR SQL ID CURSOR CHILD NO SELECT FROMtable dbms xplan display cursor null null iostatslast SELECT FROMtable dbms xplan display awr SQL ID 10046事件OradebugsetospidOradebugevent10046tracenamecontextforever level8v sql planSegmentadvisorMemoryadvisorLockmomitor 李轶楠Mail ora 600 13331192030技术服务人生 总结 优化的工具有千千万 找到适合的最关键精通两 三个工具 比什么工具都 会 使更有用工具就是工具 最终优化人来定工具是可以换的 人 才 是换不来的优化应该在系统中整体贯穿 早期的优化会带来更大的性能提升 而当需要我们用优化工具的时候似乎已经有点晚 李轶楠Mail ora 600 13331192030技术服务人生 性能优化经典案例详解 李轶楠Mail ora 600 13331192030技术服务人生 案例1OS配置不当造成的数据库挂起 场景 AIX5L 10 2 0 4rac 服务器物理内存8G故障现象 数据库启动后正常 业务连结后开始没有问题 运行一段时间后所有操作挂起 包括os的命令 报内存不足 分析思路 主要原因应该是 资源耗尽 确定哪种资源 A 死进程造成资源耗尽B 其他应用资源泄露C 服务器限制了某种资源不足也可能是bug或者异常重新启动后确定是否仍然出现查询相关文档 确定是否存在bug结论 安装os时由于使用了默认安装方式 导致交换区设置太小 仅为512M 因此在安装oracle数据库时可以安装 但运行一段时间后交换区耗尽 操作挂起 李轶楠Mail ora 600 13331192030技术服务人生 案例2一条简单SQL带来的硬解析麻烦 场景 linuxas4 10 2 0 4单机 服务器物理内存8G故障现象 服务器CPU持续高消耗 即使连结断开 CPU持续消耗30 40 分析思路 数据库中有自动运行的job存在少量job 其中有一个job每5分钟执行一次 job中存在for循环 不断查询一张表的每条记录 当发现查出的记录标志字段被改 则执行特定操作 查询语句类似如下 executeimmediate select fromempwhererownum x 服务器上有高消耗cpu的程序Cpu资源均被oracle进程消耗结论 频繁执行的未绑定SQL会带来大量硬解析 延长语句的执行时间 并严重消耗CPU 李轶楠Mail ora 600 13331192030技术服务人生 案例3增加索引带来的性能问题 场景 9i单机 windowsXP故障现象 用户原有语句执行效率很高 为了满足另一个查询的需求 用户增加了一个新的索引 造成原有语句效率严重下降分析思路 仅仅是增加了索引就造成性能下降 应该是选择了错误的索引算法结论 RBO下索引的选择很可能出错 一定要小心 必要时可以固定执行计划 李轶楠Mail ora 600 13331192030技术服务人生 案例4内存自动管理的性能问题 场景 10G数据库 物理内存16GB SGA自动管理 大小为8GB故障现象 运行一段时间 服务器上所有操作全部挂起 连接也无法建立 大约几分钟之后自动恢复正常分析思路 出现了高负载操作 造成系统突然资源消耗过度 不能相应其他请求系统bug结论 由于10GSGA自动管理 当业务操作特性发生变化造成内存收缩时 由于大内存回收会带来很大的开销 尤其是各种latch的消耗 因此在没有完成收缩之前 所有操作都会受到很大影响 李轶楠Mail ora 600 13331192030技术服务人生 案例5SQL书写与索引的使用 场景 10g 将特定的数据排除后分组汇总 使用符号故障现象 在加载了一些数据后 原有的查询语句取出的数据不多 但速度很慢分析思路 10g默认走CBO 怀疑统计信息陈旧或者参数调整导致算法错误 或者本身Oracle计算出的执行计划就有问题发现需要排除的数据特别的多结论 符号不论在CBO还是RBO中 都会带来全表扫描的操作 当数据分布均匀时 这是正确的选择 但如果出现特殊情况 需要排除的是大量数据 查询出的是少量数据 则索引是更好的选择 因此在CBO下应该将符号替换成 的写法 李轶楠Mail ora 600 13331192030技术服务人生 案例6绑定变量带来的性能问题 场景 10g 范围查询语句 为了减少硬解析 使用了绑定变量故障现象 SQL执行速度有时很快 有时很慢分析思路 绑定变量带来的好处是硬解析的减少 但硬解析的减少也意味着执行计划的不变范围查询时 取数据的多少直接决定了执行计划的选择结论 在范围查询时 如果查询获取的数据量并不是确定不变的 而是有可能有大范围的变化 不要使用绑定变量 大数据量访问时 执行计划的准确性对性能的影响远大于减少硬解析 李轶楠Mail ora 600 13331192030技术服务人生 案例7触发器对性能的影响 场景 10g aix主机 应用系统升级 新增加部分业务功能故障现象 两个db之间需要进行数据同步 原本在5分钟即可同步完成 现在需要15 20分钟分析思路 同步即数据插入 在数据量不变的情况下 数据插入速度严重下降 可能有锁竞争 回滚段竞争 日志缓存区等待或者索引 约束 触发器的影响有部分业务变更 因此并发竞争 索引 约束 触发器的可能性较大结论 由于在表上增加了行级触发器 造成每行插入时都不得不执行触发器 造成整个同步动作变慢 减少触发器的使用 尤其是行级触发器 李轶楠Mail ora 600 13331192030技术服务人生 案例8TB级分区表上分区索引的选择 3小时与0 3秒 场景 数据量在TB级的分区表 按时间字段进行范围分区 分区字段上有索引 根据用户选择条件进行数据查询故障现象 用户每次查询时间长达2 3小时分析思路 数据量巨大 用户查询时间长 很可能跟算法错误造成大量I O有关用户查询需求不确定 而且用户在查询时经常不选择时间范围结论 当分区字段没有作为查询条件出现或该字段没有过滤大量数据时 将不得不走全表扫描如果不能使用分区字段进行数据过滤 则必须在其他查询字段上建立索引一般来说 分区索引的效率仅比全局索引效率略低 主要体现在需要更多的索引分区I O 但全局索引的维护开销更大 综合考虑进行选择 李轶楠Mail ora 600 13331192030技术服务人生 案例9不同Count的效率分析 场景 9i 经常需要对一些表进行记录数汇总故障现象 统计记录数的效率较低分析思路 Count在进行汇总时 经常会走全表扫描 dba建议将count 调整为count 1 效果不大结论 Count 与count 1 均是汇总符合条件的总记录数 在没有索引或者RBO下均需要走全表扫描 要提高汇总速度 最好走索引 在CBO模式下 索引字段如果限制了非空约束 Oracle会将Count 或者count 1 转换为非空索引的全索引扫描 李轶楠Mail ora 600 13331192030技术服务人生 案例10索引创建顺序对索引选择的影响 场景 9i rbo模式故障现象 大字段和小字段都有索引 但语句执行时选择错误的大索引例如 select fromtest10wherec 2 andb 2 分析思路 RBO下执行计划与语句书写有关 但该语句书写上没有明显问题结论 当谓词级别不同时 选则优级别高的索引 当谓词级别相同时 选最新创建的索引 李轶楠Mail ora 600 13331192030技术服务人生 案例11数据分布与索引 场景 8i升级至9i 根据开发商建议 收集统计信息 开始使用CBO模式故障现象 大部分语句性能得到提升 但有部分语句效率极低 主要集中在某几个表特定字段的查询上分析思路 CBO基于统计信息进行代价计算统计信息的准确性和全面性直接影响执行计划结论 CBO对统计信息的准确性和全面性要求非常高数据分布均匀与否 决定了是否需要进行直方图的收集 而直方图的柱数决定了收集信息的准确性动态采样也是一种选择 总好过错误的信息收集 李轶楠Mail ora 600 13331192030技术服务人生 案例12索引对分组计算的影响 场景 10g 在大表上分组汇总 计算记录数 考虑到分组需要排序 而有索引可以减少排序 因此在汇总字段上建立了索引故障现象 汇总记录数的操作仍然很慢分析思路 速度很慢说明语句仍然在走全表扫描 索引没有有效利用结论 如果需要通过索引字段进行count计算 必须保证索引记录与表中记录数完全相同 而能够提供这种保证的 一定是非空约束 李轶楠Mail ora 600 13331192030技术服务人生 案例13反转索引对查询的帮助 场景 10g 用户需要做模糊查询 查询最后几个字母确定的记录故障现象 由于like在模糊查询时必须首字母确定才能够走索引 而用户的查询条件是最后几个字母确定 因此like正常使用将不得不走全表扫描分析思路 最后几个字母确定 如果最后几个确定的字母能成为like查询的首字母 则like查询可以走索引结论 反转索引除了可以在顺序字段做等值比较时分散I O 减少热点块 也能够使这种需求的查询走索引 从而更快的获取数据 李轶楠Mail ora 600 13331192030技术服务人生 案例14尽量避免的外联接 场景 9i 多表连接 选择算法为外连接 但在外连接的表上有过滤条件故障现象 效率较低分析思路 检查发现 过滤条件已经将所有不完全匹配的记录过滤结论 如果经过过滤之后的数据能够完全匹配 应该用等值连接代替外连接 李轶楠Mail ora 600 13331192030技术服务人生 案例15外键索引与delete的性能关系 场景 子表上已经删除了大量数据 需要将主表上相关的数据删除故障现象 在主表上删除数据时非常慢分析思路 Delete性能差 主要的原因是锁竞争 回滚段竞争 日志 索引维护等原因 但察看发现这些问题均不是很严重 发现i o读相当大结论 由于外键需要校验数据参照完整性 因此在删除主表记录时必须在子表上查询相关数据 而子表上外键字段上没有索引造成每校验主表一条数据 就不得不全表扫描子表一次 李轶楠Mail ora 600 13331192030技术服务人生 案例16分页查询的性能 场景 网站 10g数据库 根据用户需求分页显示结果故障现象 网页页面显示非常慢 即使是网页的第一页 只取出很少的数据分析思路 分页显示时 用户首先看到的首页的前n行 而大部分时候翻页动作不会做很多次 因此需要让前n页的显示尽可能快结论 在排序和过滤的字段上建立索引 并使用first rows提示 将会提高分页查询的效率 李轶楠Mail ora 600 13331192030技术服务人生 案例17组合索引的跳跃式索引扫描 场景 9i 组合索引 查询语句中没有出现组合索引的前导字段故障现象 查询速度非常慢 有大量的I O分析思路 RBO下 组合索引前导字段没有出现时 走全表扫描该组合索引的前导字段被大量查询使用 但该查询的where子句中没有出现前导字段 只出现了其它字段符合查询条件的数据很少结论 在取数据少的情况下 索引效率更高在CBO模式下 即使前导字段在where子句中没有出现 仍然可能走索引 索引算法是skipindexscan 李轶楠Mail ora 600 13331192030技术服务人生 案例18隐式类型转换带来的性能问题 场景 10g 大表索引字段上的查询 获取少量数据 查询子句为id 100故障现象 I O非常高 查询效率极低分析思路 索引字段 查询子句没有出现在函数表达式中 应该走索引 但从执行计划中可以看到 实际上走的是全表扫描结论 由于ID为字符类型 而比较的值为数字类型 因此会发生隐式类型转换 而且由于数字类型优先级别高 因此转换发生在索引字段上 因此索引字段实际在执行时出现在了隐式类型转换的转换函数中应该在查询子句中明确两边的类型 当两边类型不匹配时 建议使用显示类型转换控制转换发生在值上 而不是索引字段上 李轶楠Mail ora 600 13331192030技术服务人生 案例19更快的插入 场景 存储过程每天执行一次 需要从表中取出数据 经过简单处理之后插入到另一个表中 每次取出并插入的数据超过50w故障现象 插入数据的时间很长 由于夜间还需要进行其他业务 因此希望批量插入时间尽可能短分析思路 优化插入的效率 主要从日志 回滚 空间动态扩展 并发 索引维护等方面考虑除了上述常规的优化方法外 使用FORALL子句可以带来更快的插入结论 FORALL子句不再是一条一条的进行解析和执行 而是将语句批量处理 CPU的开销将更小 插入速度更快 李轶楠Mail ora 600 13331192030技术服务人生 总结 优化无处不在细节决定成败理解业务往往比理解操作更重要具体问题具体分析 优化不是生搬硬套实际测试比理论分析更有说服力 但实际测试要与理论分析相结合考虑