关于CBO对于执行计划的影响,一个实际分析的过程!

coolyl · 发表于 2003-5-29 21:45

eygle，查你的pm

快点

longbow · 发表于 2003-5-30 00:24

对于cbo选择的执行计划，
如果如上面所说的话，那么这个查询第二次执行的时候已经不需要parse，直接就是execute，速度会快吗?
或者使用stored outline稳定了执行计划，还会这么慢吗。

eygle · 发表于 2003-5-30 09:24

最初由 longbow 发布
[B]对于cbo选择的执行计划，
如果如上面所说的话，那么这个查询第二次执行的时候已经不需要parse，直接就是execute，速度会快吗?
或者使用stored outline稳定了执行计划，还会这么慢吗。 [/B]

使用stored outline肯定是一个可行的方法。
不过如果不熟悉第三方的程序，处理起来是有点麻烦的。
比如我上边这个语句，其实是动态组合的，加起来有10几个条件。而且这样的语句很多。

所以一般来说，对于多表的联合，开发时最好就添加必要的提示，否则后期维护起来就比较麻烦。

eygle · 发表于 2003-5-30 09:27

最初由 Seraphim 发布
[B]"optimizer_search_limit参数指定了在决定连接多个数据表的最好方式时，CBO需要衡量的数据表连接组合的最大数目。该参数的缺省值是5。"
而你们7张表的时候，Oracle仍然在进行所有的连接衡量，也就是说这个参数被改成了>7的数？莫非就是老大你做的 [/B]

这个问题是这样的:
optimizer_search_limit参数和optimizer_max_permutations参数要结合使用，优化器将在optimizer_search_limit参数或[/COLOR] optimizer_max_permutations参数值超出之前，生成可能的表连接转换。当优化器停止对表连接的评估时，它将选择成本最低的组合。

例如，需要连接9个表的查询已经超出了optimizer_search_limit参数的限制，但是仍然可能要花费大量的时间去试图评估所有362880个可能的连接顺序(9!),直到超过了optimizer_max_permutations参数的默认值(80000个表连接顺序)。

biti_rainy · 发表于 2003-5-30 09:42

除了执行计划本身评估的代价外，更主要的原因应该是执行计划的失误！

在选择执行计划这个过程中应该不会花费太多的时间，oracle内部肯定应该还有限制

可以做个测试，比如使用 10个只有1条记录的表做连接，加大 optimizer_search_limit参数和optimizer_max_permutations参数,看执行时间

希望 eygle 继续深入

biti_rainy · 发表于 2003-5-30 12:46

SQL> declare
  2  n1 number;
  3  n2 number;
  4  n3 number;
  5  n4 number;
  6  n number;
  7  begin
  8
  9  n1 := dbms_utility.get_time;
