ORACLE PLSQL优化学习教程.pptx

《ORACLE PLSQL优化学习教程.pptx》由会员分享，可在线阅读，更多相关《ORACLE PLSQL优化学习教程.pptx（43页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、目的&预期结果目的：1、一点经验共享。2、工作中指导作用。预期结果：1、听后感觉到似乎懂了什么。2、实际工作中确实能用上某点。第1页/共43页内容PL/SQL优化优化*索引索引索引误区索引误区索引优化原则索引优化原则索引如何选择索引如何选择*执行计划执行计划如何选择基表如何选择基表(外表外表)如何查看执行计划如何查看执行计划*常用常用sqlPL/SQL注意事项注意事项SQL的使用规范的使用规范SQL优化实例优化实例(常用语句常用语句)第2页/共43页索引误区、原则、选择索引索引第3页/共43页索引实现高效的实现高效的sql，建立合理的索引是非常必要的，建立合理的索引是非常必要的索引种类索引种类

2、1、B*tree2、反向索引、反向索引3、降序索引、降序索引4、位图索引、位图索引5、函数索引、函数索引第4页/共43页索引误区索引越多越好？索引越多越好？5个个一把双刃剑，有利也有弊一把双刃剑，有利也有弊对于一个单表的查询，可以索引对于一个单表的查询，可以索引1进行过滤再使用进行过滤再使用索引索引2进行过滤？进行过滤？索引检索一定比全表扫描快索引检索一定比全表扫描快第5页/共43页索引优化原则：原则：将索引和数据放在不同的文件组将索引和数据放在不同的文件组一定要有索引的引导项（第一个索引列）一定要有索引的引导项（第一个索引列）唯一索引的使用唯一索引的使用表关联查询时的内部表上关键列要有索引表

3、关联查询时的内部表上关键列要有索引如果该列的如果该列的“唯一键的数量唯一键的数量/表中记录行数表中记录行数”的的比值越比值越接近于接近于1，则该列建立索引可能性较高则该列建立索引可能性较高9i的跳跃索引的跳跃索引第6页/共43页索引选择1、where从句中频繁使用的关键字。从句中频繁使用的关键字。2、和其他表关联的关键字，考虑建立索引。、和其他表关联的关键字，考虑建立索引。3、关键字的可选择性比较高。、关键字的可选择性比较高。4、建立组合索引的时候要选好前导列。、建立组合索引的时候要选好前导列。5、嵌套循环的内表关联字段一定要有索引。、嵌套循环的内表关联字段一定要有索引。第7页/共43页我建立

