eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Oracle9i新特性:iSQLPLUS | Blog首页 | CBO对于Oracle SQL执行计划的影响(之二) »

CBO对于Oracle SQL执行计划的影响(之一)

1. 原始SQL语句

这个SQL语句是一个动态查询语句的一部分,该查询根据不同条件生成不同的SQL语句。
本例为查询2003年以来的入库单据,很少的数据。

 

SELECT "SP_TRANS"."TRANS_NO",   
         "SP_TRANS"."TRANS_TYPE",   
         "SP_TRANS"."STORE_NO",   
         "SP_TRANS"."BILL_NO",   
         "SP_TRANS"."TRANSDATE",   
         "SP_TRANS"."MANAGER_ID",   
         "SP_TRANS"."REMARK",   
         "SP_TRANS"."STATE",   
         "SP_TRANS_SUB"."TRANS_NO",   
         "SP_TRANS_SUB"."ITEM_CODE",   
         "SP_TRANS_SUB"."COUNTRY",   
         "SP_TRANS_SUB"."QTY",   
         "SP_TRANS_SUB"."PRICE",   
         "SP_TRANS_SUB"."TOTAL",   
         "SP_CHK"."CHK_NO",   
         "SP_CHK"."RECEIVE_NO",   
         "SP_CHK"."CHECKER",   
         "SP_CHK_SUB"."CHK_NO",   
         "SP_CHK_SUB"."ITEM_CODE",   
         "SP_CHK_SUB"."COUNTRY",   
         "SP_CHK_SUB"."PLAN_NO",   
         "SP_CHK_SUB"."PLAN_LINE",   
         "SP_CHK_SUB"."QTY_CHECKOUT",
    "SP_CHK_SUB"."NOW_QTY",   
         "SP_RECEIVE"."RECEIVE_NO",   
         "SP_RECEIVE"."VENDOR_NAME",   
         "SP_RECEIVE"."BUYER",   
         "SP_RECEIVE_SUB"."RECEIVE_NO",   
         "SP_RECEIVE_SUB"."PLAN_NO",   
         "SP_RECEIVE_SUB"."PLAN_LINE",   
         "SP_RECEIVE_SUB"."ITEM_NAME",   
         "SP_RECEIVE_SUB"."COUNTRY",
    "SP_ITEM"."ITEM_CODE",
    "SP_ITEM"."CHART_ID",
    "SP_ITEM"."SPECIFICATION"  
    FROM "SP_TRANS",
          "SP_TRANS_SUB",
     "SP_CHK",
          "SP_CHK_SUB",
          "SP_RECEIVE",
          "SP_RECEIVE_SUB",
     "SP_ITEM"
   WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
    ("SP_TRANS"."BILL_NO" = "SP_CHK"."CHK_NO") and
   ( "SP_CHK_SUB"."CHK_NO" = "SP_CHK"."CHK_NO" ) and  
            ( "SP_CHK"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and
   ( "SP_CHK"."STATE" = 15 ) and
            ( "SP_RECEIVE_SUB"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and  
   ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_ITEM"."ITEM_CODE" ) and
   ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_CHK_SUB"."ITEM_CODE" ) and  
            ( "SP_CHK_SUB"."ITEM_CODE" = "SP_RECEIVE_SUB"."ITEM_CODE" ) and  
            ( "SP_CHK_SUB"."COUNTRY" = "SP_TRANS_SUB"."COUNTRY" ) and  
            ( "SP_CHK_SUB"."COUNTRY" = "SP_RECEIVE_SUB"."COUNTRY" ) and 
   ( "SP_CHK_SUB"."PLAN_NO" = "SP_RECEIVE_SUB"."PLAN_NO" ) and
   ( "SP_CHK_SUB"."PLAN_LINE" = "SP_RECEIVE_SUB"."PLAN_LINE" ) and
            (to_char("SP_TRANS"."TRANSDATE" ,'YYYY-MM-DD') >='2003-01-01')
/
                      

2. 执行计划
我们的数据库使用dbms_stats.gather_schema_stats分析过,具有足够及时的所有数据,然而在CBO的执行计划下,优化器选择了完全
不同的执行计划.
a. no hints
这是未加任何提示时,Oralce选择的执行路径,在实际程序中,用户说死掉了,通过执行计划我们知道,不是死掉了,是慢!!!

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2057 Card=1 Bytes=288)
   1    0   NESTED LOOPS (Cost=2057 Card=1 Bytes=288)
   2    1     NESTED LOOPS (Cost=2056 Card=1 Bytes=256)
   3    2       NESTED LOOPS (Cost=2054 Card=1 Bytes=219)
   4    3         NESTED LOOPS (Cost=2053 Card=1 Bytes=178)
   5    4           NESTED LOOPS (Cost=2009 Card=1 Bytes=131)
   6    5             MERGE JOIN (Cost=2008 Card=1 Bytes=100)
   7    6               SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776)
   8    7                 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)
   9    6               SORT (JOIN) (Cost=1058 Card=36730 Bytes=1909960)
  10    9                 TABLE ACCESS (FULL) OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960)
  11    5             TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK' (Cost=1 Card=3870 Bytes=119970)
  12   11               INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)
  13    4           TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)
  14    3         TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456)
  15   14           INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
  16    2       TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost=2 Card=136371 Bytes=5045727)
  17   16         INDEX (UNIQUE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Cost=1 Card=136371)
  18    1     TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)
  19   18       INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)
       

