Oracle 有表连接的connect by 的优化

时间:2014-12-15 23:05 来源:linux.it.net.cn 作者:IT

有1个有表连接，还有connect by 的SQL，整了好几天才优化成功，感觉像游戏中打死了只大boss一样。现将这个过程整理一下。

优化前：

SELECT r.OUT_VER_BEGIN_IDdataID

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 = :1

and d.last_curent_flag = '1')

CONNECT BY r.IN_VER_BEGIN_ID = PRIOR r.OUT_VER_BEGIN_ID

优化后：

我把这个SQL先做了connect by 循环，然后再与另1个表做了连接，效果超好，我从李华值《海量数据库解决方案》3.2.5 找到相关例子，并有这样的说明： ”如果查询条件中的列位于同一表中时，并没有必要优先执行表连接“

select dataID

from(SELECT r.OUT_VER_BEGIN_ID dataID, r.out_obj_code

FROM DMS_DATA_RELA r

WHERE r.DELETE_FLAG = '0'

AND r.RELA_TYPE_CODE = 'parent'

AND r.IN_OBJ_CODE != 'o_in'

STARTWITH r.IN_DATA_ID in

(SELECT d.OUT_DATA_ID

FROM DMS_DATA_RELA d

where d.OUT_VER_BEGIN_ID=:1

and d.last_curent_flag= '1')

CONNECT BY r.IN_VER_BEGIN_ID = PRIOR r.OUT_VER_BEGIN_ID) e,

DMS_OBJ o

wheree.OUT_OBJ_CODE = o.OBJ_CODE

ANDo.DELETE_FLAG = '0'

ANDo.OPEN_STATE = '1'

优化过程：

曾中途一筹莫展时，到刘大的论坛求助过，下面是地址。

http://t.askmaclean.com/thread-3381-1-1.html

下面过程是基于以上的整理。有基本信息和试过的方法

基本信息

基本环境
操作系统：windows server 2008 r2 enterprise

SQL> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise EditionRelease 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0- Production
NLSRTL Version 10.2.0.4.0 - Production

开发中的库，在做测试时，发现有1SQL比较消耗资源，我想把他优化一下。

SQL如下：
SELECT 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 = :1
           and d.last_curent_flag = '1')
CONNECT BY r.IN_VER_BEGIN_ID = PRIORr.OUT_VER_BEGIN_ID

执行计划：（来自awrsqrpt)

0  SELECT STATEMENT       8045 (100)
1    FILTER
2    CONNECT BY WITHFILTERING
3       FILTER
4         COUNT
5         HASH JOIN 717K 111M 8045 (1)00:01:37
6            TABLE ACCESSFULL  DMS_OBJ  41  656  3 (0)00:00:01
7            TABLE ACCESSFULL  DMS_DATA_RELA  717K 100M 8037 (1)00:01:37
8         TABLE ACCESS BY INDEX ROWIDDMS_DATA_RELA  1  91  4 (0) 00:00:01
9         INDEX RANGE SCAN  OUT_VER_BEGIN_ID_INDEX  1   3 (0) 00:00:01
10       HASH JOIN
11         CONNECT BY PUMP
12         COUNT
13         HASH JOIN 717K 111M 8045 (1)00:01:37
14            TABLE ACCESSFULL  DMS_OBJ  41  656  3 (0)00:00:01
15            TABLE ACCESSFULL  DMS_DATA_RELA  717K 100M 8037 (1)00:01:37

随便找个变量单独执行这条SQL试了下，大概能执行6秒。

表上记录数
SQL> select count(*) from dms_data_rela;
  COUNT(*)
----------
858470

SQL> select count(*) from dms_obj;
  COUNT(*)
----------
      41

表上索引：（索引较多，如果不影响此条SQL的查询性能，先不考虑这些索引合不合理，因为有些非技术因素）

