一次分析的全过程,和大家交流!

eygle

我们的程序员抱怨一段执行很慢的程序，我把代码执行分析执行计划，
后果如下：

这是最初的执行效果及执行计划

SQL> SELECT "SP_TRANS"."TRANS_NO",
  2 "SP_TRANS_SUB"."ITEM_CODE",
  3 "SP_ITEM"."ITEM_NAME",
  4 "SP_ITEM"."CHART_ID",
  5 "SP_ITEM"."SPECIFICATION",
  6 "SP_TRANS_SUB"."COUNTRY",
  7 "SP_TRANS_SUB"."QTY",
  8 "SP_TRANS_SUB"."PRICE",
  9 "SP_TRANS"."VENDOR_CODE",
10 "SP_TRANS"."PAY_MODE",
11 NVL("SP_TRANS_SUB"."PAY_QTY",0),
12 0 as PAY_THIS
13 FROM "SP_ITEM",
14 "SP_TRANS_SUB",
15 "SP_TRANS"
16 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
17 ( "SP_ITEM"."ITEM_CODE" = "SP_TRANS_SUB"."ITEM_CODE" ) and
18 ( ( "SP_TRANS"."VENDOR_CODE" = '20011021023') )
19 /

8 rows selected.

Elapsed: 00: 00: 00.51

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE
   1 0 NESTED LOOPS
   2 1 NESTED LOOPS
   3 2 TABLE ACCESS (FULL) OF 'SP_TRANS'
   4 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB'
   5 4 INDEX (RANGE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE)
   6 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM'
   7 6 INDEX (UNIQUE SCAN) OF 'PK_SP_ITEM' (UNIQUE)

Statistics
----------------------------------------------------------
          0 recursive calls
          4 db block gets
        323 consistent gets
          0 physical reads
          0 redo size
       1809 bytes sent via SQL*Net to client
        425 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          8 rows processed

eygle

此前这几个表都没有分析过。

然后我analyze相关表
SQL> analyze table sp_trans_sub compute statistics;

Table analyzed.

Elapsed: 00: 00: 30.64
SQL> SELECT "SP_TRANS"."TRANS_NO",
  2 "SP_TRANS_SUB"."ITEM_CODE",
  3 "SP_ITEM"."ITEM_NAME",
  4 "SP_ITEM"."CHART_ID",
  5 "SP_ITEM"."SPECIFICATION",
  6 "SP_TRANS_SUB"."COUNTRY",
  7 "SP_TRANS_SUB"."QTY",
  8 "SP_TRANS_SUB"."PRICE",
  9 "SP_TRANS"."VENDOR_CODE",
10 "SP_TRANS"."PAY_MODE",
11 NVL("SP_TRANS_SUB"."PAY_QTY",0),
12 0 as PAY_THIS
13 FROM "SP_ITEM",
14 "SP_TRANS_SUB",
15 "SP_TRANS"
16 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
17 ( "SP_ITEM"."ITEM_CODE" = "SP_TRANS_SUB"."ITEM_CODE" ) and
18 ( ( "SP_TRANS"."VENDOR_CODE" = '20011021023') )
19 /

8 rows selected.

Elapsed: 00: 00: 06.49

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18577 Card=126726520
           Bytes=30034185240)

   1 0 MERGE JOIN (Cost=18577 Card=126726520 Bytes=30034185240)
   2 1 SORT (JOIN) (Cost=14722 Card=310300 Bytes=20790100)
   3 2 HASH JOIN (Cost=358 Card=310300 Bytes=20790100)
   4 3 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=43 Card=229
          Bytes=8473)

   5 3 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=158 Card
          =135502 Bytes=4065060)

   6 1 SORT (JOIN) (Cost=3855 Card=40840 Bytes=6942800)
   7 6 TABLE ACCESS (FULL) OF 'SP_ITEM' (Cost=77 Card=40840 B
          ytes=6942800)

Statistics
----------------------------------------------------------
        150 recursive calls
         89 db block gets
       1837 consistent gets
        755 physical reads
         60 redo size
       1732 bytes sent via SQL*Net to client
        425 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          4 sorts (memory)
          1 sorts (disk)
          8 rows processed

