《Oracle数据库性能优化精解.ppt》由会员分享，可在线阅读，更多相关《Oracle数据库性能优化精解.ppt（48页珍藏版）》请在三一办公上搜索。
1、李轶楠Mail:ora-13331192030 技术服务人生,Oracle数据库性能优化精解,李轶楠Mail:ora-13331192030 技术服务人生,诊断工具中的七种武器,多情环 sql tuning advisor/sql access advisor：多情环似乎是一个情种，谁拥有它似乎都会产生感情，从而对许多江湖事看的很淡。在Oracle应用中，谁对性能影响最大，不言而喻，是SQL，准确地说是SQL语句的算法，可以说，80%以上的性能问题都可以通过调整SQL来解决或者缓解，拥有调优SQL性能的能力，基本上可以算作一个DBA高手咯。,李轶楠Mail:ora-13331192030 技术
2、服务人生,以前检查系统使用情况查看等待事件查看数据库分散读取上的等待事件通过以下方法识别 SQL（难以操作）识别具有大量数据库分散读取等待事件的会话并跟踪它们，或者在 OEM 中查看最突出的会话获得解释计划检查被访问的对象（大小/基数）查看 SQL 统计信息和/或与对象统计信息相比较(v$sql)（难以操作）识别问题联系打包应用程序的供应商为供应商提供测试方案供应商提供补丁/升级安装在客户的下一个维护周期中的补丁/升级,Oracle10g查看 ADDM 建议根据链接来运行自动 SQL 调整接受来自 SQL 调整的 SQL 描述文件建议,李轶楠Mail:ora-13331192030 技术服务人
3、生,执行计划,执行计划是一系列的优化器用来完成SQL操作的步骤和操作,李轶楠Mail:ora-13331192030 技术服务人生,曾经我们如何查看执行计划,通过下面的工具能够看到执行计划EXPLAIN PLAN V$SQL_PLANSQL Trace SQL*Plus AUTOTRACE看到执行计划不是目的，优化与分析仍然靠DBA去努力。,李轶楠Mail:ora-13331192030 技术服务人生,SQL调优建议,SQL Tuning&Access Advisors能够对系统中的SQL语句提供优化指导 从多个不同的方向为SQL提供优化建议建议包括了：统计信息的重新收集，创建/删除索引，创建
4、/删除物化视图，是否需要物化视图日志，SQL语句的书写以及固化执行计划的SQL Profiling 通过存储在Oracle内部的SQL Profiling 能够在不改变SQL代码的基础上强制执行计划,SQLProfile,PackagedApps+,Indexes,MVs,Partitions,Well-tuned SQL,Customizable Apps+,李轶楠Mail:ora-13331192030 技术服务人生,SQL Tuning Advisor Overview,Add Missing Indexes,Modify SQL Constructs,Create a SQL Prof
5、ile,Automatic Tuning Optimizer,SQL Structure Analysis,Access Path Analysis,SQL Profiling,Statistics Analysis,Gather Missing or Stale Statistics,DBA,SQL TuningRecommendations,SQL Tuning Advisor,李轶楠Mail:ora-13331192030 技术服务人生,SQL Tuning Usage Scenarios,SQL Tuning Advisor,ADDM,High-load SQL,Cursor Cach
6、e,AWR,SQL Tuning Set(STS),User-defined,Filter/Rank,SQL Sources,Manual Selection,Automatic Selection,AWR,李轶楠Mail:ora-13331192030 技术服务人生,SQL Tuning in Oracle Database 10gEnd-to-End Workflow,Workload,AWR,one hour,A good end-to-end solution,but manual intervention is required,李轶楠Mail:ora-13331192030 技术服
7、务人生,Automatic SQL Tuning in Oracle 11g,Its Automatic!,Workload,李轶楠Mail:ora-13331192030 技术服务人生,Automatic SQL Tuning,完全自动的SQL优化自动捕捉高负载的SQL自动创建SQL Profile，不改变SQL代码自动优化 SQL不能完全取代DBA，代码的书写还是需要DBA来调整的,PackagedApps,Custom Apps,Automatic SQL Tuning,SQL Profiles,Nightly,Well-tuned SQL,Automatic implement,Man
8、ually implement,SQL Analysis,Report,李轶楠Mail:ora-13331192030 技术服务人生,SQL优化指导,李轶楠Mail:ora-13331192030 技术服务人生,顶级SQL,李轶楠Mail:ora-13331192030 技术服务人生,运行SQL优化指导,李轶楠Mail:ora-13331192030 技术服务人生,SQL Access Advisor,同时考虑 索引解决方案物化视图解决方案两者的结合优化物化视图以获得最大化查询重写使用率快速刷新为快速刷新推荐物化视图日志将类似索引合并到单一索引,李轶楠Mail:ora-13331192030
9、技术服务人生,SQL Access Advisor Overview,Partitions(11g only),MV and MV Logs,Bit-map indexes,Automatic Tuning Optimizer,Access Path Analysis,B*-tree indexes,DBA,Recommendations,SQL Access Advisor,除了像在 Oracle 数据库 10g 中一样可以分析索引、物化视图等，Oracle数据库 11g 中的 SQL Access Advisor 还可以分析表和查询以提供可能的分区策略 这在设计最佳模式时可以提供很大帮助,
10、李轶楠Mail:ora-13331192030 技术服务人生,诊断工具中的七种武器,离别钩 提示(hints)，Oracle很强大的工具，优化SQL的利器，能够强制SQL的执行算法，确保SQL按照我们希望的执行计划执行。钩，用的好伤人，用不好伤己，hints也如此。非高手者，非思路清晰者，且忌乱用，用不好的话，你会很受伤的。,李轶楠Mail:ora-13331192030 技术服务人生,为什么要用hints,为什么有了CBO仍然考虑用hints：RBO只看规则CBO代价计算仍然有缺陷不能考虑实际运行环境什么时候用hints首选用于测试执行计划其次可用于在需求确定时，固化执行计划常用的hints
11、：FIRST_ROWS,ALL_ROWS,RULEFULL(tab)INDEX(tab index)NO_INDEX(tab index)USE_NL(tab)USE_MERGE(tab.)USE_HASH(tab1 tab2)它很锋利，小心“伤人”,李轶楠Mail:ora-13331192030 技术服务人生,一些典型的hints,1 与Optimizer Mode优化模式相关:FIRST_ROWS,ALL_ROWS：强制CBO RULE：强制RBO 2 读取方式:CACHE(tab)：将数据强制保留在数据缓存中，不受LRU的影响 NOCACHE(tab)：重新回到正常的LRU管理队列中 F
12、ULL(tab)：强制全表扫描INDEX(tab index)：强制走索引 INDEX_ASC(tab index)：升序索引访问 INDEX_DESC(tab index)：降序索引访问 INDEX_FFS(tab index)：强制快速索引扫描NO_INDEX(tab index)：强制禁止某个索引的使用INDEX_JOIN(tab index)：强制索引联合查询INDEX_COMBINE(tab index)：强制使用位图索引INDEX_SS(tab index)：强制跳跃式索引扫描3 表连接顺序：ORDERED：按照FROM中表名顺序连接LEADING:将选择的表作为连接驱动表.,李轶
13、楠Mail:ora-13331192030 技术服务人生,一些典型的hints,4 连接:USE_NL(tab)/NO_USE_NL(tab)：Use table tab as the driving table in a Nested Loops join.If the driving row source is a combination of tables name one of the tables in the inner join and the NL should drive off the entire row-source.Does not work unless accom
14、panied by an ORDERED hint.USE_MERGE(tab.)/NO_USE_MERGE(tab.)：Use tab as the driving table in a sort-merge join.Does not work unless accompanied by an ORDERED hint.USE_HASH(tab1 tab2)/NO_USE_HASH(tab1 tab2)：Join each specified table with another row source with a hash join.tab1 is joined to previous
15、row source using a hash join.(=7.3)5 并行查询选项:PARALLEL(table,)/NO_ PARALLEL(table)：Use parallel degree/instances as specified PARALLEL_INDEX(table,index,degree,instances)：Parallel range scan for partitioned index 6 其他:APPEND：Only valid for INSERT.SELECT.Allows INSERT to work like direct load or to per
16、form parallel insert.NOAPPEND：Do not use INSERT APPEND functionality REWRITE(v1,v2)：在MV上启用查询重写 NOREWRITE：在MV上禁用查询重写 DYNAMIC_SAMPLING(table level):动态采样，level可以从0-10,默认是4DRIVING_SITE(table):分布式数据库中用，分布式连接确定将结果集传输到本地还是远端，如果远端结果集大，本地结果集小，而关联的结果集也很小，可以选择远端表，意味着本地的小结果集在远端作关联,李轶楠Mail:ora-13331192030 技术服务人生
17、,Optimizer Hint Syntax,hint,comment text,*/,/*+,hint,comment text,-+,SELECT,INSERT,DELETE,UPDATE,SELECT,INSERT,DELETE,UPDATE,李轶楠Mail:ora-13331192030 技术服务人生,使用Hints的规则,提示直接放在第一个SQL关键字的后面.每条语句只允许一个提示区，但是可以包含多个提示.如果一个语句中使用了别名，那么提示中也必须是用别名.,李轶楠Mail:ora-13331192030 技术服务人生,在 SQL 语句中使用提示,SQL CREATE index g
18、en_idx on customers 2(cust_gender);,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;,李轶楠Mail:ora-13331192030 技术服务人生,Optimizer Hint Example,SQL update-+INDEX(p PROD_CATAGORY_IDX)2 products p 3 set p.prod_m
19、in_price=4(select 5(pr.prod_list_price*.95)6 from products pr 7 where p.prod_id=pr.prod_id)8 where p.prod_category=Men 9 and p.prod_status=available,on stock 10/,李轶楠Mail:ora-13331192030 技术服务人生,诊断工具中的七种武器,拳头：没有武器就是有武器，有武器就是没有武器。最后一种武器-拳头，就是对整个体系的全面理解，无形的武器胜于有形的武器，就像太极，没有招数就是最好的招数。作为一个DBA，或者更高一些，作为一个架
20、构管理员，能够理解整个业务系统，对数据库、存储、网络、系统、应用软件、业务流程都非常清楚，甚至于对使用者的使用习惯都非常清楚，优化就不再是什么高难度了。天地之大皆装于我胸中，万物皆为我之神兵。如果真有那么一天一切都在你的掌握之中，优化也许会变得非常easy,李轶楠Mail:ora-13331192030 技术服务人生,七种武器之外,除了介绍到的这七种武器，实际上做优化和诊断的还有很多很多利器，不是一定要“上榜”的才是好兵器只要管用，板砖也是好武器（何况有些板砖还很趁手）例如：DBMS_XPLAN包SELECT*FROM table(DBMS_XPLAN.DISPLAY);SELECT*FROM
21、 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 8v$sql_planSegment advisorMemory advisorLock mo
22、mitor,李轶楠Mail:ora-13331192030 技术服务人生,总结,优化的工具有千千万，找到适合的最关键精通两、三个工具，比什么工具都“会”使更有用工具就是工具，最终优化人来定工具是可以换的，人“才”是换不来的优化应该在系统中整体贯穿，早期的优化会带来更大的性能提升，而当需要我们用优化工具的时候似乎已经有点晚。,李轶楠Mail:ora-13331192030 技术服务人生,性能优化经典案例详解,李轶楠Mail:ora-13331192030 技术服务人生,案例1 OS配置不当造成的数据库挂起,场景：AIX5L，10.2.0.4 rac，服务器物理内存8G故障现象：数据库启动后正常，
23、业务连结后开始没有问题，运行一段时间后所有操作挂起，包括os的命令（报内存不足）分析思路：主要原因应该是：资源耗尽（确定哪种资源）A.死进程造成资源耗尽B.其他应用资源泄露C.服务器限制了某种资源不足也可能是bug或者异常重新启动后确定是否仍然出现查询相关文档，确定是否存在bug结论：安装os时由于使用了默认安装方式，导致交换区设置太小，仅为512M，因此在安装oracle数据库时可以安装，但运行一段时间后交换区耗尽，操作挂起,李轶楠Mail:ora-13331192030 技术服务人生,案例2 一条简单SQL带来的硬解析麻烦,场景：linuxas4，10.2.0.4 单机，服务器物理内存8G
24、故障现象：服务器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-13331192030 技术服务人生,案例3 增加索引带
25、来的性能问题,场景：9i单机，windows XP故障现象：用户原有语句执行效率很高，为了满足另一个查询的需求，用户增加了一个新的索引，造成原有语句效率严重下降分析思路：仅仅是增加了索引就造成性能下降，应该是选择了错误的索引算法结论：RBO下索引的选择很可能出错，一定要小心，必要时可以固定执行计划,李轶楠Mail:ora-13331192030 技术服务人生,案例4 内存自动管理的性能问题,场景：10G数据库，物理内存16GB，SGA自动管理，大小为8GB故障现象：运行一段时间，服务器上所有操作全部挂起，连接也无法建立，大约几分钟之后自动恢复正常分析思路：出现了高负载操作，造成系统突然资源消耗
26、过度，不能相应其他请求系统bug结论：由于10G SGA自动管理，当业务操作特性发生变化造成内存收缩时，由于大内存回收会带来很大的开销，尤其是各种latch的消耗，因此在没有完成收缩之前，所有操作都会受到很大影响,李轶楠Mail:ora-13331192030 技术服务人生,案例5 SQL书写与索引的使用,场景：10g，将特定的数据排除后分组汇总，使用符号故障现象：在加载了一些数据后，原有的查询语句取出的数据不多，但速度很慢分析思路：10g默认走CBO，怀疑统计信息陈旧或者参数调整导致算法错误，或者本身Oracle计算出的执行计划就有问题发现需要排除的数据特别的多结论：符号不论在CBO还是RB
27、O中，都会带来全表扫描的操作，当数据分布均匀时，这是正确的选择，但如果出现特殊情况，需要排除的是大量数据，查询出的是少量数据，则索引是更好的选择，因此在CBO下应该将符号替换成“”的写法,李轶楠Mail:ora-13331192030 技术服务人生,案例6 绑定变量带来的性能问题,场景：10g，范围查询语句，为了减少硬解析，使用了绑定变量故障现象：SQL执行速度有时很快，有时很慢分析思路：绑定变量带来的好处是硬解析的减少，但硬解析的减少也意味着执行计划的不变范围查询时，取数据的多少直接决定了执行计划的选择结论：在范围查询时，如果查询获取的数据量并不是确定不变的，而是有可能有大范围的变化，不要使
28、用绑定变量，大数据量访问时，执行计划的准确性对性能的影响远大于减少硬解析,李轶楠Mail:ora-13331192030 技术服务人生,案例7 触发器对性能的影响,场景：10g，aix主机，应用系统升级，新增加部分业务功能故障现象：两个db之间需要进行数据同步，原本在5分钟即可同步完成，现在需要15-20分钟分析思路：同步即数据插入，在数据量不变的情况下，数据插入速度严重下降，可能有锁竞争、回滚段竞争、日志缓存区等待或者索引、约束、触发器的影响有部分业务变更，因此并发竞争、索引、约束、触发器的可能性较大结论：由于在表上增加了行级触发器，造成每行插入时都不得不执行触发器，造成整个同步动作变慢，减
29、少触发器的使用，尤其是行级触发器,李轶楠Mail:ora-13331192030 技术服务人生,案例8 TB级分区表上分区索引的选择，3小时与0.3秒,场景：数据量在TB级的分区表，按时间字段进行范围分区，分区字段上有索引，根据用户选择条件进行数据查询故障现象：用户每次查询时间长达2-3小时分析思路：数据量巨大，用户查询时间长，很可能跟算法错误造成大量I/O有关用户查询需求不确定，而且用户在查询时经常不选择时间范围结论：当分区字段没有作为查询条件出现或该字段没有过滤大量数据时，将不得不走全表扫描如果不能使用分区字段进行数据过滤，则必须在其他查询字段上建立索引一般来说，分区索引的效率仅比全局索引
30、效率略低(主要体现在需要更多的索引分区I/O)，但全局索引的维护开销更大，综合考虑进行选择,李轶楠Mail:ora-13331192030 技术服务人生,案例9 不同Count的效率分析,场景：9i，经常需要对一些表进行记录数汇总故障现象：统计记录数的效率较低分析思路：Count在进行汇总时，经常会走全表扫描，dba建议将count(*)调整为count(1)，效果不大结论：Count(*)与count(1)均是汇总符合条件的总记录数，在没有索引或者RBO下均需要走全表扫描，要提高汇总速度，最好走索引，在CBO模式下，索引字段如果限制了非空约束，Oracle会将Count(*)或者count(
31、1)转换为非空索引的全索引扫描,李轶楠Mail:ora-13331192030 技术服务人生,案例10 索引创建顺序对索引选择的影响,场景：9i，rbo模式故障现象：大字段和小字段都有索引，但语句执行时选择错误的大索引例如：select*from test10 where c2 and b2;分析思路：RBO下执行计划与语句书写有关，但该语句书写上没有明显问题结论：当谓词级别不同时，选则优级别高的索引，当谓词级别相同时，选最新创建的索引,李轶楠Mail:ora-13331192030 技术服务人生,案例11 数据分布与索引,场景：8i 升级至 9i，根据开发商建议，收集统计信息，开始使用CBO
32、模式故障现象：大部分语句性能得到提升，但有部分语句效率极低，主要集中在某几个表特定字段的查询上分析思路：CBO基于统计信息进行代价计算统计信息的准确性和全面性直接影响执行计划结论：CBO对统计信息的准确性和全面性要求非常高数据分布均匀与否，决定了是否需要进行直方图的收集，而直方图的柱数决定了收集信息的准确性动态采样也是一种选择，总好过错误的信息收集,李轶楠Mail:ora-13331192030 技术服务人生,案例12 索引对分组计算的影响,场景：10g，在大表上分组汇总，计算记录数，考虑到分组需要排序，而有索引可以减少排序，因此在汇总字段上建立了索引故障现象：汇总记录数的操作仍然很慢分析思路
33、：速度很慢说明语句仍然在走全表扫描，索引没有有效利用结论：如果需要通过索引字段进行count计算，必须保证索引记录与表中记录数完全相同，而能够提供这种保证的，一定是非空约束,李轶楠Mail:ora-13331192030 技术服务人生,案例13 反转索引对查询的帮助,场景：10g，用户需要做模糊查询，查询最后几个字母确定的记录故障现象：由于like在模糊查询时必须首字母确定才能够走索引，而用户的查询条件是最后几个字母确定，因此like正常使用将不得不走全表扫描分析思路：最后几个字母确定，如果最后几个确定的字母能成为like查询的首字母，则like查询可以走索引结论：反转索引除了可以在顺序字段做
34、等值比较时分散I/O，减少热点块，也能够使这种需求的查询走索引，从而更快的获取数据,李轶楠Mail:ora-13331192030 技术服务人生,案例14 尽量避免的外联接,场景：9i，多表连接，选择算法为外连接，但在外连接的表上有过滤条件故障现象：效率较低分析思路：检查发现，过滤条件已经将所有不完全匹配的记录过滤结论：如果经过过滤之后的数据能够完全匹配，应该用等值连接代替外连接,李轶楠Mail:ora-13331192030 技术服务人生,案例15 外键索引与delete的性能关系,场景：子表上已经删除了大量数据，需要将主表上相关的数据删除故障现象：在主表上删除数据时非常慢分析思路：Dele
35、te性能差，主要的原因是锁竞争、回滚段竞争、日志、索引维护等原因，但察看发现这些问题均不是很严重，发现i/o读相当大结论：由于外键需要校验数据参照完整性，因此在删除主表记录时必须在子表上查询相关数据，而子表上外键字段上没有索引造成每校验主表一条数据，就不得不全表扫描子表一次,李轶楠Mail:ora-13331192030 技术服务人生,案例16 分页查询的性能,场景：网站，10g数据库，根据用户需求分页显示结果故障现象：网页页面显示非常慢，即使是网页的第一页，只取出很少的数据分析思路：分页显示时，用户首先看到的首页的前n行，而大部分时候翻页动作不会做很多次，因此需要让前n页的显示尽可能快结论：
36、在排序和过滤的字段上建立索引，并使用first_rows提示，将会提高分页查询的效率,李轶楠Mail:ora-13331192030 技术服务人生,案例17 组合索引的跳跃式索引扫描,场景：9i，组合索引，查询语句中没有出现组合索引的前导字段故障现象：查询速度非常慢，有大量的I/O分析思路：RBO下，组合索引前导字段没有出现时，走全表扫描该组合索引的前导字段被大量查询使用，但该查询的where子句中没有出现前导字段，只出现了其它字段符合查询条件的数据很少结论：在取数据少的情况下，索引效率更高在CBO模式下，即使前导字段在where子句中没有出现，仍然可能走索引，索引算法是skip index
37、scan,李轶楠Mail:ora-13331192030 技术服务人生,案例18 隐式类型转换带来的性能问题,场景：10g，大表索引字段上的查询，获取少量数据，查询子句为id=100故障现象：I/O非常高，查询效率极低分析思路：索引字段，查询子句没有出现在函数表达式中，应该走索引，但从执行计划中可以看到，实际上走的是全表扫描结论：由于ID为字符类型，而比较的值为数字类型，因此会发生隐式类型转换，而且由于数字类型优先级别高，因此转换发生在索引字段上，因此索引字段实际在执行时出现在了隐式类型转换的转换函数中应该在查询子句中明确两边的类型，当两边类型不匹配时，建议使用显示类型转换控制转换发生在值上，
38、而不是索引字段上,李轶楠Mail:ora-13331192030 技术服务人生,案例19 更快的插入,场景：存储过程每天执行一次，需要从表中取出数据，经过简单处理之后插入到另一个表中，每次取出并插入的数据超过50w故障现象：插入数据的时间很长，由于夜间还需要进行其他业务，因此希望批量插入时间尽可能短分析思路：优化插入的效率，主要从日志、回滚、空间动态扩展、并发、索引维护等方面考虑除了上述常规的优化方法外，使用FOR ALL子句可以带来更快的插入结论：FOR ALL子句不再是一条一条的进行解析和执行，而是将语句批量处理，CPU的开销将更小，插入速度更快,李轶楠Mail:ora-13331192030 技术服务人生,总结,优化无处不在细节决定成败理解业务往往比理解操作更重要具体问题具体分析，优化不是生搬硬套实际测试比理论分析更有说服力，但实际测试要与理论分析相结合考虑,
