一条sql求助优化

yyp2009

接到一个sql如下：
SELECT TREE2.MAXLVL,
      TREE.LVL,
      TREE.CATNAME,
      TREE.PARENTNAME,
      TREE.DESCRIPTION,
      TREE.DEPARTMENT,
      TREE.GROUPNAME,
      NODE.SERVICENAME,
      NODE.STAGEID,
      NODE.STAGEINDEX
FROM
      ( SELECT LEVEL LVL,
            CATNAME,
            PARENTNAME,
            DESCRIPTION,
            GROUPNAME,
            DEPARTMENT
      FROM WMS_WIZARDCAT                                 
      WHERE GROUPNAME IN
            ( SELECT GROUPNAME
            FROM WMS_STAGEWIZARD                             
            WHERE WIZARDID = :1 ) AND
            DEPARTMENT = :2 AND
            STATUS = :3
      START WITH PARENTNAME = :4
      CONNECT BY PARENTNAME =PRIOR CATNAME AND
            GROUPNAME = PRIOR GROUPNAME AND
            DEPARTMENT=PRIOR DEPARTMENT ) TREE,
      ( SELECT MAX(LEVEL) MAXLVL
      FROM WMS_WIZARDCAT                                      
      WHERE GROUPNAME IN
            ( SELECT GROUPNAME
            FROM WMS_STAGEWIZARD                           
            WHERE WIZARDID = :5 ) AND
            DEPARTMENT = :6 AND
            STATUS = :7
      START WITH PARENTNAME = :8
      CONNECT BY PARENTNAME =PRIOR CATNAME ) TREE2,
      WMS_WIZARDNODE NODE                                   
WHERE TREE.CATNAME = NODE.SERVICENAME(+) AND
      TREE.GROUPNAME = NODE.GROUPNAME(+) AND
      NODE.DEPARTMENT(+) = :9 AND
      NODE.STATUS(+) = :10
ORDER BY LVL,CATNAME,STAGEINDEX
由此语句的统计信息为依据，oracle执行路径cost如下：
Plan
SELECT STATEMENT  CHOOSE  Cost: 27  Bytes: 302  Cardinality: 1         

29 SORT ORDER BY  Cost: 27  Bytes: 302  Cardinality: 1         
  28 MERGE JOIN CARTESIAN  Cost: 22  Bytes: 302  Cardinality: 1         
   15 NESTED LOOPS OUTER  Cost: 2  Bytes: 289  Cardinality: 1        
    12 VIEW WMSUSERN. Cost: 1  Bytes: 210  Cardinality: 1      
     11 FILTER  EXISTS (SELECT /*+ */ 0 FROM "WMSUSERN"."DB_WMS_STAGEWIZARD"
"WMS_STAGEWIZARD" WHERE "WMS_STAGEWIZARD"."GROUPNAME"=:B1 AND "WMS_STAGEWIZARD"."WIZARDID"=TO_NUMBER(:Z)) AND
"SYS_ALIAS_1"."DEPARTMENT"=:Z AND "SYS_ALIAS_1"."STATUS"=:Z   
      9 CONNECT BY WITH FILTERING  "SYS_ALIAS_1"."PARENTNAME"=:Z   
       2 FILTER  "SYS_ALIAS_1"."PARENTNAME"=:Z  
        1 TABLE ACCESS FULL WMSUSERN.DB_WMS_WIZARDCAT  
       7 NESTED LOOPS   
        4 BUFFER SORT  Bytes: 199  Cardinality: 1   
         3 CONNECT BY PUMP  
        6 TABLE ACCESS BY INDEX ROWID WMSUSERN.DB_WMS_WIZARDCAT
"SYS_ALIAS_1"."PARENTNAME"=NULLCost: 1  Bytes: 199  Cardinality: 1   
         5 INDEX RANGE SCAN WMSUSERN.PK_WMS_WIZARDCAT
"SYS_ALIAS_1"."GROUPNAME"=NULL AND "SYS_ALIAS_1"."DEPARTMENT"=NULL "SYS_ALIAS_1"."DEPARTMENT"=NULLCost: 2  Cardinality: 1

       8 TABLE ACCESS FULL WMSUSERN.DB_WMS_WIZARDCAT   
      10 INDEX UNIQUE SCAN WMSUSERN.PK_WMS_STAGEWIZARD
"WMS_STAGEWIZARD"."WIZARDID"=TO_NUMBER(:Z) AND "WMS_STAGEWIZARD"."GROUPNAME"=:B1Cost: 1  Bytes: 23  Cardinality: 1   

    14 TABLE ACCESS BY INDEX ROWID WMSUSERN.DB_WMS_WIZARDNODE "NODE"."STATUS"(+)=:ZCost: 1  
