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

1、李轶楠 Mail:ora- 13331192030 技术服务人生,Oracle数据库性能优化精解,李轶楠 Mail:ora- 13331192030 技术服务人生,诊断工具中的七种武器,多情环 sql tuning advisor/sql access advisor：多情环似乎是一个情种，谁拥有它似乎都会产生感情，从而对许多江湖事看的很淡。在Oracle应用中，谁对性能影响最大，不言而喻，是SQL，准确地说是SQL语句的算法，可以说，80%以上的性能问题都可以通过调整SQL来解决或者缓解，拥有调优SQL性能的能力，基本上可以算作一个DBA高手咯。,李轶楠 Mail:ora- 13331192

2、030 技术服务人生,以前 检查系统使用情况 查看等待事件 查看数据库分散读取上的等待事件 通过以下方法识别 SQL（难以操作） 识别具有大量数据库分散读取等待事件的会话并跟踪它们，或者在 OEM 中查看最突出的会话 获得解释计划 检查被访问的对象（大小/基数） 查看 SQL 统计信息和/或与对象统计信息相比较 (v$sql) （难以操作） 识别问题 联系打包应用程序的供应商 为供应商提供测试方案 供应商提供补丁/升级 安装在客户的下一个维护周期中的补丁/升级,Oracle10g 查看 ADDM 建议 根据链接来运行自动 SQL 调整 接受来自 SQL 调整的 SQL 描述文件建议,李轶楠 M

3、ail:ora- 13331192030 技术服务人生,执行计划,执行计划是一系列的优化器用来完成SQL操作的步骤和操作,李轶楠 Mail:ora- 13331192030 技术服务人生,曾经我们如何查看执行计划,通过下面的工具能够看到执行计划 EXPLAIN PLAN V$SQL_PLAN SQL Trace SQL*Plus AUTOTRACE 看到执行计划不是目的，优化与分析仍然靠DBA去努力。,李轶楠 Mail:ora- 13331192030 技术服务人生,SQL调优建议,SQL Tuning & Access Advisors 能够对系统中的SQL语句提供优化指导 从多个不同的方向

4、为SQL提供优化建议 建议包括了：统计信息的重新收集，创建/删除索引，创建/删除物化视图，是否需要物化视图日志，SQL语句的书写以及固化执行计划的SQL Profiling 通过存储在Oracle内部的SQL Profiling 能够在不改变SQL代码的基础上强制执行计划,SQL Profile,Packaged Apps +,Indexes, MVs, Partitions,Well-tuned SQL,Customizable Apps +,李轶楠 Mail:ora- 13331192030 技术服务人生,SQL Tuning Advisor Overview,Add Missing In

5、dexes,Modify SQL Constructs,Create a SQL Profile,Automatic Tuning Optimizer,SQL Structure Analysis,Access Path Analysis,SQL Profiling,Statistics Analysis,Gather Missing or Stale Statistics,DBA,SQL Tuning Recommendations,SQL Tuning Advisor,李轶楠 Mail:ora- 13331192030 技术服务人生,SQL Tuning Usage Scenarios,S

6、QL Tuning Advisor,ADDM,High-load SQL,Cursor Cache,AWR,SQL Tuning Set (STS),User-defined,Filter / Rank,SQL Sources,Manual Selection,Automatic Selection,AWR,李轶楠 Mail:ora- 13331192030 技术服务人生,SQL Tuning in Oracle Database 10g End-to-End Workflow,Workload,AWR,one hour,A good end-to-end solution, but manu

7、al intervention is required,李轶楠 Mail:ora- 13331192030 技术服务人生,Automatic SQL Tuning in Oracle 11g,Its Automatic!,Workload,李轶楠 Mail:ora- 13331192030 技术服务人生,Automatic SQL Tuning,完全自动的SQL优化 自动捕捉高负载的SQL 自动创建SQL Profile，不改变SQL代码自动优化 SQL 不能完全取代DBA，代码的书写还是需要DBA来调整的,Packaged Apps,Custom Apps,Automatic SQL Tun

8、ing,SQL Profiles,Nightly,Well-tuned SQL,Automatic implement,Manually implement,SQL Analysis,Report,李轶楠 Mail:ora- 13331192030 技术服务人生,SQL优化指导,李轶楠 Mail:ora- 13331192030 技术服务人生,顶级SQL,李轶楠 Mail:ora- 13331192030 技术服务人生,运行SQL优化指导,李轶楠 Mail:ora- 13331192030 技术服务人生,SQL Access Advisor,同时考虑 索引解决方案 物化视图解决方案 两者的结合