10  for i in 1..1000 loop
11  select /*+RULE*/ q.OBJECT_ID into n
12  from q,q1,q2,q3,q4,q5,q6,q7,q8,q9,q0
13  where q.OBJECT_NAME = q1.OBJECT_NAME
14  and q.OBJECT_NAME = q1.OBJECT_NAME
15  and q1.OBJECT_NAME = q2.OBJECT_NAME
16  and q2.OBJECT_NAME = q3.OBJECT_NAME
17  and q3.OBJECT_NAME = q4.OBJECT_NAME
18  and q4.OBJECT_NAME = q5.OBJECT_NAME
19  and q5.OBJECT_NAME = q6.OBJECT_NAME
20  and q6.OBJECT_NAME = q7.OBJECT_NAME
21  and q7.OBJECT_NAME = q8.OBJECT_NAME
22  and q8.OBJECT_NAME = q9.OBJECT_NAME
23  and q9.OBJECT_NAME = q0.OBJECT_NAME;
24  end loop;
25  n2 := dbms_utility.get_time;
26  for i in 1..1000 loop
27  select /*+first_rows*/ q.OBJECT_ID into n
28  from q,q1,q2,q3,q4,q5,q6,q7,q8,q9,q0
29  where q.OBJECT_NAME = q1.OBJECT_NAME
30  and q.OBJECT_NAME = q1.OBJECT_NAME
31  and q1.OBJECT_NAME = q2.OBJECT_NAME
32  and q2.OBJECT_NAME = q3.OBJECT_NAME
33  and q3.OBJECT_NAME = q4.OBJECT_NAME
34  and q4.OBJECT_NAME = q5.OBJECT_NAME
35  and q5.OBJECT_NAME = q6.OBJECT_NAME
36  and q6.OBJECT_NAME = q7.OBJECT_NAME
37  and q7.OBJECT_NAME = q8.OBJECT_NAME
38  and q8.OBJECT_NAME = q9.OBJECT_NAME
39  and q9.OBJECT_NAME = q0.OBJECT_NAME;
40  end loop;
41  n3 := dbms_utility.get_time;
42  for i in 1..1000 loop
43  select /*+all_rows*/ q.OBJECT_ID into n
44  from q,q1,q2,q3,q4,q5,q6,q7,q8,q9,q0
45  where q.OBJECT_NAME = q1.OBJECT_NAME
46  and q.OBJECT_NAME = q1.OBJECT_NAME
47  and q1.OBJECT_NAME = q2.OBJECT_NAME
48  and q2.OBJECT_NAME = q3.OBJECT_NAME
49  and q3.OBJECT_NAME = q4.OBJECT_NAME
50  and q4.OBJECT_NAME = q5.OBJECT_NAME
51  and q5.OBJECT_NAME = q6.OBJECT_NAME
52  and q6.OBJECT_NAME = q7.OBJECT_NAME
53  and q7.OBJECT_NAME = q8.OBJECT_NAME
54  and q8.OBJECT_NAME = q9.OBJECT_NAME
55  and q9.OBJECT_NAME = q0.OBJECT_NAME;
56  end loop;
57  n4 := dbms_utility.get_time;
58
59  dbms_output.put_line('rule :'||to_char(n2 - n1));
60  dbms_output.put_line('first_rows :'||to_char(n3 - n2));
61  dbms_output.put_line('all_rows :'||to_char(n4 - n3));
62  end;
63  /
rule :118
first_rows :118
all_rows :121

PL/SQL procedure successfully completed.

SQL> analyze table q compute statistics;

Table analyzed.

SQL> declare
  2  n1 number;
  3  n2 number;
  4  n3 number;
  5  n4 number;
  6  n number;
  7  begin
  8
  9  n1 := dbms_utility.get_time;
