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

ACOUGITPUB Oracle数据库性能优化精解 Mail:ora-600163.co技术服务人生 ACOUGITPUB 诊断工具中的七种武器 多情环 sql tuning advisor/sql access advisor：多情环似乎是一 个情种，谁拥有它似乎都会产生感情，从而对许多江湖事看的很 淡。在Oracle应用中，谁对性能影响最大，不言而喻，是SQL， 准确地说是SQL语句的算法，可以说，80%以上的性能问题都可 以通过调整SQL来解决或者缓解，拥有调优SQL性能的能力，基 本上可以算作一个DBA高手咯。 Mail:ora-600163.co技术服务人生 ACOUGITPUB 以前 1.检查系统使用情况 2.查看等待事件 3.查看数据库分散读取上的等待事件 4.通过以下方法识别 SQL（难以操作） 识别具有大量数据库分散读取等待事件的会话并跟踪它们，或 者在 OEM 中查看最突出的会话 5.获得解释计划 6.检查被访问的对象（大小/基数） 7.查看 SQL 统计信息和/或与对象统计信息相比较 (v$sql) （难以操作 ） 8.识别问题 9.联系打包应用程序的供应商 10.为供应商提供测试方案 11.供应商提供补丁/升级 12.安装在客户的下一个维护周期中的补丁/升级 Oracle10g 1.查看 ADDM 建议 2.根据链接来运行自动 SQL 调 整 3.接受来自 SQL 调整的 SQL 描 述文件建议 SQL 调调整：以前与现现在的情况 情况：打包应应用程序中的不良 SQL Mail:ora-600163.co技术服务人生 ACOUGITPUB 执行计划 执行计划是一系列的优化器用来完成SQL操作的步骤 和操作 Mail:ora-600163.co技术服务人生 ACOUGITPUB 曾经我们如何查看执行计划 通过下面的工具能够看到执行计划 EXPLAIN PLAN V$SQL_PLAN SQL Trace SQL*Plus AUTOTRACE 看到执行计划不是目的，优化与分析仍然靠DBA去努 力。 Mail:ora-600163.co技术服务人生 ACOUGITPUB SQL调优建议 SQL Tuning SQL SELECT /*+ INDEX(customers gen_idx)*/ 2 cust_last_name, cust_street_address, 3 cust_postal_code 4 FROM sh.customers 5 WHERE UPPER (cust_gender) = M; ACOUGITPUB Optimizer Hint Example SQL update -+ INDEX(p PROD_CATAGORY_IDX) 2 products p 3 set d_min_price = 4 (select 5 (d_list_price*.95) 6 from products pr 7 where d_id = d_id) 8 where d_category = Men 9 and d_status = available, on stock 10 / ACOUGITPUB 诊断工具中的七种武器 拳头：没有武器就是有武器，有武器就是没有武器。最后一种武 器-拳头，就是对整个体系的全面理解，无形的武器胜于有形的武 器，就像太极，没有招数就是最好的招数。 作为一个DBA，或者更高一些，作为一个架构管理员，能够理解 整个业务系统，对数据库、存储、网络、系统、应用软件、业务 流程都非常清楚，甚至于对使用者的使用习惯都非常清楚，优化 就不再是什么高难度了。天地之大皆装于我胸中，万物皆为我之 神兵。如果真有那么一天一切都在你的掌握之中，优化也许会变 得非常easy Mail:ora-600163.co技术服务人生 ACOUGITPUB 七种武器之外 除了介绍到的这七种武器，实际上做优化和诊断的还有很多很多利器，不是一 定要“上榜”的才是好兵器 只要管用，板砖也是好武器（何况有些板砖还很趁手） 例如： DBMS_XPLAN包 SELECT * FROM table(DBMS_XPLAN.DISPLAY); SELECT * FROM table(DBMS_XPLAN. DISPLAY_CURSOR(SQL_ID, CURSOR_CHILD_NO); SELECT * FROM table(dbms_xplan.display_cursor(null,null,iostats last); SELECT * FROM table(dbms_xplan.display_awr(SQL_ID); 10046事件 Oradebug setospid Oradebug event 10046 trace name context forever,level 8 v$sql_plan Segment advisor Memory advisor Lock momitor Mail:ora-600163.co技术服务人生 ACOUGITPUB 总结 优化的工具有千千万，找到适合的最关键 精通两、三个工具，比什么工具都“会”使更有用 工具就是工具，最终优化人来定 工具是可以换的，人“才”是换不来的 优化应该在系统中整体贯穿，早期的优化会带来更大 的性能提升，而当需要我们用优化工具的时候似乎已 经有点晚。 Mail:ora-600163.co技术服务人生 ACOUGITPUB 性能优化经典案例详解 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例1 OS配置不当造成的数据库挂起 场景： AIX5L， rac，服务器物理内存8G 故障现象： 数据库启动后正常，业务连结后开始没有问题，运行一段时间后所有操作挂起 ，包括os的命令（报内存不足） 分析思路： 主要原因应该是：资源耗尽（确定哪种资源） A.死进程造成资源耗尽 B.其他应用资源泄露 C.服务器限制了某种资源不足 也可能是bug或者异常 重新启动后确定是否仍然出现 查询相关文档，确定是否存在bug 结论： 安装os时由于使用了默认安装方式，导致交换区设置太小，仅为512M，因此 在安装oracle数据库时可以安装，但运行一段时间后交换区耗尽，操作挂起 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例2 一条简单SQL带来的硬解析麻烦 场景： linuxas4， 单机，服务器物理内存8G 故障现象： 服务器CPU持续高消耗，即使连结断开，CPU持续消耗30-40% 分析思路： 数据库中有自动运行的job 存在少量job，其中有一个job每5分钟执行一次，job中存在for循环 ，不断查询一张表的每条记录，当发现查出的记录标志字段被改 ，则执行特定操作，查询语句类似如下： execute immediate select * from emp where rownum=|x; 服务器上有高消耗cpu的程序 Cpu资源均被oracle进程消耗 结论： 频繁执行的未绑定SQL会带来大量硬解析，延长语句的执行时间，并严重消耗 CPU Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例3 增加索引带来的性能问题 场景： 9i单机，windows XP 故障现象： 用户原有语句执行效率很高，为了满足另一个查询的需求，用户增加 了一个新的索引，造成原有语句效率严重下降 分析思路： 仅仅是增加了索引就造成性能下降，应该是选择了错误的索引算法 结论： RBO下索引的选择很可能出错，一定要小心，必要时可以固定执行计划 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例4 内存自动管理的性能问题 场景： 10G数据库，物理内存16GB，SGA自动管理，大小为8GB 故障现象： 运行一段时间，服务器上所有操作全部挂起，连接也无法建立，大约 几分钟之后自动恢复正常 分析思路： 出现了高负载操作，造成系统突然资源消耗过度，不能相应其他请求 系统bug 结论： 由于10G SGA自动管理，当业务操作特性发生变化造成内存收缩时，由于大 内存回收会带来很大的开销，尤其是各种latch的消耗，因此在没有完成收缩 之前，所有操作都会受到很大影响 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例5 SQL书写与索引的使用 场景： 10g，将特定的数据排除后分组汇总，使用符号不论在CBO还是RBO中，都会带来全表扫描的操作，当数据分布均匀 时，这是正确的选择，但如果出现特殊情况，需要排除的是大量数据，查询出 的是少量数据，则索引是更好的选择，因此在CBO下应该将符号替换成“” 的写法 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例6 绑定变量带来的性能问题 场景： 10g，范围查询语句，为了减少硬解析，使用了绑定变量 故障现象： SQL执行速度有时很快，有时很慢 分析思路： 绑定变量带来的好处是硬解析的减少，但硬解析的减少也意味着执行 计划的不变 范围查询时，取数据的多少直接决定了执行计划的选择 结论： 在范围查询时，如果查询获取的数据量并不是确定不变的，而是有可能有大范 围的变化，不要使用绑定变量，大数据量访问时，执行计划的准确性对性能的 影响远大于减少硬解析 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例7 触发器对性能的影响 场景： 10g，aix主机，应用系统升级，新增加部分业务功能 故障现象： 两个db之间需要进行数据同步，原本在5分钟即可同步完成，现在需 要15-20分钟 分析思路： 同步即数据插入，在数据量不变的情况下，数据插入速度严重下降， 可能有锁竞争、回滚段竞争、日志缓存区等待或者索引、约束、触发 器的影响 有部分业务变更，因此并发竞争、索引、约束、触发器的可能性较大 结论： 由于在表上增加了行级触发器，造成每行插入时都不得不执行触发器，造成整 个同步动作变慢，减少触发器的使用，尤其是行级触发器 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例8 TB级分区表上分区索引的选择，3小时与 0.3秒 场景： 数据量在TB级的分区表，按时间字段进行范围分区，分区字段上有索 引，根据用户选择条件进行数据查询 故障现象： 用户每次查询时间长达2-3小时 分析思路： 数据量巨大，用户查询时间长，很可能跟算法错误造成大量I/O有关 用户查询需求不确定，而且用户在查询时经常不选择时间范围 结论： 当分区字段没有作为查询条件出现或该字段没有过滤大量数据时，将不得不走 全表扫描 如果不能使用分区字段进行数据过滤，则必须在其他查询字段上建立索引 一般来说，分区索引的效率仅比全局索引效率略低(主要体现在需要更多的索 引分区I/O)，但全局索引的维护开销更大，综合考虑进行选择 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例9 不同Count的效率分析 场景： 9i，经常需要对一些表进行记录数汇总 故障现象： 统计记录数的效率较低 分析思路： Count在进行汇总时，经常会走全表扫描，dba建议将count(*)调整为 count(1)，效果不大 结论： Count(*)与count(1)均是汇总符合条件的总记录数，在没有索引或者RBO下均 需要走全表扫描，要提高汇总速度，最好走索引，在CBO模式下，索引字段如 果限制了非空约束，Oracle会将Count(*)或者count(1)转换为非空索引的全索 引扫描 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例10 索引创建顺序对索引选择的影响 场景： 9i，rbo模式 故障现象： 大字段和小字段都有索引，但语句执行时选择错误的大索引 例如：select * from test10 where c2 and b2; 分析思路： RBO下执行计划与语句书写有关，但该语句书写上没有明显问题 结论： 当谓词级别不同时，选则优级别高的索引，当谓词级别相同时，选最新创建的 索引 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例11 数据分布与索引 场景： 8i 升级至 9i，根据开发商建议，收集统计信息，开始使用CBO模式 故障现象： 大部分语句性能得到提升，但有部分语句效率极低，主要集中在某几 个表特定字段的查询上 分析思路： CBO基于统计信息进行代价计算 统计信息的准确性和全面性直接影响执行计划 结论： CBO对统计信息的准确性和全面性要求非常高 数据分布均匀与否，决定了是否需要进行直方图的收集，而直方图的柱数决定 了收集信息的准确性 动态采样也是一种选择，总好过错误的信息收集 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例12 索引对分组计算的影响 场景： 10g，在大表上分组汇总，计算记录数，考虑到分组需要排序，而有 索引可以减少排序，因此在汇总字段上建立了索引 故障现象： 汇总记录数的操作仍然很慢 分析思路： 速度很慢说明语句仍然在走全表扫描，索引没有有效利用 结论： 如果需要通过索引字段进行count计算，必须保证索引记录与表中记录数完全 相同，而能够提供这种保证的，一定是非空约束 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例13 反转索引对查询的帮助 场景： 10g，用户需要做模糊查询，查询最后几个字母确定的记录 故障现象： 由于like在模糊查询时必须首字母确定才能够走索引，而用户的查询 条件是最后几个字母确定，因此like正常使用将不得不走全表扫描 分析思路： 最后几个字母确定，如果最后几个确定的字母能成为like查询的首字 母，则like查询可以走索引 结论： 反转索引除了可以在顺序字段做等值比较时分散I/O，减少热点块，也能够使 这种需求的查询走索引，从而更快的获取数据 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例14 尽量避免的外联接 场景： 9i，多表连接，选择算法为外连接，但在外连接的表上有过滤条件 故障现象： 效率较低 分析思路： 检查发现，过滤条件已经将所有不完全匹配的记录过滤 结论： 如果经过过滤之后的数据能够完全匹配，应该用等值连接代替外连接 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例15 外键索引与delete的性能关系 场景： 子表上已经删除了大量数据，需要将主表上相关的数据删除 故障现象： 在主表上删除数据时非常慢 分析思路： Delete性能差，主要的原因是锁竞争、回滚段竞争、日志、索引维护 等原因，但察看发现这些问题均不是很严重，发现i/o读相当大 结论： 由于外键需要校验数据参照完整性，因此在删除主表记录时必须在子表上查询 相关数据，而子表上外键字段上没有索引造成每校验主表一条数据，就不得不 全表扫描子表一次 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例16 分页查询的性能 场景： 网站，10g数据库，根据用户需求分页显示结果 故障现象： 网页页面显示非常慢，即使是网页的第一页，只取出很少的数据 分析思路： 分页显示时，用户首先看到的首页的前n行，而大部分时候翻页动作 不会做很多次，因此需要让前n页的显示尽可能快 结论： 在排序和过滤的字段上建立索引，并使用first_rows提示，将会提高分页查询 的效率 Mail:ora-600163.co技术服务人生 ACOUGITPUB 案例17 组合索引的跳跃式索引扫描 场景： 9i，组合索引，查询语句中没有出现组合索引的前导字段 故障现象： 查询速度非常慢，有大量的I/O 分析思路： RBO下，组合索引前导字段没有出现时，走全表扫描 该组合索引的前导字段被大量查询使用，但该查询的where子句中没 有出现前导字段，只出现了其它字段 符合查询条件的数据很少 结论： 在取数据少的情况下，索引效率更高 在CBO模式下，即使前导字段在where子句中没有出现，仍然可能走索引，索 引算法是skip index scan Mail:ora-60