绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
位图与或运算导致的执行计划异常分析-cost更低执行时间更长
2021-11-23 10:39:05


索引位图与或运算导致的执行计划异常分析-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;







分享好友

分享这个小栈给你的朋友们,一起进步吧。

Oracle运维新鲜事-技术与管理各占半边天
创建时间:2020-08-04 11:34:57
本技术栈旨在分享技术心得,运维趣事,故障处理经验,调优案例,故障处理涉及集群,DG,OGG,大家生产中遇到的问题基本都会囊括了,我会发布生产库遇到的故障,希望在交流中互助互益,共同提高,也希望大家讨论,如果您有生产中遇到的集群问题,也可以在这里提出来,一起讨论,现实中也帮助不少同学解决了生产库的故障。
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

栈主、嘉宾

查看更多
  • Abraham林老师
    栈主
  • 小雨滴
    嘉宾
  • hawkliu
    嘉宾
  • u_97a59a25246404
    嘉宾

小栈成员

查看更多
  • 栈栈
  • dapan
  • 小菜鸟___
  • hwayw
戳我,来吐槽~