本章提要
-------------------------------------------如何设置, 来获取执行计划如何读懂执行计划-------------------------------------------执行计划描述了SQL引擎为执行SQL语句进行的操作. 在任何时候, 要处理执行计划, 需以下三步, 获取, 解释, 判定效率.6.1 获取执行计划 获取的办法: 1) 执行SQL语句EXPLAIN PLAN, 查询结果输出表 2) 查看动态视图 3) 查询如 statspack 表 4) 启动提供执行计划的跟踪功能 看来, 3,4比较常用, 另外, 绑定变量可能会对执行计划产生影响 display函数, 可以显示已经存储的执行计划 例如 select * from table(dbms_xplan.display(null, null, 'typical')); select /*+ gather_plan_statistics */ count(*) -- 使用提示产生执行计划 from t where mod(n,19) = 0; select * from table(dbms_xplan.dispaly_cursor(null, null, 'isotats last')); -- isotats last 是控制I/O显示, 也可以使用默认级别6.2 解释执行计划 父子关系 执行计划是一个树形结构, 它不仅阐述了SQL引擎执行操作的顺序, 也阐明了它们之间的关系. 树的每一个结点都代表一个 操作, 比如, 表访问, 连接或排序, 在各操作(结点)之间, 存在父子关系. 父子关系规则如下: ~ 父有一个或多个子 ~ 子只有一个父 ~ 唯一没有父的操作是树的根 ~ 在执行计划显示时, 子在父的右侧, 一个父亲的所有孩子都有相同的步调(缩进相同的字符数) ~ 父在他孩子的前面(父亲的ID小于其孩子的ID), 如果一个孩子有多个包含相同缩进的前导操作, 靠孩子最近的就是他的父亲. 下面举例: --------------------------------------- | Id | Operation | --------------------------------------- | 1 | UPDATE | | 2 | NESTED LOOPS | |* 3 | TABLE ACCESS FULL | |* 4 | INDEX UNIQUE SCAN | | 5 | SORT AGGREGATE | | 6 | TABLE ACCESS BY INDEX ROWID| |* 7 | INDEX RANGE SCAN | | 8 | TABLE ACCESS BY INDEX ROWID | |* 9 | INDEX UNIQUE SCAN | ---------------------------------------
~ 操作1是树的根, 它有三个孩子, 2,5,8 ~ 操作2有两个孩子, 3,4 ~ 操作3,4 没有孩子 ~ 操作5有一个孩子, 6 ~ 操作6有一个孩子, 7 ~ 操作7没有孩子 ~ 操作8有一个孩子, 9 ~ 操作9没有孩子 操作类型 有很多中操作, 目前先搞清楚三种主要操作, 单独操作, 非相关联合型操作, 相关联合型操作.(作者自己创造术语) 按照这三种类型, 操作可以被分为块操作和非块操作. 块操作: 一批一批的处理数据 非块操作: 一行一行的处理数据 如 排序(sort)操作是块操作, 使用一个简单约束条件过滤(filter)操作就是一个非块操作, 因为它独立的评估每一条记录. 单独操作 只有不到20种操作不属于这种类型 把最多有一个孩子的操作定义为单独操作. ~ 孩子在父亲前面执行, 然后, 本章后面出现两个优化技术会导致例外. ~ 每个孩子只执行一次 ~ 每个孩子向他的父亲提供输出 例如: SELECT deptno, count(*) FROM emp WHERE job = 'CLERK' AND sal < 1200 GROUP BY deptno -------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | -------------------------------------------------------------------- | 1 | HASH GROUP BY | | 1 | 2 | |* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 3 | |* 3 | INDEX RANGE SCAN | EMP_JOB_I | 1 | 4 | -------------------------------------------------------------------- 2 - filter("SAL"<1200) 3 - access("JOB"='CLERK') 1) 这是一个单独操作, 父子关系是, 1->2->3(即1是2的父亲, 2是3的父亲), 那么根据单独操作的规则, 应该首先执行3. 2) 操作3通过访问型谓词"JOB=CLERK"扫描索引emp_job_i(index range scan), 在此过程中, 它从索引里抽取了4个rowid(此信息为A-Rows得到), 并把他们传递给它的父操作2. 3) 操作2通过操作3传过来的4个rowid访问表emp(TABLE access by index rowid), 对每个rowid, 有一条记录被读取, 然后再应用过滤型谓词"SAL<1200"过滤, 这个过滤导致一条记录被排除, 剩下的三条记录被传递给它的父操作1 4) 操作1对操作2传递过来的记录进行分组(GROUP BY), 最终结果变成两条记录, 因为这已经是最后一步操作了, 之后 数据将被返回给调用者. 注意: starts 清晰的显示了每一步操作被执行一次. 下边的, 2-filter 和 3-access 分别对应 2,3操作的对应语句. 有一条规则是, 子操作要在父操作之前进行, 但是智能优化器引进了一些不同的情况, 就是父操作合一决定不需要等 子操作完成或根本就不等, 换句话说, 父操作控制着子操作的执行, 例如: 1) COUNT STOPKEY 优化 COUNT STOPKEY 操作通常用来执行 top-n查询, 它的目的是在所需数量的记录一返回给调用者之后, 就立即停止 当前操作. SELECT * FROM emp WHERE rownum <= 10 ----------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | ----------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | 10 | | 2 | TABLE ACCESS FULL| EMP | 1 | 10 | ----------------------------------------------------- 1 - filter(ROWNUM<=10) 注意, 操作2只执行了1次, 并且操作了10条记录, 而表中实际有14条记录, 而你做的是全表扫描(TABLE ACCESS FULL), 按道理说, 应该是操作了14条记录, 但是, 发生的事情是当操作2已经返回必要数量的记录后, 操作1即刻停止操作2的 处理. 不过要小心, 块操作不能被停止. 事实上, 块操作必须等全部处理完以后才能返回记录给父操作. 例如: 在下面 的查询中, 表EMP的所有记录都要被读取, 因为order by 子句: SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC) WHERE rownum <= 10 ---------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | ---------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | 10 | | 2 | VIEW | | 1 | 10 | |* 3 | SORT ORDER BY STOPKEY| | 1 | 10 | | 4 | TABLE ACCESS FULL | EMP | 1 | 14 | ---------------------------------------------------------- 2) 操作FILTER 的优化 它可以决定让子操作和所有相依赖的操作(它的孙辈和更小辈的操作) 例如: 在下面的查询中有一个谓词永远也不可能 为真, 实际应用中, 当应用程序动态生成部分SQL语句时会产生这样的谓词. SELECT * FROM emp WHERE job = 'CLERK' AND 1 = 2 -------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | -------------------------------------------------------------------- |* 1 | FILTER | | 1 | 0 | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 0 | 0 | |* 3 | INDEX RANGE SCAN | EMP_JOB_I | 0 | 0 | -------------------------------------------------------------------- 1 - filter(NULL IS NOT NULL) 3 - access("JOB"='CLERK') 根据规则, 这个执行计划应该从操作3开始执行, 事实上, 观察 starts 字段将会发现只有操作1被执行, 优化器完全避免 了操作2和3的运行, 因为数据根本没有任何机会通过操作1的筛选. 非相关联合型操作 把多个相互独立孩子的操作定义为非相关联合型操作. 下面这些属于这个类型: AND-EQUAL, BITMAP AND, BITMAP OR, BITMAP MINUS, CONCATENATION, CONNECT BY WITHOUT FILTERING, HASH JOIN, INTERSECTION, MERGE JOIN, MINUS, MULTI-TABLE INSERT, SQL MODEL, TEMP TABLE TRANSFORMATION, and UNION-ALL. 非相关联合型操作的特点: ~ 孩子在父亲前面执行 ~ 孩子按顺序执行, 从ID最小到ID最大, 在开始下一个孩子操作前, 当前孩子操作必须全部完成. ~ 每一个孩子操作至多执行一次, 并且与其他孩子相互独立. ~ 每个孩子向他的父亲提供输出 例如: SELECT ename FROM emp UNION ALL SELECT dname FROM dept UNION ALL SELECT '%' FROM dual ----------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | ----------------------------------------------------- | 1 | UNION-ALL | | 1 | 19 | | 2 | TABLE ACCESS FULL| EMP | 1 | 14 | | 3 | TABLE ACCESS FULL| DEPT | 1 | 4 | | 4 | FAST DUAL | | 1 | 1 | ----------------------------------------------------- 在这个执行计划中, 非相关联合型操作是 union-all, 其他的三个是独立操作, 执行顺序是: 1) 操作1有三个孩子, 并且其中, 操作2是需要最小的, 因此, 从操作2开始执行. 2) 操作2扫描全表emp, 并返回14条记录给它的父操作, 操作1. 3) 当操作2执行完后, 操作3开始执行. 4) 操作3扫描全表dept, 并返回4条记录给它的父操作, 操作1. 5) 当操作3完成后, 操作4开始执行. 6) 操作4扫描表dual并返回1条记录给它的父操作, 操作1. 7) 操作1基于从子操作的数据建立一个19条记录的记录集, 并返回给调用者. 相关联合型操作 把多个孩子其中一个孩子控制其他孩子执行的操作定义为相关联合型操作. 这样的操作, 有以下几种: NESTED LOOPS, UPDATE, FILTER, CONNECT BY WITH FILTERING, BITMAP KEY ITERATION. 相关联合型操作的特点: ~ 孩子在父亲前面执行 ~ 有最小ID的孩子控制其他孩子的执行 ~ 孩子从ID最小的开始执行, 到ID最大的结束. 但是和非相关联合型操作不一样的是, 它们并不严格 按照从小到大的顺序执行, 而是一种交错方式执行. ~ 只有第一个孩子是最多执行一次的, 所有其他的孩子则可能执行多次获根本不执行. ~ 不是每个孩子都向他的父亲提供数据, 有些孩子只是用来应用约束而已. 1) 嵌套循环(NESTED LOOPS)操作 嵌套循环操作用来连接两个记录集, 所以, 它总是不多不少刚好两个孩子, 有最小ID的孩子叫做外循环, 第二个孩子 叫做内循环, 这个操作的特征是外循环每返回一条记录内循环就要执行一次. 例如: SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno AND emp.comm IS NULL AND dept.dname != 'SALES' ------------------------------------------------------------------ | Id | Operation | Name | Starts | A-Rows | ------------------------------------------------------------------ | 1 | NESTED LOOPS | | 1 | 8 | |* 2 | TABLE ACCESS FULL | EMP | 1 | 10 | |* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 10 | 8 | |* 4 | INDEX UNIQUE SCAN | DEPT_PK | 10 | 10 | ------------------------------------------------------------------ 2 - filter("EMP"."COMM" IS NULL) 3 - filter("DEPT"."DNAME"<>'SALES') 4 – access("EMP"."DEPTNO"="DEPT"."DEPTNO") 在这个执行计划里, 相关联合操作NESTED LOOPS的所有孩子都是独立型操作, 按下面顺序执行: 1) 操作1有两个孩子, 其中, 操作2根据序号先执行, 因此, 执行从操作2开始(外循环). 2) 操作2扫描全表EMP, 应用过滤谓词"EMP.COMM IS NULL", 返回了10条记录数据给它的父操作, 操作1. 3) 对于操作2返回的每条记录, 嵌套循环操作的第二个孩子内循环操作都要执行一次, 通过比较操作2的字段A-Rows和 操作3及4的字段starts可以确认这一点. 4) 内循环由两个单独型操作组成, 基于这种类型操作的规则, 操作4在操作3之前执行. 5) 操作4通过访问型谓词"emp.deptno=dept.deptno"来扫描索引dept_pk, 在操作过程中, 它从索引抽取10条记录, 并 传递给操作3 6) 操作3通过操作4传递过来的10个rowid来访问表dept, 对应每一个rowid, 一条记录被读取, 然后通过谓词 "dept.dname<>sales"进行过滤, 此过滤导致两条记录被排除, 它传递剩余的8条记录给它的父操作, 操作1. 7) 操作1返回8条记录给调用者. 2) 过滤(Filter)操作 过滤操作的特征是它支持不定数目的孩子操作, 如果它只有一个孩子, 就可以认为是单独型操作, 如果它有两个或更多 的孩子, 它的功能就类似于嵌套循环操作. 第一个孩子驱动其他孩子执行. 例如: SELECT * FROM emp WHERE NOT EXISTS (SELECT 0 FROM dept WHERE dept.dname = 'SALES' AND dept.deptno = emp.deptno) AND NOT EXISTS (SELECT 0 FROM bonus WHERE bonus.ename = emp.ename); ------------------------------------------------------------------ | Id | Operation | Name | Starts | A-Rows | ------------------------------------------------------------------ |* 1 | FILTER | | 1 | 8 | | 2 | TABLE ACCESS FULL | EMP | 1 | 14 | |* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 1 | |* 4 | INDEX UNIQUE SCAN | DEPT_PK | 3 | 3 | |* 5 | TABLE ACCESS FULL | BONUS | 8 | 0 | ------------------------------------------------------------------ 1 - filter( NOT EXISTS (SELECT 0 FROM "DEPT" "DEPT" WHERE "DEPT"."DEPTNO"=:B1 AND "DEPT"."DNAME"='SALES') AND NOT EXISTS (SELECT 0 FROM "BONUS" "BONUS" WHERE "BONUS"."ENAME"=:B2)) 3 - filter("DEPT"."DNAME"='SALES') 4 - access("DEPT"."DEPTNO"=:B1) 5 – filter("BONUS"."ENAME"=:B1) 在这个执行计划中, 相关联合型操作FILTER有三个孩子都是独立型操作, 执行顺序: 1) 操作1有三个孩子(2,3,5), 操作2序号最小, 因此, 执行从操作2开始 2) 操作2全表扫描emp, 并返回14条记录给父操作, 操作1. 3) 对于操作2返回的每一条记录, 过滤操作(filter)的第2和第3个孩子都要执行一次, 事实上, 这里使用了一种缓存技术 将执行次数降到最低, 这可以通过比较操作2的字段A-Rows和操作3及操作5的字段starts得到确认(正常应该操作3和 操作5都执行14次) 4) 根据单独型操作的规则, 操作4在操作3之前执行, 通过谓词"dept.deptno=:b1"扫描索引dept_pk, 绑定变量(b1)用来 传输上一步查询得到的值. 通过执行3个这样的查询, 此操作从索引中抽取3个rowid, 并传递给父操作, 操作3. 5) 操作3使用它的子操作4传递过来的rowid来访问表dept, 然后使用谓词"dept.dname=sales"进行过滤, 既然这个操作 只是为了应用一个约束条件, 它不会对其父操作1返回任何数据, 无论怎样, 认识到这点很重要, 就是只有一条记录 是满足过滤谓词的, 因为使用了不存在(not exists)条件, 这条匹配的记录将被抛弃. 6) 操作5全表扫描bonus表, 并应用谓词"bonus.ename=:b1"进行过滤, 绑定变量(b1)用来输入上一步查询得到的值, 同样 这个操作值是为了应用一下约束条件, 它不会对其父操作1返回任何数据, 然而注意, 没有一条记录满足这个谓词条件, 既然使用了不存在(not exists)条件, 就没有任何一条记录被抛弃. 7) 在操作3和5应用完过滤谓词筛选后, 操作1返回8条记录给调用者. 3) 更新(update)操作 当SQL语句UPDATE执行时使用此操作, 它的特征是支持不定数目的孩子操作, 在大多数时候,它只有一个孩子, 因此, 它常 被认为是一种单独型操作, 只有当SET子句中使用子查询的时候, 它才有两个或更多的孩子. 如果它有一个以上的孩子,其 行为类似过滤操作. 也就是说, 第一个孩子驱动其他孩子执行. 例如: UPDATE emp e1 SET sal = (SELECT avg(sal) FROM emp e2 WHERE e2.deptno = e1.deptno), comm = (SELECT avg(comm) FROM emp e3) ------------------------------------------------------ | Id | Operation | Name | Starts | A-Rows | ------------------------------------------------------ | 1 | UPDATE | EMP | 1 | 0 | | 2 | TABLE ACCESS FULL | EMP | 1 | 14 | | 3 | SORT AGGREGATE | | 3 | 3 | |* 4 | TABLE ACCESS FULL| EMP | 3 | 14 | | 5 | SORT AGGREGATE | | 1 | 1 | | 6 | TABLE ACCESS FULL| EMP | 1 | 14 | ------------------------------------------------------ 4 - filter("E2"."DEPTNO"=:B1) 在这个执行计划中, 相关联合型操作update的三个孩子都是单独型操作, 执行顺序如下: 1) 操作1有三个孩子(2,3,5), 按照序号其中操作2排在最先, 因此, 执行从操作2开始. 2) 操作2全表扫描emp并返回14条记录给它的父操作, 操作1. 3) 字段 deptno 中每一个唯一值, 两个孩子(3,5)都要执行一次, 而且这两个操作都有孩子, 所以从(4,6)开始 4) 操作4全表扫描emp, 并应用谓词"E2.deptno=:b1", 在其三次执行过程中, 抽取了14条记录并返回给它的父操作, 操作3 5) 操作3计算操作4传递过来记录的平均工资值, 并传递给父操作1. 6) 操作6扫描表emp, 抽取14条记录, 传递给它的父操作5, 注意到这个子查询只执行一次, 因为它是一个和主查询不相关 的独立查询. 7) 操作5计算操作6传递过来的记录的平均值, 并传递结果给父操作1. 8) 操作2(个人认为应该是操作1)从其子操作(3,5)中接收返回值并传递给操作1, 操作1将其更新, 注意,即使更新操作修改 了14条记录, 此操作的字段 A-Rows仍返回 0. 4) 带过滤的连接(connect by with filtering)操作 这个操作用来处理层级查询, 它的特征是有两个孩子操作, 第一个操作用来得到层级的根, 第二个操作在每一个层级上执 行一次, 例如: SELECT level, rpad('-',level-1,'-')||ename AS ename, prior ename AS manager FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr --------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | --------------------------------------------------------------------- |* 1 | CONNECT BY WITH FILTERING | | 1 | 14 | |* 2 | TABLE ACCESS FULL | EMP | 1 | 1 | | 3 | NESTED LOOPS | | 4 | 13 | | 4 | CONNECT BY PUMP | | 4 | 14 | | 5 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 13 | |* 6 | INDEX RANGE SCAN | EMP_MGR_I | 14 | 13 | --------------------------------------------------------------------- 1 - access("MGR"=PRIOR "EMPNO") 2 - filter("MGR" IS NULL) 6 - access("MGR"=PRIOR "EMPNO") 在这个执行计划中, 相关联合型操作connect by with filtering的第一个孩子是个独立型操作, 而第二个孩子本身是一个 相关联合型操作, 在这种情况下, 要从上到下, 递归的应用基本规则, 执行顺序是: 1) 操作1有两个孩子(2,3), 按照序号其中2排在前, 因此, 执行从2开始. 2) 操作2全表扫描emp, 应用谓词"MGR IS NULL"进行过滤, 返回层级的根给它的父操作1. 3) 操作3是操作1的第二个孩子, 因此在每一个层级执行一次, 在这里是4次, 前面讨论的嵌套规则应用与操作3. 操作3的 第一个孩子操作4先执行, 它每返回一条记录, 内循环(由操作5和它的孩子操作6组成)就执行一次, 这里, 就如我们所 希望的那样, 操作4的A-Rows字段和操作5及6的starts字段是一样的. 4) 在第一次执行时, 操作4通过connect by pump操作得到层级的根, 在这里, 第一级只有一条记录, 根据字段mgr中的值, 操作6通过应用谓词"MGR=PRIOR EMPNO"扫描索引EMP_MGR_I,从中抽取rowid, 并返回给它的父操作5, 操作5通过rowid 访问表emp, 并将得到的记录返回给它的父操作3. 5) 操作4第二次执行. 6) 操作4第三次执行. 7) 操作4第四次执行 8) 操作3从它的子操作中得到的记录返回给它的父操作1. 9) 操作1使用谓词"MGR=PRIOR EMPNO", 并将结果的14条记录返回给调用者. 分而治之 针对很长的执行计划, 把执行计划分解成基本的块并认识执行顺序很重要, 为此, 可采取如下步骤: 首先, 你必须找出组成它的联合型操作(包括相关和不相关的), 也就是说, 需要识别出全部至少有两个子操作的操作. 然后, 把每个联合操作的每一个子操作, 定义成一个块. (个人感觉通过画父子关系图好一点, 当然三种操作类型要用不同图形区别) 特殊的例子 SELECT ename, (SELECT dname FROM dept WHERE dept.deptno = emp.deptno) FROM emp ----------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | ----------------------------------------------------------------- | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 3 | |* 2 | INDEX UNIQUE SCAN | DEPT_PK | 3 | 3 | | 3 | TABLE ACCESS FULL | EMP | 1 | 14 | ----------------------------------------------------------------- 2 - access("DEPT"."DEPTNO"=:B1) 这个例子有两个根, 从starts判断, 操作3先执行 SELECT deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp) -------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | -------------------------------------------------------- |* 1 | INDEX FULL SCAN | DEPT_PK | 1 | 1 | |* 2 | TABLE ACCESS FULL| EMP | 4 | 3 | -------------------------------------------------------- 1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "EMP" "EMP" WHERE LNNVL("DEPTNO"<>:B1))) 2 - filter(LNNVL("DEPTNO"<>:B1)) 乍看上去, 这是两个单独型操作, 再仔细看字段starts, 发现是从操作1开始执行.6.3 识别低效的执行计划 错误的评估, 比如你认为会执行多少次, 而实际执行了多少次 未识别约束, 查询优化器是否正确的识别的SQL语句中的约束条件