Bytes: 79  Cardinality: 1      
     13 INDEX RANGE SCAN WMSUSERN.PK_WMS_WIZARDNODE
"TREE"."CATNAME"="NODE"."SERVICENAME"(+) AND "TREE"."GROUPNAME"="NODE"."GROUPNAME"(+) AND "NODE"."DEPARTMENT"(+)=:Z
"NODE"."DEPARTMENT"(+)=:ZCost: 1  Cardinality: 1      
   27 BUFFER SORT  Cost: 26  Bytes: 13  Cardinality: 1        
    26 VIEW WMSUSERN. Cost: 20  Bytes: 13  Cardinality: 1      
     25 SORT AGGREGATE  Bytes: 70  Cardinality: 1      
      24 FILTER  EXISTS (SELECT /*+ */ 0 FROM "WMSUSERN"."DB_WMS_STAGEWIZARD"
"WMS_STAGEWIZARD" WHERE "WMS_STAGEWIZARD"."GROUPNAME"=:B1 AND "WMS_STAGEWIZARD"."WIZARDID"=TO_NUMBER(:Z)) AND
"SYS_ALIAS_1"."DEPARTMENT"=:Z AND "SYS_ALIAS_1"."STATUS"=:Z   
       22 CONNECT BY WITH FILTERING  "SYS_ALIAS_1"."PARENTNAME"=:Z  
        17 FILTER  "SYS_ALIAS_1"."PARENTNAME"=:Z
         16 TABLE ACCESS FULL WMSUSERN.DB_WMS_WIZARDCAT
Cost: 20  Bytes: 580,440  Cardinality: 8,292  
        20 HASH JOIN   
         18 CONNECT BY PUMP  
         19 TABLE ACCESS FULL WMSUSERN.DB_WMS_WIZARDCAT
Cost: 20  Bytes: 580,440  Cardinality: 8,292  
        21 TABLE ACCESS FULL WMSUSERN.DB_WMS_WIZARDCAT Cost: 20  
Bytes: 580,440  Cardinality: 8,292   
       23 INDEX UNIQUE SCAN WMSUSERN.PK_WMS_STAGEWIZARD
"WMS_STAGEWIZARD"."WIZARDID"=TO_NUMBER(:Z) AND "WMS_STAGEWIZARD"."GROUPNAME"=:B1Cost: 1  Bytes: 23  Cardinality: 1   
调整后的sql如下；
SELECT /*+ ORDERED */ TREE2.MAXLVL,
       TREE.LVL,
       TREE.CATNAME,
       TREE.PARENTNAME,
       TREE.DESCRIPTION,
       TREE.DEPARTMENT,
       TREE.GROUPNAME,
       NODE.SERVICENAME,
       NODE.STAGEID,
       NODE.STAGEINDEX
  FROM (SELECT LEVEL LVL,
               CATNAME,
               PARENTNAME,
               DESCRIPTION,
               GROUPNAME,
               DEPARTMENT
          FROM WMS_WIZARDCAT WMS_WIZARDCAT1
         WHERE GROUPNAME IN (SELECT GROUPNAME
                               FROM WMS_STAGEWIZARD WMS_STAGEWIZARD1
                              WHERE WIZARDID = :1
                              GROUP BY GROUPNAME)
           AND DEPARTMENT = :2
           AND STATUS = :3
        START WITH PARENTNAME = :4
        CONNECT BY PARENTNAME = PRIOR CATNAME
           AND GROUPNAME = PRIOR GROUPNAME
           AND DEPARTMENT = PRIOR DEPARTMENT) TREE,
       (SELECT MAX(LEVEL) MAXLVL
          FROM WMS_WIZARDCAT WMS_WIZARDCAT2
         WHERE GROUPNAME IN (SELECT GROUPNAME || ''
                               FROM WMS_STAGEWIZARD WMS_STAGEWIZARD2
                              WHERE WIZARDID = :5)
           AND DEPARTMENT = :6
           AND STATUS = :7
        START WITH PARENTNAME = :8
        CONNECT BY PARENTNAME = PRIOR CATNAME) TREE2,
       WMS_WIZARDNODE NODE
WHERE TREE.CATNAME = NODE.SERVICENAME (+)
   AND TREE.GROUPNAME = NODE.GROUPNAME (+)
   AND NODE.DEPARTMENT (+) = :9
   AND NODE.STATUS (+) = :10
ORDER BY LVL, CATNAME, STAGEINDEX
调整后的执行路径和cost信息如下：
Plan
SELECT STATEMENT  ALL_ROWS Cost: 8  Bytes: 302  Cardinality: 1         
   
