菜鸟追梦旅行

这个屌丝很懒，什么也没留下！

热门标签

oracle connect by 表,Oracle 有表连接的connect by 的优化

作者：菜鸟追梦旅行 | 2024-06-09 03:13:40

踩

oracle connect by

代入变量值，加nl的hint,走NESTED LOOPS

SELECT /*+ use_nl(r,o) */

r.OUT_VER_BEGIN_ID dataID

FROM DMS_DATA_RELA r, DMS_OBJ o

WHERE r.DELETE_FLAG = '0'

AND r.RELA_TYPE_CODE = 'parent'

AND r.OUT_OBJ_CODE = o.OBJ_CODE

AND o.DELETE_FLAG = '0'

AND o.OPEN_STATE = '1'

AND r.IN_OBJ_CODE != 'o_in'

START WITH r.IN_DATA_ID in

(SELECT d.OUT_DATA_ID

FROM DMS_DATA_RELA d

where d.OUT_VER_BEGIN_ID = '20130131036703_syspro_o_wbs'

and d.last_curent_flag = '1')

CONNECT BY r.IN_VER_BEGIN_ID = PRIORr.OUT_VER_BEGIN_ID

已用时间: 00: 00: 08.06

执行计划

----------------------------------------------------------

Plan hash value: 1611995296

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 0| SELECT STATEMENT | | 637K| 92M| 159K (1)| 00:31:52 |

|* 1| FILTER | | | | | |

|* 2| CONNECT BY WITH FILTERING | | | | | |

|* 3| FILTER | | | | | |

| 4| COUNT | | | | | |

| 5| NESTED LOOPS | | 637K| 92M| 159K (1)| 00:31:52 |

| 6| TABLE ACCESS FULL | DMS_OBJ | 63 | 945 | 3 (0)| 00:00:01 |

| 7| TABLE ACCESS BY INDEX ROWID|DMS_DATA_RELA | 10119 | 1353K| 3449 (1)| 00:00:42 |

|* 8| INDEX RANGE SCAN | OUT_OBJ_CODE_INDEX | 13858 | | 37 (0)| 00:00:01 |

|* 9| TABLE ACCESS BY INDEX ROWID | DMS_DATA_RELA | 1 | 82 | 4 (0)| 00:00:01 |

|* 10 | INDEX RANGE SCAN | OUT_DATA_ID_INDEX | 1| | 3 (0)| 00:00:01 |

|* 11 | HASH JOIN | | | | | |

| 12| CONNECT BY PUMP | | | | | |

| 13| COUNT | | | | | |

| 14| NESTED LOOPS | | 637K| 92M| 159K (1)| 00:31:52 |

| 15| TABLE ACCESS FULL | DMS_OBJ | 63 | 945 | 3 (0)| 00:00:01 |

| 16| TABLE ACCESS BY INDEX ROWID|DMS_DATA_RELA | 10119 | 1353K| 3449 (1)| 00:00:42 |

|* 17 | INDEX RANGE SCAN | OUT_OBJ_CODE_INDEX | 13858 | | 37 (0)| 00:00:01 |

| 18| COUNT | | | | | |

| 19| NESTED LOOPS | | 637K| 92M| 159K (1)| 00:31:52 |

| 20| TABLE ACCESS FULL | DMS_OBJ | 63 | 945 | 3 (0)| 00:00:01 |

| 21| TABLE ACCESS BY INDEX ROWID |DMS_DATA_RELA | 10119 | 1353K| 3449 (1)| 00:00:42 |

|* 22 | INDEX RANGE SCAN | OUT_OBJ_CODE_INDEX | 13858 | | 37 (0)| 00:00:01 |

|* 23 | TABLE ACCESS BY INDEX ROWID |DMS_DATA_RELA | 1 | 82 | 4 (0)| 00:00:01 |

|* 24 | INDEX RANGE SCAN |OUT_DATA_ID_INDEX | 1 | | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