SQL> selectindex_name,column_name,table_name from dba_ind_columns where table_name='DMS_DATA_RELA' order by index_name;
INDEX_NAME                  COLUMN_NAME       TABLE_NAME
-------------------------------------------------- ------------------------------
CREATE_TIME_INDEX            SYS_NC00031$      DMS_DATA_RELA
DELETE_FLAG_INDEX            DELETE_FLAG      DMS_DATA_RELA
IN_DATA_ID_INDEX            IN_DATA_ID         DMS_DATA_RELA
IN_DATA_NAME_INDEX         IN_DATA_NAME       DMS_DATA_RELA
IN_DATA_SOURCE_CODE_INDEX    IN_DATA_SOURCE_CODE  DMS_DATA_RELA
IN_DATA_SOURCE_ID_INDEX      IN_DATA_SOURCE_ID DMS_DATA_RELA
IN_OBJ_CODE_INDEX            IN_OBJ_CODE      DMS_DATA_RELA
IN_VER_BEGIN_ID_INDEX      IN_VER_BEGIN_ID    DMS_DATA_RELA
LAST_CURENT_FLAG_INDEX      LAST_CURENT_FLAG    DMS_DATA_RELA
OUT_DATA_ID_INDEX            OUT_DATA_ID      DMS_DATA_RELA
OUT_DATA_NAME_INDEX         OUT_DATA_NAME      DMS_DATA_RELA
OUT_DATA_SOURCE_ID_INDEX     OUT_DATA_SOURCE_ID DMS_DATA_RELA
OUT_OBJ_CODE_INDEX         OUT_OBJ_CODE       DMS_DATA_RELA
OUT_VER_BEGIN_ID_INDEX      OUT_VER_BEGIN_ID    DMS_DATA_RELA
PK_DMS_DATA_RELA            RELA_ID            DMS_DATA_RELA
RELA_TYPE_CODE_INDEX         RELA_TYPE_CODE    DMS_DATA_RELA

SQL> selectindex_name,column_name,table_name from dba_ind_columns where table_name='DMS_OBJ' order by index_name;
INDEX_NAME                  COLUMN_NAME       TABLE_NAME
-------------------------------------------------- ------------------------------
PK_DMS_OBJ                  OBJ_ID             DMS_OBJ

上面SQL走全表，我找了一些资料说是在IN_VER_BEGIN_ID 列上有索引的话，会走索引，但是实际有索引，还是全表，加hint也还是全表。
另外表也收集过统计信息了。

表上的列：

SQL> desc DMS_DATA_RELA
Name                         Type       Nullable Default Comments
------------------------------ ------------- -------- ------- --------
RELA_ID                      CHAR(32)
IN_DATA_ID                   VARCHAR2(200) Y
IN_DATA_NAME                VARCHAR2(200) Y
IN_DATA_SOURCE_ID             VARCHAR2(200) Y
IN_DATA_SOURCE_CODE          VARCHAR2(200) Y
IN_OBJ_CODE                   VARCHAR2(200) Y
IN_VER_BEGIN_ID             VARCHAR2(200) Y
IN_VER_BEGIN_DATA_SOURCE_ID VARCHAR2(200) Y
IN_VER_BEGIN_DATA_SOURCE_CODE  VARCHAR2(64)  Y
IN_VER_BEGIN_DATA_OBJ_CODE    VARCHAR2(64)  Y
OUT_DATA_ID                   VARCHAR2(200) Y
OUT_DATA_NAME                VARCHAR2(200) Y
OUT_DATA_SOURCE_ID          VARCHAR2(200) Y
OUT_DATA_SOURCE_CODE          VARCHAR2(200) Y
OUT_OBJ_CODE                VARCHAR2(200) Y
OUT_VER_BEGIN_ID             VARCHAR2(200) Y
OUT_VER_BEGIN_DATA_SOURCE_ID VARCHAR2(200) Y
OUT_VER_BEGIN_DATA_SOURCE_CODE VARCHAR2(64)  Y
OUT_VER_BEGIN_DATA_OBJ_CODE VARCHAR2(64)  Y
RELA_TYPE_CODE                VARCHAR2(200) Y
MIDDLE_OBJ_CODE             VARCHAR2(64)  Y
LAST_CURENT_FLAG             CHAR(1)    Y
CREATE_USER_NAME             VARCHAR2(200) Y
CREATE_USER_REAL_NAME       VARCHAR2(200) Y
CREATE_TIME                   TIMESTAMP(6)  Y
UPDATE_USER_NAME             VARCHAR2(200) Y
UPDATE_USER_REAL_NAME       VARCHAR2(200) Y
UPDATE_TIME                   TIMESTAMP(6)  Y
DELETE_FLAG                   CHAR(1)    Y
ORDER_NUM                   NUMBER(10) Y

另外也有人提到清理索引后，效果会好。    首先，索引有些非技术原因不让清理。另外我弄了1个新环境，没有过多索引，试过不同的列上建不同的索引，效果也是一样的。单就此条SQL来说，过多的索引应该影响不大。

