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

« Oracle9i新特性：iSQLPLUS | Blog首页 | 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就不需要这一计算步骤，它只需要使用我们指定的
顺序，然后快速的给出结果。然后问题迎刃而解。

下一页>>

历史上的今天...
>> 2021-10-30文章:

openGauss 数据库免费实训平台：墨天轮云主机随时测

openGauss 入门指南：这些文档你不能错过

>> 2011-10-30文章:

欧洲纪行 - 2011年10月1-6日巴黎游记 & 攻略

>> 2009-10-30文章:

了解健康 - PSA,FPSA、TPSA是什么涵义

美东印象之尼亚加拉瀑布 - 浩荡之水天上来

>> 2008-10-30文章:

Oracle DBA 入门、进阶学习路线图

>> 2007-10-30文章: