BITMAP CONVERSION FROM ROWIDS

2021年4月15日 929点热度 0人点赞 0条评论

一个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万数据里面有约一半的数据可以清理或转移,因此待数据清理后应该可以解决问题。

liking

这个人很懒,什么都没留下

文章评论