一个oracle执行计划不好的例子，dm的计划如何？

aio_o_

经过测试，貌似12c没有这问题........人家早改了....LZ用的哪个版本，我也学习下oracle怎么改进的。

1. SQL> set autot trace exp
   
2. SQL> select b.b,
   
3.   2         max(b.id) , /*很快*/
   
4.   3         min(b.id) min_used
   
5.   4    from big b,
   
6.   5         (select distinct e.a
   
7.   6            from e
   
8.   7          where e.a like '1%') m
   
9.   8  where b.a>=3
   
10.   9     and b.a<=4
    
11. 10     and b.b= m.a
    
12. 11  group by b.b;
    
13. 已用时间:  00: 00: 00.00
    
14. 
    
15. 执行计划
    
16. ----------------------------------------------------------
    
17. Plan hash value: 1357920637
    
18. 
    
19. ------------------------------------------------------------------------------
    
20. | Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    
21. ------------------------------------------------------------------------------
    
22. |   0 | SELECT STATEMENT      |      |    13 |   182 |   631   (2)| 00:00:01 |
    
23. |   1 |  HASH GROUP BY        |      |    13 |   182 |   631   (2)| 00:00:01 |
    
24. |*  2 |   HASH JOIN RIGHT SEMI|      |   128K|  1751K|   629   (2)| 00:00:01 |
    
25. |*  3 |    TABLE ACCESS FULL  | E    |   500 |  1500 |     7   (0)| 00:00:01 |
    
26. |*  4 |    TABLE ACCESS FULL  | BIG  |   285K|  3069K|   621   (2)| 00:00:01 |
    
27. ------------------------------------------------------------------------------
    
28. 
    
29. Predicate Information (identified by operation id):
    
30. ---------------------------------------------------
    
31. 
    
32.    2 - access("B"."B"="E"."A")
    
33.    3 - filter(TO_CHAR("E"."A") LIKE '1%')
    
34.    4 - filter("B"."A">=3 AND "B"."A"<=4)
    
35. 
    
36. Note
    
37. -----
    
38.    - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
    
39. 
    
40. SQL> select b.b,
    
41.   2         max(b.id) - /*很慢*/
    
42.   3         min(b.id) min_used
    
43.   4    from big b,
    
44.   5         (select distinct e.a
    
45.   6            from e
    
46.   7          where e.a like '1%') m
    
47.   8  where b.a>=3
    
48.   9     and b.a<=4
    
49. 10     and b.b= m.a
    
50. 11  group by b.b;
    
51. 已用时间:  00: 00: 00.00
    
52. 
    
53. 执行计划
    
54. ----------------------------------------------------------
    
55. Plan hash value: 1357920637
    
56. 
    
57. ------------------------------------------------------------------------------
    
58. | Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    
59. ------------------------------------------------------------------------------
    
60. |   0 | SELECT STATEMENT      |      |    13 |   182 |   631   (2)| 00:00:01 |
    
61. |   1 |  HASH GROUP BY        |      |    13 |   182 |   631   (2)| 00:00:01 |
    
62. |*  2 |   HASH JOIN RIGHT SEMI|      |   128K|  1751K|   629   (2)| 00:00:01 |
    
63. |*  3 |    TABLE ACCESS FULL  | E    |   500 |  1500 |     7   (0)| 00:00:01 |
    
64. |*  4 |    TABLE ACCESS FULL  | BIG  |   285K|  3069K|   621   (2)| 00:00:01 |
    
65. ------------------------------------------------------------------------------
    
66. 
    
67. Predicate Information (identified by operation id):
    
68. ---------------------------------------------------
    
69. 
    
70.    2 - access("B"."B"="E"."A")
    
71.    3 - filter(TO_CHAR("E"."A") LIKE '1%')
    
72.    4 - filter("B"."A">=3 AND "B"."A"<=4)
    
73. 
    
74. Note
    
75. -----
    
76.    - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
    
77. 
    
78. SQL> set autot off
    
79. SQL> select b.b,
    
80.   2         max(b.id) - /*很慢*/
    
81.   3         min(b.id) min_used
    
82.   4    from big b,
    
83.   5         (select distinct e.a
    
84.   6            from e
    
85.   7          where e.a like '1%') m
    
86.   8  where b.a>=3
    
87.   9     and b.a<=4
    
88. 10     and b.b= m.a
    
89. 11  group by b.b;
    
90. 
    
91.          B   MIN_USED
    
92. ---------- ----------
    
93.          1     999804
    
94.         11     999978
    
95.         12     999804
    
96.         10     999978
    
97. 
    
98. 已用时间:  00: 00: 00.09

复制代码

〇〇

aio_o_ 发表于 2013-9-12 09:18
经过测试，貌似12c没有这问题........人家早改了....LZ用的哪个版本，我也学习下oracle怎么改进的。

SQL> show rel
release 1102000200

myth8860

〇〇 发表于 2013-9-12 09:22
SQL> show rel
release 1102000200

产品总会不断的改进的。

〇〇

aio_o_ 发表于 2013-9-12 09:18
经过测试，貌似12c没有这问题........人家早改了....LZ用的哪个版本，我也学习下oracle怎么改进的。

我的12c set autot on很奇怪
http://www.itpub.net/thread-1816462-1-1.html

pkmonster

lyzhanhun 发表于 2013-9-11 01:55
楼主的插入在Dm中一个存在的问题, 上面的Insert语句connect by后面用改用ROWNUM条件，跟用Level计划上有区别 ...

牛人啊!

northcliff

lyzhanhun 发表于 2013-9-11 01:55
楼主的插入在Dm中一个存在的问题, 上面的Insert语句connect by后面用改用ROWNUM条件，跟用Level计划上有区别 ...

分析得相当透彻。
原因基本分析到位，最关键的问题是connect by里面的rownum造成bdta size减少为1，从而导致insert执行次数多。

关于rownum在connect by里面的实现，需要进一步优化。

〇〇

northcliff 发表于 2013-9-16 11:19
分析得相当透彻。
原因基本分析到位，最关键的问题是connect by里面的rownum造成bdta size减少为1，从而 ...

不知道这个问题什么版本能修复

myth8860

〇〇 发表于 2013-9-23 20:26
不知道这个问题什么版本能修复

下一个更新的版本估计能够解决

dajun539

mark下，回去测试看看

myth8860

dajun539 发表于 2013-9-27 14:51
mark下，回去测试看看

好，多发点价值贴。