9、 优化物化视图以获得 最大化查询重写使用率 快速刷新 为快速刷新推荐物化视图日志 将类似索引合并到单一索引,李轶楠 Mail:ora- 13331192030 技术服务人生,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 中一样可以分析索引

10、、物化视图等，Oracle数据库 11g 中的 SQL Access Advisor 还可以分析表和查询以提供可能的分区策略 这在设计最佳模式时可以提供很大帮助,李轶楠 Mail:ora- 13331192030 技术服务人生,诊断工具中的七种武器,离别钩 提示(hints)，Oracle很强大的工具，优化SQL的利器，能够强制SQL的执行算法，确保SQL按照我们希望的执行计划执行。钩，用的好伤人，用不好伤己，hints也如此。非高手者，非思路清晰者，且忌乱用，用不好的话，你会很受伤的。,李轶楠 Mail:ora- 13331192030 技术服务人生,为什么要用hints,为什么有了CBO仍

11、然考虑用hints： RBO只看规则 CBO代价计算仍然有缺陷 不能考虑实际运行环境 什么时候用hints 首选用于测试执行计划 其次可用于在需求确定时，固化执行计划 常用的hints： FIRST_ROWS, ALL_ROWS ,RULE FULL(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优化模式相关:

12、FIRST_ROWS, ALL_ROWS ： 强制CBO RULE ： 强制RBO 2 读取方式: CACHE(tab) ： 将数据强制保留在数据缓存中，不受LRU的影响 NOCACHE(tab)：重新回到正常的LRU管理队列中 FULL(tab) ： 强制全表扫描 INDEX( tab index ) ： 强制走索引 INDEX_ASC( tab index ) ： 升序索引访问 INDEX_DESC( tab index ) ： 降序索引访问 INDEX_FFS( tab index) ： 强制快速索引扫描 NO_INDEX ( tab index) ： 强制禁止某个索引的使用 INDEX

13、_JOIN ( tab index) ： 强制索引联合查询 INDEX_COMBINE( tab index) ： 强制使用位图索引 INDEX_SS ( tab index) ： 强制跳跃式索引扫描3 表连接顺序： ORDERED ： 按照FROM中表名顺序连接 LEADING: 将选择的表作为连接驱动表.,李轶楠 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 th

14、e 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 accompanied 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

15、 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 row source using a hash join. (=7.3) 5 并行查询选项: PARALLEL ( table, , ) / NO_ PARALLEL (table) ： Use parallel degree / instances a

16、s 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 perform parallel insert. NOAPPEND： Do not use INSERT APPEND functionality REWRITE(v1,v2) ： 在MV上启用查询重写 NORE

17、WRITE ：在MV上禁用查询重写 DYNAMIC_SAMPLING(table level) : 动态采样，level可以从0-10,默认是4 DRIVING_SITE(table): 分布式数据库中用，分布式连接确定将结果集传输到本地还是远端，如果远端结果集大，本地结果集小，而关联的结果集也很小，可以选择远端表，意味着本地的小结果集在远端作关联,李轶楠 Mail:ora- 13331192030 技术服务人生,Optimizer Hint Syntax,hint,comment text,*/,/*+,hint,comment text,-+,SELECT,INSERT,DELETE,UP

18、DATE,SELECT,INSERT,DELETE,UPDATE,李轶楠 Mail:ora- 13331192030 技术服务人生,使用Hints的规则,提示直接放在第一个SQL关键字的后面. 每条语句只允许一个提示区，但是可以包含多个提示. 如果一个语句中使用了别名，那么提示中也必须是用别名.,李轶楠 Mail:ora- 13331192030 技术服务人生,在 SQL 语句中使用提示,SQL CREATE index gen_idx on customers 2 (cust_gender);,SQL SELECT /*+ INDEX(customers gen_idx)*/ 2 cust_

19、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 p3 set p.prod_min_price =4 (select 5 (pr.prod_list_price*.95)6 from products pr7 where p.

20、prod_id = pr.prod_id)8 where p.prod_category = Men9 and p.prod_status = available, on stock10 /,李轶楠 Mail:ora- 13331192030 技术服务人生,诊断工具中的七种武器,拳头：没有武器就是有武器，有武器就是没有武器。最后一种武器-拳头，就是对整个体系的全面理解，无形的武器胜于有形的武器，就像太极，没有招数就是最好的招数。作为一个DBA，或者更高一些，作为一个架构管理员，能够理解整个业务系统，对数据库、存储、网络、系统、应用软件、业务流程都非常清楚，甚至于对使用者的使用习惯都非常清楚，优

21、化就不再是什么高难度了。天地之大皆装于我胸中，万物皆为我之神兵。如果真有那么一天一切都在你的掌握之中，优化也许会变得非常easy,李轶楠 Mail:ora- 13331192030 技术服务人生,七种武器之外,除了介绍到的这七种武器，实际上做优化和诊断的还有很多很多利器，不是一定要“上榜”的才是好兵器 只要管用，板砖也是好武器（何况有些板砖还很趁手） 例如： DBMS_XPLAN包 SELECT * FROM table(DBMS_XPLAN.DISPLAY); SELECT * FROM table(DBMS_XPLAN. DISPLAY_CURSOR(SQL_ID, CURSOR_CHIL

22、D_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- 13331192030 技术服务人生

23、,总结,优化的工具有千千万，找到适合的最关键 精通两、三个工具，比什么工具都“会”使更有用 工具就是工具，最终优化人来定 工具是可以换的，人“才”是换不来的 优化应该在系统中整体贯穿，早期的优化会带来更大的性能提升，而当需要我们用优化工具的时候似乎已经有点晚。,李轶楠 Mail:ora- 13331192030 技术服务人生,性能优化经典案例详解,李轶楠 Mail:ora- 13331192030 技术服务人生,案例1 OS配置不当造成的数据库挂起,场景： AIX5L，10.2.0.4 rac，服务器物理内存8G 故障现象： 数据库启动后正常，业务连结后开始没有问题，运行一段时间后所有操作挂起

24、，包括os的命令（报内存不足） 分析思路： 主要原因应该是：资源耗尽（确定哪种资源） A.死进程造成资源耗尽 B.其他应用资源泄露 C.服务器限制了某种资源不足 也可能是bug或者异常 重新启动后确定是否仍然出现 查询相关文档，确定是否存在bug 结论： 安装os时由于使用了默认安装方式，导致交换区设置太小，仅为512M，因此在安装oracle数据库时可以安装，但运行一段时间后交换区耗尽，操作挂起,李轶楠 Mail:ora- 13331192030 技术服务人生,案例2 一条简单SQL带来的硬解析麻烦,场景： linuxas4，10.2.0.4 单机，服务器物理内存8G 故障现象： 服务器CP

25、U持续高消耗，即使连结断开，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 增加索

26、引带来的性能问题,场景： 9i单机，windows XP 故障现象： 用户原有语句执行效率很高，为了满足另一个查询的需求，用户增加了一个新的索引，造成原有语句效率严重下降 分析思路： 仅仅是增加了索引就造成性能下降，应该是选择了错误的索引算法 结论： RBO下索引的选择很可能出错，一定要小心，必要时可以固定执行计划,李轶楠 Mail:ora- 13331192030 技术服务人生,案例4 内存自动管理的性能问题,场景： 10G数据库，物理内存16GB，SGA自动管理，大小为8GB 故障现象： 运行一段时间，服务器上所有操作全部挂起，连接也无法建立，大约几分钟之后自动恢复正常 分析思路： 出现了

27、高负载操作，造成系统突然资源消耗过度，不能相应其他请求 系统bug 结论： 由于10G SGA自动管理，当业务操作特性发生变化造成内存收缩时，由于大内存回收会带来很大的开销，尤其是各种latch的消耗，因此在没有完成收缩之前，所有操作都会受到很大影响,李轶楠 Mail:ora- 13331192030 技术服务人生,案例5 SQL书写与索引的使用,场景： 10g，将特定的数据排除后分组汇总，使用符号 故障现象： 在加载了一些数据后，原有的查询语句取出的数据不多，但速度很慢 分析思路： 10g默认走CBO，怀疑统计信息陈旧或者参数调整导致算法错误，或者本身Oracle计算出的执行计划就有问题 发

28、现需要排除的数据特别的多 结论： 符号不论在CBO还是RBO中，都会带来全表扫描的操作，当数据分布均匀时，这是正确的选择，但如果出现特殊情况，需要排除的是大量数据，查询出的是少量数据，则索引是更好的选择，因此在CBO下应该将符号替换成“” 的写法,李轶楠 Mail:ora- 13331192030 技术服务人生,案例6 绑定变量带来的性能问题,场景： 10g，范围查询语句，为了减少硬解析，使用了绑定变量 故障现象： SQL执行速度有时很快，有时很慢 分析思路： 绑定变量带来的好处是硬解析的减少，但硬解析的减少也意味着执行计划的不变 范围查询时，取数据的多少直接决定了执行计划的选择 结论： 在范

29、围查询时，如果查询获取的数据量并不是确定不变的，而是有可能有大范围的变化，不要使用绑定变量，大数据量访问时，执行计划的准确性对性能的影响远大于减少硬解析,李轶楠 Mail:ora- 13331192030 技术服务人生,案例7 触发器对性能的影响,场景： 10g，aix主机，应用系统升级，新增加部分业务功能 故障现象： 两个db之间需要进行数据同步，原本在5分钟即可同步完成，现在需要15-20分钟 分析思路： 同步即数据插入，在数据量不变的情况下，数据插入速度严重下降，可能有锁竞争、回滚段竞争、日志缓存区等待或者索引、约束、触发器的影响 有部分业务变更，因此并发竞争、索引、约束、触发器的可能性

30、较大 结论： 由于在表上增加了行级触发器，造成每行插入时都不得不执行触发器，造成整个同步动作变慢，减少触发器的使用，尤其是行级触发器,李轶楠 Mail:ora- 13331192030 技术服务人生,案例8 TB级分区表上分区索引的选择，3小时与0.3秒,场景： 数据量在TB级的分区表，按时间字段进行范围分区，分区字段上有索引，根据用户选择条件进行数据查询 故障现象： 用户每次查询时间长达2-3小时 分析思路： 数据量巨大，用户查询时间长，很可能跟算法错误造成大量I/O有关 用户查询需求不确定，而且用户在查询时经常不选择时间范围 结论： 当分区字段没有作为查询条件出现或该字段没有过滤大量数据时

31、，将不得不走全表扫描 如果不能使用分区字段进行数据过滤，则必须在其他查询字段上建立索引 一般来说，分区索引的效率仅比全局索引效率略低(主要体现在需要更多的索引分区I/O)，但全局索引的维护开销更大，综合考虑进行选择,李轶楠 Mail:ora- 13331192030 技术服务人生,案例9 不同Count的效率分析,场景： 9i，经常需要对一些表进行记录数汇总 故障现象： 统计记录数的效率较低 分析思路： Count在进行汇总时，经常会走全表扫描，dba建议将count(*)调整为count(1)，效果不大 结论： Count(*)与count(1)均是汇总符合条件的总记录数，在没有索引或者RB

32、O下均需要走全表扫描，要提高汇总速度，最好走索引，在CBO模式下，索引字段如果限制了非空约束，Oracle会将Count(*)或者count(1)转换为非空索引的全索引扫描,李轶楠 Mail:ora- 13331192030 技术服务人生,案例10 索引创建顺序对索引选择的影响,场景： 9i，rbo模式 故障现象： 大字段和小字段都有索引，但语句执行时选择错误的大索引 例如：select * from test10 where c2 and b2; 分析思路： RBO下执行计划与语句书写有关，但该语句书写上没有明显问题 结论： 当谓词级别不同时，选则优级别高的索引，当谓词级别相同时，选最新创建

33、的索引,李轶楠 Mail:ora- 13331192030 技术服务人生,案例11 数据分布与索引,场景： 8i 升级至 9i，根据开发商建议，收集统计信息，开始使用CBO模式 故障现象： 大部分语句性能得到提升，但有部分语句效率极低，主要集中在某几个表特定字段的查询上 分析思路： CBO基于统计信息进行代价计算 统计信息的准确性和全面性直接影响执行计划 结论： CBO对统计信息的准确性和全面性要求非常高 数据分布均匀与否，决定了是否需要进行直方图的收集，而直方图的柱数决定了收集信息的准确性 动态采样也是一种选择，总好过错误的信息收集,李轶楠 Mail:ora- 13331192030 技术服

34、务人生,案例12 索引对分组计算的影响,场景： 10g，在大表上分组汇总，计算记录数，考虑到分组需要排序，而有索引可以减少排序，因此在汇总字段上建立了索引 故障现象： 汇总记录数的操作仍然很慢 分析思路： 速度很慢说明语句仍然在走全表扫描，索引没有有效利用 结论： 如果需要通过索引字段进行count计算，必须保证索引记录与表中记录数完全相同，而能够提供这种保证的，一定是非空约束,李轶楠 Mail:ora- 13331192030 技术服务人生,案例13 反转索引对查询的帮助,场景： 10g，用户需要做模糊查询，查询最后几个字母确定的记录 故障现象： 由于like在模糊查询时必须首字母确定才能够

35、走索引，而用户的查询条件是最后几个字母确定，因此like正常使用将不得不走全表扫描 分析思路： 最后几个字母确定，如果最后几个确定的字母能成为like查询的首字母，则like查询可以走索引 结论： 反转索引除了可以在顺序字段做等值比较时分散I/O，减少热点块，也能够使这种需求的查询走索引，从而更快的获取数据,李轶楠 Mail:ora- 13331192030 技术服务人生,案例14 尽量避免的外联接,场景： 9i，多表连接，选择算法为外连接，但在外连接的表上有过滤条件 故障现象： 效率较低 分析思路： 检查发现，过滤条件已经将所有不完全匹配的记录过滤 结论： 如果经过过滤之后的数据能够完全匹配

36、，应该用等值连接代替外连接,李轶楠 Mail:ora- 13331192030 技术服务人生,案例15 外键索引与delete的性能关系,场景： 子表上已经删除了大量数据，需要将主表上相关的数据删除 故障现象： 在主表上删除数据时非常慢 分析思路： Delete性能差，主要的原因是锁竞争、回滚段竞争、日志、索引维护等原因，但察看发现这些问题均不是很严重，发现i/o读相当大 结论： 由于外键需要校验数据参照完整性，因此在删除主表记录时必须在子表上查询相关数据，而子表上外键字段上没有索引造成每校验主表一条数据，就不得不全表扫描子表一次,李轶楠 Mail:ora- 13331192030 技术服务人

37、生,案例16 分页查询的性能,场景： 网站，10g数据库，根据用户需求分页显示结果 故障现象： 网页页面显示非常慢，即使是网页的第一页，只取出很少的数据 分析思路： 分页显示时，用户首先看到的首页的前n行，而大部分时候翻页动作不会做很多次，因此需要让前n页的显示尽可能快 结论： 在排序和过滤的字段上建立索引，并使用first_rows提示，将会提高分页查询的效率,李轶楠 Mail:ora- 13331192030 技术服务人生,案例17 组合索引的跳跃式索引扫描,场景： 9i，组合索引，查询语句中没有出现组合索引的前导字段 故障现象： 查询速度非常慢，有大量的I/O 分析思路： RBO下，组合

38、索引前导字段没有出现时，走全表扫描 该组合索引的前导字段被大量查询使用，但该查询的where子句中没有出现前导字段，只出现了其它字段 符合查询条件的数据很少 结论： 在取数据少的情况下，索引效率更高 在CBO模式下，即使前导字段在where子句中没有出现，仍然可能走索引，索引算法是skip index scan,李轶楠 Mail:ora- 13331192030 技术服务人生,案例18 隐式类型转换带来的性能问题,场景： 10g，大表索引字段上的查询，获取少量数据，查询子句为id=100 故障现象： I/O非常高，查询效率极低 分析思路： 索引字段，查询子句没有出现在函数表达式中，应该走索引，

39、但从执行计划中可以看到，实际上走的是全表扫描 结论： 由于ID为字符类型，而比较的值为数字类型，因此会发生隐式类型转换，而且由于数字类型优先级别高，因此转换发生在索引字段上，因此索引字段实际在执行时出现在了隐式类型转换的转换函数中 应该在查询子句中明确两边的类型，当两边类型不匹配时，建议使用显示类型转换控制转换发生在值上，而不是索引字段上,李轶楠 Mail:ora- 13331192030 技术服务人生,案例19 更快的插入,场景： 存储过程每天执行一次，需要从表中取出数据，经过简单处理之后插入到另一个表中，每次取出并插入的数据超过50w 故障现象： 插入数据的时间很长，由于夜间还需要进行其他业务，因此希望批量插入时间尽可能短 分析思路： 优化插入的效率，主要从日志、回滚、空间动态扩展、并发、索引维护等方面考虑 除了上述常规的优化方法外，使用FOR ALL子句可以带来更快的插入 结论： FOR ALL子句不再是一条一条的进行解析和执行，而是将语句批量处理，CPU的开销将更小，插入速度更快,李轶楠 Mail:ora- 13331192030 技术服务人生,总结,优化无处不在 细节决定成败 理解业务往往比理解操作更重要 具体问题具体分析，优化不是生搬硬套 实际测试比理论分析更有说服力，但实际测试要与理论分析相结合考虑,