10  for i in 1..1000 loop
11  select /*+RULE*/ q.OBJECT_ID into n
12  from q,q1,q2,q3,q4,q5,q6,q7,q8,q9,q0
13  where q.OBJECT_NAME = q1.OBJECT_NAME
14  and q.OBJECT_NAME = q1.OBJECT_NAME
15  and q1.OBJECT_NAME = q2.OBJECT_NAME
16  and q2.OBJECT_NAME = q3.OBJECT_NAME
17  and q3.OBJECT_NAME = q4.OBJECT_NAME
18  and q4.OBJECT_NAME = q5.OBJECT_NAME
19  and q5.OBJECT_NAME = q6.OBJECT_NAME
20  and q6.OBJECT_NAME = q7.OBJECT_NAME
21  and q7.OBJECT_NAME = q8.OBJECT_NAME
22  and q8.OBJECT_NAME = q9.OBJECT_NAME
23  and q9.OBJECT_NAME = q0.OBJECT_NAME;
24  end loop;
25  n2 := dbms_utility.get_time;
26  for i in 1..1000 loop
27  select  q.OBJECT_ID into n
28  from q,q1,q2,q3,q4,q5,q6,q7,q8,q9,q0
29  where q.OBJECT_NAME = q1.OBJECT_NAME
30  and q.OBJECT_NAME = q1.OBJECT_NAME
31  and q1.OBJECT_NAME = q2.OBJECT_NAME
32  and q2.OBJECT_NAME = q3.OBJECT_NAME
33  and q3.OBJECT_NAME = q4.OBJECT_NAME
34  and q4.OBJECT_NAME = q5.OBJECT_NAME
35  and q5.OBJECT_NAME = q6.OBJECT_NAME
36  and q6.OBJECT_NAME = q7.OBJECT_NAME
37  and q7.OBJECT_NAME = q8.OBJECT_NAME
38  and q8.OBJECT_NAME = q9.OBJECT_NAME
39  and q9.OBJECT_NAME = q0.OBJECT_NAME;
40  end loop;
41  n3 := dbms_utility.get_time;
42  for i in 1..1000 loop
43  select /*+all_rows*/ q.OBJECT_ID into n
44  from q,q1,q2,q3,q4,q5,q6,q7,q8,q9,q0
45  where q.OBJECT_NAME = q1.OBJECT_NAME
46  and q.OBJECT_NAME = q1.OBJECT_NAME
47  and q1.OBJECT_NAME = q2.OBJECT_NAME
48  and q2.OBJECT_NAME = q3.OBJECT_NAME
49  and q3.OBJECT_NAME = q4.OBJECT_NAME
50  and q4.OBJECT_NAME = q5.OBJECT_NAME
51  and q5.OBJECT_NAME = q6.OBJECT_NAME
52  and q6.OBJECT_NAME = q7.OBJECT_NAME
53  and q7.OBJECT_NAME = q8.OBJECT_NAME
54  and q8.OBJECT_NAME = q9.OBJECT_NAME
55  and q9.OBJECT_NAME = q0.OBJECT_NAME;
56  end loop;
57  n4 := dbms_utility.get_time;
58
59  dbms_output.put_line('rule :'||to_char(n2 - n1));
60  dbms_output.put_line('analyze,cost:'||to_char(n3 - n2));
61  dbms_output.put_line('all_rows :'||to_char(n4 - n3));
62  end;
63
64  /
rule :76
analyze,cost:75
all_rows :74

PL/SQL procedure successfully completed.

SQL>

xiaba · 发表于 2003-5-30 16:37

1。EAGLE给出的三种情况CBO、RBO、CBO+HINTS得到的执行计划各不相同，因此无法判断究竟是选择表的连接顺序费时还是计划本身不够优化。建议在CBO情况下使用/不使用OUTLINE判断选择表的连接顺序所花费时间。建议在都使用OUTLINE的情况下判断CBO、RBO、CBO+HINTS得到的哪个执行计划最优。
2。BITI的建议我在机器上实际运行，看不出对回答问题有什么提示作用。在RBO情况下查看执行计划使用MERGE JOIN，在CBO情况下使用HASH JOIN。RBO优于CBO。但在判断是选择执行计划费时还是执行计划本身差别导致性能差异方面无法判断。且系统本身对SQL语句的执行计划已缓存，执行1000遍与执行1遍无助于判断优劣，查看EXPLAN PLAN结果就可比较。
3。我的猜想是否由于EAGLE的表结构不同，表中数据多少不同，数据分布差异，系统在每进行一次执行计划选择时比较费时，要产生至少N！个执行计划就更费时了。BITI表过于简单，无法模拟EAGLE表环境。
4。有关优化器生成执行计划的实现和影响因素十分迷惑，各位可有高见或相关资源？

biti_rainy · 发表于 2003-5-30 16:47

去模拟复杂的　表和索引状况　
然后来做实验

但是我相信一点：　　
在执行计划的产生的过程中，oracle是不会花费太多的时间的，所以我认为问题的根本是oracle 在不充分考究执行计划的情况下，或者是　优化器本身的问题，导致选择了不合理的执行计划
而不是　　消耗的　过多的时间去生成执行计划而导致　SQL很慢，除非大量的并发，这才有很大的影响

所以，可以eygle 当初应该给出　查询的　响应时间　和完成时间来做对比！

biti_rainy · 发表于 2003-5-30 16:50

参考这个有些帮助　

eygle · 发表于 2003-5-30 17:38

