## CSDN博客

### 一次SQL优化分析的全过程

Mail: eygle@itpub.net

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

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 Bytes=6942800)
Statistics
----------------------------------------------------------
150 recursive calls
89 db block gets
1837 consistent gets
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 Bytes=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 Bytes=7098)
4 2 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=158 Card=135502 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 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 Bytes=7098)
4 2 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=158 Card=135502 Bytes=4065060)
5 1 TABLE ACCESS (FULL) OF 'SP_ITEM' (Cost=77 Card=29547 Bytes=1122786)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
1820 consistent gets
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

block gets 、consistent gets 全都“提高”了。

SQL> create index idx_vendor on sp_trans(vendor_code);

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 /

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 Card=273 Bytes=7098)
4 3 INDEX (RANGE SCAN) OF 'IDX_VENDOR' (NON-UNIQUE) (Cost=1 Card=273)
5 2 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=158 Card=135502 Bytes=4065060)
6 1 TABLE ACCESS (FULL) OF 'SP_ITEM' (Cost=77 Card=29547 Bytes=1122786)
Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
1546 consistent gets
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>

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 /

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

SQL> create index idx_vendor on sp_trans(vendor_code);

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 /

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

2 /

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 /

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

OPTIMIZER_INDEX_CACHING

SQL> drop index idx_vendor;

SQL> /

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 Bytes=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) (Cost=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 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>

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

http://www.centrexcc.com/A%20Look%20under%20the%20Hood%20of%20CBO%20-%20the%201
0053%20Event.pdf
http://www.centrexcc.com/A%20Look%20under%20the%20Hood%20of%20CBO%20-%20the%201
0053%20Event.ppt