4、的索引有效吗？为甚么要分析？CBO优化分析的必要性。第8页/共43页执行计划第9页/共43页SQL如何执行？第10页/共43页内表&外表RBO：外表是：外表是from后面的最右边的表。后面的最右边的表。CBO：外表自动调节的：外表自动调节的,具体演示实例具体演示实例(看执行计划看执行计划)CBO为什么要分析表？为什么要分析表？RBO：基于规则的优化CBO：基于代价的优化参数调节：optimizer_mode:Rule、choose、all_rows、first_rows、first_rows_n表连接方式：排序-合并连接(Sort Merge Join(SMJ)嵌套循环(Nested Loop

5、s(NL)哈希连接(Hash Join)第11页/共43页如何查询SQL效率？1、sql*plus2、explain3、工具（工具（plsql)第12页/共43页如何查询SQL效率？第13页/共43页如何查询SQL效率(续)？使用ExplainPlan$ORACLE_HOME/rdbms/admin/utlxplan.sql第14页/共43页如何查询SQL效率(续)？第15页/共43页PL块的注意事项第16页/共43页PL/SQL优化PL/SQL（ProceduralLanguage/StructuredQueryLanguage）处理复杂的业务逻辑、快速开发。处理复杂的业务逻辑、快速开发。与

6、应用程序分离，确保与应用程序分离，确保sql语句的一致性。语句的一致性。高度安全高度安全好的好的PL程序表面特征：程序表面特征：整洁、统一对其格式、关键字大写。整洁、统一对其格式、关键字大写。处理要完整。处理要完整。有针对性的捕获异常。有针对性的捕获异常。第17页/共43页下面程序需要做哪些优化？没有初始化缺少提交缺少异常第18页/共43页参数会传吗？尽量使用尽量使用%type使用使用%rowtype整体传输参数整体传输参数第19页/共43页数组的使用临时表临时表or数组？数组？为甚么使用数组？为甚么使用数组？一个处理块内使用。一个处理块内使用。减少减少IO访问，快速访问，快速。第20页/共4

7、3页数组的使用（例）第21页/共43页SQL的使用规范第22页/共43页SQL的使用规范1、采用共享、采用共享sql特别是动态特别是动态sql，必须使用变量绑定，避免，必须使用变量绑定，避免sql的重复解析。的重复解析。2、SELECT子句中避免使用子句中避免使用*oracle需要转换成表的列，通过查询数据字典获得，耗费需要转换成表的列，通过查询数据字典获得，耗费时间时间3、使用表的别名使用表的别名(Alias)明确的告诉明确的告诉oracle是哪个表上的列。不要将工作留给是哪个表上的列。不要将工作留给oracle。第23页/共43页SQL的使用规范4、尽量避免反复访问同一张或几张表，尤其是数

8、据量较大、尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然的表，可以考虑先根据条件提取数据到临时表中，然后再后再做连接。做连接。5、注意、注意where字句写法，必须考虑语句顺序，应该根据索字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。尽可能的让字段顺序与索引顺序相一致，范围从大到小。第24页/共43页SQL的使用规范6、不要在、不要在where子句中的子句中的“=”左边进行函数、算术运算或左边进行函数、算术运算或其他表达式

9、运算，否则系统将可能无法正确使用索引。尽量其他表达式运算，否则系统将可能无法正确使用索引。尽量使用使用“=”，不要使用，不要使用“”。7、count(1)比比count(*)更有效率。更有效率。第25页/共43页SQL的使用规范8、注意表之间连接的数据类型，避免不同类型数据之间的、注意表之间连接的数据类型，避免不同类型数据之间的连接。注意存储过程中参数和数据类型的关系连接。注意存储过程中参数和数据类型的关系。9、注意、注意insert、update操作的数据量，防止与其他应用冲操作的数据量，防止与其他应用冲突。如果数据量超过突。如果数据量超过200个数据页面（个数据页面（400k），那么系统将

10、），那么系统将会进行锁升级，页级锁会升级成表级锁，不利于并行开发。会进行锁升级，页级锁会升级成表级锁，不利于并行开发。第26页/共43页SQL优化实例第27页/共43页SQL优化1、选择最有效率的表名顺序：、选择最有效率的表名顺序：选择记录条数最少的表作为基础表选择记录条数最少的表作为基础表说明：以上说明：以上oracle的的optimizer是是RBO的，的，CBO则无此限制，基表是怎么选则无此限制，基表是怎么选择的？内表的关联字段上要有索引。择的？内表的关联字段上要有索引。第28页/共43页SQL优化2、where子句的连接顺序：子句的连接顺序：ORACLE采用自下而上的顺序解析采用自下而

11、上的顺序解析WHERE子句子句,根据这个原理根据这个原理,表之间的连接必须写在其他表之间的连接必须写在其他WHERE条件之前条件之前,那些可以过滤掉最大数量记录的条件必须写在那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾子句的末尾第29页/共43页SQL优化3、减少对表的查询、减少对表的查询第30页/共43页SQL优化4、需要当心的、需要当心的WHERE子句（子句（在索引列上做运算后，查询将不走索引）在索引列上做运算后，查询将不走索引）第31页/共43页SQL优化5、用、用EXISTS替代替代IN：并不是绝对的并不是绝对的第32页/共43页SQL优化in适合内外表都很大的情况，适

12、合内外表都很大的情况，exists适合外表结果集很小的情况。适合外表结果集很小的情况。第33页/共43页SQL优化6、用、用EXISTS替换替换DISTINCT（条件：外表与内表是一对多的情况下使用）条件：外表与内表是一对多的情况下使用）第34页/共43页SQL优化7、减少不必要的查询、减少不必要的查询误区：误区：删除数据前，先检测是否存在，存在再删除吧。删除数据前，先检测是否存在，存在再删除吧。检测是多此一举，直接删除！缺点：如果存在，多了一次交互，增加负担。删除不会出现异常，为什么要多一次查询呢？第35页/共43页SQL优化8、伪列的使用、伪列的使用rowid表里有重复数据，需要将其捡出并

13、删除。表里有重复数据，需要将其捡出并删除。最好将最好将rowid保留下来，做为后续处理的条件。保留下来，做为后续处理的条件。(行迁移？行迁移？)第36页/共43页SQL优化9、执行速度、执行速度当处理大批量的数据的时候，处理时间可能较长，有耐心等待吗？当处理大批量的数据的时候，处理时间可能较长，有耐心等待吗？时间允许等待吗？该怎么办？时间允许等待吗？该怎么办？多线程处理！第37页/共43页SQL优化10、增加提示、增加提示(hint)-改变优化器改变优化器第38页/共43页SQL优化11、优化优化like%abc或者或者like%abc%语句语句第39页/共43页Q&A第40页/共43页回顾1、如何选择基表，内表关、如何选择基表，内表关联列要有索引。联列要有索引。2、如果选择索引列、如果选择索引列3、sql语句对比语句对比4、执行计划、执行计划！第41页/共43页谢谢第42页/共43页谢谢您的观看！第43页/共43页