31 SORT ORDER BY  Cost: 8  Bytes: 302  Cardinality: 1           

  30 NESTED LOOPS OUTER  Cost: 3  Bytes: 302  Cardinality: 1         
  
   27 MERGE JOIN CARTESIAN  Cost: 2  Bytes: 223  Cardinality: 1        
  
    12 VIEW WMSUSERN. Cost: 1  Bytes: 210  Cardinality: 1        

     11 FILTER  EXISTS (SELECT /*+ */ 0 FROM "WMSUSERN"."DB_WMS_STAGEWIZARD"
"WMS_STAGEWIZARD" WHERE "WMS_STAGEWIZARD"."GROUPNAME"=:B1 AND "WMS_STAGEWIZARD"."WIZARDID"=TO_NUMBER(:Z)) AND
"SYS_ALIAS_1"."DEPARTMENT"=:Z AND "SYS_ALIAS_1"."STATUS"=:Z      
      9 CONNECT BY WITH FILTERING  "SYS_ALIAS_1"."PARENTNAME"=:Z   
  
       2 TABLE ACCESS BY INDEX ROWID WMSUSERN.DB_WMS_WIZARDCAT   
  
        1 INDEX RANGE SCAN WMSUSERN.QUEST_SX_IDXA7029CC8162391959
"WMS_WIZARDCAT"."PARENTNAME"=:ZCost: 1  Bytes: 1,411  Cardinality: 83     
       7 NESTED LOOPS      
        4 BUFFER SORT  Bytes: 199  Cardinality: 1     
         3 CONNECT BY PUMP   
        6 TABLE ACCESS BY INDEX ROWID WMSUSERN.DB_WMS_WIZARDCAT
"SYS_ALIAS_1"."PARENTNAME"=NULLCost: 1  Bytes: 199  Cardinality: 1     
         5 INDEX RANGE SCAN WMSUSERN.PK_WMS_WIZARDCAT
"SYS_ALIAS_1"."GROUPNAME"=NULL AND "SYS_ALIAS_1"."DEPARTMENT"=NULL "SYS_ALIAS_1"."DEPARTMENT"=NULLCost: 2  Cardinality: 1
   
       8 TABLE ACCESS FULL WMSUSERN.DB_WMS_WIZARDCAT     
      10 INDEX UNIQUE SCAN WMSUSERN.PK_WMS_STAGEWIZARD
"WMS_STAGEWIZARD"."WIZARDID"=TO_NUMBER(:Z) AND "WMS_STAGEWIZARD"."GROUPNAME"=:B1Cost: 1  Bytes: 23  Cardinality: 1   
   
    26 BUFFER SORT  Cost: 2  Bytes: 13  Cardinality: 1        

     25 VIEW WMSUSERN. Cost: 1  Bytes: 13  Cardinality: 1      

      24 SORT AGGREGATE  Bytes: 70  Cardinality: 1      
       23 FILTER  EXISTS (SELECT /*+ */ 0 FROM
"WMSUSERN"."DB_WMS_STAGEWIZARD" "WMS_STAGEWIZARD" WHERE "WMS_STAGEWIZARD"."GROUPNAME"=:B1 AND
"WMS_STAGEWIZARD"."WIZARDID"=TO_NUMBER(:Z)) AND "SYS_ALIAS_1"."DEPARTMENT"=:Z AND "SYS_ALIAS_1"."STATUS"=:Z   

        21 CONNECT BY WITH FILTERING  
"SYS_ALIAS_1"."PARENTNAME"=:Z   
         14 TABLE ACCESS BY INDEX ROWID
WMSUSERN.DB_WMS_WIZARDCAT   
          13 INDEX RANGE SCAN
WMSUSERN.QUEST_SX_IDXA7029CC8162391959 "WMS_WIZARDCAT"."PARENTNAME"=:ZCost: 1  Bytes: 1,411  Cardinality: 83   
         19 NESTED LOOPS   
          16 BUFFER SORT  Bytes: 70  Cardinality: 1
  
           15 CONNECT BY PUMP  
          18 TABLE ACCESS BY INDEX ROWID
WMSUSERN.DB_WMS_WIZARDCAT Cost: 1  Bytes: 5,810  Cardinality: 83   
           17 INDEX RANGE SCAN
WMSUSERN.QUEST_SX_IDXA7029CC8162391959 "SYS_ALIAS_1"."PARENTNAME"=NULLCost: 1  Cardinality: 62  
         20 TABLE ACCESS FULL WMSUSERN.DB_WMS_WIZARDCAT   
        22 INDEX UNIQUE SCAN WMSUSERN.PK_WMS_STAGEWIZARD
