BITMAPCONVERSIONTOROWIDS导致sql变慢

我们的sql 5分钟没有跑完&＃xff0c;查看计划&＃xff0c;走了BITMAP CONVERSION TO ROWIDS。
 

1. SQL> explain plan for 
2.   2     select    a.username , 
3.   3  a.membertype , 
4.   4         a.providername , 
5.   5         a.tradename , 
6.   6         a.actor , 
7.   7         a.telephone , 
8.   8         a.mobilephone  , 
9.   9         a.email , 
10.  10         b.title , 
11.  11         b.starttime , 
12.  12         b.adminname  
13.  13    from Seller000 a, Chatroom000 b 
14.  14   where a.roomid &＃61; b.roomid 
15.  15     and b.isshow &＃61; &＃39;1&＃39; 
16.  16     and b.roomtype in (&＃39;0&＃39;, &＃39;1&＃39;) 
17.  17     and a.signupdate > to_date(&＃39;2012-08-01&＃39;, &＃39;yyyy-mm-dd hh24:mi:ss&＃39;) 
18.  18     and a.signupdate < to_date(&＃39;2012-09-01&＃39;, &＃39;yyyy-mm-dd hh24:mi:ss&＃39;) 
19.  19   order by b.roomid; 
20.  
21. 已解释。 
22.  
23. SQL> select * from table(dbms_xplan.display); 
24.  
25. PLAN_TABLE_OUTPUT 
26. -------------------------------------------------------------------------------------------------------------------- 
27.  
28. Plan hash value: 273647176 
29.  
30. ---------------------------------------------------------------------------------------------------------- 
31. | Id  | Operation                          | Name                | Rows  | Bytes | Cost (%CPU)| Time  | 
32. ---------------------------------------------------------------------------------------------------------- 
33. |   0 | SELECT STATEMENT                   |                     |    11 |  1782 |    28  (11)| 00:00:01 | 
34. |*  1 |  FILTER                            |                     |       |       |            |       | 
35. |   2 |   TABLE ACCESS BY INDEX ROWID      | SELLER              |     1 |   112 |    28  (11)| 00:00:01 | 
36. |   3 |    NESTED LOOPS                    |                     |    11 |  1782 |    28  (11)| 00:00:01 | 
37. |*  4 |     TABLE ACCESS BY INDEX ROWID    | CHATROOM            | 10975 |   535K|     3   (0)| 00:00:01 | 
38. |   5 |      INDEX FULL SCAN               | PK_CHATROOM         |     9 |       |     1   (0)| 00:00:01 | 
39.  
40. PLAN_TABLE_OUTPUT 
41. -------------------------------------------------------------------------------------------------------------------- 
42.  
43. |   6 |     BITMAP CONVERSION TO ROWIDS    |                     |       |       |            |       | 
44. |   7 |      BITMAP AND                    |                     |       |       |            |       | 
45. |   8 |       BITMAP CONVERSION FROM ROWIDS|                     |       |       |            |       | 
46. |*  9 |        INDEX RANGE SCAN            | IDX_SELLER_ROOMID   |   141 |       |     1   (0)| 00:00:01 | 
47. |  10 |       BITMAP CONVERSION FROM ROWIDS|                     |       |       |            |       | 
48. |  11 |        SORT ORDER BY               |                     |       |       |            |       | 
49. |* 12 |         INDEX RANGE SCAN           | I_SELLER_SIGNUPDATE |   141 |       |     2   (0)| 00:00:01 | 
50. ---------------------------------------------------------------------------------------------------------- 
51.  
52. Predicate Information (identified by operation id): 
53. --------------------------------------------------- 
54.  
55. PLAN_TABLE_OUTPUT 
56. -------------------------------------------------------------------------------------------------------------------- 
57.  
58.  
59.    1 - filter(TO_DATE(&＃39;2012-08-01&＃39;,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;)&＃39;2012-09-01&＃39;,&＃39;yyyy-mm-dd 
60.               hh24:mi:ss&＃39;)) 
61.    4 - filter("B"."ISSHOW"&＃61;&＃39;1&＃39; AND ("B"."ROOMTYPE"&＃61;&＃39;0&＃39; OR "B"."ROOMTYPE"&＃61;&＃39;1&＃39;)) 
62.    9 - access("A"."ROOMID"&＃61;"B"."ROOMID") 
63.   12 - access("A"."SIGNUPDATE">TO_DATE(&＃39;2012-08-01&＃39;,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;) AND 
64.               "A"."SIGNUPDATE"&＃39;2012-09-01&＃39;,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;)) 
65.  
66. 已选择29行。 
67.  
68.  
69. SQL> explain plan for 
70.   2    select  /*&＃43; opt_param(&＃39;_b_tree_bitmap_plans&＃39;, &＃39;false&＃39;) */   a.username , 
71.   3  a.membertype , 
72.   4         a.providername , 
73.   5         a.tradename , 
74.   6         a.actor , 
75.   7         a.telephone , 
76.   8         a.mobilephone  , 
77.   9         a.email , 
78.  10         b.title , 
79.  11         b.starttime , 
80.  12         b.adminname  
81.  13    from Seller000 a, Chatroom000 b 
82.  14   where a.roomid &＃61; b.roomid 
83.  15     and b.isshow &＃61; &＃39;1&＃39; 
84.  16     and b.roomtype in (&＃39;0&＃39;, &＃39;1&＃39;) 
85.  17     and a.signupdate > to_date(&＃39;2012-08-01&＃39;, &＃39;yyyy-mm-dd hh24:mi:ss&＃39;) 
86.  18     and a.signupdate < to_date(&＃39;2012-09-01&＃39;, &＃39;yyyy-mm-dd hh24:mi:ss&＃39;) 
87.  19   order by b.roomid; 
88.  
89. 已解释。 
90.  
91. SQL> select * from table(dbms_xplan.display); 
92.  
93. PLAN_TABLE_OUTPUT 
94. --------------------------------------------------------------------------------------------------------------- 
95.  
96. Plan hash value: 4238594111 
97.  
98. ---------------------------------------------------------------------------------------------------- 
99. | Id  | Operation                      | Name              | Rows  | Bytes | Cost (%CPU)| Time     | 
100. ---------------------------------------------------------------------------------------------------- 
101. |   0 | SELECT STATEMENT               |                   |    11 |  1782 |   240   (0)| 00:00:03 | 
102. |*  1 |  FILTER                        |                   |       |       |            |          | 
103. |*  2 |   TABLE ACCESS BY INDEX ROWID  | SELLER            |     1 |   112 |    34   (0)| 00:00:01 | 
104. |   3 |    NESTED LOOPS                |                   |    11 |  1782 |   240   (0)| 00:00:03 | 
105. |*  4 |     TABLE ACCESS BY INDEX ROWID| CHATROOM          | 10975 |   535K|     3   (0)| 00:00:01 | 
106. |   5 |      INDEX FULL SCAN           | PK_CHATROOM       |     9 |       |     1   (0)| 00:00:01 | 
107.  
108. PLAN_TABLE_OUTPUT 
109. --------------------------------------------------------------------------------------------------------------- 
110.  
111. |*  6 |     INDEX RANGE SCAN           | IDX_SELLER_ROOMID |   141 |       |     1   (0)| 00:00:01 | 
112. ---------------------------------------------------------------------------------------------------- 
113.  
114. Predicate Information (identified by operation id): 
115. --------------------------------------------------- 
116.  
117.    1 - filter(TO_DATE(&＃39;2012-08-01&＃39;,&＃39;yyyy-mm-dd 
118.               hh24:mi:ss&＃39;)2012-09-01&＃39;,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;)) 
119.    2 - filter("A"."SIGNUPDATE">TO_DATE(&＃39;2012-08-01&＃39;,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;) AND 
120.               "A"."SIGNUPDATE"&＃39;2012-09-01&＃39;,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;)) 
121.    4 - filter("B"."ISSHOW"&＃61;&＃39;1&＃39; AND ("B"."ROOMTYPE"&＃61;&＃39;0&＃39; OR "B"."ROOMTYPE"&＃61;&＃39;1&＃39;)) 
122.  
123. PLAN_TABLE_OUTPUT 
124. --------------------------------------------------------------------------------------------------------------- 
125.  
126.    6 - access("A"."ROOMID"&＃61;"B"."ROOMID") 
127.  
128. 已选择23行。 