SQL>

SQL> analyze table sp_trans compute statistics;

Table analyzed.

Elapsed: 00: 00: 13.00
SQL>
SQL> SELECT "SP_TRANS"."TRANS_NO",
  2 "SP_TRANS_SUB"."ITEM_CODE",
  3 "SP_ITEM"."ITEM_NAME",
  4 "SP_ITEM"."CHART_ID",
  5 "SP_ITEM"."SPECIFICATION",
  6 "SP_TRANS_SUB"."COUNTRY",
  7 "SP_TRANS_SUB"."QTY",
  8 "SP_TRANS_SUB"."PRICE",
  9 "SP_TRANS"."VENDOR_CODE",
10 "SP_TRANS"."PAY_MODE",
11 NVL("SP_TRANS_SUB"."PAY_QTY",0),
12 0 as PAY_THIS
13 FROM "SP_ITEM",
14 "SP_TRANS_SUB",
15 "SP_TRANS"
16 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
17 ( "SP_ITEM"."ITEM_CODE" = "SP_TRANS_SUB"."ITEM_CODE" ) and
18 ( ( "SP_TRANS"."VENDOR_CODE" = '20011021023') )
19 /

8 rows selected.

Elapsed: 00: 00: 01.62

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1453 Card=447198 Byt
          es=101066748)

   1 0 NESTED LOOPS (Cost=1453 Card=447198 Bytes=101066748)
   2 1 HASH JOIN (Cost=358 Card=1095 Bytes=61320)
   3 2 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=43 Card=273 By
          tes=7098)

   4 2 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=158 Card=1
          35502 Bytes=4065060)

   5 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=
          40840 Bytes=6942800)

   6 5 INDEX (UNIQUE SCAN) OF 'PK_SP_ITEM' (UNIQUE)

Statistics
----------------------------------------------------------
          0 recursive calls
          8 db block gets
       1344 consistent gets
          0 physical reads
          0 redo size
       1824 bytes sent via SQL*Net to client
        425 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          3 sorts (memory)
          0 sorts (disk)
          8 rows processed

SQL>
SQL> analyze table sp_item compute statistics
  2 /

Table analyzed.

Elapsed: 00: 00: 11.67
SQL> SELECT "SP_TRANS"."TRANS_NO",
  2 "SP_TRANS_SUB"."ITEM_CODE",
  3 "SP_ITEM"."ITEM_NAME",
  4 "SP_ITEM"."CHART_ID",
  5 "SP_ITEM"."SPECIFICATION",
  6 "SP_TRANS_SUB"."COUNTRY",
  7 "SP_TRANS_SUB"."QTY",
  8 "SP_TRANS_SUB"."PRICE",
  9 "SP_TRANS"."VENDOR_CODE",
10 "SP_TRANS"."PAY_MODE",
11 NVL("SP_TRANS_SUB"."PAY_QTY",0),
12 0 as PAY_THIS
13 FROM "SP_ITEM",
14 "SP_TRANS_SUB",
15 "SP_TRANS"
16 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
17 ( "SP_ITEM"."ITEM_CODE" = "SP_TRANS_SUB"."ITEM_CODE" ) and
18 ( ( "SP_TRANS"."VENDOR_CODE" = '20011021023') )
19 /

8 rows selected.

Elapsed: 00: 00: 01.43

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=687 Card=1362 Bytes=
          128028)

   1 0 HASH JOIN (Cost=687 Card=1362 Bytes=128028)
   2 1 HASH JOIN (Cost=358 Card=1362 Bytes=76272)
   3 2 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=43 Card=273 By
          tes=7098)

   4 2 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=158 Card=1
          35502 Bytes=4065060)

   5 1 TABLE ACCESS (FULL) OF 'SP_ITEM' (Cost=77 Card=29547 Byt
          es=1122786)

Statistics
----------------------------------------------------------
          0 recursive calls
         12 db block gets
       1820 consistent gets
          0 physical reads
          0 redo size
       1732 bytes sent via SQL*Net to client
        425 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          3 sorts (memory)
          0 sorts (disk)
          8 rows processed


大家可以看出执行计划是怎样一步一步的改变的。

最后Oracle给我选择了三个全表扫描。而且，从db block gets 、consistent gets 全都“提高”了。

eygle

然后我analyze索引


SQL> analyze table sp_trans compute statistics for all indexes;

表已分析。

已用时间: 00: 00: 01.12
SQL> SELECT "SP_TRANS"."TRANS_NO",
  2 "SP_TRANS_SUB"."ITEM_CODE",
  3 "SP_ITEM"."ITEM_NAME",
  4 "SP_ITEM"."CHART_ID",
  5 "SP_ITEM"."SPECIFICATION",
  6 "SP_TRANS_SUB"."COUNTRY",
  7 "SP_TRANS_SUB"."QTY",
  8 "SP_TRANS_SUB"."PRICE",
  9 "SP_TRANS"."VENDOR_CODE",
10 "SP_TRANS"."PAY_MODE",
11 NVL("SP_TRANS_SUB"."PAY_QTY",0),
12 0 as PAY_THIS
13 FROM "SP_ITEM",
14 "SP_TRANS_SUB",
15 "SP_TRANS"
16 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
17 ( "SP_ITEM"."ITEM_CODE" = "SP_TRANS_SUB"."ITEM_CODE" ) and
18 ( ( "SP_TRANS"."VENDOR_CODE" = '20011021023') )
19 /

已选择8行。

已用时间: 00: 00: 01.82

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=687 Card=1362 Bytes=
          128028)

   1 0 HASH JOIN (Cost=687 Card=1362 Bytes=128028)
   2 1 HASH JOIN (Cost=358 Card=1362 Bytes=76272)
   3 2 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=43 Card=273 By
          tes=7098)

   4 2 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=158 Card=1
          35502 Bytes=4065060)

   5 1 TABLE ACCESS (FULL) OF 'SP_ITEM' (Cost=77 Card=29547 Byt
          es=1122786)

Statistics
----------------------------------------------------------
         72 recursive calls
         12 db block gets
       1851 consistent gets
          0 physical reads
          0 redo size
       1732 bytes sent via SQL*Net to client
        425 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          3 sorts (memory)
          0 sorts (disk)
          8 rows processed

SQL> analyze table sp_trans_sub compute statistics for all indexes;

表已分析。

已用时间: 00: 00: 05.67
SQL> SELECT "SP_TRANS"."TRANS_NO",
  2 "SP_TRANS_SUB"."ITEM_CODE",
  3 "SP_ITEM"."ITEM_NAME",
  4 "SP_ITEM"."CHART_ID",
  5 "SP_ITEM"."SPECIFICATION",
  6 "SP_TRANS_SUB"."COUNTRY",
  7 "SP_TRANS_SUB"."QTY",
  8 "SP_TRANS_SUB"."PRICE",
  9 "SP_TRANS"."VENDOR_CODE",
10 "SP_TRANS"."PAY_MODE",
11 NVL("SP_TRANS_SUB"."PAY_QTY",0),
12 0 as PAY_THIS
13 FROM "SP_ITEM",
14 "SP_TRANS_SUB",
15 "SP_TRANS"
16 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
17 ( "SP_ITEM"."ITEM_CODE" = "SP_TRANS_SUB"."ITEM_CODE" ) and
18 ( ( "SP_TRANS"."VENDOR_CODE" = '20011021023') )
19 /

已选择8行。

已用时间: 00: 00: 01.82

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=687 Card=1362 Bytes=
          128028)

   1 0 HASH JOIN (Cost=687 Card=1362 Bytes=128028)
   2 1 HASH JOIN (Cost=358 Card=1362 Bytes=76272)
   3 2 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=43 Card=273 By
          tes=7098)

   4 2 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=158 Card=1
          35502 Bytes=4065060)

   5 1 TABLE ACCESS (FULL) OF 'SP_ITEM' (Cost=77 Card=29547 Byt
          es=1122786)