"WMS_STAGEWIZARD"."WIZARDID"=TO_NUMBER(:Z) AND "WMS_STAGEWIZARD"."GROUPNAME"=:B1Cost: 1  Bytes: 23  Cardinality: 1   

   29 TABLE ACCESS BY INDEX ROWID WMSUSERN.DB_WMS_WIZARDNODE "NODE"."STATUS"(+)=:ZCost: 1  Bytes: 79
Cardinality: 1         
    28 INDEX RANGE SCAN WMSUSERN.PK_WMS_WIZARDNODE "TREE"."CATNAME"="NODE"."SERVICENAME"(+)
AND "TREE"."GROUPNAME"="NODE"."GROUPNAME"(+) AND "NODE"."DEPARTMENT"(+)=:Z "NODE"."DEPARTMENT"(+)=:ZCost: 1  Cardinality:
1         

但是把绑定变量换做具体的参数和数据时  consistent gets仍然很大历时16s多（公司要求2s左右我也是刚开始做很为难！）：
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=302)
   1    0   SORT (ORDER BY) (Cost=8 Card=1 Bytes=302)
   2    1     NESTED LOOPS (OUTER) (Cost=3 Card=1 Bytes=302)
   3    2       MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=223)
   4    3         VIEW (Cost=1 Card=1 Bytes=210)
   5    4           FILTER
   6    5             CONNECT BY (WITH FILTERING)
   7    6               TABLE ACCESS (BY INDEX ROWID) OF 'DB_WMS_WIZARDCAT'
   8    7                 INDEX (RANGE SCAN) OF 'YP_WMS_WIZARDCAT' (NON-UNIQUE) (Cost=1 Ca
          rd=689 Bytes=11713)
   9    6               NESTED LOOPS
  10    9                 BUFFER (SORT)
  11   10                   CONNECT BY PUMP
  12    9                 TABLE ACCESS (BY INDEX ROWID) OF 'DB_WMS_WIZARDCAT' (Cost=1 Card
          =1 Bytes=199)
  13   12                   INDEX (RANGE SCAN) OF 'PK_WMS_WIZARDCAT' (UNIQUE) (Cost=2 Card
          =1)
  14    6               TABLE ACCESS (FULL) OF 'DB_WMS_WIZARDCAT'
  15    5             FILTER
  16   15               SORT (GROUP BY NOSORT) (Cost=1 Card=1 Bytes=23)
  17   16                 INDEX (RANGE SCAN) OF 'PK_WMS_STAGEWIZARD' (UNIQUE) (Cost=2 Card
          =1 Bytes=23)
  18    3         BUFFER (SORT) (Cost=2 Card=1 Bytes=13)
  19   18           VIEW (Cost=1 Card=1 Bytes=13)
  20   19             SORT (AGGREGATE)
  21   20               FILTER
  22   21                 CONNECT BY (WITH FILTERING)
  23   22                   TABLE ACCESS (BY INDEX ROWID) OF 'DB_WMS_WIZARDCAT'
  24   23                     INDEX (RANGE SCAN) OF 'YP_WMS_WIZARDCAT' (NON-UNIQUE) (Cost=
          1 Card=689 Bytes=11713)
  25   22                   NESTED LOOPS
  26   25                     BUFFER (SORT)
  27   26                       CONNECT BY PUMP
  28   25                     TABLE ACCESS (BY INDEX ROWID) OF 'DB_WMS_WIZARDCAT' (Cost=1
          Card=83 Bytes=5810)
  29   28                       INDEX (RANGE SCAN) OF 'YP_WMS_WIZARDCAT' (NON-UNIQUE) (Cos
          t=1 Card=62)
  30   22                   TABLE ACCESS (FULL) OF 'DB_WMS_WIZARDCAT'
  31   21                 INDEX (UNIQUE SCAN) OF 'PK_WMS_STAGEWIZARD' (UNIQUE) (Cost=1 Car
          d=1 Bytes=23)
  32    2       TABLE ACCESS (BY INDEX ROWID) OF 'DB_WMS_WIZARDNODE' (Cost=1 Card=1 Bytes=
          79)
  33   32         INDEX (RANGE SCAN) OF 'PK_WMS_WIZARDNODE' (UNIQUE) (Cost=1 Card=1)


Statistics
----------------------------------------------------------
         25  recursive calls
          0  db block gets
     635757  consistent gets
          0  physical reads
          0  redo size
       1455  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         29  sorts (memory)
          0  sorts (disk)
          5  rows processed







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

yyp2009

渴望大家热心帮助帮忙看看！谢谢！

棉花糖ONE

去掉hint,先把统计信息收集一遍

sandao381

大神不是说2s 响应么 ？后来咋样了 ？能回想起来么 树形结构的优化 好无奈