出现这样的情况&＃xff0c;是因为表中存在不适当的索引&＃xff0c;这些索引列的唯一度不高&＃xff0c;oracle就有可能选择两个这样的索引转为bitmap来执行&＃xff0c;
根据这两个索引的值再确认共同有的ROWID&＃xff0c;最后再通过ROWID回表提取符合条件的数据。
可以使用/*&＃43; opt_param(&＃39;_b_tree_bitmap_plans&＃39;, &＃39;false&＃39;) */ hint在sql级消除bitmap。
最终解决方法&＃xff0c;删除唯一度低的index&＃xff0c;建立组合index。

删除索引IDX_SELLER_ROOMID&＃xff0c;如果I_SELLER_SIGNUPDATE索引确认无用&＃xff0c;也可以删除&＃xff0c;IND_ROOMID_SIGNUPDATE是我们建立的组合索引&＃xff0c;这样消除了BITMAP CONVERSION TO ROWIDS&＃xff0c;30秒出结果。以下是新的执行计划&＃xff1a;

1. SQL> explain plan for 
2.   2    select   a.username , 
3.   3  a.membertype , 
4.   4         a.providername , 
5.   5         a.tradename , 
6.   6         a.actor , 
7.   7         a.telephone , 
8.   8         a.mobilephone  , 
9.   9         a.email , 
10.  10         b.title , 
11.  11         b.starttime , 
12.  12         b.adminname  
13.  13    from Seller000 a, Chatroom000 b 
14.  14   where a.roomid &＃61; b.roomid 
15.  15     and b.isshow &＃61; &＃39;1&＃39; 
16.  16     and b.roomtype in (&＃39;0&＃39;, &＃39;1&＃39;) 
17.  17     and a.signupdate > to_date(&＃39;2012-08-01&＃39;, &＃39;yyyy-mm-dd hh24:mi:ss&＃39;) 
18.  18     and a.signupdate < to_date(&＃39;2012-09-01&＃39;, &＃39;yyyy-mm-dd hh24:mi:ss&＃39;) 
19.  19   order by b.roomid; 
20.  
21. 已解释。 
22.  
23. SQL> select * from table(dbms_xplan.display); 
24.  
25. PLAN_TABLE_OUTPUT 
26. ------------------------------------------------------------------------------------------------------------------------ 
27.  
28. Plan hash value: 2603971898 
29.  
30. -------------------------------------------------------------------------------------------------------- 
31. | Id  | Operation                      | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | 
32. -------------------------------------------------------------------------------------------------------- 
33. |   0 | SELECT STATEMENT               |                       |    11 |  1672 |     9   (0)| 00:00:01 | 
34. |*  1 |  FILTER                        |                       |       |       |            |          | 
35. |   2 |   TABLE ACCESS BY INDEX ROWID  | SELLER                |     1 |   102 |     1   (0)| 00:00:01 | 
36. |   3 |    NESTED LOOPS                |                       |    11 |  1672 |     9   (0)| 00:00:01 | 
37. |*  4 |     TABLE ACCESS BY INDEX ROWID| CHATROOM              |  9978 |   487K|     3   (0)| 00:00:01 | 
38. |   5 |      INDEX FULL SCAN           | PK_CHATROOM           |     9 |       |     1   (0)| 00:00:01 | 
39.  
40. PLAN_TABLE_OUTPUT 
41. ------------------------------------------------------------------------------------------------------------------------ 
42.  
43. |*  6 |     INDEX RANGE SCAN           | IND_ROOMID_SIGNUPDATE |     1 |       |     1   (0)| 00:00:01 | 
44. -------------------------------------------------------------------------------------------------------- 
45.  
46. Predicate Information (identified by operation id): 
47. --------------------------------------------------- 
48.  
49.    1 - filter(TO_DATE(&＃39;2012-08-01&＃39;,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;)&＃39;2012-09-01&＃39;,&＃39;yyyy-mm-dd 
50.               hh24:mi:ss&＃39;)) 
51.    4 - filter("B"."ISSHOW"&＃61;&＃39;1&＃39; AND ("B"."ROOMTYPE"&＃61;&＃39;0&＃39; OR "B"."ROOMTYPE"&＃61;&＃39;1&＃39;)) 
52.    6 - access("A"."ROOMID"&＃61;"B"."ROOMID" AND "A"."SIGNUPDATE">TO_DATE(&＃39;2012-08-01&＃39;,&＃39;yyyy-mm-dd 
53.               hh24:mi:ss&＃39;) AND "A"."SIGNUPDATE"2012-09-01&＃39;,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;)) 
54.  
55. 已选择22行。