Script:SQL Execution Order By Maclean Liu
Script: PRINT SQL Execution Order By Maclean Liu分享一个可以打印出正确 执行计划 执行顺序的脚本
使用方法如下：
将该文件 名修改为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
谢谢老大分享 学习一下 感谢分享，学习一下 mark一下 学习一下 学习了，感谢分享 这个要赞，不知道复杂sql的执行计划能不能呢个列出来。买来看看先。 本帖最后由 dla001 于 2013-10-14 17:32 编辑
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;OO OBJECT_NAME Execution Order
------------------------------ ------------------------------------------------------------------------------------------ ---------------
SELECT STATEMENT 4
SORT AGGREGATE 2
INDEX RANGE SCAN SE_ORDER_DETAIL_1 1
INDEX UNIQUE SCAN SE_ORDER_0 3使用Adrian Billington的xplan扩展包也显示与你的一样的结果。SQL_ID 3vkh6vctzafhu, child number 0
-------------------------------------
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
Plan hash value: 3057353959
-----------------------------------------------------------------------------------------------
| Id | Order | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | 4 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | 2 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | 1 | INDEX RANGE SCAN| SE_ORDER_DETAIL_1 | 4 | 40 | 3 (0)| 00:00:01 |
|* 3 | 3 | INDEX UNIQUE SCAN| SE_ORDER_0 | 1 | 10 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------
2 - access("D"."ORDER_UKID"=:B1)
3 - access("S"."ORDER_UKID"=1111)这是有问题的吧。应该是先执行3，再执行2。如果是先执行2，access("D"."ORDER_UKID"=:B1)，那么它怎么可能知道 :B1是什么，这个应该是下面传过来的才对。 dla001 发表于 2013-10-14 17:30 static/image/common/back.gif
Hi maclean
select s.order_ukid ,(select count(*) from ecmdta.se_order_detail d where d.order_ukid=s. ...
请给出测试用例 本帖最后由 dla001 于 2013-10-15 10:07 编辑
Maclean Liu(刘相兵 发表于 2013-10-14 22:07 static/image/common/back.gif
请给出测试用例
11.2.0.3.7 x64
OEL5.8 x64
执行计划显示的是先执行子查询，但子查询中的条件是主查询传过的才对，应该是先主再子。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
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());
SQL_ID 56aha300yf6gt, child number 0
-------------------------------------
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
Plan hash value: 4037143566
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ZWH_SUB | 10 | 260 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_ZWH_SUB | 4 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | ZWH_MAIN | 1 | 40 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_ZWH_MAIN | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ZWH_SUB"."ID"=:B1)
5 - access("ZWH_MAIN"."ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
28 rows selected.
SQL> @sqlorder
Enter value for macsqlid: 56aha300yf6gt
old 1: select 'Input SQL_ID : ',lower('&&macsqlid') macsqlid from dual
new 1: select 'Input SQL_ID : ',lower('56aha300yf6gt') macsqlid from dual
Input SQL_ID : 56aha300yf6gt
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 2: sql_id='&&macsqlid' and child_number=&&child_number
new 2: sql_id='56aha300yf6gt' and child_number=0
Table created.
Table created.
PL/SQL procedure successfully completed.
old 2: sql_id='&&macsqlid' and child_number=&&child_number
new 2: sql_id='56aha300yf6gt' and child_number=0
6 rows created.
Commit complete.
OO OBJECT_NAME Execution Order
------------------------------ ------------------------------ ---------------
SELECT STATEMENT 6
SORT AGGREGATE 3
TABLE ACCESS BY INDEX ROWID ZWH_SUB 2
INDEX RANGE SCAN IDX_ZWH_SUB 1
TABLE ACCESS BY INDEX ROWID ZWH_MAIN 5
INDEX RANGE SCAN IDX_ZWH_MAIN 4
6 rows selected.
Commit complete.
Table dropped.
Table dropped.
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 中的执行计划顺序， 同样是错误的
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;
这种 表达式中 加入子查询的方法看来对于 执行计划顺序存在特例， 无法适用正常的执行计划 解析顺序 感谢 dla001同学的例子 ，这个例子我会加到 PPT 里 感谢给出实验步骤，学习。 感谢分享 谢谢刘大 感谢dla001和刘大 有感于Maclean的脚本和视频，我也写了一个sql，把标量顺序放在了后面，只能查select的，因为update的标量显示顺序不一样，请指正/*
查看执行计划次序。
按：1.这是查select语句次序的。2。语句中标量执行次序放在了最后。
尚未大量测试，如遇到错误请提出，谢谢
*/
WITH t AS
(SELECT LEVEL AS lv,
connect_by_isleaf AS isleaf,
connect_by_root(id) AS root_id,
sys_connect_by_path(id, ',') AS id_path,
id,
operation,
object_name,
parent_id
FROM (SELECT id, operation, object_name, parent_id
FROM v$sql_plan
WHERE sql_id = '&sqlid'
AND child_number = &child_number)
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id),
l AS
(SELECT id2,
MAX(CASE
WHEN id >= id0 AND id2 > 0 THEN
id
ELSE
id + 999999
END) AS lv2
FROM (SELECT id,
MAX(to_number(regexp_substr(id_path, '[^,]+', 1, 2))) over() AS id0,
to_number(regexp_substr(id_path, '[^,]+', 1, LEVEL)) AS id2
FROM t
WHERE isleaf = 1
CONNECT BY LEVEL <= lv
AND PRIOR id = id
AND PRIOR dbms_random.value() IS NOT NULL)
GROUP BY id2)
SELECT row_number() over(ORDER BY l.lv2, t.lv DESC) AS sn,
t.id,
t.root_id,
lpad('*', lv, '*') || operation AS operation,
t.object_name,
t.parent_id,
t.isleaf,
t.id_path
FROM t
INNER JOIN l
ON l.id2 = t.id
ORDER BY 2 mark 一下先 学习ing，多谢
谢谢刘大 多谢分享. 谢谢分享！ 谢谢刘大 多谢分享 3ks 学习下
感谢分享，学习一下 感谢分享 感谢分享，学习一下 谢谢分享！ 谢谢分享！ 各位都是有才之人，谢谢分享 谢谢老大分享 学习中，非常感谢分享！
页:
[1]