《ORACLE执行计划和日常注意事项.ppt》由会员分享，可在线阅读，更多相关《ORACLE执行计划和日常注意事项.ppt（35页珍藏版）》请在三一办公上搜索。
1、ORACLE 执行计划和SQL日常注意事项,影 子565420009,内容安排：,第一章 执行计划,1.1 什么是执行计划？我的解释：针对查询语句，按照一定的规则做出的一个执行方案。比如：我要回四川，有两种基本方案。一是时间最快的回去，二是最省钱的回去。但是我比较穷，同时我也不是很傻，那么我就会在两者之间寻求平衡。找到回四川的最优方案（既省钱又快）。这个方案就是我回家的执行方案。其他解释：执行计划实际上是由一系列数据处理过程构成的一个树型的函数链；那些可以访问到物理数据的函数被称为访问路径（Access Path,如Index Range Scan,Full Table Scan等）；该树形函
2、数链的最底层（叶子节点）从物理对象中获取到原始数据（Row Source）后，将数据由底向上传递，每一个节点的函数对其进行处理（如JOIN匹配、过滤等）得到一个新的Row Source，并继续向上层传递，直到根节点，此时得到的数据就是查询结果。,对于查询，SQL语句仅仅是在不违背语法的前提下，表达出了我要的东西。但是至于怎么去得到它，SQL语句本身并不包含此类信息。此类信息是由数据库来决定的。面向对象的思想嘛_-。对于开发人员，尤其是对于ONTP系统的开发人员来讲，写出高效的SQL尤其重要，而执行计划与SQL效率可以想象的关系。见备注下节：执行计划相关概念介绍。,共享SQLRowid的概念可选
3、择性 得到执行计划执行计划的步骤,1.2 执行计划前传之 概念介绍,Row Source(行源)/Driving Table(驱动表)/组合索引(concatenated index,引导列)/访问路径,ROWID：rowid是一个伪列，不是用户定义，而是系统自己加上的。对每个表都有一个rowid的伪列，但是表中并不物理存储rowid列的值。可以像使用其它列那样使用它，但是不能删除改列，也不能对该列的值进行修改、插入。一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的，即即使该行产生行迁移，行的rowid也不会改变。利用DBMS_ROWID可以将rowid分解成上述的各部分，也可以
4、将上述的各部分组成一个有效的rowid,可选择性(selectivity)：列中唯一键的数量和表中的行数，就可以判断该列的可选择性。唯一键的数量/表中的行数-1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。,得到执行计划：共有三种方法得到执行计划。1、Sql set autotrace on Sql set autotrace traceonly2、用explain plan命令此方法时，并不执行sql语句，所以只会列出执行计划，不会列出统计信息，并且执行计划只存在plan_table中。所以该语句
5、比起set autotrace traceonly可用性要差。需要用下面的命令格式化输出。,上面这2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条SQL语句去优化。其实，在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如：SELECT ADDRESS,substr(SQL_TEXT,1,20)Text,buffer_gets,executions,buffer_gets/executions AVGFROM v$sqlareaWHEREe
6、xecutions0AND buffer_gets 100000ORDER BY 5;,3、用dbms_system存储过程生成执行计划。因为使用dbms_system存储过程可以跟踪另一个会话发出的sql语句，并记录所使用的执行计划，而且还提供其它对性能调整有用的信息。因其使用方式与上面2种方式有些不太一样，所以在附录中单独介绍。这种方法是对SQL进行调整比较有用的方式之一，有些情况下非它不可。4.DBMS_XPALN演示,执行计划步骤：在了解执行计划步骤之前认知性的了解下SQL的执行步骤。SQL处理的基本过程。主要包括：*查询语句处理*DML语句处理(insert,update,delet
7、e)*DDL 语句处理(create.,drop.,alter.,)*事务控制(commit,rollback)主要讲DML语句的处理过程，第1步:Create a Cursor 创建游标第2步:Parse the Statement分析语句第3步:Bind Any Variables 绑定变量第4步:Run the Statement 运行语句第5步:Close the Cursor 关闭游标,Oracle执行计划的每一步返回一组行，它们或者为下一步所使用，或者在最后一步时返回给发出SQL语句的用户或应用。由每一步返回的一组行叫做行源(row source)。下面得树状图显示了从一步到另一步
8、行数据的流动情况。每步的编号反映了在你观察Oracle执行计划时所示步骤的顺序(如何观察Oracle执行计划将被简短地说明)。一般来说这并不是每一步被执行的先后顺序。,SELECT ename,job,sal,dnameFROM emp,deptWHERE emp.deptno=dept.deptnoAND NOT EXISTS(SELECT*FROM salgradeWHERE emp.sal BETWEEN losal AND hisal);,Oracle执行计划的每一步或者从数据库中检索行，或者接收来自一个或多个行源的行数据作为输入。第3步和第6步分别的从EMP表和SALGRADE表读所
9、有的行。第5步在PK_DEPTNO索引中查找由步骤3返回的每个DEPTNO值。它找出与DEPT表中相关联的那些行的ROWID。第4步从DEPT表中检索出ROWID为第5步返回的那些行。由黑色字框指出的步骤在行源上操作，如做2表之间的关联，排序，或过滤等操作，后面也会给出详细的介绍：第2步实现嵌套的循环操作，接收从第3步和第4步来的行源，把来自第3步源的每一行与它第4步中相应的行连接在一起，返回结果行到第1步。第1步完成一个过滤器操作。它接收来自第2步和第6步的行源，消除掉第2步中来的，在第6步有相应行的那些行，并将来自第2步的剩下的行返回给发出语句的用户或应用。,对第3步返回的每一行，Orac
10、le实现这些步骤：Oracle实现步骤5，并将结果ROWID返回给第4步。Oracle实现步骤4，并将结果行返回给第2步。Oracle实现步骤2，将接受来自第3步的一行和来自第4步的一行，并返回给第1步一行。Oracle实现步骤6，如果有结果行的话，将它返回给第1步。Oracle实现步骤1，如果从步骤6返回行，Oracle将来自第2步的行返回给发出SQL语句的用户。,第二章 SQL日常注意事项,2.1前情回顾,1.WHERE子句中的连接顺序,ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHE
11、RE 子句的末尾.(低效)SELECT FROM EMP E WHERE SAL 50000 AND JOB=MANAGER AND 25 50000 AND JOB=“MANAGER”;,2.减少访问数据库的次数,当执行每条SQL语句时,ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量.例如：以下有三种方法可以检索出雇员号等于0342或0291的职员.方法1(最低效)SELECT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO=342;SELE
12、CT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO=291;,方法2(次低效)DECLARE CURSOR C1(E_NO NUMBER)IS SELECT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO=E_NO;BEGIN OPEN C1(342);FETCH C1 INTO,.,.;.OPEN C1(291);FETCH C1 INTO,.,.;CLOSE C1;END;方法3(高效)SELECT A.EMP_NAME,A.SALARY,A.GRADE,B.EMP_NAME,B.SALARY,B.GRADE
13、 FROM EMP A,EMP B WHERE A.EMP_NO=342 AND B.EMP_NO=291;,3.使用DECODE函数来减少处理时间,使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.SELECT COUNT(*)，SUM(SAL)FROM EMP WHERE DEPT_NO=0020 AND ENAME LIKE SMITH%;SELECT COUNT(*)，SUM(SAL)FROM EMP WHERE DEPT_NO=0030 AND ENAME LIKE SMITH%;你可以用DECODE函数高效地得到相同结果SELECT COUNT(DECODE(DEPT
14、_NO,0020,X,NULL)D0020_COUNT,COUNT(DECODE(DEPT_NO,0030,X,NULL)D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,NULL)D0020_SAL,SUM(DECODE(DEPT_NO,0030,SAL,NULL)D0030_SAL FROM EMP WHERE ENAME LIKE SMITH%;,4.删除重复记录,最高效的删除重复记录方法(因为使用了ROWID)DELETE FROM EMP E WHERE E.ROWID(SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_
15、NO=E.EMP_NO);,5.用TRUNCATE替代DELETE,当删除表中的记录时,在通常情况下,回滚段(rollback segments)用来存放可以被恢复的信息.如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.备注(PS:TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML),6.尽量多使用COMMIT,只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也
16、会因为COMMIT所释放的资源而减少:COMMIT所释放的资源:a.回滚段上用于恢复数据的信息.b.被程序语句获得的锁 c.redo log buffer 中的空间 d.ORACLE为管理上述3种资源中的内部花费(PS:在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼),7.计算记录条数,和一般的观点相反,count(*)比count(1)稍快,当然如果可以通过索引检索,对索引列的计数仍旧是最快的.例如COUNT(EMPNO)。在某论坛（屏蔽广告）中,有热烈的讨论,可能我这里的观点并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差,8.用Wh
17、ere子句替换HAVING子句,避免使用HAVING子句,HAVING 只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.低效:SELECT REGION，AVG(LOG_SIZE)FROM LOCATION GROUP BY REGION HAVING REGION REGION!=SYDNEY AND REGION!=PERTH,高效：SELECT REGION，AVG(LOG_SIZE)FROM LOCATION WHERE REGION REGION!=SYDNEY AND REGION!=PERT
18、H GROUP BY REGION(PS:HAVING 中的条件一般用于对一些集合函数的比较,如COUNT()等等.除此而外,一般的条件应该写在WHERE子句中),9.减少对表的查询,在含有子查询的SQL语句中,要特别注意减少对表的查询.低效：SELECT TAB_NAME FROM TABLES WHERE TAB_NAME=(SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION=604)AND DB_VER=(SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION=604),高效：SELECT TAB_NAME FR
19、OM TABLES WHERE(TAB_NAME,DB_VER)=(SELECT TAB_NAME,DB_VER)FROM TAB_COLUMNS WHERE VERSION=604),Update 多个Column 例子:低效:UPDATE EMP SET EMP_CAT=(SELECT MAX(CATEGORY)FROM EMP_CATEGORIES),SAL_RANGE=(SELECT MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT=0020;高效:UPDATE EMP SET(EMP_CAT,SAL_RANGE)=(SELECT MAX(CATEGORY),MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT=0020;,没完,
