分享
 
 
 

CBO对于Oracle SQL执行计划的影响

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

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

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=2057 Card=1 Bytes=288)

10 NESTED LOOPS (Cost=2057 Card=1 Bytes=288)

21 NESTED LOOPS (Cost=2056 Card=1 Bytes=256)

32 NESTED LOOPS (Cost=2054 Card=1 Bytes=219)

43 NESTED LOOPS (Cost=2053 Card=1 Bytes=178)

54 NESTED LOOPS (Cost=2009 Card=1 Bytes=131)

65 MERGE JOIN (Cost=2008 Card=1 Bytes=100)

76 SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776)

87 TABLE Access (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)

96 SORT (JOIN) (Cost=1058 Card=36730 Bytes=1909960)

109 TABLE ACCESS (FULL) OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960)

115 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK' (Cost=1 Card=3870 Bytes=119970)

12 11 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)

134 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)

143 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456)

15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)

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

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

36FROM "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

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=2057 Card=1 Bytes=288)

10 NESTED LOOPS (Cost=2057 Card=1 Bytes=288)

21 NESTED LOOPS (Cost=2056 Card=1 Bytes=256)

32 NESTED LOOPS (Cost=2054 Card=1 Bytes=219)

43 NESTED LOOPS (Cost=2053 Card=1 Bytes=178)

54 NESTED LOOPS (Cost=2009 Card=1 Bytes=131)

65 MERGE JOIN (Cost=2008 Card=1 Bytes=100)

76 SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776)

87 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)

96 SORT (JOIN) (Cost=1058 Card=36730 Bytes=1909960)

109 TABLE ACCESS (FULL) OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960)

115 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK' (Cost=1 Card=3870 Bytes=119970)

12 11 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)

134 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)

143 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456)

15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)

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

181 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)

19 18 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)

Statistics

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

16recursive calls

186307db block gets

10685361consistent gets

2329physical reads

0redo size

38486bytes sent via SQL*Net to client

1117bytes received via SQL*Net from client

10SQL*Net roundtrips to/from client

7sorts (memory)

2sorts (disk)

130rows processed

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

b. rule

在RBO条件下,该语句是执行很快的

加入rule提示,我们得到以下执行计划:

Execution Plan

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

0SELECT STATEMENT Optimizer=HINT: RULE

10 NESTED LOOPS

21 NESTED LOOPS

32 NESTED LOOPS

43 NESTED LOOPS

54 NESTED LOOPS

65 NESTED LOOPS

76 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB'

86 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM'

98 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)

105 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS'

11 10 INDEX (UNIQUE SCAN) OF 'PK_HSP_TRANS' (UNIQUE)

124 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK'

13 12 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)

143 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE'

15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)

162 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK_SUB'

17 16 INDEX (RANGE SCAN) OF 'IDX_CHK_SUB_ITEM_CODE' (NON-UNIQUE)

181 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

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

0SELECT STATEMENT Optimizer=HINT: RULE

10 NESTED LOOPS

21 NESTED LOOPS

32 NESTED LOOPS

43 NESTED LOOPS

54 NESTED LOOPS

65 NESTED LOOPS

76 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB'

86 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM'

98 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)

105 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS'

11 10 INDEX (UNIQUE SCAN) OF 'PK_HSP_TRANS' (UNIQUE)

124 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK'

13 12 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)

143 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE'

15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)

162 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK_SUB'

17 16 INDEX (RANGE SCAN) OF 'IDX_CHK_SUB_ITEM_CODE' (NON-UNIQUE)

181 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB'

19 18 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE)

Statistics

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

0recursive calls

6db block gets

829182consistent gets

0physical reads

0redo size

37383bytes sent via SQL*Net to client

1127bytes received via SQL*Net from client

10SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

130rows processed

SQL>

c. ordered

然后我想起了Ordered提示

使用该提示的执行计划如下:

SQL>@sql

已选择130行。

已用时间:00: 00: 05.67

Execution Plan

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=3284 Card=1 Bytes=288)

10 NESTED LOOPS (Cost=3284 Card=1 Bytes=288)

21 NESTED LOOPS (Cost=3283 Card=1 Bytes=256)

32 MERGE JOIN (Cost=3282 Card=1 Bytes=204)

