ORACLE执行计划和日常注意事项.ppt

oracle 执行计划和sql日常注意事项,影 子 565420009,内容安排：,第一章 执行计划,1.1 什么是执行计划？ 我的解释：针对查询语句，按照一定的规则做出的一个执行方案。 比如：我要回四川，有两种基本方案。一是时间最快的回去，二是最省钱的回去。但是我比较穷，同时我也不是很傻，那么我就会在两者之间寻求平衡。找到回四川的最优方案（既省钱又快）。这个方案就是我回家的执行方案。 其他解释：执行计划实际上是由一系列数据处理过程构成的一个树型的函数链；那些可以访问到物理数据的函数被称为访问路径（access path, 如index range scan, full table scan等）；该树形函数链的最底层（叶子节点）从物理对象中获取到原始数据（row source）后，将数据由底向上传递，每一个节点的函数对其进行处理（如join匹配、过滤等）得到一个新的row source，并继续向上层传递，直到根节点，此时得到的数据就是查询结果。,对于查询，sql语句仅仅是在不违背语法的前提下，表达出了我要的东西。但是至于怎么去得到它，sql语句本身并不包含此类信息。此类信息是由数据库来决定的。面向对象的思想嘛_-。 对于开发人员，尤其是对于ontp系统的开发人员来讲，写出高效的sql尤其重要，而执行计划与sql效率可以想象的关系。见备注 下节：执行计划相关概念介绍。,共享sql rowid的概念 可选择性 得到执行计划 执行计划的步骤,1.2 执行计划前传之 概念介绍,row source (行源)/driving table(驱动表)/组合索引(concatenated index,引导列)/访问路径,rowid： rowid是一个伪列，不是用户定义，而是系统自己加上的。对每个表都有一个rowid的伪列，但是表中并不物理存储rowid列的值。可以像使用其它列那样使用它，但是不能删除改列，也不能对该列的值进行修改、插入。一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的，即即使该行产生行迁移，行的rowid也不会改变。 利用dbms_rowid可以将rowid分解成上述的各部分，也可以将上述的各部分组成一个有效的rowid,可选择性(selectivity)： 列中唯一键的数量和表中的行数，就可以判断该列的可选择性。 唯一键的数量/表中的行数 1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。,得到执行计划： 共有三种方法得到执行计划。 1、 sql set autotrace on sql set autotrace traceonly 2、用explain plan命令 此方法时，并不执行sql语句，所以只会列出执行计划，不会列出统计信息，并且执行计划只存在plan_table中。所以该语句比起set autotrace traceonly可用性要差。需要用下面的命令格式化输出。,上面这2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条sql语句去优化。其实，在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个sql引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如： select address, substr(sql_text,1,20) text, buffer_gets, executions, buffer_gets/executions 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处理的基本过程。主要包括： * 查询语句处理 * 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执行计划的每一步返回一组行，它们或者为下一步所使用，或者在最后一步时返回给发出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 );,oracle执行计划的每一步或者从数据库中检索行，或者接收来自一个或多个行源的行数据作为输入。 第3步和第6步分别的从emp表和salgrade表读所有的行。 第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步返回的每一行，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. where子句中的连接顺序,oracle采用自下而上的顺序解析where子句,根据这个原理,表之间的连接必须写在其他where条件之前, 那些可以过滤掉最大数量记录的条件必须写在where 子句的末尾. (低效) 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; 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 , ; 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 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) 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会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用truncate时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. 备注 (ps: truncate只在删除全表适用,truncate是ddl不是dml),6.尽量多使用commit,只要有可能,在程序中尽量多使用commit, 这样程序的性能得到提高,需求也会因为commit所释放的资源而减少: commit所释放的资源: a. 回滚段上用于恢复数据的信息. b. 被程序语句获得的锁 c. redo log buffer 中的空间 d. oracle为管理上述3种资源中的内部花费 (ps: 在使用commit时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼),7.计算记录条数,和一般的观点相反, count(*) 比count(1)稍快, 当然如果可以通过索引检索, 对索引列的计数仍旧是最快的. 例如count(empno) 。 在某论坛（屏蔽广告）中,有热烈的讨论, 可能我这里的观点并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差,8.用where子句替换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 != perth group by region (ps: having 中的条件一般用于对一些集合函数的比较,如count() 等等. 除此而外,一般的条件应该写在where子句中),9.减少对表的查询,在含有子查询的sql语句中,要特别注