最初由 biti_rainy 发布
[B]SQL> declare
  2  n1 number;
  3  n2 number;
  4  n3 number;
  5  n4 number;
  6  n number;
  7  begin
  8
  9  n1 := dbms_utility.get_time;
10  for i in 1..1000 loop
11  select /*+RULE*/ q.OBJECT_ID into n
12  from q,q1,q2,q3,q4,q5,q6,q7,q8,q9,q0
13  where q.OBJECT_NAME = q1.OBJECT_NAME
14  and q.OBJECT_NAME = q1.OBJECT_NAME
15  and q1.OBJECT_NAME = q2.OBJECT_NAME
16  and q2.OBJECT_NAME = q3.OBJECT_NAME
17  and q3.OBJECT_NAME = q4.OBJECT_NAME
18  and q4.OBJECT_NAME = q5.OBJECT_NAME
19  and q5.OBJECT_NAME = q6.OBJECT_NAME
20  and q6.OBJECT_NAME = q7.OBJECT_NAME
21  and q7.OBJECT_NAME = q8.OBJECT_NAME
22  and q8.OBJECT_NAME = q9.OBJECT_NAME
23  and q9.OBJECT_NAME = q0.OBJECT_NAME;
24  end loop;
25  n2 := dbms_utility.get_time;
26  for i in 1..1000 loop
27  select /*+first_rows*/ q.OBJECT_ID into n
28  from q,q1,q2,q3,q4,q5,q6,q7,q8,q9,q0
29  where q.OBJECT_NAME = q1.OBJECT_NAME
30  and q.OBJECT_NAME = q1.OBJECT_NAME
31  and q1.OBJECT_NAME = q2.OBJECT_NAME
32  and q2.OBJECT_NAME = q3.OBJECT_NAME
33  and q3.OBJECT_NAME = q4.OBJECT_NAME
34  and q4.OBJECT_NAME = q5.OBJECT_NAME
35  and q5.OBJECT_NAME = q6.OBJECT_NAME
36  and q6.OBJECT_NAME = q7.OBJECT_NAME
37  and q7.OBJECT_NAME = q8.OBJECT_NAME
38  and q8.OBJECT_NAME = q9.OBJECT_NAME
39  and q9.OBJECT_NAME = q0.OBJECT_NAME;
40  end loop;
41  n3 := dbms_utility.get_time;
42  for i in 1..1000 loop
43  select /*+all_rows*/ q.OBJECT_ID into n
44  from q,q1,q2,q3,q4,q5,q6,q7,q8,q9,q0
45  where q.OBJECT_NAME = q1.OBJECT_NAME
46  and q.OBJECT_NAME = q1.OBJECT_NAME
47  and q1.OBJECT_NAME = q2.OBJECT_NAME
48  and q2.OBJECT_NAME = q3.OBJECT_NAME
49  and q3.OBJECT_NAME = q4.OBJECT_NAME
50  and q4.OBJECT_NAME = q5.OBJECT_NAME
51  and q5.OBJECT_NAME = q6.OBJECT_NAME
52  and q6.OBJECT_NAME = q7.OBJECT_NAME
53  and q7.OBJECT_NAME = q8.OBJECT_NAME
54  and q8.OBJECT_NAME = q9.OBJECT_NAME
55  and q9.OBJECT_NAME = q0.OBJECT_NAME;
56  end loop;
57  n4 := dbms_utility.get_time;
58
59  dbms_output.put_line('rule :'||to_char(n2 - n1));
60  dbms_output.put_line('first_rows :'||to_char(n3 - n2));
61  dbms_output.put_line('all_rows :'||to_char(n4 - n3));
62  end;
63  /
rule :118
first_rows :118
all_rows :121

PL/SQL procedure successfully completed.

SQL> analyze table q compute statistics;

Table analyzed.

SQL> declare
  2  n1 number;
  3  n2 number;
  4  n3 number;
  5  n4 number;
  6  n number;
  7  begin
  8
  9  n1 := dbms_utility.get_time;