43 SORT (JOIN) (Cost=2333 Card=6823 Bytes=1064388)

54 HASH JOIN (Cost=1848 Card=6823 Bytes=1064388)

65 HASH JOIN (Cost=216 Card=1717 Bytes=204323)

76 HASH JOIN (Cost=96 Card=1717 Bytes=133926)

87 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)

97 TABLE ACCESS (FULL) OF 'SP_CHK' (Cost=13 Card=3870 Bytes=119970)

106 TABLE ACCESS (FULL) OF 'SP_RECEIVE' (Cost=17 Card=7816 Bytes=320456)

115 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=155 Card=136371 Bytes=5045727)

123 SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776)

13 12 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)

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

161 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)

17 16 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)

Statistics

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

8recursive calls

88db block gets

2667consistent gets

1093physical reads

0redo size

37285bytes sent via SQL*Net to client

1109bytes received via SQL*Net from client

10SQL*Net roundtrips to/from client

8sorts (memory)

1sorts (disk)

130rows processed

SQL>

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

所以会产生这样的效果,是因为在CBO的执行计划中,对于7张数据表,Oracle需要计算7!(5040)个连接顺序,然后比较各个顺序的

成本,最后选择成本较低的执行计划

显然,在这一判定上耗费了大量的时间。当我们使用ordered hints的时候,Oracle就不需要这一计算步骤,它只需要使用我们指定的

顺序,然后快速的给出结果。然后问题迎刃而解。

初试化参数对于执行计划的影响

有几个初试化参数对于多表连接的执行计划有重要的关系。

在Oracle 8 release 8.0.5中引入了两个参数OPTIMIZER_MAX_PERMUTATIONS 和 OPTIMIZER_SEARCH_LIMIT

optimizer_search_limit参数指定了在决定连接多个数据表的最好方式时,CBO需要衡量的数据表连接组合的最大数目。

该参数的缺省值是5。

假如连接表的数目小于optimizer_search_limit参数,那么Oracle会执行所有可能的连接。可能连接的组合数目是数据表数目的阶乘。

我们刚才有7张表,那么有7!(5040)种组合。

optimizer_max_permutations参数定义了CBO所考虑的表连接的最大数目的上限。

当我们给这个参数设置很小的一个值的时候,Oracle的计算比较很快就可以被遏制。然后执行计划,给出结果。

optimizer_search_limit参数和optimizer_max_permutations参数和Ordered参数不相容,假如定义了ordered提示,那么

optimizer_max_permutations参数将会失效。

实际上,当你定义了ordered提示时,oracle已经无需计算了。

optimizer_search_limit参数和optimizer_max_permutations参数要结合使用,优化器将在optimizer_search_limit参数或

optimizer_max_permutations参数值超出之前,生成可能的表连接转换。当优化器停止对表连接的评估时,它将选择成本最低的组合。

例如,需要连接9个表的查询已经超出了optimizer_search_limit参数的限制,但是仍然可能要花费大量的时间去试图评估所有362880个

可能的连接顺序(9!),直到超过了optimizer_max_permutations参数的默认值(80000个表连接顺序)。

optimizer_max_permutations参数为CBO需要评估的排列数量的最大值。

optimizer_max_permutations的默认值是80000。

在确定查询排列评估数量的上限时,CBO采用的原则是:

假如查询中存在的非单一记录表的数目小于optimizer_search_limit+1,那么排列的最大值等于下面两个表达式中较大的数值:

optimizer_max_permutations

______________________________

(可能启动表的数目+1)

optimizer_search_limit!

___________________________

(可能启动表的数目+1)

例如5个表连接

排列的最大值= 80000/6=13333

____________________________

搜索限制=5!/6=120/6=20

较大值是13333,这就是优化器要考虑的排列的最大数值(当然实际的数值要比这个小的多,Oracle会排除掉大部分不可能组合)。

SQL> alter session set optimizer_search_limit = 3;

会话已更改。

已用时间:00: 00: 00.60

SQL> alter session set optimizer_max_permutations = 100;

会话已更改。

已用时间:00: 00: 00.90

SQL> set autotrace traceonly

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"

36FROM "SP_TRANS" ,

37"SP_CHK" ,

38"SP_RECEIVE" ,

39"SP_TRANS_SUB" ,