用足够的耐心,我们得到了该计划的执行结果。

 

SQL>   SELECT "SP_TRANS"."TRANS_NO",   
  2           "SP_TRANS"."TRANS_TYPE",   
  3           "SP_TRANS"."STORE_NO",   
  4           "SP_TRANS"."BILL_NO",   
  5           "SP_TRANS"."TRANSDATE",   
  6           "SP_TRANS"."MANAGER_ID",   
  7           "SP_TRANS"."REMARK",   
  8           "SP_TRANS"."STATE",   
  9           "SP_TRANS_SUB"."TRANS_NO",   
 10           "SP_TRANS_SUB"."ITEM_CODE",   
 11           "SP_TRANS_SUB"."COUNTRY",   
 12           "SP_TRANS_SUB"."QTY",   
 13           "SP_TRANS_SUB"."PRICE",   
 14           "SP_TRANS_SUB"."TOTAL",   
 15           "SP_CHK"."CHK_NO",   
 16           "SP_CHK"."RECEIVE_NO",   
 17           "SP_CHK"."CHECKER",   
 18           "SP_CHK_SUB"."CHK_NO",   
 19           "SP_CHK_SUB"."ITEM_CODE",   
 20           "SP_CHK_SUB"."COUNTRY",   
 21           "SP_CHK_SUB"."PLAN_NO",   
 22           "SP_CHK_SUB"."PLAN_LINE",   
 23           "SP_CHK_SUB"."QTY_CHECKOUT",
 24           "SP_CHK_SUB"."NOW_QTY",   
 25           "SP_RECEIVE"."RECEIVE_NO",   
 26           "SP_RECEIVE"."VENDOR_NAME",   
 27           "SP_RECEIVE"."BUYER",   
 28           "SP_RECEIVE_SUB"."RECEIVE_NO",   
 29           "SP_RECEIVE_SUB"."PLAN_NO",   
 30           "SP_RECEIVE_SUB"."PLAN_LINE",   
 31           "SP_RECEIVE_SUB"."ITEM_NAME",   
 32           "SP_RECEIVE_SUB"."COUNTRY",
 33           "SP_ITEM"."ITEM_CODE",
 34           "SP_ITEM"."CHART_ID",
 35           "SP_ITEM"."SPECIFICATION"  
 36      FROM "SP_TRANS",
 37           "SP_TRANS_SUB",
 38           "SP_CHK",
 39           "SP_CHK_SUB",
 40           "SP_RECEIVE",
 41           "SP_RECEIVE_SUB",
 42           "SP_ITEM"
 43     WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
 44           ( "SP_TRANS"."BILL_NO" = "SP_CHK"."CHK_NO") and
 45           ( "SP_CHK_SUB"."CHK_NO" = "SP_CHK"."CHK_NO" ) and  
 46           ( "SP_CHK"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and
 47           ( "SP_CHK"."STATE" = 15 ) and
 48           ( "SP_RECEIVE_SUB"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and  
 49           ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_ITEM"."ITEM_CODE" ) and
 50           ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_CHK_SUB"."ITEM_CODE" ) and  
 51           ( "SP_CHK_SUB"."ITEM_CODE" = "SP_RECEIVE_SUB"."ITEM_CODE" ) and  
 52           ( "SP_CHK_SUB"."COUNTRY" = "SP_TRANS_SUB"."COUNTRY" ) and  
 53           ( "SP_CHK_SUB"."COUNTRY" = "SP_RECEIVE_SUB"."COUNTRY" ) and 
 54           ( "SP_CHK_SUB"."PLAN_NO" = "SP_RECEIVE_SUB"."PLAN_NO" ) and
 55           ( "SP_CHK_SUB"."PLAN_LINE" = "SP_RECEIVE_SUB"."PLAN_LINE" ) and
 56           (to_char("SP_TRANS"."TRANSDATE" ,'YYYY-MM-DD') >='2003-01-01')
 57  /

130 rows selected.

Elapsed:  00: 29: 1785.47

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2057 Card=1 Bytes=288)
   1    0   NESTED LOOPS (Cost=2057 Card=1 Bytes=288)
   2    1     NESTED LOOPS (Cost=2056 Card=1 Bytes=256)
   3    2       NESTED LOOPS (Cost=2054 Card=1 Bytes=219)
   4    3         NESTED LOOPS (Cost=2053 Card=1 Bytes=178)
   5    4           NESTED LOOPS (Cost=2009 Card=1 Bytes=131)
   6    5             MERGE JOIN (Cost=2008 Card=1 Bytes=100)
   7    6               SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776)
   8    7                 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)
   9    6               SORT (JOIN) (Cost=1058 Card=36730 Bytes=1909960)
  10    9                 TABLE ACCESS (FULL) OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960)
  11    5             TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK' (Cost=1 Card=3870 Bytes=119970)
  12   11               INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)
  13    4           TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)
  14    3         TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456)
  15   14           INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
  16    2       TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost=2 Card=136371 Bytes=5045727)
  17   16         INDEX (UNIQUE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Cost=1 Card=136371)
  18    1     TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)
  19   18       INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)




