还是之前的项目的数据库优化过程，这次换成了分析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 | | | | | |----------------------------------------------------复制
看这个执行计划，发现计划中有很多BITMAP CONVERSION TO ROWIDS 字样，看起来貌似用了位图索引。
位图索引说明：
引用资料：http://www.cnblogs.com/LBSer/p/3322630.html
从这篇资料中可以分析出来，位图索引使用的场景为:
位图索引适合静态数据，而不适合索引频繁更新的列
但是我们的表没有建立bitmap类型的索引，询问过开发，都是建立的Normal索引，这是为什么呢？
通过询问公司DBA同事，推荐了一篇文档给我：
引用资料：http://raugher.blog.51cto.com/3472678/1064435
看到这里：
出现这样的情况，是因为表中存在不适当的索引，这些索引列的唯一度不高，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慢的原因不只是因为索引建立不当，跟库表设计还有关系。
这样的话后续优化方向基本明确了，交给开发部门吧。后续有进展的话继续更新此文。
文章转载自小西瓜的技术之路，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
800次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
666次阅读
2025-04-15 17:24:06
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
602次阅读
2025-04-20 10:07:02
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
551次阅读
2025-04-22 00:13:51
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
536次阅读
2025-04-17 17:02:24
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
511次阅读
2025-04-22 00:20:37
一页概览：Oracle GoldenGate
甲骨文云技术
503次阅读
2025-04-30 12:17:56
火焰图--分析复杂SQL执行计划的利器
听见风的声音
473次阅读
2025-04-17 09:30:30
OR+DBLINK的关联SQL优化思路
布衣
385次阅读
2025-05-05 19:28:36
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
383次阅读
2025-04-15 14:48:05
