还是之前的项目的数据库优化过程，这次换成了分析AWR报告中的TOP sql
项目中有一个很长的sql，篇幅原因就不给大家写出来了，一共4个UNION组合。。。很长。
一共这个sql查出来了20多条数据，但是用了2.1秒左右，考虑到该sql在业务中的实际应用情况，每2分钟都要刷新一次，这样的话对DB性能消耗也是个影响，于是决定优化这个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慢的原因不只是因为索引建立不当，跟库表设计还有关系。这样的话后续优化方向基本明确了，交给开发部门吧。后续有进展的话继续更新此文。