Statistics
----------------------------------------------------------
         16  recursive calls
     186307  db block gets
   10685361  consistent gets
       2329  physical reads
          0  redo size
      38486  bytes sent via SQL*Net to client
       1117  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          7  sorts (memory)
          2  sorts (disk)
        130  rows processed

      

 

可以看到,该执行计划消耗了大量的资源以及时间,这种情况是无法忍受的。

b. rule
在RBO条件下,该语句是执行很快的
加入rule提示,我们得到以下执行计划:

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         NESTED LOOPS
   5    4           NESTED LOOPS
   6    5             NESTED LOOPS
   7    6               TABLE ACCESS (FULL) OF 'SP_TRANS_SUB'
   8    6               TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM'
   9    8                 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)
  10    5             TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS'
  11   10               INDEX (UNIQUE SCAN) OF 'PK_HSP_TRANS' (UNIQUE)
  12    4           TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK'
  13   12             INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)
  14    3         TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE'
  15   14           INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
  16    2       TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK_SUB'
  17   16         INDEX (RANGE SCAN) OF 'IDX_CHK_SUB_ITEM_CODE' (NON-UNIQUE)
  18    1     TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB'
  19   18       INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE)
      

执行该计划,我们得到以下输出:

 

SQL>@sql

130 rows selected.

Elapsed:  00: 00: 12.17

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         NESTED LOOPS
   5    4           NESTED LOOPS
   6    5             NESTED LOOPS
   7    6               TABLE ACCESS (FULL) OF 'SP_TRANS_SUB'
   8    6               TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM'
   9    8                 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)
  10    5             TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS'
  11   10               INDEX (UNIQUE SCAN) OF 'PK_HSP_TRANS' (UNIQUE)
  12    4           TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK'
  13   12             INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)
  14    3         TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE'
  15   14           INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
  16    2       TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK_SUB'
  17   16         INDEX (RANGE SCAN) OF 'IDX_CHK_SUB_ITEM_CODE' (NON-UNIQUE)
  18    1     TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB'
  19   18       INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          6  db block gets
     829182  consistent gets
          0  physical reads
          0  redo size
      37383  bytes sent via SQL*Net to client
       1127  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        130  rows processed

SQL> 

      

c. ordered
然后我想起了Ordered提示
使用该提示的执行计划如下:

 

SQL>@sql

已选择130行。

已用时间:  00: 00: 05.67


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3284 Card=1 Bytes=288)
   1    0   NESTED LOOPS (Cost=3284 Card=1 Bytes=288)
   2    1     NESTED LOOPS (Cost=3283 Card=1 Bytes=256)
   3    2       MERGE JOIN (Cost=3282 Card=1 Bytes=204)
   4    3         SORT (JOIN) (Cost=2333 Card=6823 Bytes=1064388)
   5    4           HASH JOIN (Cost=1848 Card=6823 Bytes=1064388)
   6    5             HASH JOIN (Cost=216 Card=1717 Bytes=204323)
   7    6               HASH JOIN (Cost=96 Card=1717 Bytes=133926)
   8    7                 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)
   9    7                 TABLE ACCESS (FULL) OF 'SP_CHK' (Cost=13 Card=3870 Bytes=119970)
  10    6               TABLE ACCESS (FULL) OF 'SP_RECEIVE' (Cost=17 Card=7816 Bytes=320456)
  11    5             TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=155 Card=136371 Bytes=5045727)
  12    3         SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776)
  13   12           TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)
  14    2       TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB' (Cost=1 Card=36730 Bytes=1909960)
  15   14         INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE)
  16    1     TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)
  17   16       INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)

Statistics
----------------------------------------------------------
          8  recursive calls
         88  db block gets
       2667  consistent gets
       1093  physical reads
          0  redo size
      37285  bytes sent via SQL*Net to client
       1109  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          8  sorts (memory)
          1  sorts (disk)
        130  rows processed

SQL>
      

很幸运,Ordered提示使Oracle选择了较好的执行计划。

所以会产生这样的效果,是因为在CBO的执行计划中,对于7张数据表,Oracle需要计算7!(5040)个连接顺序,然后比较各个顺序的
成本,最后选择成本较低的执行计划。
显然,在这一判断上耗费了大量的时间。当我们使用ordered hints的时候,Oracle就不需要这一计算步骤,它只需要使用我们指定的
顺序,然后快速的给出结果。然后问题迎刃而解。

 

下一页>>

 


历史上的今天...
    >> 2011-10-30文章:
    >> 2009-10-30文章:
    >> 2008-10-30文章:
    >> 2007-10-30文章:
    >> 2006-10-30文章:
    >> 2005-10-30文章:
           Lilina与李敖
           穿越15年的记忆

无觅

By eygle on 2004-10-30 09:25 | Comments (0) | SQL.PLSQL | 78 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com