Statistics
----------------------------------------------------------
          0 recursive calls
         12 db block gets
       1820 consistent gets
          0 physical reads
          0 redo size
       1732 bytes sent via SQL*Net to client
        425 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          3 sorts (memory)
          0 sorts (disk)
          8 rows processed

SQL> analyze table sp_item compute statistics for all indexes
  2 /

表已分析。

已用时间: 00: 00: 01.12
SQL> SELECT "SP_TRANS"."TRANS_NO",
  2 "SP_TRANS_SUB"."ITEM_CODE",
  3 "SP_ITEM"."ITEM_NAME",
  4 "SP_ITEM"."CHART_ID",
  5 "SP_ITEM"."SPECIFICATION",
  6 "SP_TRANS_SUB"."COUNTRY",
  7 "SP_TRANS_SUB"."QTY",
  8 "SP_TRANS_SUB"."PRICE",
  9 "SP_TRANS"."VENDOR_CODE",
10 "SP_TRANS"."PAY_MODE",
11 NVL("SP_TRANS_SUB"."PAY_QTY",0),
12 0 as PAY_THIS
13 FROM "SP_ITEM",
14 "SP_TRANS_SUB",
15 "SP_TRANS"
16 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
17 ( "SP_ITEM"."ITEM_CODE" = "SP_TRANS_SUB"."ITEM_CODE" ) and
18 ( ( "SP_TRANS"."VENDOR_CODE" = '20011021023') )
19 /

已选择8行。

已用时间: 00: 00: 01.82

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=687 Card=1362 Bytes=
          128028)

   1 0 HASH JOIN (Cost=687 Card=1362 Bytes=128028)
   2 1 HASH JOIN (Cost=358 Card=1362 Bytes=76272)
   3 2 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=43 Card=273 By
          tes=7098)

   4 2 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=158 Card=1
          35502 Bytes=4065060)

   5 1 TABLE ACCESS (FULL) OF 'SP_ITEM' (Cost=77 Card=29547 Byt
          es=1122786)

Statistics
----------------------------------------------------------
          0 recursive calls
         12 db block gets
       1820 consistent gets
          0 physical reads
          0 redo size
       1732 bytes sent via SQL*Net to client
        425 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          3 sorts (memory)
          0 sorts (disk)
          8 rows processed

SQL>

eygle

然后我在VENDOR_CODE列上建了一个索引

SQL> create index idx_vendor on sp_trans(vendor_code);

索引已创建。

已用时间: 00: 00: 02.03
SQL> SELECT "SP_TRANS"."TRANS_NO",
  2 "SP_TRANS_SUB"."ITEM_CODE",
  3 "SP_ITEM"."ITEM_NAME",
  4 "SP_ITEM"."CHART_ID",
  5 "SP_ITEM"."SPECIFICATION",
  6 "SP_TRANS_SUB"."COUNTRY",
  7 "SP_TRANS_SUB"."QTY",
  8 "SP_TRANS_SUB"."PRICE",
  9 "SP_TRANS"."VENDOR_CODE",
10 "SP_TRANS"."PAY_MODE",
11 NVL("SP_TRANS_SUB"."PAY_QTY",0),
12 0 as PAY_THIS
13 FROM "SP_ITEM",
14 "SP_TRANS_SUB",
15 "SP_TRANS"
16 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
17 ( "SP_ITEM"."ITEM_CODE" = "SP_TRANS_SUB"."ITEM_CODE" ) and
18 ( ( "SP_TRANS"."VENDOR_CODE" = '20011021023') )
19 /

已选择8行。

已用时间: 00: 00: 01.42

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=646 Card=1362 Bytes=
          128028)

   1 0 HASH JOIN (Cost=646 Card=1362 Bytes=128028)
   2 1 HASH JOIN (Cost=317 Card=1362 Bytes=76272)
   3 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS' (Cost=2 Ca
          rd=273 Bytes=7098)

   4 3 INDEX (RANGE SCAN) OF 'IDX_VENDOR' (NON-UNIQUE) (Cos
          t=1 Card=273)

   5 2 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=158 Card=1
          35502 Bytes=4065060)

   6 1 TABLE ACCESS (FULL) OF 'SP_ITEM' (Cost=77 Card=29547 Byt
          es=1122786)

