`

执行计划(Execution Plan)过程分析

AccessSQLCC++C# 
阅读更多

简单点说，执行计划实际上是由一系列数据处理过程构成的一个树型的函数链；那些可以访问到物理数据的函数被称为访问路径（Access Path, 如Index Range Scan, Full Table Scan等）；该树形函数链的最底层（叶子节点）从物理对象中获取到原始数据（Row Source）后，将数据由底向上传递，每一个节点的函数对其进行处理（如JOIN匹配、过滤等）得到一个新的Row Source，并继续向上层传递，直到根节点，此时得到的数据就是查询结果。

    我们下面通过对Row Source的产生，对执行计划的运作过程做个简单分析。

SQL代码
  1. HELLODBA.COM>explain plan for  
  2.   2  select /*+index(t1 t_test1_pk) index(t2 t_test2_pk)*/   
  3.   3   t1.data_object_id,   
  4.   4   t2.TABLE_NAME,   
  5.   5   t2.STATUS,   
  6.   6   t2.NUM_ROWS,   
  7.   7   t2.COMPRESSION   
  8.   8    from demo.t_test1 t1, demo.t_test2 t2   
  9.   9   where t1.owner = t2.owner   
  10.  10     and t1.object_name = t2.table_name   
  11.  11     and t1.object_id <= 1000;   
  12.   
  13. Explained.   
  14.   
  15. HELLODBA.COM>select * from table(dbms_xplan.display());   
  16.   
  17. PLAN_TABLE_OUTPUT   
  18. --------------------------------------------------------------------------------------------------   
  19. --------------------------------------------------------------------------------------------------   
  20.   
  21. Plan hash value: 2465336739   
  22.   
  23. -------------------------------------------------------------------------------------------   
  24. | Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |   
  25. -------------------------------------------------------------------------------------------   
  26. |   0 | SELECT STATEMENT             |            |   905 | 71495 |   554   (0)| 00:09:15 |   
  27. |   1 |  NESTED LOOPS                |            |   905 | 71495 |   554   (0)| 00:09:15 |   
  28. |   2 |   TABLE ACCESS BY INDEX ROWID| T_TEST1    |   905 | 33485 |    11   (0)| 00:00:12 |   
  29. |*  3 |    INDEX RANGE SCAN          | T_TEST1_PK |   905 |       |     2   (0)| 00:00:02 |   
  30. |   4 |   TABLE ACCESS BY INDEX ROWID| T_TEST2    |     1 |    42 |     1   (0)| 00:00:02 |   
  31. |*  5 |    INDEX UNIQUE SCAN         | T_TEST2_PK |     1 |       |     1   (0)| 00:00:02 |   
  32. -------------------------------------------------------------------------------------------   
  33.   
  34. Predicate Information (identified by operation id):   
  35. ---------------------------------------------------   
  36.   
  37.    3 - access("T1"."OBJECT_ID"<=1000)   
  38.    5 - access("T1"."OBJECT_NAME"="T2"."TABLE_NAME" AND "T1"."OWNER"="T2"."OWNER")   
  39.   
  40. 18 rows selected.  

    其中T_TEST1_PK(OBJECT_ID)为T_TEST1的主键，T_TEST2_PK(TABLE_NAME, OWNER)为T_TEST2的主键。我们同时对其做10046和Row Source的跟踪：

SQL代码
  1. HELLODBA.COM>alter session set events '10046 trace name CONTEXT forever, level 1';   
  2.   
  3. Session altered.   
  4.   
  5. HELLODBA.COM>alter session set "_rowsrc_trace_level"=4;   
  6.   
  7. Session altered.   
  8.   
  9. HELLODBA.COM>select /*+index(t1 t_test1_pk) index(t2 t_test2_pk)*/   
  10.   2   t1.data_object_id,   
  11.   3   t2.TABLE_NAME,   
  12.   4   t2.STATUS,   
  13.   5   t2.NUM_ROWS,   
  14.   6   t2.COMPRESSION   
  15.   7    from demo.t_test1 t1, demo.t_test2 t2   
  16.   8   where t1.owner = t2.owner   
  17.   9     and t1.object_name = t2.table_name   
  18.  10     and t1.object_id <= 1000;   
  19. ...  

    下面是跟踪文件已经对Row Source产生过程的分析：

SQL代码
  1. PARSING IN CURSOR #3 len=243 dep=0 uid=0 oct=3 lid=0 tim=990048514 hv=3222390654 ad='1a5507f4'  
  2. select /*+index(t1 t_test1_pk) index(t2 t_test2_pk)*/   
  3.  t1.data_object_id,   
  4.  t2.TABLE_NAME,   
  5.  t2.STATUS,   
  6.  t2.NUM_ROWS,   
  7.  t2.COMPRESSION   
  8.   from demo.t_test1 t1, demo.t_test2 t2   
  9.  where t1.owner = t2.owner   
  10.    and t1.object_name = t2.table_name   
  11.    and t1.object_id <= 1000   
  12. END OF STMT   
  13. PARSE #3:c=390625,e=372892,p=0,cr=276,cu=32,mis=1,r=0,dep=0,og=4,tim=990048507   
  14. EXEC #3:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=990049106   
  15. VR: rws[3], opn(1)[0]             #INDEX RANGE SCAN (T_TEST1_PK)，获取符合Access Precidate("T1"."OBJECT_ID"<=1000)的Row Source:上层节点需要的Projection/Predication字段以及 Rowid [ROWID]   
  16. VR: rws[2], opn(3)[0,1,2]         #TABLE ACCESS BY INDEX ROWID (T_TEST1)，获取Row Source：上层节点需要的Projection/Predication字段[DATA_OBJECT_ID, OWNER, OBJECT_NAME]   
  17.                                   #对T_TEST2_PK做INDEX UNIQUE SCAN，没有相应记录，不会产生Row Source   
  18. VR: rws[3], opn(1)[0]             #INDEX RANGE SCAN (T_TEST1_PK)，获取符合Access Precidate("T1"."OBJECT_ID"<=1000)的Row Source:上层节点需要的Projection/Predication字段以及 Rowid [ROWID]   
  19. VR: rws[2], opn(3)[0,1,2]         #TABLE ACCESS BY INDEX ROWID (T_TEST1)，获取Row Source：上层节点需要的Projection/Predication字段[DATA_OBJECT_ID, OWNER, OBJECT_NAME]   
  20.                                   #对T_TEST2_PK做INDEX UNIQUE SCAN，没有相应记录，不会产生Row Source   
  21. VR: rws[3], opn(1)[0]             #INDEX RANGE SCAN (T_TEST1_PK)，获取符合Access Precidate("T1"."OBJECT_ID"<=1000)的Row Source:上层节点需要的Projection/Predication字段以及 Rowid [ROWID]   
  22. VR: rws[2], opn(3)[0,1,2]         #TABLE ACCESS BY INDEX ROWID (T_TEST1)，获取Row Source：上层节点需要的Projection/Predication字段[DATA_OBJECT_ID, OWNER, OBJECT_NAME]   
  23. VR: rws[5], opn(2)[0,1]           #INDEX UNIQUE SCAN(T_TEST2_PK)，获取符合Access Precidate("T1"."OBJECT_NAME"="T2"."TABLE_NAME" AND "T1"."OWNER"="T2"."OWNER")的Row Source:上层节点需要的Projection/Predication字段以及 Rowid [DATA_OBJECT_ID, ROWID]   
  24. VR: rws[4], opn(4)[0,1,2,3]       #TABLE ACCESS BY INDEX ROWID (T_TEST1)，获取Row Source：上层节点需要的Projection/Predication字段[TABLE_NAME, STATUS, NUM_ROWS, COMPRESSION]   
  25. VR: rws[1], opn(5)[0,1,2,3,4]     #从节点2和节点4的Row Source中获取到Projection字段，是最终符合查询条件的Row Source[DATA_OBJECT_ID, OBJECT_NAME, STATUS, NUM_ROWS, COMPRESSION]   
  26. FETCH #3:c=0,e=452,p=0,cr=9,cu=0,mis=0,r=1,dep=0,og=4,tim=990049641   
  27. ...  

    对比Row Source Trace和以下查询结果，可以看到Row Source的产生和实际数据相符。以上面的分析为例，T_TEST1中符合Access Precidate的第1、2条件记录在T_TEST2_PK没有对应记录，第三条记录则JOIN成功。

SQL代码
  1. HELLODBA.COM>select /*+index(t1 t_test1_pk) index(t2 t_test2_pk)*/t1.OWNER, t1.OBJECT_NAME, t2.OWNER, t2.TABLE_NAME   
  2.   2   from demo.t_test1 t1, demo.t_test2 t2   
  3.   3  where t1.owner = t2.owner(+)   
  4.   4    and t1.object_name = t2.table_name(+)   
  5.   5    and t1.object_id <= 1000;   
  6.   
  7. OWNER                          OBJECT_NAME                    OWNER                          TABLE_NAME   
  8. ------------------------------ ------------------------------ ------------------------------ -----------------------   
  9. SYS                            C_OBJ#   
  10. SYS                            I_OBJ#   
  11. SYS                            TAB$                           SYS                            TAB$   
  12. SYS                            CLU$                           SYS                            CLU$   
  13. SYS                            C_TS#   
  14. SYS                            I_TS#   
  15. SYS                            C_FILE#_BLOCK#   
  16. SYS                            I_FILE#_BLOCK#   
  17. SYS                            C_USER#   
  18. SYS                            I_USER#   
  19. SYS                            FET$                           SYS                            FET$   
  20. SYS                            UET$                           SYS                            UET$   
  21. SYS                            SEG$                           SYS                            SEG$   
  22. SYS                            UNDO$                          SYS                            UNDO$   
  23. SYS                            TS$                            SYS                            TS$   
  24. ...  

    我们再看如果上面的查询计划中加入了Filter Predication，Row Source的产生过程是如何的。

SQL代码
  1. HELLODBA.COM>explain plan for  
  2.   2  select /*+index(t1 t_test1_pk) index(t2 t_test2_pk)*/   
  3.   3   t1.data_object_id,   
  4.   4   t2.TABLE_NAME,   
  5.   5   t2.STATUS,   
  6.   6   t2.NUM_ROWS,   
  7.   7   t2.COMPRESSION   
  8.   8    from demo.t_test1 t1, demo.t_test2 t2   
  9.   9   where t1.owner = t2.owner   
  10.  10     and t1.object_name = t2.table_name   
  11.  11     and t1.object_id <= 1000   
  12.  12     and t1.temporary='Y';   
  13.   
  14. Explained.   
  15.   
  16. HELLODBA.COM>select * from table(dbms_xplan.display());   
  17.   
  18. PLAN_TABLE_OUTPUT   
  19. ---------------------------------------------------------------------------------------------------   
  20. ---------------------------------------------------------------------------------------------------   
  21.   
  22. Plan hash value: 2465336739   
  23.   
  24. -------------------------------------------------------------------------------------------   
  25. | Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |   
  26. -------------------------------------------------------------------------------------------   
  27. |   0 | SELECT STATEMENT             |            |   453 | 36693 |   283   (0)| 00:04:44 |   
  28. |   1 |  NESTED LOOPS                |            |   453 | 36693 |   283   (0)| 00:04:44 |   
  29. |*  2 |   TABLE ACCESS BY INDEX ROWID| T_TEST1    |   453 | 17667 |    11   (0)| 00:00:12 |   
  30. |*  3 |    INDEX RANGE SCAN          | T_TEST1_PK |   905 |       |     2   (0)| 00:00:02 |   
  31. |   4 |   TABLE ACCESS BY INDEX ROWID| T_TEST2    |     1 |    42 |     1   (0)| 00:00:02 |   
  32. |*  5 |    INDEX UNIQUE SCAN         | T_TEST2_PK |     1 |       |     1   (0)| 00:00:02 |   
  33. -------------------------------------------------------------------------------------------   
  34.   
  35. Predicate Information (identified by operation id):   
  36. ---------------------------------------------------   
  37.   
  38.    2 - filter("T1"."TEMPORARY"='Y')   
  39.    3 - access("T1"."OBJECT_ID"<=1000)   
  40.    5 - access("T1"."OBJECT_NAME"="T2"."TABLE_NAME" AND "T1"."OWNER"="T2"."OWNER")   
  41.   
  42. 19 rows selected.  

    下面是跟踪内容及分析：

SQL代码
  1. PARSING IN CURSOR #2 len=288 dep=0 uid=0 oct=3 lid=0 tim=1850866257 hv=1801581673 ad='1aa7beb4'  
  2. select /*+index(t1 t_test1_pk) index(t2 t_test2_pk)*/   
  3.  t1.data_object_id,   
  4.  t2.TABLE_NAME,   
  5.  t2.STATUS,   
  6.  t2.NUM_ROWS,   
  7.  t2.COMPRESSION   
  8.   from demo.t_test1 t1, demo.t_test2 t2   
  9.  where t1.owner = t2.owner   
  10.    and t1.object_name = t2.table_name   
  11.    and t1.object_id <= 1000   
  12.    and t1.temporary='Y'  
  13. END OF STMT   
  14. PARSE #2:c=0,e=2770,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1850866251   
  15. EXEC #2:c=0,e=65,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1850906163   
  16. VR: rws[3], opn(1)[0]           #INDEX RANGE SCAN (T_TEST1_PK)，获取符合Access Precidate("T1"."OBJECT_ID"<=1000)的Row Source:上层节点需要的Projection/Predication字段以及 Rowid [ROWID]   
  17.                                 #Row Source被Filter Predicate("T1"."TEMPORARY"='Y')过滤   
  18. VR: rws[3], opn(1)[0]           #INDEX RANGE SCAN (T_TEST1_PK)，获取符合Access Precidate("T1"."OBJECT_ID"<=1000)的Row Source:上层节点需要的Projection/Predication字段以及 Rowid [ROWID]   
  19.                                 #Row Source被Filter Predicate("T1"."TEMPORARY"='Y')过滤   
  20. ...   
  21. VR: rws[3], opn(1)[0]           #INDEX RANGE SCAN (T_TEST1_PK)，获取符合Access Precidate("T1"."OBJECT_ID"<=1000)的Row Source:上层节点需要的Projection/Predication字段以及 Rowid [ROWID]   
  22. VR: rws[2], opn(3)[0,1,2]       #TABLE ACCESS BY INDEX ROWID (T_TEST1)，获取Row Source：上层节点需要的Projection/Predication字段[DATA_OBJECT_ID, OWNER, OBJECT_NAME]   
  23. VR: rws[5], opn(2)[0,1]        #INDEX UNIQUE SCAN(T_TEST2_PK)，获取符合Access Precidate("T1"."OBJECT_NAME"="T2"."TABLE_NAME" AND "T1"."OWNER"="T2"."OWNER")的Row Source:上层节点需要的Projection/Predication字段以及 Rowid [DATA_OBJECT_ID, ROWID]   
  24. VR: rws[4], opn(4)[0,1,2,3]    #TABLE ACCESS BY INDEX ROWID (T_TEST1)，获取Row Source：上层节点需要的Projection/Predication字段[TABLE_NAME, STATUS, NUM_ROWS, COMPRESSION]   
  25. VR: rws[1], opn(5)[0,1,2,3,4]  #从节点2和节点4的Row Source中获取到Projection字段，是最终符合查询条件的Row Source[DATA_OBJECT_ID, OBJECT_NAME, STATUS, NUM_ROWS, COMPRESSION]   
  26. FETCH #2:c=1281250,e=1326739,p=0,cr=17,cu=0,mis=0,r=1,dep=0,og=4,tim=1852235821   
  27. ...  

    可以看到Access Predicate和Filter Predicate的重要区别：Access Predicate在访问数据时做判断，不满足条件的数据不会形成Row Source；而Filter Predicate对已产生的Row Source再做判断，不满足条件的则被丢弃（Throw-Away）。而降低执行计划中的Throw-Away是我们做SQL调优的一项重要参考指标，因此，一些将Filter Predicate转为Access Predicate的方法也是我们的重要调优手段。

--- Fuyuncat ---

分享到：
| TKPROF使用学习
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    SQL执行计划简单分析

    SQL 执行计划简单分析 在 SQL 优化中，分析执行计划是一个非常重要的步骤。执行计划可以帮助我们了解 SQL 语句的执行过程、资源消耗和性能瓶颈。Oracle 数据库提供了多种方法来查看 SQL 语句的执行计划，例如使用 ...

    Oracle_cluster_executionPlan

    "ch02execution_plan.pdf"和"ch01execution_plan.pdf"可能包含了关于如何分析和优化SQL查询执行计划的内容。在Oracle数据库中，执行计划是通过解析SQL语句、估计操作成本、选择最佳路径来生成的。这个过程由Oracle的...

    oracle怎么查看执行计划

    假设我们需要分析以下SQL语句的执行计划： ```sql SELECT d.dname, e.ename, e.sal FROM dept d, emp e WHERE d.deptno = e.deptno; ``` 我们可以按照以下步骤操作： 1. 开启AUTOTRACE： ```sql SQL&gt; set ...

    oracle执行计划建立与阅读

    `SQLTRACE`会记录详细的执行过程，包括执行计划和其他跟踪信息。启用`SQLTRACE`后执行SQL语句，再用`TKPROF`工具对产生的跟踪文件进行格式化，以查看执行计划。 二、执行计划解读 执行计划通常由以下部分组成： ...

    读懂oracle的执行计划

    ExecutionPlan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=392) 1 0 HASH JOIN (Cost=5 Card=14 Bytes=392) 2 1 TABLE ACCESS (FULL)...

    MongoDB的执行计划分析与巡检分享.pdf

    在MongoDB中，查询优化器负责选择最佳的方法来执行查询操作，这一过程称为“执行计划”(Execution Plan)。执行计划对于查询性能至关重要，它直接影响着查询速度和资源消耗。 #### 二、执行计划的重要性 - **性能...

    Oracle数据库中SQL语句的优化与分析.pdf

    这些步骤的集合被称为执行计划（Execution Plan）。执行计划的设计目的是为了优化SQL语句的执行效率，以保证在最短时间内完成SQL语句的执行。执行计划的优化是SQL语句优化中最为复杂和关键的部分。只有深入理解...

    行业-89 再次重温写出各种SQL语句的时候，会用什么执行计划？（2）.rar

    DBMS还提供了查询分析工具，如Oracle的Explain Plan或SQL Server的Execution Plan，它们能展示每一步的预计成本、实际资源消耗和操作顺序，帮助开发者理解并优化执行计划。 总结来说，"行业-89 再次重温写出各种SQL...

    eBOOK_SQLServerExecutionPlans_2Ed_G_Fritchey.pdf

    - **Execution Plan Reuse**（执行计划重用）：探讨了SQL Server如何重用已有的执行计划以提高性能。 - **Clearing Plans from the Plan Cache**（清除计划缓存中的计划）：讨论了如何管理计划缓存，包括何时以及...

    显示 sql 执行效率.

    通过设置 Auto Trace，可以方便地获取 SQL 语句的执行计划（Execution Plan）和其他统计信息。 #### 设置 Auto Trace 设置 Auto Trace 可以通过以下命令实现： ```sql SET AUTOTRACE ON ``` 此命令开启后，执行...

    MySql执行一条查询语句的内部执行过程？

    4. **执行计划（Execution Plan）**：优化器选定最佳执行计划后，将其转化为一系列操作，如表扫描、索引查找、排序、分组、连接等。每个操作都对应一个存储引擎API调用，以执行实际的数据操作。 5. **存储引擎**：...

    Spark Adaptive Execution

    在Spark SQL中，执行计划（Execution Plan）在规划阶段（planning phase）确定后是固定的。这是基于对数据集大小、数据分布和其他相关因素的预估而生成的。然而，预估不可能完全准确，尤其是在复杂查询中，中间结果...

    Oracle并行执行

    执行计划显示了并行执行的不同阶段，包括并行协调器、并行发送、并行接收等操作，以及如何将表扫描操作并行化。 #### 九、结语 通过上述介绍，我们可以看出Oracle并行执行技术在提高数据库查询性能方面具有重要的...

    将近1300行的sql执行计划

    2. **Execution Plan**: 这部分展示了查询的执行步骤，由多个操作（Operations）组成。每个操作都有相应的行数（Rows）、字节数（Bytes）、成本（Cost，基于CBO Cost-Based Optimizer的估算）、CPU使用百分比（% CPU...

    oracle使用autotrace 功能

    Autotrace的主要功能包括显示执行计划（Execution Plan）、显示统计信息（Statistics）和显示时间分析（Timing Analysis）。执行计划展示了查询如何在数据库中执行，包括哪些操作（如全表扫描、索引查找等）以及它们...

    Spark-Sql源码解析

    Logical Plan 是一个抽象的计划，描述了查询的逻辑执行过程。SparkPlan 是一个物理的计划，描述了查询的物理执行过程。 Spark-Sql 源码解析的主要优点包括： * 高性能：Spark-Sql 源码解析可以快速地将 SQL 语句...

    Re-post: DBMS_XPLAN : Display Oracle Execution Plans

    1. `xplan.package.sql` - 这可能是一个创建或调整DBMS_XPLAN包的脚本，包含定义函数和过程的源代码，用户可以使用这些函数和过程来获取执行计划信息。 2. `xplan.display_awr.sql` - AWR（Automatic Workload ...

    Oracle培训讲义(性能分析与调整).docx

    通过分析执行计划，DBA和开发人员可以更好地理解查询是如何被优化的，并且可以识别出性能瓶颈。 **1.1 什么是SQL执行计划** SQL执行计划是Oracle优化器根据查询条件自动生成的一组指令，用于指导数据库如何有效地...

    ORACLE数据库运作原理

    接下来，Oracle会基于查询的内容生成一个或多个可能的执行计划（Execution Plan），并从中选择最优的一个来执行查询操作。 执行计划是指数据库执行SQL语句的具体步骤，它包含了访问表的方式、数据排序的方法以及...

Magicbox
Global site tag (gtag.js) - Google Analytics