位图与或运算导致的执行计划异常分析-cost更低执行时间更长
索引位图与或运算导致的执行计划异常分析-cost更低执行时间更长
近遇到一个系统主备之间对象完全一致，区别就是主备在表对象上创建索引的时间不同，发现同一个SQL执行计划和执行时间不同。
主库执行了位图与或运算的执行计划（下面会有），备库执行了走索引回表的方式，但是没有通过索引与或运算再回表的方式，后来在备库
收集了统计信息后主备执行计划一致都是通过索引与或计算获得，我们知道Oracle会根据cost的大小决定执行计划，但是这里Oracle选择了
认为正确的执行计划但是执行时间却更长，下面我们分析过程。
explain plan for
SELECT *
FROM (SELECT TMP_PAGE.*, ROWNUM ROW_ID
FROM (SELECT *
FROM YSZX.VI_MAIN
WHERE flag != 'N'
and (ydid = czydid or (czydid is null))
and ztgjend in ('35', '40', '60', '70', '80')
and ysfs = '1'
and zcrq >= '20211114'
and zcrq <= '20211117'
and (LAST_MODIFY_TIME >= sysdate - 124)
and (dj in ('H00') or dztmism in ('38552', '20093'))
order By LAST_MODIFY_TIME desc, dzsj desc, ydid asc) TMP_PAGE)
WHERE ROW_ID <= 10
AND ROW_ID > 0;
select * from table(dbms_xplan.display());
主库执行计划
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 960144431
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35941 | 166M| | 303K (1)| 00:00:12 |
|* 1 | VIEW | | 35941 | 166M| | 303K (1)| 00:00:12 |
| 2 | COUNT | | | | | | |
| 3 | VIEW | | 35941 | 166M| | 303K (1)| 00:00:12 |
| 4 | SORT ORDER BY | | 35941 | 46M| 56M| 303K (1)| 00:00:12 |
|* 5 | FILTER | | | | | | |
|* 6 | HASH JOIN RIGHT OUTER | | 35941 | 46M| | 293K (1)| 00:00:12 |
|* 7 | TABLE ACCESS FULL | AQNAL | 1 | 32 | | 3016 (1)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 35941 | 45M| | 290K (1)| 00:00:12 |
|* 9 | TABLE ACCESS BY INDEX ROWID BATCHED| T_SI_YD | 35941 | 33M| | 182K (1)| 00:00:08 |
| 10 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 11 | BITMAP AND | | | | | | |
| 12 | BITMAP OR | | | | | | |
| 13 | BITMAP CONVERSION FROM ROWIDS | | | | | | |
|* 14 | INDEX RANGE SCAN | YD_DJ_IDX | 1306K| | | 9062 (1)| 00:00:01 |
| 15 | BITMAP CONVERSION FROM ROWIDS | | | | | | |
|* 16 | INDEX RANGE SCAN | YD_DZTMISM_IDX | 1306K| | | 38 (0)| 00:00:01 |
| 17 | BITMAP CONVERSION FROM ROWIDS | | | | | | |
|* 18 | INDEX RANGE SCAN | YD_DZTMISM_IDX | 1306K| | | 38 (0)| 00:00:01 |
| 19 | BITMAP CONVERSION FROM ROWIDS | | | | | | |
| 20 | SORT ORDER BY | | | | 20M| | |
|* 21 | INDEX RANGE SCAN | YD_ZCRQ_IDX | 1306K| | | 4935 (1)| 00:00:01 |
| 22 | BITMAP CONVERSION FROM ROWIDS | | | | | | |
|* 23 | INDEX RANGE SCAN | YD_YSFS_IDX | 1306K| | | 72798 (1)| 00:00:03 |
| 24 | TABLE ACCESS BY INDEX ROWID | T_S_Y_JH | 1 | 355 | | 3 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | SYS_C0016306 | 1 | | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_ID"<=10 AND "ROW_ID">0)
5 - filter......
6 - access("DD"."HPH"="AQNAL"."WAYBILLCODE"(+))
7 - filter("STOPFLAG"(+)='Y' AND "FLAG"(+)='Y')
9 - filter(("FS"."CZYDID" IS NULL OR "FS"."YDID"="FS"."CZYDID") AND "FS"."FLAG"<>'N' AND
"FS"."LAST_MODIFY_TIME">=SYSDATE@!-124)
14 - access("FS"."DJ"='H00')
16 - access("FS"."DZTMISM"='20093')
18 - access("FS"."DZTMISM"='38552')
21 - access("FS"."ZCRQ">='20211114' AND "FS"."ZCRQ"<='20211117')
23 - access("FS"."YSFS"='1')
25 - access("FS"."YDID"="DD"."YDID"(+))
63 rows selected.
该执行计划SQL执行需要
Elapsed: 00:00:02.76
从执行计划看对表T_SI_YD的几个索引通过BITMAP CONVERSION FROM ROWIDS转换为位图，再做与或计算，然后回表的方式获取数据，然后
走了一个嵌套和一个hash连接两个join操作。
下面看二中心的执行计划（之前没有走索引与或计算的执行计划）
索引创建时间（一中心）
SQL> select created from dba_objects where object_name='YD_DZTMISM_IDX';
CREATED
-------------------
2021-05-24 14:53:26
Elapsed: 00:00:00.04
SQL> select created from dba_objects where object_name='YD_ZCRQ_IDX';
CREATED
-------------------
2021-05-24 15:00:00
Elapsed: 00:00:00.03
索引创建时间（二中心）
SQL> select created from dba_objects where object_name='YD_DZTMISM_IDX';
CREATED
-------------------
2021-10-22 14:52:51
Elapsed: 00:00:00.04
SQL> select created from dba_objects where object_name='YD_ZCRQ_IDX';
CREATED
-------------------
2021-10-22 14:49:53
Elapsed: 00:00:00.03
备库的执行计划（未收集统计信息前）
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3731573047
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34927 | 162M| | 397K (1)| 00:00:16 |
|* 1 | VIEW | | 34927 | 162M| | 397K (1)| 00:00:16 |
| 2 | COUNT | | | | | | |
| 3 | VIEW | | 34927 | 161M| | 397K (1)| 00:00:16 |
| 4 | SORT ORDER BY | | 34927 | 45M| 54M| 397K (1)| 00:00:16 |
|* 5 | FILTER | | | | | | |
|* 6 | HASH JOIN RIGHT OUTER | | 34927 | 45M| | 387K (1)| 00:00:16 |
|* 7 | TABLE ACCESS FULL | AQNAL | 1 | 32 | | 3022 (1)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 34927 | 44M| | 384K (1)| 00:00:16 |
|* 9 | TABLE ACCESS BY INDEX ROWID BATCHED| T_SI_YD | 34927 | 32M| | 280K (1)| 00:00:11 |
|* 10 | INDEX RANGE SCAN | YD_ZCRQ_IDX | 1365K| | | 4131 (1)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | T_S_Y_JH | 1 | 353 | | 3 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | SYS_C0024237 | 1 | | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
这里直接通过索引，而没有通过索引位图计算的方式，显然这种方式cost更高，但是这种方式Oracle执行更快，这里的问题出在位图与或计算上。
Oracle的初衷是使用同一个表上的索引与或计算的方式减少回表次数，通过一次位图计算通过索引再回表的方式获得数据，但是这种方式确实在
不合理的情况下，执行更慢，比如索引创建的不合理。那我们看下那些索引不合理。
涉及三张表T_SI_YD，T_S_Y_JH，AQNAL其中表T_SI_YD涉及的索引YD_DJ_IDX和YD_DZTMISM_IDX都不合理，没必要创建。显然第二个执行计划更好
解决方案： 1 删除索引，或者建立符合索引
2 设置隐藏参数，回避索引的位图扫描
3 在SQL语句中直接绑定hint回表索引的位图与或计算方式
1. drop index idx_name;
2. alter system set "_b_tree_bitmap_plans"=false;
3.
explain plan for
SELECT *
FROM (SELECT TMP_PAGE.*, ROWNUM ROW_ID
FROM (SELECT /*+ opt_param('_b_tree_bitmap_plans','false') */ *
........
WHERE ROW_ID <= 10
AND ROW_ID > 0;
相关文章