1- filter("R"."DELETE_FLAG"='0' AND"R"."RELA_TYPE_CODE"='parent' AND

"O"."DELETE_FLAG"='0' AND"O"."OPEN_STATE"='1' AND"R"."IN_OBJ_CODE"<>'o_in')

2- filter( EXISTS (SELECT 0 FROM "DMS_DATA_RELA" "D" WHERE"D"."OUT_DATA_ID"=:B1 AND

"D"."OUT_VER_BEGIN_ID"='20130131036703_syspro_o_wbs'AND "D"."LAST_CURENT_FLAG"='1'))

3- filter( EXISTS (SELECT 0 FROM "DMS_DATA_RELA" "D" WHERE"D"."OUT_DATA_ID"=:B1 AND

"D"."OUT_VER_BEGIN_ID"='20130131036703_syspro_o_wbs'AND "D"."LAST_CURENT_FLAG"='1'))

8-access("R"."OUT_OBJ_CODE"="O"."OBJ_CODE")

9-filter("D"."OUT_VER_BEGIN_ID"='20130131036703_syspro_o_wbs'AND

"D"."LAST_CURENT_FLAG"='1')

10- access("D"."OUT_DATA_ID"=:B1)

11- access("R"."IN_VER_BEGIN_ID"=NULL)

17-access("R"."OUT_OBJ_CODE"="O"."OBJ_CODE")

22-access("R"."OUT_OBJ_CODE"="O"."OBJ_CODE")

23-filter("D"."OUT_VER_BEGIN_ID"='20130131036703_syspro_o_wbs'AND

"D"."LAST_CURENT_FLAG"='1')

24- access("D"."OUT_DATA_ID"=:B1)

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

2733284 consistent gets

0 physical reads

0 redo size

596 bytes sent via SQL*Net toclient

350 bytes received via SQL*Netfrom client

2 SQL*Net roundtrips to/fromclient

4 sorts (memory)

0 sorts (disk)

5 rows processed

试过hint :unnest full(d)

主要在于逻辑读较高达到1663816

-access("D"."OUT_DATA_ID"=:B1)即

==>这个谓词让步骤9 认为之返回1行数据，所以 8-9TABLE ACCESS BY INDEX ROWID+INDEX RANGESCAN消耗了绝大多数buffer 42M和时间02:20.46

总的buffer get是42M ，耗时是2:23.58

SQL> set lines 1000 pages 1000

SQL> explain plan for

2 SELECT r.OUT_VER_BEGIN_IDdataID

3 FROM DMS_DATA_RELA r, DMS_OBJo

4 WHERE r.DELETE_FLAG = '0'

5 AND r.RELA_TYPE_CODE ='parent'

6 AND r.OUT_OBJ_CODE =o.OBJ_CODE

7 AND o.DELETE_FLAG = '0'

8 AND o.OPEN_STATE = '1'

9 AND r.IN_OBJ_CODE != 'o_in'

10 START WITH r.IN_DATA_ID IN

11 (SELECT /*+ unnest FULL(d) */

12 d.OUT_DATA_ID

13 FROM DMS_DATA_RELA d

14 WHERE d.OUT_VER_BEGIN_ID = :1

15 AND d.last_curent_flag = '1')

16 CONNECT BY r.IN_VER_BEGIN_ID = PRIOR r.OUT_VER_BEGIN_ID

17 ;

已解释。