40"SP_CHK_SUB" ,

41"SP_RECEIVE_SUB" ,

42"SP_ITEM"

43 WHERE

44 ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and

45 ("SP_TRANS"."BILL_NO" = "SP_CHK"."CHK_NO") and

46 ( "SP_CHK_SUB"."CHK_NO" = "SP_CHK"."CHK_NO" ) and

47 ( "SP_CHK"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and

48 ( "SP_CHK"."STATE" = 15 ) and

49 ( "SP_RECEIVE_SUB"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and

50 ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_ITEM"."ITEM_CODE" ) and

51 ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_CHK_SUB"."ITEM_CODE" ) and

52 ( "SP_CHK_SUB"."ITEM_CODE" = "SP_RECEIVE_SUB"."ITEM_CODE" ) and

53 ( "SP_CHK_SUB"."COUNTRY" = "SP_TRANS_SUB"."COUNTRY" ) and

54 ( "SP_CHK_SUB"."COUNTRY" = "SP_RECEIVE_SUB"."COUNTRY" ) and

55 ( "SP_CHK_SUB"."PLAN_NO" = "SP_RECEIVE_SUB"."PLAN_NO" ) and

56 ( "SP_CHK_SUB"."PLAN_LINE" = "SP_RECEIVE_SUB"."PLAN_LINE" ) and

57 (to_char("SP_TRANS"."TRANSDATE" ,'YYYY-MM-DD') >='2003-01-01')

58/

已选择130行。

已用时间:00: 00: 05.78

Execution Plan

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=2177 Card=1 Bytes=288)

10 NESTED LOOPS (Cost=2177 Card=1 Bytes=288)

21 NESTED LOOPS (Cost=2176 Card=1 Bytes=256)

32 NESTED LOOPS (Cost=2174 Card=1 Bytes=219)

43 MERGE JOIN (Cost=2173 Card=1 Bytes=178)

54 SORT (JOIN) (Cost=1115 Card=8081 Bytes=1018206)

65 HASH JOIN (Cost=645 Card=8081 Bytes=1018206)

76 HASH JOIN (Cost=96 Card=1717 Bytes=133926)

87 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)

97 TABLE ACCESS (FULL) OF 'SP_CHK' (Cost=13 Card=3870 Bytes=119970)

106 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)

114 SORT (JOIN) (Cost=1058 Card=36730 Bytes=1909960)

12 11 TABLE ACCESS (FULL) OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960)

133 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456)

14 13 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)

152 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost=2 Card=136371 Bytes=5045727)

16 15 INDEX (UNIQUE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Cost=1 Card=136371)

171 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)

18 17 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)

Statistics

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

8recursive calls

131db block gets

3436consistent gets

1397physical reads

0redo size

38555bytes sent via SQL*Net to client

1085bytes received via SQL*Net from client

10SQL*Net roundtrips to/from client

8sorts (memory)

1sorts (disk)

130rows processed

SQL>

3. 其他

在有的系统视图查询中,很多时候会出现问题,比如以下的SQL:

select a.username, a.sid, a.serial#, b.id1

from v$session a, v$lock b

where a.lockwait = b.kaddr

/

这个语句用来查找锁,在Oracle7的年代,这样的SQL语句执行的很快,但是在Oracle8以后的数据库,假如碰巧你用的是CBO,那么

这样的语句执行结果可能是Hang了(其实不是死了,只是很多人没有耐心等而已),在Oracle7里,这样的语句毫无疑问使用RBO,

很快你就可以得到执行结果。可以对于CBO,你所看到的两个视图,对于数据库来说,实际上是6个表,单只6个表的可能顺序组合就有

6!(720)种,数据库时间都消耗在计算这些执行路径上了,所以你得到的就是hang的结果。

最简单的解决办法就是使用rule提示,或者使用ordered提示

我们可以看一下这两种方式的执行计划,假如你有爱好的话,还可以研究一下X$视图:

SQL> select /*+ rule */ a.username, a.sid, a.serial#, b.id1

2from v$session a, v$lock b

3where a.lockwait = b.kaddr

4/

未选定行

Execution Plan

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

0SELECT STATEMENT Optimizer=HINT: RULE

10 MERGE JOIN

21 SORT (JOIN)

32 MERGE JOIN

