Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀软件旗下网站

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

2135

积分

498

好友

185

主题
发表于 2013-10-13 20:09:37 | 查看: 15579| 回复: 34
Script: PRINT SQL Execution Order By Maclean Liu

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

SQL Execution Order By Maclean Liu.txt (2.55 KB, 下载次数: 345, 售价: 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
下载专业ORACLE数据库恢复工具PRM-DUL  For Oracle http://www.parnassusdata.com/zh-hans/emergency-services

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638  QQ: 47079569   
发表于 2013-10-14 10:21:04
谢谢老大分享

回复 显示全部楼层 道具 举报

发表于 2013-10-14 10:23:20
学习一下

回复 显示全部楼层 道具 举报

发表于 2013-10-14 10:31:19
感谢分享,学习一下

回复 显示全部楼层 道具 举报

发表于 2013-10-14 10:32:50
mark一下

回复 显示全部楼层 道具 举报

发表于 2013-10-14 10:41:32
学习一下

回复 显示全部楼层 道具 举报

发表于 2013-10-14 10:50:59
学习了,感谢分享

回复 显示全部楼层 道具 举报

发表于 2013-10-14 14:20:24
这个要赞,不知道复杂sql的执行计划能不能呢个列出来。买来看看先。

回复 显示全部楼层 道具 举报

发表于 2013-10-14 17:30:13
本帖最后由 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;
  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. Plan hash value: 3057353959

  7. -----------------------------------------------------------------------------------------------
  8. | Id  | Order | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
  9. -----------------------------------------------------------------------------------------------
  10. |   0 |     4 | SELECT STATEMENT  |                   |       |       |     2 (100)|          |
  11. |   1 |     2 |  SORT AGGREGATE   |                   |     1 |    10 |            |          |
  12. |*  2 |     1 |   INDEX RANGE SCAN| SE_ORDER_DETAIL_1 |     4 |    40 |     3   (0)| 00:00:01 |
  13. |*  3 |     3 |  INDEX UNIQUE SCAN| SE_ORDER_0        |     1 |    10 |     2   (0)| 00:00:01 |
  14. -----------------------------------------------------------------------------------------------

  15. Predicate Information (identified by operation id):
  16. -----------------------------------------------------------

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

回复 显示全部楼层 道具 举报

发表于 2013-10-14 22:07:49
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. ...

请给出测试用例

回复 显示全部楼层 道具 举报

发表于 2013-10-15 10:03:50
本帖最后由 dla001 于 2013-10-15 10:07 编辑
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. SQL_ID  56aha300yf6gt, child number 0
  15. -------------------------------------
  16. select zwh_main.*,(select sum(id2) from zwh_sub where
  17. zwh_sub.id=zwh_main.id) s from zwh_main where zwh_main.id=100

  18. Plan hash value: 4037143566

  19. ---------------------------------------------------------------------------------------------
  20. | Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
  21. ---------------------------------------------------------------------------------------------
  22. |   0 | SELECT STATEMENT             |              |       |       |     2 (100)|          |
  23. |   1 |  SORT AGGREGATE              |              |     1 |    26 |            |          |
  24. |   2 |   TABLE ACCESS BY INDEX ROWID| ZWH_SUB      |    10 |   260 |     2   (0)| 00:00:01 |
  25. |*  3 |    INDEX RANGE SCAN          | IDX_ZWH_SUB  |     4 |       |     1   (0)| 00:00:01 |
  26. |   4 |  TABLE ACCESS BY INDEX ROWID | ZWH_MAIN     |     1 |    40 |     2   (0)| 00:00:01 |
  27. |*  5 |   INDEX RANGE SCAN           | IDX_ZWH_MAIN |     1 |       |     1   (0)| 00:00:01 |
  28. ---------------------------------------------------------------------------------------------

  29. Predicate Information (identified by operation id):
  30. ---------------------------------------------------

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

  33. Note
  34. -----
  35.    - dynamic sampling used for this statement (level=2)


  36. 28 rows selected.

  37. SQL> @sqlorder
  38. Enter value for macsqlid: 56aha300yf6gt
  39. old   1: select 'Input SQL_ID        : ',lower('&&macsqlid') macsqlid from dual
  40. new   1: select 'Input SQL_ID        : ',lower('56aha300yf6gt') macsqlid from dual

  41. Input SQL_ID        :  56aha300yf6gt

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

  45. Input Child_number  :  0

  46. old   2: sql_id='&&macsqlid' and child_number=&&child_number
  47. new   2: sql_id='56aha300yf6gt' and child_number=0

  48. Table created.


  49. Table created.


  50. PL/SQL procedure successfully completed.

  51. old   2: sql_id='&&macsqlid' and child_number=&&child_number
  52. new   2: sql_id='56aha300yf6gt' and child_number=0

  53. 6 rows created.


  54. Commit complete.


  55. OO                             OBJECT_NAME                    Execution Order
  56. ------------------------------ ------------------------------ ---------------
  57. SELECT STATEMENT                                                            6
  58. SORT AGGREGATE                                                              3
  59. TABLE ACCESS BY INDEX ROWID    ZWH_SUB                                      2
  60. INDEX RANGE SCAN               IDX_ZWH_SUB                                  1
  61. TABLE ACCESS BY INDEX ROWID    ZWH_MAIN                                     5
  62. INDEX RANGE SCAN               IDX_ZWH_MAIN                                 4

  63. 6 rows selected.


  64. Commit complete.


  65. Table dropped.


  66. Table dropped.


复制代码

回复 显示全部楼层 道具 举报

发表于 2013-10-15 10:29:27
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 中的执行计划顺序, 同样是错误的


em1.png

回复 显示全部楼层 道具 举报

发表于 2013-10-15 10:30:46
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;


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

回复 显示全部楼层 道具 举报

发表于 2013-10-15 10:31:15
感谢 dla001同学的例子 ,这个例子我会加到  PPT 里

回复 显示全部楼层 道具 举报

发表于 2013-10-15 12:01:26
感谢给出实验步骤,学习。

回复 显示全部楼层 道具 举报

发表于 2013-10-16 15:25:02
感谢分享

回复 显示全部楼层 道具 举报

发表于 2013-10-22 10:18:12
谢谢刘大

回复 显示全部楼层 道具 举报

发表于 2013-10-23 09:46:21
感谢dla001和刘大

回复 显示全部楼层 道具 举报

发表于 2013-10-28 08:58:00
有感于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
复制代码

回复 显示全部楼层 道具 举报

发表于 2013-10-28 11:21:06
mark 一下先

回复 显示全部楼层 道具 举报

发表于 2014-2-24 16:26:00
学习ing,多谢

回复 显示全部楼层 道具 举报

发表于 2014-2-27 15:45:41

谢谢刘大

回复 显示全部楼层 道具 举报

发表于 2014-3-2 13:45:47
多谢分享.

回复 显示全部楼层 道具 举报

发表于 2014-5-11 21:26:07
谢谢分享!

回复 显示全部楼层 道具 举报

发表于 2014-5-15 20:20:08
谢谢刘大

回复 显示全部楼层 道具 举报

发表于 2014-7-6 17:31:45
多谢分享

回复 显示全部楼层 道具 举报

发表于 2014-7-21 13:20:49
3ks 学习下

回复 显示全部楼层 道具 举报

发表于 2014-7-23 12:44:53

感谢分享,学习一下

回复 显示全部楼层 道具 举报

发表于 2014-8-5 15:34:33
感谢分享

回复 显示全部楼层 道具 举报

发表于 2014-9-12 01:05:49
感谢分享,学习一下

回复 显示全部楼层 道具 举报

发表于 2014-12-8 19:26:51
谢谢分享!

回复 显示全部楼层 道具 举报

发表于 2015-4-17 13:46:09
谢谢分享!

回复 显示全部楼层 道具 举报

发表于 2015-12-9 09:43:23
各位都是有才之人,谢谢分享

回复 显示全部楼层 道具 举报

发表于 2016-2-4 15:40:44
谢谢老大分享

回复 显示全部楼层 道具 举报

发表于 2019-3-14 10:47:29
学习中,非常感谢分享!

回复 显示全部楼层 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀软件旗下网站

GMT+8, 2021-4-20 20:22 , Processed in 0.075617 second(s), 25 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569