SQL> select * fromtable(dbms_xplan.display(null,null,'typical -bytes'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------

Plan hash value: 3788880977

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 0| SELECT STATEMENT | | 829K| 11853 (1)| 00:02:23 |

|* 1| FILTER | | | | |

|* 2| CONNECT BY WITH FILTERING| | | | |

|* 3| FILTER | | | | |

| 4| COUNT | | | | |

|* 5| HASH JOIN | | 829K| 11853 (1)| 00:02:23 |

| 6| TABLE ACCESS FULL | DMS_OBJ | 41 | 3 (0)| 00:00:01 |

| 7| TABLE ACCESS FULL | DMS_DATA_RELA | 829K| 11843 (1)| 00:02:23 |

|* 8| TABLE ACCESS FULL | DMS_DATA_RELA | 1 | 11838 (1)| 00:02:23 |

|* 9| HASH JOIN | | | | |

| 10| CONNECT BY PUMP | | | | |

| 11| COUNT | | | | |

|* 12 | HASH JOIN | | 829K| 11853 (1)| 00:02:23 |

| 13| TABLE ACCESS FULL | DMS_OBJ | 41 | 3 (0)| 00:00:01 |

| 14| TABLE ACCESS FULL | DMS_DATA_RELA | 829K| 11843 (1)| 00:02:23 |

------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

1- filter("R"."DELETE_FLAG"='0' AND"R"."RELA_TYPE_CODE"='parent' AND

"O"."DELETE_FLAG"='0' AND"O"."OPEN_STATE"='1' AND

"R"."IN_OBJ_CODE"<>'o_in')

2- access("R"."IN_VER_BEGIN_ID"=PRIOR"R"."OUT_VER_BEGIN_ID")

3- filter( EXISTS (SELECT /*+ UNNEST FULL ("D") */ 0 FROM

"DMS_DATA_RELA""D" WHERE "D"."OUT_DATA_ID"=:B1 AND

"D"."OUT_VER_BEGIN_ID"=:1 AND"D"."LAST_CURENT_FLAG"='1'))

5- access("R"."OUT_OBJ_CODE"="O"."OBJ_CODE")

8- filter("D"."OUT_DATA_ID"=:B1 AND"D"."OUT_VER_BEGIN_ID"=:1 AND

"D"."LAST_CURENT_FLAG"='1')

9- access("R"."IN_VER_BEGIN_ID"=PRIOR"R"."OUT_VER_BEGIN_ID")

12-access("R"."OUT_OBJ_CODE"="O"."OBJ_CODE")

已选择37行。

大概50分钟过去了，还没有返回结果。

试过sqltrpt

未代入变量，提问中AWR中的SQL_ID

SQL> set lines 200 pages 1400

SQL> @?/rdbms/admin/sqltrpt

15 Most expensive SQL in the cursor cache

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID ELAPSED SQL_TEXT_FRAGMENT

------------- -----------------------------------------------------------------

7haaph0d5sq1a ########## SELECTr.OUT_VER_BEGIN_ID dataID FROM DMS_D

d13jncf0y817s 17,241.22 select this_.OID as OID92_0_,this_.VERSION as VERSION9

bkdmbpvxft9d4 17,148.85 select this_.OID as OID92_0_,this_.VERSION as VERSION9

crjtjnphxxn5m 13,548.57 SELECT r.OUT_VER_BEGIN_ID dataIDFROM DMS_DATA_RELA r,

dj62wg39uj9jv 2,609.61 BEGIN P_DATA_RELA_BATCH_INSERT(:1);END;

8f7hwwhahbuxx 2,590.32 INSERT INTO DMS_DATA_RELA (RELA_ID,IN_DATA_ID, IN_DATA

54juvpy15uxht 2,497.35 select count(*) as y0_ from ERM_TASK_USERTASK0this_ wh

6x1cu1a4axk9m 921.50 select * from ( select this_.OID asOID92_0_, this_.VER

cz1pkv54juzvf 918.26 select this_.OID as OID42_0_,this_.VERSION as VERSION4

7h35uxf5uhmm1 803.54 select sysdate from dual

c34ytmkk0p59g 584.03 WITH fra_space AS ( SELECT file_type, ROUND (mb_us

292t7nmq0zg2v 449.69 select count(*) as y0_ fromERM_TASK_USERTASK0 this_ wh

d6r3crk75q95b 418.50 select count(*) as y0_ fromERM_TASK_USERTASK0 this_ wh

c5g88t5jx4p0x 408.91 select * from ( select this_.OID asOID65_0_, this_.VER

cbafus0z7aw7r 366.54 SELECT ID,DATA_FIELD_ID,USER_NAME,RELATION_ID,PERMI

15 Most expensive SQL in the workloadrepository

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID ELAPSED SQL_TEXT_FRAGMENT

------------- -----------------------------------------------------------------------------------

7haaph0d5sq1a ########## SELECTr.OUT_VER_BEGIN_ID dataID FROM DMS_D

d13jncf0y817s 30,387.75 select this_.OID as OID92_0_,this_.VERSION as VERSION9

bkdmbpvxft9d4 30,228.19 select this_.OID as OID92_0_,this_.VERSION as VERSION9

96gm4b8z5b0wm 14,814.72 SELECT k.wb_code aspwbCode,k.wb_name pwbName, b.*

crjtjnphxxn5m 13,548.57 SELECT r.OUT_VER_BEGIN_ID dataIDFROM DMS_DATA_RELA r,

61ndacbvb67g6 12,512.02 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

579trysnvs28y 12,061.27 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

47bphtjkat5xb 12,046.08 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

3mtq8jf1yu15r 11,930.41 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

4zmn7j4jq74zj 11,695.78 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

9hp6yya3j74b2 10,892.15 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

54juvpy15uxht 9,920.55 select count(*) as y0_ fromERM_TASK_USERTASK0 this_ wh

6t46kc2j71wuq 5,532.33 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

f2j42gp02s0tb 5,307.70 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

gcayhw5dhv9np 5,191.69 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

Specify the Sql id

~~~~~~~~~~~~~~~~~~

输入 sqlid 的值: 7haaph0d5sq1a

Sql Id specified: 7haaph0d5sq1a

Tune the sql

~~~~~~~~~~~~

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name : 任务_6520

Tuning Task Owner : DMS

Scope : COMPREHENSIVE

Time Limit(seconds): 1800

Completion Status : COMPLETED

Started at : 11/04/2013 13:35:17

Completed at : 11/04/2013 13:35:17

-------------------------------------------------------------------------------

Schema Name: DMS

SQL ID : 7haaph0d5sq1a

SQL Text : SELECT r.OUT_VER_BEGIN_ID dataID

FROM DMS_DATA_RELA r,DMS_OBJ o

WHEREr.DELETE_FLAG = '0'

ANDr.RELA_TYPE_CODE = 'parent'

AND r.OUT_OBJ_CODE= o.OBJ_CODE

AND o.DELETE_FLAG= '0'

AND o.OPEN_STATE ='1'

AND r.IN_OBJ_CODE!= 'o_in'

START WITHr.IN_DATA_ID in (

SELECTd.OUT_DATA_ID FROM DMS_DATA_RELA d

whered.OUT_VER_BEGIN_ID=:1 and

d.last_curent_flag='1'

)

CONNECT BYr.IN_VER_BEGIN_ID = PRIOR

r.OUT_VER_BEGIN_ID

-------------------------------------------------------------------------------

There are no recommendations to improve thestatement.

-------------------------------------------------------------------------------

带入变量执行后的SQL_ID

SQL> @?/rdbms/admin/sqltrpt

15 Most expensive SQL in the cursor cache

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID ELAPSED SQL_TEXT_FRAGMENT

------------- -----------------------------------------------------------------

7haaph0d5sq1a ########## SELECT r.OUT_VER_BEGIN_IDdataID FROM DMS_D

d13jncf0y817s 17,471.85 select this_.OID as OID92_0_,this_.VERSION as VERSION9

bkdmbpvxft9d4 17,378.48 select this_.OID as OID92_0_,this_.VERSION as VERSION9

crjtjnphxxn5m 13,548.57 SELECT r.OUT_VER_BEGIN_ID dataIDFROM DMS_DATA_RELA r,

dj62wg39uj9jv 2,609.61 BEGIN P_DATA_RELA_BATCH_INSERT(:1);END;

8f7hwwhahbuxx 2,590.32 INSERT INTO DMS_DATA_RELA (RELA_ID,IN_DATA_ID, IN_DATA

54juvpy15uxht 2,518.78 select count(*) as y0_ fromERM_TASK_USERTASK0 this_ wh

6x1cu1a4axk9m 921.87 select * from ( select this_.OID asOID92_0_, this_.VER

cz1pkv54juzvf 918.26 select this_.OID as OID42_0_,this_.VERSION as VERSION4

7h35uxf5uhmm1 803.95 select sysdate from dual

c34ytmkk0p59g 604.67 WITH fra_space AS ( SELECT file_type, ROUND (mb_us

292t7nmq0zg2v 453.16 select count(*) as y0_ fromERM_TASK_USERTASK0 this_ wh

d6r3crk75q95b 421.19 select count(*) as y0_ fromERM_TASK_USERTASK0 this_ wh

c5g88t5jx4p0x 409.25 select * from ( select this_.OID asOID65_0_, this_.VER

cbafus0z7aw7r 366.54 SELECT ID,DATA_FIELD_ID,USER_NAME,RELATION_ID,PERMI

15 Most expensive SQL in the workloadrepository

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID ELAPSED SQL_TEXT_FRAGMENT

------------- ---------------------------------------------------------------------

7haaph0d5sq1a ########## SELECTr.OUT_VER_BEGIN_ID dataID FROM DMS_D

d13jncf0y817s 30,773.63 select this_.OID as OID92_0_,this_.VERSION as VERSION9

bkdmbpvxft9d4 30,607.56 select this_.OID as OID92_0_,this_.VERSION as VERSION9

96gm4b8z5b0wm 14,814.72 SELECT k.wb_code aspwbCode,k.wb_nam

crjtjnphxxn5m 13,548.57 SELECT r.OUT_VER_BEGIN_ID dataIDFROM DMS_DATA_RELA r,

61ndacbvb67g6 12,512.02 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

579trysnvs28y 12,061.27 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

47bphtjkat5xb 12,046.08 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

3mtq8jf1yu15r 11,930.41 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

4zmn7j4jq74zj 11,695.78 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

9hp6yya3j74b2 10,892.15 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

54juvpy15uxht 9,956.40 select count(*) as y0_ fromERM_TASK_USERTASK0 this_ wh

6t46kc2j71wuq 5,532.33 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

f2j42gp02s0tb 5,307.70 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

gcayhw5dhv9np 5,191.69 SELECT * FROM ( SELECT COUNT (1) CNT FROM (SELECT (

Specify the Sql id

~~~~~~~~~~~~~~~~~~

输入 sqlid 的值: fc1pnzudc1xwd

Sql Id specified: fc1pnzudc1xwd

Tune the sql

~~~~~~~~~~~~

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name : 任务_6523

Tuning Task Owner : DMS

Scope : COMPREHENSIVE

Time Limit(seconds): 1800

Completion Status : COMPLETED

Started at : 11/04/2013 14:10:38

Completed at : 11/04/2013 14:10:38

-------------------------------------------------------------------------------

Schema Name: DMS

SQL ID : fc1pnzudc1xwd

SQL Text : SELECT /* bamuta */

r.OUT_VER_BEGIN_ID dataID

FROM DMS_DATA_RELA r,DMS_OBJ o

WHERE r.DELETE_FLAG = '0'

AND r.RELA_TYPE_CODE = 'parent'

AND r.OUT_OBJ_CODE = o.OBJ_CODE

AND o.DELETE_FLAG = '0'

AND o.OPEN_STATE = '1'

AND r.IN_OBJ_CODE != 'o_in'

START WITH r.IN_DATA_ID IN

(SELECT d.OUT_DATA_ID

FROM DMS_DATA_RELAd

WHEREd.OUT_VER_BEGIN_ID =

'20130131036703_syspro_o_wbs'

ANDd.last_curent_flag = '1')

CONNECT BY r.IN_VER_BEGIN_ID = PRIOR r.OUT_VER_BEGIN_ID

-------------------------------------------------------------------------------

There are no recommendations to improve thestatement.

-------------------------------------------------------------------------------

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/菜鸟追梦旅行/article/detail/692356