Script:SQL Execution Order By Maclean Liu

Maclean Liu(刘相兵

Script: PRINT SQL Execution Order By Maclean Liu

分享一个可以打印出正确 执行计划 执行顺序的脚本

SQL Execution Order By Maclean Liu.txt (2.55 KB, 下载次数: 347, 售价: 1 金钱)

2013-10-13 20:18 上传

点击文件名下载附件
售价: 1 金钱  [记录]

使用方法如下：


将该文件 名修改为execution_order.sql

输入2个变量  SQL_ID 和child_number

SQL> @execution_order
Enter value for macsqlid: d6jhhrsc63b22
old   1: select 'Input SQL_ID        : ',lower('&&macsqlid') macsqlid from dual
new   1: select 'Input SQL_ID        : ',lower('d6jhhrsc63b22') macsqlid from dual

Input SQL_ID        :  d6jhhrsc63b22

Enter value for child_number: 0
old   1: select 'Input Child_number  : ',lower('&&child_number') child_number from dual
new   1: select 'Input Child_number  : ',lower('0') child_number from dual

Input Child_number  :  0

old   1: create table READ_SQL_PLAN as select * FROM V$SQL_PLAN where sql_id='&&macsqlid' and child_number=&&child_number
new   1: create table READ_SQL_PLAN as select * FROM V$SQL_PLAN where sql_id='d6jhhrsc63b22' and child_number=0



执行结果为打印出执行计划， Execution Order为实际执行时的顺序

例如 这里第一步指定的是 TABLE ACCESS FULL              TIMES


OO                             OBJECT_NAME                    Execution Order
------------------------------ ------------------------------ ---------------
SELECT STATEMENT                                                           10
HASH GROUP BY                                                               9
HASH JOIN                                                                   8
TABLE ACCESS FULL              TIMES                                        1
HASH JOIN                                                                   7
MERGE JOIN CARTESIAN                                                        5
TABLE ACCESS FULL              CHANNELS                                     2
BUFFER SORT                                                                 4
TABLE ACCESS FULL              CUSTOMERS                                    3
TABLE ACCESS FULL              SALES                                        6

tcczhj

谢谢老大分享

lidahe

学习一下

ghy1215

感谢分享，学习一下

wonder

mark一下

sky13and23

学习一下

踏水寻龙

学习了，感谢分享

lnwxzyp

这个要赞，不知道复杂sql的执行计划能不能呢个列出来。买来看看先。

dla001

Hi maclean
select s.order_ukid ,(select count(*) from ecmdta.se_order_detail d where d.order_ukid=s.order_ukid )from ecmdta.se_order s where s.order_ukid=1111;

1. OO                             OBJECT_NAME                                                                                Execution Order
   
2. ------------------------------ ------------------------------------------------------------------------------------------ ---------------
   
3. SELECT STATEMENT                                                                                                                        4
   
4. SORT AGGREGATE                                                                                                                          2
   
5. INDEX RANGE SCAN               SE_ORDER_DETAIL_1                                                                                        1
   
6. INDEX UNIQUE SCAN              SE_ORDER_0                                                                                               3

复制代码

使用Adrian Billington的xplan扩展包也显示与你的一样的结果。

1. SQL_ID  3vkh6vctzafhu, child number 0
   
2. -------------------------------------
   
3. select s.order_ukid ,(select count(*) from ecmdta.se_order_detail d
   
4. where d.order_ukid=s.order_ukid )from ecmdta.se_order s where
   
5. s.order_ukid=1111
   
6. 
   
7. Plan hash value: 3057353959
   
8. 
   
9. -----------------------------------------------------------------------------------------------
   
10. | Id  | Order | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
    
11. -----------------------------------------------------------------------------------------------
    
12. |   0 |     4 | SELECT STATEMENT  |                   |       |       |     2 (100)|          |
    
13. |   1 |     2 |  SORT AGGREGATE   |                   |     1 |    10 |            |          |
    
14. |*  2 |     1 |   INDEX RANGE SCAN| SE_ORDER_DETAIL_1 |     4 |    40 |     3   (0)| 00:00:01 |
    
15. |*  3 |     3 |  INDEX UNIQUE SCAN| SE_ORDER_0        |     1 |    10 |     2   (0)| 00:00:01 |
    
16. -----------------------------------------------------------------------------------------------
    
17. 
    
18. Predicate Information (identified by operation id):
    
19. -----------------------------------------------------------
    
20. 
    
21.    2 - access("D"."ORDER_UKID"=:B1)
    
22.    3 - access("S"."ORDER_UKID"=1111)

复制代码

这是有问题的吧。应该是先执行3，再执行2。如果是先执行2，access("D"."ORDER_UKID"=:B1)，那么它怎么可能知道 :B1是什么，这个应该是下面传过来的才对。

Maclean Liu(刘相兵

dla001 发表于 2013-10-14 17:30
Hi maclean
select s.order_ukid ,(select count(*) from ecmdta.se_order_detail d where d.order_ukid=s. ...

请给出测试用例

dla001

Maclean Liu(刘相兵 发表于 2013-10-14 22:07
请给出测试用例

11.2.0.3.7  x64
OEL5.8 x64

执行计划显示的是先执行子查询，但子查询中的条件是主查询传过的才对，应该是先主再子。

1. create table zwh_main (id number, c1 varchar2(50));
   
2. create table zwh_sub  (id number, id2 number);
   
3. insert into zwh_main select rownum, 'hello' from dual connect by level <10000;
   
4. insert into zwh_sub  select rownum,rownum from dual connect by level <1000;
   
5. create index idx_zwh_main  on zwh_main(id);
   
6. create index idx_zwh_sub on zwh_sub(id);
   
7. set linesize 150
   
8. set long 999
   
9. set pagesize 99
   
10. select zwh_main.*,(select sum(id2) from zwh_sub where zwh_sub.id=zwh_main.id) s
    
11. from zwh_main
    
12. where zwh_main.id=100;
    
13. select * from table(dbms_xplan.display_cursor());
    
14. 
    
15. SQL_ID  56aha300yf6gt, child number 0
    
16. -------------------------------------
    
17. select zwh_main.*,(select sum(id2) from zwh_sub where
    
18. zwh_sub.id=zwh_main.id) s from zwh_main where zwh_main.id=100
    
19. 
    
20. Plan hash value: 4037143566
    
21. 
    
22. ---------------------------------------------------------------------------------------------
    
23. | Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    
24. ---------------------------------------------------------------------------------------------
    
25. |   0 | SELECT STATEMENT             |              |       |       |     2 (100)|          |
    
26. |   1 |  SORT AGGREGATE              |              |     1 |    26 |            |          |
    
27. |   2 |   TABLE ACCESS BY INDEX ROWID| ZWH_SUB      |    10 |   260 |     2   (0)| 00:00:01 |
    
28. |*  3 |    INDEX RANGE SCAN          | IDX_ZWH_SUB  |     4 |       |     1   (0)| 00:00:01 |
    
29. |   4 |  TABLE ACCESS BY INDEX ROWID | ZWH_MAIN     |     1 |    40 |     2   (0)| 00:00:01 |
    
30. |*  5 |   INDEX RANGE SCAN           | IDX_ZWH_MAIN |     1 |       |     1   (0)| 00:00:01 |
    
31. ---------------------------------------------------------------------------------------------
    
32. 
    
33. Predicate Information (identified by operation id):
    
34. ---------------------------------------------------
    
35. 
    
36.    3 - access("ZWH_SUB"."ID"=:B1)
    
37.    5 - access("ZWH_MAIN"."ID"=100)
    
38. 
    
39. Note
    
40. -----
    
41.    - dynamic sampling used for this statement (level=2)
    
42. 
    
43. 
    
44. 28 rows selected.
    
45. 
    
46. SQL> @sqlorder
    
47. Enter value for macsqlid: 56aha300yf6gt
    
48. old   1: select 'Input SQL_ID        : ',lower('&&macsqlid') macsqlid from dual
    
49. new   1: select 'Input SQL_ID        : ',lower('56aha300yf6gt') macsqlid from dual
    
50. 
    
51. Input SQL_ID        :  56aha300yf6gt
    
52. 
    
53. Enter value for child_number: 0
    
54. old   1: select 'Input Child_number  : ',lower('&&child_number') child_number from dual
    
55. new   1: select 'Input Child_number  : ',lower('0') child_number from dual
    
56. 
    
57. Input Child_number  :  0
    
58. 
    
59. old   2: sql_id='&&macsqlid' and child_number=&&child_number
    
60. new   2: sql_id='56aha300yf6gt' and child_number=0
    
61. 
    
62. Table created.
    
63. 
    
64. 
    
65. Table created.
    
66. 
    
67. 
    
68. PL/SQL procedure successfully completed.
    
69. 
    
70. old   2: sql_id='&&macsqlid' and child_number=&&child_number
    
71. new   2: sql_id='56aha300yf6gt' and child_number=0
    
72. 
    
73. 6 rows created.
    
74. 
    
75. 
    
76. Commit complete.
    
77. 
    
78. 
    
79. OO                             OBJECT_NAME                    Execution Order
    
80. ------------------------------ ------------------------------ ---------------
    
81. SELECT STATEMENT                                                            6
    
82. SORT AGGREGATE                                                              3
    
83. TABLE ACCESS BY INDEX ROWID    ZWH_SUB                                      2
    
84. INDEX RANGE SCAN               IDX_ZWH_SUB                                  1
    
85. TABLE ACCESS BY INDEX ROWID    ZWH_MAIN                                     5
    
86. INDEX RANGE SCAN               IDX_ZWH_MAIN                                 4
    
87. 
    
88. 6 rows selected.
    
89. 
    
90. 
    
91. Commit complete.
    
92. 
    
93. 
    
94. Table dropped.
    
95. 
    
96. 
    
97. Table dropped.
    
98. 
    
99. 
    

复制代码

Maclean Liu(刘相兵

create table zwh_main (id number, c1 varchar2(50));

create table zwh_sub  (id number, id2 number);

insert into zwh_main select rownum, 'hello' from dual connect by level <10000;

insert into zwh_sub  select rownum,rownum from dual connect by level <1000;

create index idx_zwh_main  on zwh_main(id);

create index idx_zwh_sub on zwh_sub(id);

set linesize 150

set long 999

set pagesize 99



exec dbms_stats.gather_table_stats(user,'ZWH_MAIN');
exec dbms_stats.gather_table_stats(user,'ZWH_SUB');





  alter system flush buffer_cache;
  set linesize 200 pagesize 2000
  alter session set statistics_level=ALL;
  
  alter session set events '10046 trace name context forever,level 8';

select zwh_main.*,(select sum(id2) from zwh_sub where zwh_sub.id=zwh_main.id) s
from zwh_main
where zwh_main.id=100;

select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL IOSTATS LAST'));

oradebug setmypid
oradebug tracefile_name


------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       4 |      3 |
|   1 |  SORT AGGREGATE              |              |      1 |      1 |     8 |            |          |      1 |00:00:00.01 |       3 |      3 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ZWH_SUB      |      1 |      1 |     8 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |      3 |
|*  3 |    INDEX RANGE SCAN          | IDX_ZWH_SUB  |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      2 |
|   4 |  TABLE ACCESS BY INDEX ROWID | ZWH_MAIN     |      1 |      1 |    10 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |      3 |
|*  5 |   INDEX RANGE SCAN           | IDX_ZWH_MAIN |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |      2 |
------------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ZWH_SUB"."ID"=:B1)
   5 - access("ZWH_MAIN"."ID"=100)

       
SQL> select object_id,object_name from dba_objects where object_id in (92620,92618,92621,92619);

OBJECT_ID OBJECT_NAME
---------- --------------------
     92618 ZWH_MAIN
     92619 ZWH_SUB
     92620 IDX_ZWH_MAIN
     92621 IDX_ZWH_SUB

         
         

WAIT #140552674633184: nam='Disk file operations I/O' ela= 96 FileOperation=2 fileno=1 filetype=2 obj#=92620 tim=1381803018648141
WAIT #140552674633184: nam='db file sequential read' ela= 45 file#=1 block#=109393 blocks=1 obj#=92620 tim=1381803018648264
WAIT #140552674633184: nam='db file sequential read' ela= 18 file#=1 block#=109394 blocks=1 obj#=92620 tim=1381803018648444
WAIT #140552674633184: nam='db file sequential read' ela= 29 file#=1 block#=109361 blocks=1 obj#=92618 tim=1381803018648573
WAIT #140552674633184: nam='db file sequential read' ela= 19 file#=1 block#=109417 blocks=1 obj#=92621 tim=1381803018648804
WAIT #140552674633184: nam='db file sequential read' ela= 17 file#=1 block#=109418 blocks=1 obj#=92621 tim=1381803018648903
WAIT #140552674633184: nam='db file sequential read' ela= 18 file#=1 block#=109369 blocks=1 obj#=92619 tim=1381803018648986
FETCH #140552674633184:c=0,e=1216,p=6,cr=6,cu=0,mis=0,r=1,dep=0,og=1,plh=4037143566,tim=1381803018649065
WAIT #140552674633184: nam='SQL*Net message from client' ela= 379 driver id=1650815232 #bytes=1 p3=0 obj#=92619 tim=1381803018649537
FETCH #140552674633184:c=0,e=17,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=4037143566,tim=1381803018649616
STAT #140552674633184 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=3 pr=3 pw=0 time=364 us)'
STAT #140552674633184 id=2 cnt=1 pid=1 pos=1 obj=92619 op='TABLE ACCESS BY INDEX ROWID ZWH_SUB (cr=3 pr=3 pw=0 time=273 us cost=2 size=8 card=1)'
STAT #140552674633184 id=3 cnt=1 pid=2 pos=1 obj=92621 op='INDEX RANGE SCAN IDX_ZWH_SUB (cr=2 pr=2 pw=0 time=190 us cost=1 size=0 card=1)'
STAT #140552674633184 id=4 cnt=1 pid=0 pos=2 obj=92618 op='TABLE ACCESS BY INDEX ROWID ZWH_MAIN (cr=4 pr=3 pw=0 time=818 us cost=2 size=10 card=1)'
STAT #140552674633184 id=5 cnt=1 pid=4 pos=1 obj=92620 op='INDEX RANGE SCAN IDX_ZWH_MAIN (cr=3 pr=2 pw=0 time=674 us cost=1 size=0 card=1)'
WAIT #140552674633184: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=92619 tim=1381803018649949






查看EM 中的执行计划顺序， 同样是错误的

Maclean Liu(刘相兵

select zwh_main.*,(select sum(id2) from zwh_sub where zwh_sub.id=zwh_main.id) s
from zwh_main
where zwh_main.id=100;


这种 表达式中 加入子查询的方法看来对于 执行计划顺序存在特例， 无法适用正常的执行计划 解析顺序

Maclean Liu(刘相兵

感谢 dla001同学的例子 ，这个例子我会加到  PPT 里

dla001

感谢给出实验步骤，学习。

mangmangdream

感谢分享

了了

谢谢刘大

alexanderrein

感谢dla001和刘大

26856649

有感于Maclean的脚本和视频，我也写了一个sql，把标量顺序放在了后面，只能查select的，因为update的标量显示顺序不一样，请指正

1. /*
   
2. 查看执行计划次序。
   
3. 按：1.这是查select语句次序的。2。语句中标量执行次序放在了最后。
   
4. 尚未大量测试，如遇到错误请提出，谢谢
   
5. */
   
6. WITH t AS
   
7. (SELECT LEVEL AS lv,
   
8.          connect_by_isleaf AS isleaf,
   
9.          connect_by_root(id) AS root_id,
   
10.          sys_connect_by_path(id, ',') AS id_path,
    
11.          id,
    
12.          operation,
    
13.          object_name,
    
14.          parent_id
    
15.     FROM (SELECT id, operation, object_name, parent_id
    
16.             FROM v$sql_plan
    
17.            WHERE sql_id = '&sqlid'
    
18.              AND child_number = &child_number)
    
19.    START WITH parent_id IS NULL
    
20.   CONNECT BY PRIOR id = parent_id),
    
21. l AS
    
22. (SELECT id2,
    
23.          MAX(CASE
    
24.                WHEN id >= id0 AND id2 > 0 THEN
    
25.                 id
    
26.                ELSE
    
27.                 id + 999999
    
28.              END) AS lv2
    
29.     FROM (SELECT id,
    
30.                  MAX(to_number(regexp_substr(id_path, '[^,]+', 1, 2))) over() AS id0,
    
31.                  to_number(regexp_substr(id_path, '[^,]+', 1, LEVEL)) AS id2
    
32.             FROM t
    
33.            WHERE isleaf = 1
    
34.           CONNECT BY LEVEL <= lv
    
35.                  AND PRIOR id = id
    
36.                  AND PRIOR dbms_random.value() IS NOT NULL)
    
37.    GROUP BY id2)
    
38. SELECT row_number() over(ORDER BY l.lv2, t.lv DESC) AS sn,
    
39.        t.id,
    
40.        t.root_id,
    
41.        lpad('*', lv, '*') || operation AS operation,
    
42.        t.object_name,
    
43.        t.parent_id,
    
44.        t.isleaf,
    
45.        t.id_path
    
46.   FROM t
    
47. INNER JOIN l
    
48.     ON l.id2 = t.id
    
49. ORDER BY 2

复制代码

yaohongdi1987

mark 一下先

vivien_zh

学习ing，多谢

ffcxyq

谢谢刘大

我也向上

多谢分享.

dss507137

谢谢分享！

zhp19891010

谢谢刘大

shuizhuan

多谢分享

0425tracy

3ks 学习下

liupzmin

感谢分享，学习一下

renjie_41

感谢分享

黑风夜影

感谢分享，学习一下

wjl7813

谢谢分享！

mengfm

谢谢分享！

wrx0815

各位都是有才之人，谢谢分享

sendayang

谢谢老大分享

chriszhang

学习中，非常感谢分享！