10  for i in 1..1000 loop
11  select /*+RULE*/ q.OBJECT_ID into n
12  from q,q1,q2,q3,q4,q5,q6,q7,q8,q9,q0
13  where q.OBJECT_NAME = q1.OBJECT_NAME
14  and q.OBJECT_NAME = q1.OBJECT_NAME
15  and q1.OBJECT_NAME = q2.OBJECT_NAME
16  and q2.OBJECT_NAME = q3.OBJECT_NAME
17  and q3.OBJECT_NAME = q4.OBJECT_NAME
18  and q4.OBJECT_NAME = q5.OBJECT_NAME
19  and q5.OBJECT_NAME = q6.OBJECT_NAME
20  and q6.OBJECT_NAME = q7.OBJECT_NAME
21  and q7.OBJECT_NAME = q8.OBJECT_NAME
22  and q8.OBJECT_NAME = q9.OBJECT_NAME
23  and q9.OBJECT_NAME = q0.OBJECT_NAME;
24  end loop;
25  n2 := dbms_utility.get_time;
26  for i in 1..1000 loop
27  select  q.OBJECT_ID into n
28  from q,q1,q2,q3,q4,q5,q6,q7,q8,q9,q0
29  where q.OBJECT_NAME = q1.OBJECT_NAME
30  and q.OBJECT_NAME = q1.OBJECT_NAME
31  and q1.OBJECT_NAME = q2.OBJECT_NAME
32  and q2.OBJECT_NAME = q3.OBJECT_NAME
33  and q3.OBJECT_NAME = q4.OBJECT_NAME
34  and q4.OBJECT_NAME = q5.OBJECT_NAME
35  and q5.OBJECT_NAME = q6.OBJECT_NAME
36  and q6.OBJECT_NAME = q7.OBJECT_NAME
37  and q7.OBJECT_NAME = q8.OBJECT_NAME
38  and q8.OBJECT_NAME = q9.OBJECT_NAME
39  and q9.OBJECT_NAME = q0.OBJECT_NAME;
40  end loop;
41  n3 := dbms_utility.get_time;
42  for i in 1..1000 loop
43  select /*+all_rows*/ q.OBJECT_ID into n
44  from q,q1,q2,q3,q4,q5,q6,q7,q8,q9,q0
45  where q.OBJECT_NAME = q1.OBJECT_NAME
46  and q.OBJECT_NAME = q1.OBJECT_NAME
47  and q1.OBJECT_NAME = q2.OBJECT_NAME
48  and q2.OBJECT_NAME = q3.OBJECT_NAME
49  and q3.OBJECT_NAME = q4.OBJECT_NAME
50  and q4.OBJECT_NAME = q5.OBJECT_NAME
51  and q5.OBJECT_NAME = q6.OBJECT_NAME
52  and q6.OBJECT_NAME = q7.OBJECT_NAME
53  and q7.OBJECT_NAME = q8.OBJECT_NAME
54  and q8.OBJECT_NAME = q9.OBJECT_NAME
55  and q9.OBJECT_NAME = q0.OBJECT_NAME;
56  end loop;
57  n4 := dbms_utility.get_time;
58
59  dbms_output.put_line('rule :'||to_char(n2 - n1));
60  dbms_output.put_line('analyze,cost:'||to_char(n3 - n2));
61  dbms_output.put_line('all_rows :'||to_char(n4 - n3));
62  end;
63
64  /
rule :76
analyze,cost:75
all_rows :74

PL/SQL procedure successfully completed.

SQL> [/B]

你这个测试本身没问题(如果是在9i的环境下)
可是要明确执行计划评估是在评估什么，这当然要包括对于数据量的考虑。

否则NESTED LOOPS、FULL TABLE SCAN的COST评估也就没有什么意义了。

[精华] 关于CBO对于执行计划的影响,一个实际分析的过程!

同意 longbow 的意见

不知道这是否可以给大家一个提示？

疑惑中

你可以参考我的思路

有关优化器生成执行计划的实现和影响因素

Re: 不知道这是否可以给大家一个提示？

浏览过的版块