Statistics
----------------------------------------------------------
          0 recursive calls
          8 db block gets
       1546 consistent gets
          1 physical reads
          0 redo size
       1732 bytes sent via SQL*Net to client
        425 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          3 sorts (memory)
          0 sorts (disk)
          8 rows processed

SQL>

这时的结果稍好一点，可是不如最初的执行计划。

eygle

加rule提示，把后来建的索引删了的结果，这就是最初的执行计划

SQL> SELECT /*+ rule */ "SP_TRANS"."TRANS_NO",
  2 "SP_TRANS_SUB"."ITEM_CODE",
  3 "SP_ITEM"."ITEM_NAME",
  4 "SP_ITEM"."CHART_ID",
  5 "SP_ITEM"."SPECIFICATION",
  6 "SP_TRANS_SUB"."COUNTRY",
  7 "SP_TRANS_SUB"."QTY",
  8 "SP_TRANS_SUB"."PRICE",
  9 "SP_TRANS"."VENDOR_CODE",
10 "SP_TRANS"."PAY_MODE",
11 NVL("SP_TRANS_SUB"."PAY_QTY",0),
12 0 as PAY_THIS
13 FROM "SP_ITEM",
14 "SP_TRANS_SUB",
15 "SP_TRANS"
16 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
17 ( "SP_ITEM"."ITEM_CODE" = "SP_TRANS_SUB"."ITEM_CODE" ) and
18 ( ( "SP_TRANS"."VENDOR_CODE" = '20011021023') )
19 /

已选择8行。

已用时间: 00: 00: 00.71

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=HINT: RULE
   1 0 NESTED LOOPS
   2 1 NESTED LOOPS
   3 2 TABLE ACCESS (FULL) OF 'SP_TRANS'
   4 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB'
   5 4 INDEX (RANGE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE)
   6 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM'
   7 6 INDEX (UNIQUE SCAN) OF 'PK_SP_ITEM' (UNIQUE)

Statistics
----------------------------------------------------------
          0 recursive calls
          4 db block gets
        323 consistent gets
          0 physical reads
          0 redo size
       1809 bytes sent via SQL*Net to client
        426 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          8 rows processed

SQL>

eygle

然后创建这个索引，得出的时间大大缩短，看来CBO在某些情况下确实会选择非最优的执行计划

SQL> create index idx_vendor on sp_trans(vendor_code);

索引已创建。

已用时间: 00: 00: 02.43
SQL> SELECT /*+ rule */ "SP_TRANS"."TRANS_NO",
  2 "SP_TRANS_SUB"."ITEM_CODE",
  3 "SP_ITEM"."ITEM_NAME",
  4 "SP_ITEM"."CHART_ID",
  5 "SP_ITEM"."SPECIFICATION",
  6 "SP_TRANS_SUB"."COUNTRY",
  7 "SP_TRANS_SUB"."QTY",
  8 "SP_TRANS_SUB"."PRICE",
  9 "SP_TRANS"."VENDOR_CODE",
10 "SP_TRANS"."PAY_MODE",
11 NVL("SP_TRANS_SUB"."PAY_QTY",0),
12 0 as PAY_THIS
13 FROM "SP_ITEM",
14 "SP_TRANS_SUB",
15 "SP_TRANS"
16 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
17 ( "SP_ITEM"."ITEM_CODE" = "SP_TRANS_SUB"."ITEM_CODE" ) and
18 ( ( "SP_TRANS"."VENDOR_CODE" = '20011021023') )
19 /

已选择8行。

已用时间: 00: 00: 00.31

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=HINT: RULE
   1 0 NESTED LOOPS
   2 1 NESTED LOOPS
   3 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS'
   4 3 INDEX (RANGE SCAN) OF 'IDX_VENDOR' (NON-UNIQUE)
   5 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB'
   6 5 INDEX (RANGE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE)
   7 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM'
   8 7 INDEX (UNIQUE SCAN) OF 'PK_SP_ITEM' (UNIQUE)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         49 consistent gets
          1 physical reads
          0 redo size
       1809 bytes sent via SQL*Net to client
        426 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          8 rows processed