有人问索引状态，也一并附上：
SQL> select table_name, index_name,index_type,statusfrom user_indexes where table_name='DMS_DATA_RELA';
TABLE_NAME                  INDEX_NAME                   INDEX_TYPE               STATUS
------------------------------------------------------------ --------------------------- --------
DMS_DATA_RELA               OUT_VER_BEGIN_ID_INDEX       NORMAL                    VALID
DMS_DATA_RELA               IN_DATA_ID_INDEX            NORMAL                  VALID
DMS_DATA_RELA               IN_DATA_NAME_INDEX          NORMAL                  VALID
DMS_DATA_RELA               IN_DATA_SOURCE_CODE_INDEX    NORMAL                    VALID
DMS_DATA_RELA               IN_DATA_SOURCE_ID_INDEX       NORMAL                    VALID
DMS_DATA_RELA               IN_OBJ_CODE_INDEX            NORMAL                  VALID
DMS_DATA_RELA               IN_VER_BEGIN_ID_INDEX       NORMAL                    VALID
DMS_DATA_RELA               OUT_DATA_NAME_INDEX          NORMAL                  VALID
DMS_DATA_RELA               OUT_DATA_SOURCE_ID_INDEX    NORMAL                    VALID
DMS_DATA_RELA               OUT_OBJ_CODE_INDEX          NORMAL                  VALID
DMS_DATA_RELA               RELA_TYPE_CODE_INDEX         NORMAL                  VALID
DMS_DATA_RELA               DELETE_FLAG_INDEX            NORMAL                  VALID
DMS_DATA_RELA               LAST_CURENT_FLAG_INDEX       NORMAL                    VALID
DMS_DATA_RELA               CREATE_TIME_INDEX            FUNCTION-BASED NORMAL    VALID
DMS_DATA_RELA               OUT_DATA_ID_INDEX            NORMAL                  VALID
DMS_DATA_RELA               PK_DMS_DATA_RELA            NORMAL                  VALID
16 rows selected

我试过物化视图

CREATE MATERIALIZED VIEW mv_dms_ddr
REFRESH force
ON demand
WITH ROWID enable query rewrite AS
SELECT r.OUT_VER_BEGIN_ID, r.IN_DATA_ID,r.IN_VER_BEGIN_ID
  FROM DMS_DATA_RELA r, DMS_OBJ o
WHERE r.DELETE_FLAG = '0'
AND r.RELA_TYPE_CODE = 'parent'
AND r.IN_OBJ_CODE != 'o_in'
AND r.OUT_OBJ_CODE =o.OBJ_CODE
AND o.DELETE_FLAG = '0'
AND o.OPEN_STATE = '1'
改写SQL
SELECT OUT_VER_BEGIN_ID dataID
FROM mv_dms_ddr
START WITH IN_DATA_ID in
        (SELECT d.OUT_DATA_ID
           FROM DMS_DATA_RELA d
           where d.OUT_VER_BEGIN_ID = :1
           and d.last_curent_flag = '1')
CONNECT BY PRIOR OUT_VER_BEGIN_ID =IN_VER_BEGIN_ID
（没用fast更新），带入变量试了，第1次慢，后面快，执行时间大概是3秒多了。有提升，但不是很理想，而且表更新也频繁，刷新方式没做on commit。（我猜测在数据库变动频繁的情况下，是不是这个查询还是会慢，这个想法还没验证）

管理

试过Nested Loop

有人提到nested loop，我前面测过了，效果不怎么好，现在再把nl的执行计划附上。执行计划是代入变量测的。

代入变量值，不加nl的hint

set autot traceonly

SELECT

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: 06.45

执行计划

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

Plan hash value: 3423681500

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

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

| 0| SELECT STATEMENT | | 637K| 92M| 6643 (1)| 00:01:20 |

|* 1| FILTER | | | | | |

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

|* 3| FILTER | | | | | |

| 4| COUNT | | | | | |

|* 5| HASH JOIN | | 637K| 92M| 6643 (1)| 00:01:20 |

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

| 7| TABLE ACCESS FULL | DMS_DATA_RELA | 637K| 83M| 6636 (1)| 00:01:20 |

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

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

|* 10 | HASH JOIN | | | | | |

| 11| CONNECT BY PUMP | | | | | |

| 12| COUNT | | | | | |

|* 13 | HASH JOIN | | 637K| 92M| 6643 (1)| 00:01:20 |

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

| 15| TABLE ACCESS FULL | DMS_DATA_RELA | 637K| 83M| 6636 (1)| 00:01:20 |

| 16| COUNT | | | | | |

|* 17 | HASH JOIN | | 637K| 92M| 6643 (1)| 00:01:20 |

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

| 19| TABLE ACCESS FULL | DMS_DATA_RELA | 637K| 83M| 6636 (1)| 00:01:20 |

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

|* 21 | 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'))

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

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

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

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

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

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

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

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

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

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

统计信息

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

1 recursive calls

0 db block gets

2103709 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

代入变量值，加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.

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

(责任编辑：IT)