ORACLE PLSQL优化.ppt

,PL/SQL优化,Copyright2008ByNeusoftGroup.Allrightsreserved,软件开发事业部马小凌,目的&预期结果,目的：1、一点经验共享。2、工作中指导作用。,预期结果：1、听后感觉到似乎懂了什么。2、实际工作中确实能用上某点。,内容,PL/SQL优化*索引索引误区索引优化原则索引如何选择*执行计划如何选择基表(外表)如何查看执行计划*常用sqlPL/SQL注意事项SQL的使用规范SQL优化实例(常用语句),索引误区、原则、选择,索引,索引,实现高效的sql，建立合理的索引是非常必要的,索引种类1、B*tree2、反向索引3、降序索引4、位图索引5、函数索引,索引误区,索引越多越好？5个一把双刃剑，有利也有弊对于一个单表的查询，可以索引1进行过滤再使用索引2进行过滤？索引检索一定比全表扫描快,索引优化,原则：将索引和数据放在不同的文件组一定要有索引的引导项（第一个索引列）唯一索引的使用表关联查询时的内部表上关键列要有索引如果该列的“唯一键的数量/表中记录行数”的比值越接近于1，则该列建立索引可能性较高9i的跳跃索引,索引选择,1、where从句中频繁使用的关键字。2、和其他表关联的关键字，考虑建立索引。3、关键字的可选择性比较高。4、建立组合索引的时候要选好前导列。5、嵌套循环的内表关联字段一定要有索引。,我建立的索引有效吗？,为甚么要分析？CBO优化分析的必要性。,执行计划,SQL如何执行？,内表&外表,RBO：外表是from后面的最右边的表。CBO：外表自动调节的,具体演示实例(看执行计划)CBO为什么要分析表？,RBO：基于规则的优化CBO：基于代价的优化参数调节：optimizer_mode:Rule、choose、all_rows、first_rows、first_rows_n表连接方式：排序-合并连接(SortMergeJoin(SMJ)嵌套循环(NestedLoops(NL)哈希连接(HashJoin),如何查询SQL效率？,1、sql*plus2、explain3、工具（plsql),如何查询SQL效率？,？,如何查询SQL效率(续)？,？,使用ExplainPlan,$ORACLE_HOME/rdbms/admin/utlxplan.sql,如何查询SQL效率(续)？,？,PL块的注意事项,PL/SQL优化,PL/SQL（ProceduralLanguage/StructuredQueryLanguage）处理复杂的业务逻辑、快速开发。与应用程序分离，确保sql语句的一致性。高度安全好的PL程序表面特征：整洁、统一对其格式、关键字大写。处理要完整。有针对性的捕获异常。,下面程序需要做哪些优化？,没有初始化,缺少提交,缺少异常,参数会传吗？,尽量使用%type使用%rowtype整体传输参数,数组的使用,临时表or数组？为甚么使用数组？一个处理块内使用。减少IO访问，快速。,数组的使用（例）,SQL的使用规范,SQL的使用规范,1、采用共享sql特别是动态sql，必须使用变量绑定，避免sql的重复解析。2、SELECT子句中避免使用*oracle需要转换成表的列，通过查询数据字典获得，耗费时间3、使用表的别名(Alias)明确的告诉oracle是哪个表上的列。不要将工作留给oracle。,SQL的使用规范,4、尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。5、注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。,SQL的使用规范,6、不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。尽量使用“=”，不要使用“”。7、count(1)比count(*)更有效率。,SQL的使用规范,8、注意表之间连接的数据类型，避免不同类型数据之间的连接。注意存储过程中参数和数据类型的关系。9、注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁，不利于并行开发。,SQL优化实例,SQL优化,1、选择最有效率的表名顺序：选择记录条数最少的表作为基础表,说明：以上oracle的optimizer是RBO的，CBO则无此限制，基表是怎么选择的？内表的关联字段上要有索引。,SQL优化,2、where子句的连接顺序：ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾,SQL优化,3、减少对表的查询,SQL优化,4、需要当心的WHERE子句（在索引列上做运算后，查询将不走索引）,SQL优化,5、用EXISTS替代IN：并不是绝对的,SQL优化,in适合内外表都很大的情况，exists适合外表结果集很小的情况。,SQL优化,6、用EXISTS替换DISTINCT（条件：外表与内表是一对多的情况下使用）,SQL优化,7、减少不必要的查询误区：删除数据前，先检测是否存在，存在再删除吧。,检测是多此一举，直接删除！缺点：如果存在，多了一次交互，增加负担。删除不会出现异常，为什么要多一次查询呢？,SQL优化,8、伪列的使用rowid表里有重复数据，需要将其捡出并删除。最好将rowid保留下来，做为后续处理的条件。(行迁移？),SQL优化,9、执行速度当处理大批量的数据的时候，处理时间可能较长，有耐心等待吗？时间允许等待吗？该怎么办