执行计划(Execution Plan)过程分析
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
简单点说，执行计划实际上是由一系列数据处理过程构成的一个树型的函数链；那些可以访问到物理数据的函数被称为访问路径（Access Path, 如Index Range Scan, Full Table Scan等）；该树形函数链的最底层（叶子节点）从物理对象中获取到原始数据（Row Source）后，将数据由底向上传递，每一个节点的函数对其进行处理（如JOIN匹配、过滤等）得到一个新的Row Source，并继续向上层传递，直到根节点，此时得到的数据就是查询结果。
我们下面通过对Row Source的产生，对执行计划的运作过程做个简单分析。
SQL代码
- HELLODBA.COM>explain plan for
- 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;
- Explained.
- HELLODBA.COM>select * from table(dbms_xplan.display());
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------------------------
- --------------------------------------------------------------------------------------------------
- Plan hash value: 2465336739
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 905 | 71495 | 554 (0)| 00:09:15 |
- | 1 | NESTED LOOPS | | 905 | 71495 | 554 (0)| 00:09:15 |
- | 2 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 905 | 33485 | 11 (0)| 00:00:12 |
- |* 3 | INDEX RANGE SCAN | T_TEST1_PK | 905 | | 2 (0)| 00:00:02 |
- | 4 | TABLE ACCESS BY INDEX ROWID| T_TEST2 | 1 | 42 | 1 (0)| 00:00:02 |
- |* 5 | INDEX UNIQUE SCAN | T_TEST2_PK | 1 | | 1 (0)| 00:00:02 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("T1"."OBJECT_ID"<=1000)
- 5 - access("T1"."OBJECT_NAME"="T2"."TABLE_NAME" AND "T1"."OWNER"="T2"."OWNER")
- 18 rows selected.
其中T_TEST1_PK(OBJECT_ID)为T_TEST1的主键，T_TEST2_PK(TABLE_NAME, OWNER)为T_TEST2的主键。我们同时对其做10046和Row Source的跟踪：
SQL代码
- HELLODBA.COM>alter session set events '10046 trace name CONTEXT forever, level 1';
- Session altered.
- HELLODBA.COM>alter session set "_rowsrc_trace_level"=4;
- Session altered.
- HELLODBA.COM>select /*+index(t1 t_test1_pk) index(t2 t_test2_pk)*/
- 2 t1.data_object_id,
- 3 t2.TABLE_NAME,
- 4 t2.STATUS,
- 5 t2.NUM_ROWS,
- 6 t2.COMPRESSION
- 7 from demo.t_test1 t1, demo.t_test2 t2
- 8 where t1.owner = t2.owner
- 9 and t1.object_name = t2.table_name
- 10 and t1.object_id <= 1000;
- ...
下面是跟踪文件已经对Row Source产生过程的分析：
SQL代码
- PARSING IN CURSOR #3 len=243 dep=0 uid=0 oct=3 lid=0 tim=990048514 hv=3222390654 ad='1a5507f4'
- select /*+index(t1 t_test1_pk) index(t2 t_test2_pk)*/
- t1.data_object_id,
- t2.TABLE_NAME,
- t2.STATUS,
- t2.NUM_ROWS,
- t2.COMPRESSION
- from demo.t_test1 t1, demo.t_test2 t2
- where t1.owner = t2.owner
- and t1.object_name = t2.table_name
- and t1.object_id <= 1000
- END OF STMT
- PARSE #3:c=390625,e=372892,p=0,cr=276,cu=32,mis=1,r=0,dep=0,og=4,tim=990048507
- EXEC #3:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=990049106
- VR: rws[3], opn(1)[0] #INDEX RANGE SCAN (T_TEST1_PK)，获取符合Access Precidate("T1"."OBJECT_ID"<=1000)的Row Source:上层节点需要的Projection/Predication字段以及 Rowid [ROWID]
- 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]
- #对T_TEST2_PK做INDEX UNIQUE SCAN，没有相应记录，不会产生Row Source
- VR: rws[3], opn(1)[0] #INDEX RANGE SCAN (T_TEST1_PK)，获取符合Access Precidate("T1"."OBJECT_ID"<=1000)的Row Source:上层节点需要的Projection/Predication字段以及 Rowid [ROWID]
- 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]
- #对T_TEST2_PK做INDEX UNIQUE SCAN，没有相应记录，不会产生Row Source
- VR: rws[3], opn(1)[0] #INDEX RANGE SCAN (T_TEST1_PK)，获取符合Access Precidate("T1"."OBJECT_ID"<=1000)的Row Source:上层节点需要的Projection/Predication字段以及 Rowid [ROWID]
- 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]
- 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]
- 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]
- 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]
- FETCH #3:c=0,e=452,p=0,cr=9,cu=0,mis=0,r=1,dep=0,og=4,tim=990049641
- ...
对比Row Source Trace和以下查询结果，可以看到Row Source的产生和实际数据相符。以上面的分析为例，T_TEST1中符合Access Precidate的第1、2条件记录在T_TEST2_PK没有对应记录，第三条记录则JOIN成功。
SQL代码
- HELLODBA.COM>select /*+index(t1 t_test1_pk) index(t2 t_test2_pk)*/t1.OWNER, t1.OBJECT_NAME, t2.OWNER, t2.TABLE_NAME
- 2 from demo.t_test1 t1, demo.t_test2 t2
- 3 where t1.owner = t2.owner(+)
- 4 and t1.object_name = t2.table_name(+)
- 5 and t1.object_id <= 1000;
- OWNER OBJECT_NAME OWNER TABLE_NAME
- ------------------------------ ------------------------------ ------------------------------ -----------------------
- SYS C_OBJ#
- SYS I_OBJ#
- SYS TAB$ SYS TAB$
- SYS CLU$ SYS CLU$
- SYS C_TS#
- SYS I_TS#
- SYS C_FILE#_BLOCK#
- SYS I_FILE#_BLOCK#
- SYS C_USER#
- SYS I_USER#
- SYS FET$ SYS FET$
- SYS UET$ SYS UET$
- SYS SEG$ SYS SEG$
- SYS UNDO$ SYS UNDO$
- SYS TS$ SYS TS$
- ...
我们再看如果上面的查询计划中加入了Filter Predication，Row Source的产生过程是如何的。
SQL代码
- HELLODBA.COM>explain plan for
- 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';
- Explained.
- HELLODBA.COM>select * from table(dbms_xplan.display());
- PLAN_TABLE_OUTPUT
- ---------------------------------------------------------------------------------------------------
- ---------------------------------------------------------------------------------------------------
- Plan hash value: 2465336739
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 453 | 36693 | 283 (0)| 00:04:44 |
- | 1 | NESTED LOOPS | | 453 | 36693 | 283 (0)| 00:04:44 |
- |* 2 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 453 | 17667 | 11 (0)| 00:00:12 |
- |* 3 | INDEX RANGE SCAN | T_TEST1_PK | 905 | | 2 (0)| 00:00:02 |
- | 4 | TABLE ACCESS BY INDEX ROWID| T_TEST2 | 1 | 42 | 1 (0)| 00:00:02 |
- |* 5 | INDEX UNIQUE SCAN | T_TEST2_PK | 1 | | 1 (0)| 00:00:02 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("T1"."TEMPORARY"='Y')
- 3 - access("T1"."OBJECT_ID"<=1000)
- 5 - access("T1"."OBJECT_NAME"="T2"."TABLE_NAME" AND "T1"."OWNER"="T2"."OWNER")
- 19 rows selected.
下面是跟踪内容及分析：
SQL代码
- PARSING IN CURSOR #2 len=288 dep=0 uid=0 oct=3 lid=0 tim=1850866257 hv=1801581673 ad='1aa7beb4'
- select /*+index(t1 t_test1_pk) index(t2 t_test2_pk)*/
- t1.data_object_id,
- t2.TABLE_NAME,
- t2.STATUS,
- t2.NUM_ROWS,
- t2.COMPRESSION
- from demo.t_test1 t1, demo.t_test2 t2
- where t1.owner = t2.owner
- and t1.object_name = t2.table_name
- and t1.object_id <= 1000
- and t1.temporary='Y'
- END OF STMT
- PARSE #2:c=0,e=2770,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1850866251
- EXEC #2:c=0,e=65,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1850906163
- VR: rws[3], opn(1)[0] #INDEX RANGE SCAN (T_TEST1_PK)，获取符合Access Precidate("T1"."OBJECT_ID"<=1000)的Row Source:上层节点需要的Projection/Predication字段以及 Rowid [ROWID]
- #Row Source被Filter Predicate("T1"."TEMPORARY"='Y')过滤
- VR: rws[3], opn(1)[0] #INDEX RANGE SCAN (T_TEST1_PK)，获取符合Access Precidate("T1"."OBJECT_ID"<=1000)的Row Source:上层节点需要的Projection/Predication字段以及 Rowid [ROWID]
- #Row Source被Filter Predicate("T1"."TEMPORARY"='Y')过滤
- ...
- VR: rws[3], opn(1)[0] #INDEX RANGE SCAN (T_TEST1_PK)，获取符合Access Precidate("T1"."OBJECT_ID"<=1000)的Row Source:上层节点需要的Projection/Predication字段以及 Rowid [ROWID]
- 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]
- 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]
- 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]
- 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]
- FETCH #2:c=1281250,e=1326739,p=0,cr=17,cu=0,mis=0,r=1,dep=0,og=4,tim=1852235821
- ...
可以看到Access Predicate和Filter Predicate的重要区别：Access Predicate在访问数据时做判断，不满足条件的数据不会形成Row Source；而Filter Predicate对已产生的Row Source再做判断，不满足条件的则被丢弃（Throw-Away）。而降低执行计划中的Throw-Away是我们做SQL调优的一项重要参考指标，因此，一些将Filter Predicate转为Access Predicate的方法也是我们的重要调优手段。
--- Fuyuncat ---