《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效率可以想象的关系。见备注 下节：执行计划相关概念介绍。,共享SQL R
3、owid的概念 可选择性 得到执行计划 执行计划的步骤,1.2 执行计划前传之 概念介绍,Row Source (行源)/Driving Table(驱动表)/组合索引(concatenated index,引导列)/访问路径,ROWID： rowid是一个伪列，不是用户定义，而是系统自己加上的。对每个表都有一个rowid的伪列，但是表中并不物理存储rowid列的值。可以像使用其它列那样使用它，但是不能删除改列，也不能对该列的值进行修改、插入。一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的，即即使该行产生行迁移，行的rowid也不会改变。 利用DBMS_ROWID可以将rowi
4、d分解成上述的各部分，也可以将上述的各部分组成一个有效的rowid,可选择性(selectivity)： 列中唯一键的数量和表中的行数，就可以判断该列的可选择性。 唯一键的数量/表中的行数 1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。,得到执行计划： 共有三种方法得到执行计划。 1、 Sql set autotrace on Sql set autotrace traceonly 2、用explain plan命令 此方法时，并不执行sql语句，所以只会列出执行计划，不会列出统计信息，并且执行计
5、划只存在plan_table中。所以该语句比起set autotrace traceonly可用性要差。需要用下面的命令格式化输出。,上面这2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条SQL语句去优化。其实，在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如： SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions, buffer_gets/executi
6、ons AVG FROM v$sqlarea WHERE executions0 AND buffer_gets 100000 ORDER BY 5;,3、用dbms_system存储过程生成执行计划。 因为使用dbms_system存储过程可以跟踪另一个会话发出的sql语句，并记录所使用的执行计划，而且还提供其它对性能调整有用的信息。因其使用方式与上面2种方式有些不太一样，所以在附录中单独介绍。这种方法是对SQL进行调整比较有用的方式之一，有些情况下非它不可。 4.DBMS_XPALN 演示,执行计划步骤： 在了解执行计划步骤之前认知性的了解下SQL的执行步骤。SQL处理的基本过程。主要包括
7、： * 查询语句处理 * DML语句处理(insert, update, delete) * 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执行计划的每一步返回一组行，它们或者为下一步所使用，或者在最后一
8、步时返回给发出SQL语句的用户或应用。由每一步返回的一组行叫做行源(row source)。下面得树状图显示了从一步到另一步行数据的流动情况。每步的编号反映了在你观察Oracle执行计划时所示步骤的顺序(如何观察Oracle执行计划将被简短地说明)。一般来说这并不是每一步被执行的先后顺序。,SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND NOT EXISTS ( SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal
9、);,Oracle执行计划的每一步或者从数据库中检索行，或者接收来自一个或多个行源的行数据作为输入。 第3步和第6步分别的从EMP表和SALGRADE表读所有的行。 第5步在PK_DEPTNO索引中查找由步骤3返回的每个DEPTNO值。它找出与DEPT表中相关联的那些行的ROWID。 第4步从DEPT表中检索出ROWID为第5步返回的那些行。 由黑色字框指出的步骤在行源上操作，如做2表之间的关联，排序，或过滤等操作，后面也会给出详细的介绍： 第2步实现嵌套的循环操作，接收从第3步和第4步来的行源，把来自第3步源的每一行与它第4步中相应的行连接在一起，返回结果行到第1步。 第1步完成一个过滤器操
10、作。它接收来自第2步和第6步的行源，消除掉第2步中来的，在第6步有相应行的那些行，并将来自第2步的剩下的行返回给发出语句的用户或应用。,对第3步返回的每一行，Oracle实现这些步骤： 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. WH
11、ERE子句中的连接顺序,ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾. (低效) SELECT FROM EMP E WHERE SAL 50000 AND JOB = MANAGER AND 25 50000 AND JOB = “MANAGER”;,当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量, 读数据块等等. 由此可见, 减少访问数据库的次数, 就能实际上减少ORACLE的工作量. 例如：以下有三种
12、方法可以检索出雇员号等于0342或0291的职员. 方法1 (最低效) SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342; SELECT 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 ,
13、 ; 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 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
14、 ENAME LIKE SMITH%; SELECT COUNT(*)，SUM(SAL) FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE SMITH%; 你可以用DECODE函数高效地得到相同结果 SELECT COUNT(DECODE(DEPT_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)
15、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_NO = E.EMP_NO);,5.用TRUNCATE替代DELETE,当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的
16、状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. 备注 (PS: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML),6.尽量多使用COMMIT,只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: COMMIT所释放的资源: a. 回滚段上用于恢复数据的信息. b. 被程序语句获得的锁 c. redo log buffer 中的空间 d. ORACLE为管理上述3种资源中的内部花费 (PS: 在使用COMMIT时必须要
17、注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼),7.计算记录条数,和一般的观点相反, count(*) 比count(1)稍快, 当然如果可以通过索引检索, 对索引列的计数仍旧是最快的. 例如COUNT(EMPNO) 。 在某论坛（屏蔽广告）中,有热烈的讨论, 可能我这里的观点并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差,8.用Where子句替换HAVING子句,避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. 低效: SE
18、LECT 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 != PERTH GROUP BY REGION (PS: HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中),9.减少对表的查询,在含有
19、子查询的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 FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHE
20、RE 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;,没完,