43 SORT (JOIN)

54 MERGE JOIN

65 FIXED TABLE (FULL) OF 'X$KSQRS'

75 SORT (JOIN)

87 VIEW OF 'GV$_LOCK'

98 UNION-ALL

109 VIEW OF 'GV$_LOCK1'

11 10 UNION-ALL

12 11 FIXED TABLE (FULL) OF 'X$KDNSSF'

13 11 FIXED TABLE (FULL) OF 'X$KSQEQ'

149 FIXED TABLE (FULL) OF 'X$KTADM'

159 FIXED TABLE (FULL) OF 'X$KTCXB'

163 SORT (JOIN)

17 16 FIXED TABLE (FULL) OF 'X$KSUSE'

181 SORT (JOIN)

19 18 FIXED TABLE (FULL) OF 'X$KSUSE'

Statistics

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

0recursive calls

0db block gets

0consistent gets

0physical reads

0redo size

196bytes sent via SQL*Net to client

246bytes received via SQL*Net from client

1SQL*Net roundtrips to/from client

5sorts (memory)

0sorts (disk)

0rows processed

对于Ordered提示:

SQL> select /*+ ordered */ a.username, a.sid, a.serial#, b.id1

2from v$session a, v$lock b

3where a.lockwait = b.kaddr

4/

未选定行

Execution Plan

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=112 Card=1 Bytes=145 )

10 NESTED LOOPS (Cost=112 Card=1 Bytes=145)

21 NESTED LOOPS (Cost=96 Card=1 Bytes=128)

32 NESTED LOOPS (Cost=80 Card=1 Bytes=111)

43 FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=16 Card=1 Bytes=86)

53 VIEW OF 'GV$_LOCK'

65 UNION-ALL

76 VIEW OF 'GV$_LOCK1' (Cost=32 Card=2 Bytes=162)

87 UNION-ALL

98 FIXED TABLE (FULL) OF 'X$KDNSSF' (Cost=16 Card=1 Bytes=94)

108 FIXED TABLE (FULL) OF 'X$KSQEQ' (Cost=16 Card=1 Bytes=94)

116 FIXED TABLE (FULL) OF 'X$KTADM' (Cost=16 Card=1 Bytes=94)

126 FIXED TABLE (FULL) OF 'X$KTCXB' (Cost=16 Card=1 Bytes=94)

132 FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=16 Card=1 Bytes=17)

141 FIXED TABLE (FIXED INDEX #1) OF 'X$KSQRS' (Cost=16 Card=100 Bytes=1700)

Statistics

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

0recursive calls

67db block gets

0consistent gets

0physical reads

0redo size

202bytes sent via SQL*Net to client

244bytes received via SQL*Net from client

1SQL*Net roundtrips to/from client

17sorts (memory)

0sorts (disk)

0rows processed

SQL>

类似的

SELECT /*+ RULE */

s.SID, s.serial#, l.TYPE, l.id1, l.id2, l.lmode, l.request, l.addr,

l.kaddr, l.ctime, l.BLOCK, s.username, s.osuser, s.machine,

DECODE (l.id2,

0, TO_CHAR (o.owner#) '-' o.NAME,

'Trans-' TO_CHAR (l.id1) '-' l.id2

) object_name,

DECODE (l.lmode,

0, '--Waiting--',

1, 'Null',

2, 'Row Share',

3, 'Row Excl',

4, 'Share',

5, 'Sha Row Exc',

6, 'Exclusive',

'Other'

) lock_mode,

DECODE (l.request,

0, ' ',

1, 'Null',

2, 'Row Share',

3, 'Row Excl',

4, 'Share',

5, 'Sha Row Exc',

6, 'Exclusive',

'Other'

) req_mode

FROM v$lock l, v$session s, SYS.obj$ o

WHERE l.request = 0

AND l.SID = s.SID

AND l.id1 = o.obj#(+)

AND s.username IS NOT NULL

ORDER BY s.username, l.SID, l.BLOCK;

以上问题对于CBO优化器普遍存在,对于Oracle9i2同样如此。

幸运的是在Oracle9i中,optimizer_max_permutations初始值降低到2000,从80000到2000,这是一个重大的进步

其实或者这不能算是问题,对于Oracle这只是一种知识,一种方法而已。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有