oracle表中没有用bitmap索引，但是执行计划中却有bit
2017-08-05 本文已影响0人
小西瓜的Summer
还是之前的项目的数据库优化过程，这次换成了分析AWR报告中的TOP SQL
项目中有一个很长的SQL，篇幅原因就不给大家写出来了，一共4个UNION组合。。。很长。
一共这个sql查出来了20多条数据，但是用了2.1秒左右，考虑到该sql在业务中的实际应用情况，每2分钟都要刷新一次，这样的话对DB性能消耗也是个影响，于是决定优化这个SQL一下。
获取该sql的执行计划
Plan Hash Value : 1613327291
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 45240 | 66239 | 00:13:15 |
| * 1 | VIEW | | 40 | 45240 | 66239 | 00:13:15 |
| * 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 5586 | 6245148 | 66239 | 00:13:15 |
| * 4 | SORT UNIQUE STOPKEY | | 5586 | 3376642 | 65901 | 00:13:11 |
| 5 | UNION-ALL | | | | | |
| 6 | NESTED LOOPS OUTER | | 4919 | 1485538 | 43893 | 00:08:47 |
| * 7 | HASH JOIN | | 4919 | 1323211 | 29271 | 00:05:52 |
| * 8 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARM_DEAL | 23800 | 2332400 | 8512 | 00:01:43 |
| 9 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 10 | BITMAP AND | | | | | |
| 11 | BITMAP OR | | | | | |
| 12 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 13 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_DEALSTATE | | | 551 | 00:00:07 |
| 14 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 15 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_DEALSTATE | | | 122 | 00:00:02 |
| 16 | BITMAP OR | | | | | |
| 17 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 18 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 19 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 20 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 21 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 22 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 23 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 24 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 25 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 26 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 969 | 00:00:12 |
| 27 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 28 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 29 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 30 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 31 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 32 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 33 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 34 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 35 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 36 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 37 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 38 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 39 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 40 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 41 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 42 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 43 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 44 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 163 | 00:00:02 |
| 45 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 46 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 47 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 48 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 49 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 50 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 51 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 52 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 53 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 54 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 55 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 56 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 57 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 58 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 59 | TABLE ACCESS FULL | RAMS_ALARM_WORKSHEET | 681549 | 116544879 | 14736 | 00:02:57 |
| 60 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARMMAIN | 1 | 33 | 3 | 00:00:01 |
| * 61 | INDEX RANGE SCAN | RAMS_ALARMMAIN_SHEETNO | 1 | | 2 | 00:00:01 |
| 62 | NESTED LOOPS OUTER | | 1 | 344 | 15062 | 00:03:01 |
| 63 | NESTED LOOPS | | 1 | 311 | 15059 | 00:03:01 |
| * 64 | HASH JOIN | | 273 | 38220 | 14240 | 00:02:51 |
| * 65 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARM_DEAL | 6819 | 722814 | 4937 | 00:01:00 |
| 66 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 67 | BITMAP AND | | | | | |
| 68 | BITMAP OR | | | | | |
| 69 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 70 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_DEALSTATE | | | 551 | 00:00:07 |
| 71 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 72 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_DEALSTATE | | | 122 | 00:00:02 |
| 73 | BITMAP OR | | | | | |
| 74 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 75 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 76 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 77 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 78 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 79 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 80 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 81 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 969 | 00:00:12 |
| 82 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 83 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 84 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 85 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 86 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 87 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 88 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 89 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 90 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 91 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 92 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 93 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 94 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 95 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 96 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 97 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 98 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 99 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 100 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 101 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 163 | 00:00:02 |
| 102 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 103 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 104 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 105 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 106 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 107 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 108 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 109 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 110 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 111 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 112 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 113 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 114 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 115 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_B | | | 7 | 00:00:01 |
| 116 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 117 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_DEALTYPE | | | 1389 | 00:00:17 |
| 118 | VIEW | VW_SQ_1 | 73352 | 2493968 | 9302 | 00:01:52 |
| 119 | HASH GROUP BY | | 73352 | 3227488 | 9302 | 00:01:52 |
| * 120 | INDEX FAST FULL SCAN | RAMS_ALARM_DEAL_SHEETNO | 76580 | 3369520 | 8468 | 00:01:42 |
| 121 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARM_WORKSHEET | 1 | 171 | 3 | 00:00:01 |
| * 122 | INDEX RANGE SCAN | RAMS_WORKSHEET_WS_NUM | 1 | | 2 | 00:00:01 |
| 123 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARMMAIN | 1 | 33 | 3 | 00:00:01 |
| * 124 | INDEX RANGE SCAN | RAMS_ALARMMAIN_SHEETNO | 1 | | 2 | 00:00:01 |
| 125 | NESTED LOOPS OUTER | | 665 | 202160 | 6620 | 00:01:20 |
| 126 | NESTED LOOPS | | 661 | 179131 | 4656 | 00:00:56 |
| * 127 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARM_DEAL | 661 | 66100 | 2672 | 00:00:33 |
| 128 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 129 | BITMAP AND | | | | | |
| 130 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 131 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_DEALTYPE | 61440 | | 145 | 00:00:02 |
| 132 | BITMAP OR | | | | | |
| 133 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 134 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_DEALSTATE | 61440 | | 551 | 00:00:07 |
| 135 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 136 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_DEALSTATE | 61440 | | 122 | 00:00:02 |
| 137 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARM_WORKSHEET | 1 | 171 | 3 | 00:00:01 |
| * 138 | INDEX RANGE SCAN | RAMS_WORKSHEET_WS_NUM | 1 | | 2 | 00:00:01 |
| 139 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARMMAIN | 1 | 33 | 3 | 00:00:01 |
| * 140 | INDEX RANGE SCAN | RAMS_ALARMMAIN_SHEETNO | 1 | | 2 | 00:00:01 |
| * 141 | FILTER | | | | | |
| 142 | NESTED LOOPS OUTER | | 1 | 279 | 35 | 00:00:01 |
| 143 | NESTED LOOPS | | 1 | 246 | 32 | 00:00:01 |
| * 144 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARM_DEAL | 1 | 75 | 29 | 00:00:01 |
| 145 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 146 | BITMAP AND | | | | | |
| 147 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 148 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_DEALROLE | 449 | | 4 | 00:00:01 |
| 149 | BITMAP OR | | | | | |
| 150 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 151 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_DEALTYPE | 449 | | 10 | 00:00:01 |
| 152 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 153 | INDEX RANGE SCAN | RAMS_ALARM_DEAL_DEALTYPE | 449 | | 15 | 00:00:01 |
| * 154 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARM_WORKSHEET | 1 | 171 | 3 | 00:00:01 |
| * 155 | INDEX RANGE SCAN | RAMS_WORKSHEET_WS_NUM | 1 | | 2 | 00:00:01 |
| 156 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARMMAIN | 1 | 33 | 3 | 00:00:01 |
| * 157 | INDEX RANGE SCAN | RAMS_ALARMMAIN_SHEETNO | 1 | | 2 | 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
看这个执行计划，发现计划中有很多BITMAP CONVERSION TO ROWIDS 字样，看起来貌似用了位图索引。
位图索引说明：
从这篇资料中可以分析出来，位图索引使用的场景为:
位图索引适合静态数据，而不适合索引频繁更新的列
但是我们的表没有建立bitmap类型的索引，询问过开发，都是建立的Normal索引，这是为什么呢？
通过询问公司DBA同事，推荐了一篇文档给我：
看到这里：
出现这样的情况，是因为表中存在不适当的索引，这些索引列的唯一度不高，oracle就有可能选择两个这样的索引转为bitmap来执行
之后询问开发，发现有一张表中建立了很多不能通过该字段判断唯一性的索引列，这样导致oracle自行判断，就在执行计划中触发了bitmap。
知道原因后，将表中不能判断唯一性的字段索引去掉，重新执行SQL，观察执行计划：
Plan Hash Value : 1709251740
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 45240 | 71112 | 00:14:14 |
| * 1 | VIEW | | 40 | 45240 | 71112 | 00:14:14 |
| * 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 39042 | 43648956 | 71112 | 00:14:14 |
| * 4 | SORT UNIQUE STOPKEY | | 39042 | 22727766 | 68852 | 00:13:47 |
| 5 | UNION-ALL | | | | | |
| * 6 | HASH JOIN OUTER | | 37719 | 10976229 | 37682 | 00:07:33 |
| * 7 | HASH JOIN | | 35734 | 9219372 | 17105 | 00:03:26 |
| * 8 | TABLE ACCESS FULL | RAMS_ALARM_DEAL | 15417 | 1464615 | 7353 | 00:01:29 |
| 9 | TABLE ACCESS FULL | RAMS_ALARM_WORKSHEET | 457958 | 74647154 | 9751 | 00:01:58 |
| 10 | TABLE ACCESS FULL | RAMS_ALARMMAIN | 458155 | 15119115 | 19143 | 00:03:50 |
| 11 | NESTED LOOPS OUTER | | 1 | 332 | 15431 | 00:03:06 |
| 12 | NESTED LOOPS | | 1 | 299 | 15427 | 00:03:06 |
| * 13 | HASH JOIN | | 175 | 23800 | 15248 | 00:03:03 |
| * 14 | TABLE ACCESS FULL | RAMS_ALARM_DEAL | 4396 | 452788 | 7347 | 00:01:29 |
| 15 | VIEW | VW_SQ_1 | 49438 | 1631454 | 7900 | 00:01:35 |
| 16 | HASH GROUP BY | | 49438 | 2125834 | 7900 | 00:01:35 |
| * 17 | TABLE ACCESS FULL | RAMS_ALARM_DEAL | 51696 | 2222928 | 7346 | 00:01:29 |
| * 18 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARM_WORKSHEET | 1 | 163 | 2 | 00:00:01 |
| * 19 | INDEX RANGE SCAN | RAMS_WORKSHEET_WS_NUM | 1 | | 1 | 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARMMAIN | 1 | 33 | 4 | 00:00:01 |
| * 21 | INDEX RANGE SCAN | RAMS_ALARMMAIN_SHEETNO | 1 | | 2 | 00:00:01 |
| 22 | NESTED LOOPS OUTER | | 1321 | 387053 | 13375 | 00:02:41 |
| 23 | NESTED LOOPS | | 1251 | 325260 | 8370 | 00:01:41 |
| * 24 | TABLE ACCESS FULL | RAMS_ALARM_DEAL | 507 | 49179 | 7347 | 00:01:29 |
| * 25 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARM_WORKSHEET | 2 | 326 | 8 | 00:00:01 |
| * 26 | INDEX RANGE SCAN | RAMS_WORKSHEET_WS_NUM | 7 | | 1 | 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARMMAIN | 1 | 33 | 4 | 00:00:01 |
| * 28 | INDEX RANGE SCAN | RAMS_ALARMMAIN_SHEETNO | 1 | | 2 | 00:00:01 |
| * 29 | FILTER | | | | | |
| 30 | NESTED LOOPS OUTER | | 1 | 269 | 7357 | 00:01:29 |
| 31 | NESTED LOOPS | | 1 | 236 | 7353 | 00:01:29 |
| * 32 | TABLE ACCESS FULL | RAMS_ALARM_DEAL | 1 | 73 | 7345 | 00:01:29 |
| * 33 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARM_WORKSHEET | 1 | 163 | 8 | 00:00:01 |
| * 34 | INDEX RANGE SCAN | RAMS_WORKSHEET_WS_NUM | 7 | | 1 | 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | RAMS_ALARMMAIN | 1 | 33 | 4 | 00:00:01 |
| * 36 | INDEX RANGE SCAN | RAMS_ALARMMAIN_SHEETNO | 1 | | 2 | 00:00:01 |
----------------------------------------------------------------------------------------------------------------
可以看到，这次的执行计划中就没有BITMAP CONVERSION TO ROWIDS了，在测试系统上进行测试，sql的执行时间为1.2秒，比之前快了不少。
但是将这个改动搬移到正式库上的时候，一会儿就有用户反映项目卡的不能用。。。
之后对比了下正式和测试库的数据量，发现正式库比测试库这几张表的数量级大了一倍多，并且都已经到了百万级以上。
观察大表是否有分区情况，答案是没有。。。。。so sad
看来SQL慢的原因不只是因为索引建立不当，跟库表设计还有关系。
这样的话后续优化方向基本明确了，交给开发部门吧。后续有进展的话继续更新此文。
