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

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