bitmap conversion from rowids

最新推荐文章于 2021-04-15 08:57:19 发布

上海阿丽

最新推荐文章于 2021-04-15 08:57:19 发布

阅读量1k

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/u010587433/article/details/46706059

版权

Oracle 专栏收录该内容

53 篇文章 0 订阅

订阅专栏

最近刚接手一个系统（好像是TMS物流系统），数据库服务器有三个节点，版本为10.2.0.5。
由于是刚接手，所以先大概看了一下基本情况。情况基本理想，存在一些稍高的等待事件、高消耗sql等。需一步一步将性能提上来。
今天先优化了一条比较简单的sql（单表的一个count查询），这条sql逻辑读排第一。

Execution Plan
----------------------------------------------------------
Plan hash value: 833335278

-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |     1 |    43 |276   (2)| 00:00:04 |
|   1 |  SORT AGGREGATE                   |                 |     1 |    43 |         |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID     | LEG             |     1 |    43 |276   (2)| 00:00:04 |
|   3 |    BITMAP CONVERSION TO ROWIDS    |                 |       |       |         |          |
|   4 |     BITMAP AND                    |                 |       |       |         |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS|                 |       |       |         |          |
|   6 |       SORT ORDER BY               |                 |       |       |         |          |
|*  7 |        INDEX RANGE SCAN           | LEG_PLAN_S_TIME |   167 |       |106   (0)| 00:00:02 |
|   8 |      BITMAP CONVERSION FROM ROWIDS|                 |       |       |         |          |
|*  9 |       INDEX RANGE SCAN            | LEG_CARRIER     |   167 |       |162   (1)| 00:00:02 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LEG0_"."PLATFORM_ID"=16143 AND "LEG0_"."STATUS"='WAITPLAN' AND
              ("LEG0_"."LEG_TYPE"='DGPS' OR "LEG0_"."LEG_TYPE"='JZPS' OR "LEG0_"
."LEG_TYPE"='MDPS' OR
              "LEG0_"."LEG_TYPE"='TXHH') AND "LEG0_"."PLAN_ARRIVE_E_TIME">SYSDAT
E@!)
   7 - access("LEG0_"."PLAN_ARRIVE_S_TIME"<SYSDATE@!)
       filter("LEG0_"."PLAN_ARRIVE_S_TIME"<SYSDATE@!)
   9 - access("LEG0_"."CARRIER_ID"=20402)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
     234152  consistent gets
          0  physical reads
          0  redo size
        514  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

从执行计划和统计信息可以看出：
1、逻辑读20多万，存在一次内存排序
2、存在位图转换（bitmap coversion from rowids）

实际上只有where+count不应该有排序。

查看表的相关情况，索引比较多，且存在重复的情况。索引创建有失合理
不修改索引的提下位图转换可以通过隐含参数_b_tree_bitmap_plans设置为fals，禁止位图转换。
alter session set "_b_tree_bitmap_plans"=false
或者
添加Hint：/*+opt_param('_b_tree_bitmap_plans','false')*/

Execution Plan
----------------------------------------------------------
Plan hash value: 2313119379

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 |    43 |  1428(1)| 00:00:18 |
|   1 |  SORT AGGREGATE               |               |     1 |    43 |         |          |
|   2 |   INLIST ITERATOR             |               |       |       |         |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| LEG           |     1 |    43 |  1428(1)| 00:00:18 |
|*  4 |     INDEX RANGE SCAN          | IND_WRITE_SDC |   167 |       |  1311(1)| 00:00:16 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("LEG0_"."CARRIER_ID"=20402 AND "LEG0_"."PLAN_ARRIVE_S_TIME"<SYSDATE@!
              AND "LEG0_"."PLAN_ARRIVE_E_TIME">SYSDATE@!)
   4 - access("LEG0_"."STATUS"='WAITPLAN' AND ("LEG0_"."LEG_TYPE"='DGPS' OR
              "LEG0_"."LEG_TYPE"='JZPS' OR "LEG0_"."LEG_TYPE"='MDPS' OR "LEG0_"."LEG_TYPE"='TXHH')
              AND "LEG0_"."PLATFORM_ID"=16143)
       filter("LEG0_"."PLATFORM_ID"=16143)

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       7409  consistent gets
          0  physical reads
          0  redo size
        514  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

目前系统还没有解决这条语句，需要进一步熟悉环境和流程后正式解决。

参考：
http://www.eygle.com/archives/2011/12/bitmap_conversion_cpu.html