SQL>

eygle

然而这不是最快的，这是RBO

我们修改optimizer_index_cost_adj参数，然后奇迹出现了

optimizer_index_cost_adj告诉CBO倾向于使用索引扫描而不是全表扫描的程度，该参数缺省值是１００，就是不左右CBO的判断


SQL> alter session set optimizer_index_cost_adj=30
  2 /

会话已更改。

已用时间: 00: 00: 00.20

SQL> SELECT "SP_TRANS"."TRANS_NO",
  2 "SP_TRANS_SUB"."ITEM_CODE",
  3 "SP_ITEM"."ITEM_NAME",
  4 "SP_ITEM"."CHART_ID",
  5 "SP_ITEM"."SPECIFICATION",
  6 "SP_TRANS_SUB"."COUNTRY",
  7 "SP_TRANS_SUB"."QTY",
  8 "SP_TRANS_SUB"."PRICE",
  9 "SP_TRANS"."VENDOR_CODE",
10 "SP_TRANS"."PAY_MODE",
11 NVL("SP_TRANS_SUB"."PAY_QTY",0),
12 0 as PAY_THIS
13 FROM "SP_TRANS",
14 "SP_ITEM",
15 "SP_TRANS_SUB"
16 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
17 ( "SP_ITEM"."ITEM_CODE" = "SP_TRANS_SUB"."ITEM_CODE" ) and
18 ( ( "SP_TRANS"."VENDOR_CODE" = '20011021023') )
19 /

已选择8行。

已用时间: 00: 00: 00.11

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=658 Card=1095 Bytes= 102930)

   1 0 NESTED LOOPS (Cost=658 Card=1095 Bytes=102930)
   2 1 NESTED LOOPS (Cost=329 Card=1095 Bytes=61320)
   3 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS' (Cost=1 Card=273 Bytes=7098)
   4 3 INDEX (RANGE SCAN) OF 'IDX_VENDOR' (NON-UNIQUE) (Cost=1 Card=273)
   5 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost= 2 Card=135502 Bytes=4065060)
   6 5 INDEX (RANGE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Cost=3 Card=135502)
   7 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29547 Bytes=1122786)
   8 7 INDEX (UNIQUE SCAN) OF 'PK_SP_ITEM' (UNIQUE)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         49 consistent gets
          0 physical reads
          0 redo size
       1809 bytes sent via SQL*Net to client
        426 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          8 rows processed

SQL>

eygle

SQL> drop index idx_vendor;

索引已丢弃。

已用时间: 00: 00: 00.61

SQL> /

已选择8行。

已用时间: 00: 00: 00.11

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=700 Card=1095 Bytes=
          102930)

   1 0 NESTED LOOPS (Cost=700 Card=1095 Bytes=102930)
   2 1 NESTED LOOPS (Cost=371 Card=1095 Bytes=61320)
   3 2 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=43 Card=273 By
          tes=7098)

   4 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost=
          2 Card=135502 Bytes=4065060)

   5 4 INDEX (RANGE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Co
          st=3 Card=135502)

   6 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=
          29547 Bytes=1122786)

   7 6 INDEX (UNIQUE SCAN) OF 'PK_SP_ITEM' (UNIQUE)

Statistics
----------------------------------------------------------
          0 recursive calls
          4 db block gets
        323 consistent gets
          0 physical reads
          0 redo size
       1809 bytes sent via SQL*Net to client
        426 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          8 rows processed

SQL>

eygle

关于optimizer_index_cost_adj等影响CBO的参数及设置，richto介绍过一篇好文章：

http://www.evdbt.com/SearchIntelligenceCBO.doc

关于成本的计算等，请参考以下文章：

http://www.centrexcc.com/A%20Loo ... 2010053%20Event.pdf
http://www.centrexcc.com/A%20Loo ... 2010053%20Event.ppt

很简单的问题，希望对大家有点帮助!

rejoice999

UP