bitmap conversion from rowids

最近剛接手一個系統（好像是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"
從執行計劃和統計資訊可以看出：

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@!)
   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




	



	



	



	


                                                            

                    

                                                    來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26831783/viewspace-1695145/，如需轉載，請註明出處，否則將追究法律責任。