一个SQL查询很慢，查看执行计划出现了较多的“BITMAP CONVERSION FROM ROWIDS”，如下所示。
SQL_ID 1x9rd10ykjvjd, child number 0
-------------------------------------
select t.*, t.LIMIT_TIME - SYSDATE AS nowNumber,
t.LIMIT_TIME - SYSDATE - i.manual_task_limit / 24 AS limitNumber from
USER_WAITING_TASK t, t_wfd_process_info i where i.del_flag = NVL('no',
UID) and t.processdefname = i.process_name_en and t.version =
i.version and app_type = 'platform' and (flow_typeid = '4')
and (participantID = '133762' or participantID = '1485') order by
t.workCreateTime desc
Plan hash value: 1934810794
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 17442 |00:04:27.14 | 1092K| 245K| | | |
| 1 | SORT ORDER BY | | 1 | 3775K| 17442 |00:04:27.14 | 1092K| 245K| 8345K| 1135K| 7417K (0)|
| 2 | CONCATENATION | | 1 | | 17442 |00:04:26.94 | 1092K| 245K| | | |
|* 3 | HASH JOIN | | 1 | 3702K| 17436 |00:04:07.86 | 853K| 227K| 897K| 897K| 1340K (0)|
|* 4 | TABLE ACCESS FULL | T_WFD_PROCESS_INFO | 1 | 394 | 394 |00:00:00.01 | 15 | 0 | | | |
|* 5 | HASH JOIN | | 1 | 563K| 17436 |00:04:07.84 | 853K| 227K| 50M| 4080K| 72M (0)|
|* 6 | HASH JOIN RIGHT OUTER | | 1 | 563K| 205K|00:04:02.38 | 705K| 227K| 18M| 4842K| 19M (0)|
|* 7 | TABLE ACCESS FULL | T_BNS_BUSINESS_INFO | 1 | 346K| 346K|00:00:01.16 | 76285 | 0 | | | |
|* 8 | HASH JOIN | | 1 | 554K| 205K|00:04:00.73 | 629K| 227K| 18M| 2738K| 20M (0)|
|* 9 | TABLE ACCESS BY INDEX ROWID | WFWIPARTICIPANT | 1 | 90495 | 207K|00:03:54.23 | 272K| 227K| | | |
| 10 | BITMAP CONVERSION TO ROWIDS | | 1 | | 274K|00:01:32.68 | 74960 | 74520 | | | |
| 11 | BITMAP AND | | 1 | | 21 |00:01:32.45 | 74960 | 74520 | | | |
| 12 | BITMAP OR | | 1 | | 22 |00:00:00.87 | 727 | 720 | | | |
| 13 | BITMAP CONVERSION FROM ROWIDS | | 1 | | 22 |00:00:00.85 | 724 | 720 | | | |
|* 14 | INDEX RANGE SCAN | WFWIPARTICIPANT_N3 | 1 | 219K| 281K|00:00:00.78 | 724 | 720 | | | |
| 15 | BITMAP CONVERSION FROM ROWIDS | | 1 | | 0 |00:00:00.01 | 3 | 0 | | | |
|* 16 | INDEX RANGE SCAN | WFWIPARTICIPANT_N3 | 1 | 219K| 0 |00:00:00.01 | 3 | 0 | | | |
| 17 | BITMAP CONVERSION FROM ROWIDS | | 1 | | 300 |00:01:31.50 | 74233 | 73800 | | | |
|* 18 | INDEX RANGE SCAN | WFWIPARTICIPANT_N4 | 1 | 219K| 27M|00:01:26.31 | 74233 | 73800 | | | |
|* 19 | TABLE ACCESS BY INDEX ROWID | WFWORKITEM | 1 | 176K| 1089K|00:00:04.72 | 357K| 24 | | | |
|* 20 | INDEX RANGE SCAN | IDX_WFWI_STATE | 1 | 178K| 1089K|00:00:00.62 | 4383 | 6 | | | |
|* 21 | TABLE ACCESS FULL | T_BPM_FORM_INFO | 1 | 970K| 1503K|00:00:03.87 | 147K| 2 | | | |
|* 22 | HASH JOIN | | 1 | 73402 | 6 |00:00:19.08 | 238K| 18158 | 897K| 897K| 1339K (0)|
|* 23 | TABLE ACCESS FULL | T_WFD_PROCESS_INFO | 1 | 394 | 394 |00:00:00.01 | 15 | 0 | | | |
|* 24 | HASH JOIN OUTER | | 1 | 11171 | 6 |00:00:19.08 | 238K| 18158 | 713K| 713K| 1520K (0)|
| 25 | NESTED LOOPS | | 1 | 10990 | 6 |00:00:15.90 | 162K| 16691 | | | |
| 26 | NESTED LOOPS | | 1 | 10990 | 13 |00:00:15.90 | 162K| 16691 | | | |
|* 27 | HASH JOIN | | 1 | 10990 | 17 |00:00:15.90 | 162K| 16691 | 1027K| 1027K| 1262K (0)|
|* 28 | TABLE ACCESS BY INDEX ROWID | WFWIPARTICIPANT | 1 | 10990 | 88 |00:00:14.58 | 17762 | 16689 | | | |
| 29 | BITMAP CONVERSION TO ROWIDS | | 1 | | 7128 |00:00:13.41 | 11160 | 11056 | | | |
| 30 | BITMAP AND | | 1 | | 1 |00:00:13.41 | 11160 | 11056 | | | |
| 31 | BITMAP OR | | 1 | | 22 |00:00:00.77 | 728 | 717 | | | |
| 32 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 22 |00:00:00.75 | 725 | 717 | | | |
|* 33 | INDEX RANGE SCAN | WFWIPARTICIPANT_N3 | 1 | 26618 | 281K|00:00:00.69 | 725 | 717 | | | |
| 34 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 0 |00:00:00.01 | 3 | 0 | | | |
|* 35 | INDEX RANGE SCAN | WFWIPARTICIPANT_N3 | 1 | 26618 | 0 |00:00:00.01 | 3 | 0 | | | |
| 36 | BITMAP CONVERSION FROM ROWIDS | | 1 | | 179 |00:00:12.57 | 10432 | 10339 | | | |
|* 37 | INDEX RANGE SCAN | WFWIPARTICIPANT_N4 | 1 | 26618 | 3337K|00:00:11.75 | 10432 | 10339 | | | |
|* 38 | TABLE ACCESS BY INDEX ROWID | WFWORKITEM | 1 | 47401 | 258K|00:00:01.19 | 144K| 2 | | | |
|* 39 | INDEX RANGE SCAN | IDX_WFWI_STATE | 1 | 47736 | 291K|00:00:00.15 | 1034 | 1 | | | |
|* 40 | INDEX RANGE SCAN | PK_WF_FORM_INFO_UQ3 | 17 | 1 | 13 |00:00:00.01 | 36 | 0 | | | |
|* 41 | TABLE ACCESS BY INDEX ROWID | T_BPM_FORM_INFO | 13 | 1 | 6 |00:00:00.01 | 12 | 0 | | | |
|* 42 | TABLE ACCESS FULL | T_BNS_BUSINESS_INFO | 1 | 346K| 346K|00:00:02.92 | 76295 | 1467 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("WI"."PROCESSDEFNAME"="I"."PROCESS_NAME_EN" AND "I"."VERSIONSIGN"="I"."VERSION")
4 - filter("I"."DEL_FLAG"=SYS_OP_C2C('no'))
5 - access("WI"."EXTEND7"="I"."PROC_INS_ID")
6 - access("WI"."ACTIVITYINSTID"="BU"."ACTIVE_ID")
7 - filter("BU"."ACTIVE_ID" IS NOT NULL)
8 - access("WI"."WORKITEMID"="WP"."WORKITEMID")
9 - filter(("WP"."ENDTIME" IS NULL AND INTERNAL_FUNCTION("WP"."PARTIINTYPE")))
14 - access("WP"."PARTICIPANTID"='133762')
16 - access("WP"."PARTICIPANTID"='1485')
18 - access("WP"."PARTIINTYPE"='GET')
filter(("WP"."PARTIINTYPE"='EXE' OR "WP"."PARTIINTYPE"='GET'))
19 - filter("WI"."EXTEND1" IS NULL)
20 - access("WI"."CURRENTSTATE"=4)
filter(("WI"."CURRENTSTATE"=4 OR "WI"."CURRENTSTATE"=10))
21 - filter(("I"."FLOW_TYPEID"='4' AND "I"."APP_TYPE"='platform'))
22 - access("WI"."PROCESSDEFNAME"="I"."PROCESS_NAME_EN" AND "I"."VERSIONSIGN"="I"."VERSION")
23 - filter("I"."DEL_FLAG"=SYS_OP_C2C('no'))
24 - access("WI"."ACTIVITYINSTID"="BU"."ACTIVE_ID")
27 - access("WI"."WORKITEMID"="WP"."WORKITEMID")
28 - filter(("WP"."ENDTIME" IS NULL AND INTERNAL_FUNCTION("WP"."PARTIINTYPE")))
33 - access("WP"."PARTICIPANTID"='133762')
35 - access("WP"."PARTICIPANTID"='1485')
37 - access("WP"."PARTIINTYPE"='EXE')
filter(("WP"."PARTIINTYPE"='EXE' OR "WP"."PARTIINTYPE"='GET'))
38 - filter("WI"."EXTEND1" IS NULL)
39 - access("WI"."CURRENTSTATE"=10)
filter(("WI"."CURRENTSTATE"=4 OR "WI"."CURRENTSTATE"=10))
40 - access("WI"."EXTEND7"="I"."PROC_INS_ID")
41 - filter(("I"."FLOW_TYPEID"='4' AND "I"."APP_TYPE"='platform'))
42 - filter("BU"."ACTIVE_ID" IS NOT NULL)
但是实际并没有建立位图索引，这个bitmap索引是哪来的呢？如此复杂的执行计划，看着都累，各个conversion转换步骤也务必消耗较多的CPU，实际这个paticipantid的数据量也确实比较大，达到28万，经历较多的查询转换和join，也势必会消耗较多的时间。再查看这两个涉及的索引，分析发现这两个索引不合理，唯一度极其不高，尤其是WFWIPARTICIPANT_N4这个索引，总共只有4个值，显然这个索引必须删掉，另一个索引WFWIPARTICIPANT_N3的数据也很不均衡，数据量小的行数只有个位数，数据量大的行数多达20-30万，甚至还有少数50万、80万、100万以上的，显然这个索引也可以删除，如果没有其他业务需求必须要这个索引，也可以一起删掉，同时只针对这个慢查询建组合索引。
继续以上话题，这个bitmap索引到底是怎么回事呢？它到底来自哪里？由于知识所限，只能带着满脑子疑问去网搜，结果基本明晰，如下摘抄几段网文。
1、出现这样的情况，是因为表中存在不适当的索引，这些索引列的唯一度不高，oracle就有可能选择两个这样的索引转为bitmap来执行；然后根据这两个索引的值再确认共同有的ROWID，最后再通过ROWID回表提取符合条件的数据
2、可以使用/+ opt_param('_b_tree_bitmap_plans','false') /hint 在sql级消除bitmap
3、惜分飞大侠的解释：oracle的cbo是根据cost来决定大小来选择合适的执行计划，当它计算获得通过bitmap的方式执行的时候cost会更小，它就会选择使用这样的执行计划。一般出现这样的情况，都是因为对表建立的不适当的index导致，特别是对表中的唯一度不高的列建立了index，然后oracle就有可能选择两个这样的列转为为bitmap来执行。根据oracle的执行计划，肯定是cost最小的，但是它很多时候忽略了一致性读等其他条件，导致这个执行计划并非像oracle想象的那样最优，因为把btree index转为为bitmap index执行，需要消耗更多的cpu
综合以上网文，显然应该采取的措施是，删除唯一度低的index，建立组合index。
保存好原索引创建语句，新建组合索引，删掉涉及的几个导致位图转换的索引后，执行计划变得极其简洁，如下。
SQL_ID 1x9rd10ykjvjd, child number 0
-------------------------------------
select t.*, t.LIMIT_TIME - SYSDATE AS nowNumber,
t.LIMIT_TIME - SYSDATE - i.manual_task_limit / 24 AS limitNumber from
USER_WAITING_TASK t, t_wfd_process_info i where i.del_flag = NVL('no',
UID) and t.processdefname = i.process_name_en and t.version =
i.version and app_type = 'platform' and (flow_typeid = '4')
and (participantID = '133762' or participantID = '1485') order by
t.workCreateTime desc
Plan hash value: 584196048
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 17468 |00:03:06.51 | 999K| 561K| | | |
| 1 | SORT ORDER BY | | 1 | 443K| 17468 |00:03:06.51 | 999K| 561K| 8345K| 1135K| 7417K (0)|
|* 2 | HASH JOIN | | 1 | 443K| 17468 |00:03:06.39 | 999K| 561K| 914K| 914K| 1277K (0)|
|* 3 | TABLE ACCESS FULL | T_WFD_PROCESS_INFO | 1 | 394 | 395 |00:00:00.01 | 15 | 0 | | | |
|* 4 | HASH JOIN | | 1 | 67508 | 17468 |00:03:06.37 | 999K| 561K| 43M| 4175K| 46M (0)|
|* 5 | HASH JOIN RIGHT OUTER | | 1 | 73421 | 205K|00:03:03.00 | 851K| 561K| 18M| 4842K| 19M (0)|
|* 6 | TABLE ACCESS FULL | T_BNS_BUSINESS_INFO | 1 | 346K| 347K|00:00:01.12 | 76285 | 0 | | | |
|* 7 | HASH JOIN | | 1 | 73421 | 205K|00:03:01.40 | 775K| 561K| 15M| 3059K| 17M (0)|
| 8 | INLIST ITERATOR | | 1 | | 207K|00:01:27.07 | 167K| 125K| | | |
|* 9 | TABLE ACCESS BY INDEX ROWID| WFWIPARTICIPANT | 4 | 101K| 207K|00:01:27.00 | 167K| 125K| | | |
|* 10 | INDEX RANGE SCAN | WFWIPARTICIPANT_N4_N3 | 4 | 245K| 209K|00:00:00.98 | 954 | 867 | | | |
|* 11 | TABLE ACCESS FULL | WFWORKITEM | 1 | 1373K| 1348K|00:01:32.33 | 607K| 436K| | | |
|* 12 | TABLE ACCESS FULL | T_BPM_FORM_INFO | 1 | 970K| 1508K|00:00:02.08 | 147K| 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("WI"."PROCESSDEFNAME"="I"."PROCESS_NAME_EN" AND "I"."VERSIONSIGN"="I"."VERSION")
3 - filter("I"."DEL_FLAG"=SYS_OP_C2C('no'))
4 - access("WI"."EXTEND7"="I"."PROC_INS_ID")
5 - access("WI"."ACTIVITYINSTID"="BU"."ACTIVE_ID")
6 - filter("BU"."ACTIVE_ID" IS NOT NULL)
7 - access("WI"."WORKITEMID"="WP"."WORKITEMID")
filter((("WI"."CURRENTSTATE"=10 AND "WP"."PARTIINTYPE"='EXE') OR ("WI"."CURRENTSTATE"=4 AND "WP"."PARTIINTYPE"='GET')))
9 - filter("WP"."ENDTIME" IS NULL)
10 - access((("WP"."PARTIINTYPE"='EXE' OR "WP"."PARTIINTYPE"='GET')) AND (("WP"."PARTICIPANTID"='133762' OR "WP"."PARTICIPANTID"='1485')))
11 - filter((INTERNAL_FUNCTION("WI"."CURRENTSTATE") AND "WI"."EXTEND1" IS NULL))
12 - filter(("I"."FLOW_TYPEID"='4' AND "I"."APP_TYPE"='platform'))
对于数据量30万规模的id，查询时间明显减少，由原来的180秒左右，降至100秒以内，10万规模的id查询时间降至20秒左右，大部分查询降至秒级或一秒以内。鉴于这是一台比较繁忙的主机，CPU负荷较高，如此大数据量的交叉连接查询计算基本也算正常（另一台测试一体机已经全部降至10秒以内）。为进一步提高性能，也对数据做了详细分析，发现3000万数据里面有约一半的数据可以清理或转移，因此待数据清理后应该可以解决问题。
文章评论