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

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. ...

请给出测试用例

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 里