生产库cpu使用率增加，查看awr报告可见如下sql的cpu time较高：
 
条sql 的执行计划是一样的，查看其执行计划：
SQL> select child_number  from v$sql where sql_id='cdp2vwt31665p';               

CHILD_NUMBER
------------
           1

SQL> select * from table(dbms_xplan.display_cursor('cdp2vwt31665p',1,'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cdp2vwt31665p, child number 1
-------------------------------------
select distinct alias_sp_cms_info.c_node_id as a_id, alias_sp_cms_info.c_prop_sub_title_s as
a_sub_title from t_file_cms_info_s alias_sp_cms_info where ((alias_sp_cms_info.c_path like :1)
and (alias_sp_cms_info.c_parent_id = :2))

Plan hash value: 410446496

---------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                     |       |       |    79 (100)|          |
|   1 |  HASH UNIQUE                      |                     |    13 |  1170 |    79   (4)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID     | T_FILE_CMS_INFO_S   |    13 |  1170 |    78   (3)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS    |                     |       |       |            |          |
|   4 |     BITMAP AND                    |                     |       |       |            |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS|                     |       |       |            |          |
|*  6 |       INDEX RANGE SCAN            | IDX_CMS_INFO_S_PID  |   642 |       |     4   (0)| 00:00:01 |
|   7 |      BITMAP CONVERSION FROM ROWIDS|                     |       |       |            |          |
|   8 |       SORT ORDER BY               |                     |       |       |            |          |
|*  9 |        INDEX RANGE SCAN           | IDX_CMS_INFO_S_PATH |   642 |       |    68   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_××DED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OPT_PARAM('query_rewrite_enabled' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1""ALIAS_SP_CMS_INFO"@"SEL$1" AND(("T_FILE_CMS_INFO_S"."C_PARENT_ID")
              ("T_FILE_CMS_INFO_S"."C_PATH")))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (VARCHAR2(30), CSID=852): '/Home/CN/ChannelHome/%'
   2 - :2 (NUMBER): 443179

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

   6 - access("ALIAS_SP_CMS_INFO"."C_PARENT_ID"=:2)
   9 - access("ALIAS_SP_CMS_INFO"."C_PATH" LIKE :1)
       filter(("ALIAS_SP_CMS_INFO"."C_PATH" LIKE :1 AND "ALIAS_SP_CMS_INFO"."C_PATH" LIKE :1))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "ALIAS_SP_CMS_INFO"."C_NODE_ID"[NUMBER,22],
       "ALIAS_SP_CMS_INFO"."C_PROP_SUB_TITLE_S"[VARCHAR2,500]
   2 - "ALIAS_SP_CMS_INFO"."C_NODE_ID"[NUMBER,22],
       "ALIAS_SP_CMS_INFO"."C_PROP_SUB_TITLE_S"[VARCHAR2,500]
   3 - "ALIAS_SP_CMS_INFO".ROWID[ROWID,10]
   4 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
   5 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
   6 - "ALIAS_SP_CMS_INFO".ROWID[ROWID,10]
   7 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
   8 - (#keys=1) "ALIAS_SP_CMS_INFO".ROWID[ROWID,10]
   9 - "ALIAS_SP_CMS_INFO".ROWID[ROWID,10]

72 rows selected.

发现oracle使用了BITMAP CONVERSION，也就是对两个索引进行位图转换，在进行运算得出最终的结果集，这是一个很消耗cpu的操作。
查看索引列上的值分布情况：
SQL> select index_name,column_name,column_position from dba_ind_columns where index_name='IDX_CMS_INFO_S_PID';

INDEX_NAME                     COLUMN_NAME                              COLUMN_POSITION
------------------------------ ---------------------------------------- ---------------
IDX_CMS_INFO_S_PID             C_PARENT_ID                                            1

SQL> select table_name,index_name,DISTINCT_KEYS,AVG_DATA_BLOCKS_PER_KEY from dba_ind_statistics
  2  where table_name=upper('t_file_cms_info_s') and wner='CMS57' and index_name='IDX_CMS_INFO_S_PID';

TABLE_NAME                     INDEX_NAME                     DISTINCT_KEYS AVG_DATA_BLOCKS_PER_KEY
------------------------------ ------------------------------ ------------- -----------------------
T_FILE_CMS_INFO_S              IDX_CMS_INFO_S_PID                      3550                       8

SQL>  select table_name,column_name,NUM_DISTINCT,DENSITY from dba_tab_columns where table_name='T_FILE_CMS_INFO_S' and wner='CMS57'
  2  and column_name='C_PARENT_ID';

TABLE_NAME                     COLUMN_NAME                              NUM_DISTINCT    DENSITY
------------------------------ ---------------------------------------- ------------ ----------
T_FILE_CMS_INFO_S              C_PARENT_ID                                       315 .002688172

SQL> select count(distinct c_parent_id) from cms57.T_FILE_CMS_INFO_S;

COUNT(DISTINCTC_PARENT_ID)
--------------------------
                      3550

可见，索引的统计信息是准确的，DISTINCT_KEYS为3550，而表上的统计信息却很不准确，NUM_DISTINCT只有315，查看10053 的trace文件，

Using prorated density: 0.0026882 of col #6 as selectivity of out-of-range value pred
  Access Path: index (AllEqRange)
    Index: IDX_CMS_INFO_S_PID
    resc_io: 87.00  resc_cpu: 1436095
    ix_sel: 0.0026882  ix_sel_with_filters: 0.0026882
    Cost: 87.19  Resp: 87.19  Degree: 1
显然优化器在确定选择率时，把density .002688172当作了该索引的选择率，显然是错误的，差了一个数量级：

SQL> select 1/3550 from dual;

    1/3550
----------
 .00028169

这最终导致优化器没有选择这个索引，而是把两个索引进行了位图转换，优化器又一次做出了错误的选择！！！

B-tree to Bitmap Conversions
One of the optimizer’s strategies is to range scan B-tree indexes to acquire lists of rowids, convert
the lists of rowids into the equivalent bitmaps, and perform. bitwise operations to identify a
small set of rows. Effectively, the optimizer can take sets of rowids from index range scans and
convert them to bitmap indexes on the fly before doing an index_combine on the resulting
bitmap indexes.
In 8i, only tables with existing bitmap indexes could be subject to this treatment, unless
the parameter _b_tree_bitmap_plans had been set to relax the requirement for a preexisting
bitmap index.
In 9i, the default value for this parameter changed from false to true—so you may see
execution plans involving bitmap conversions after you’ve upgraded, even though you don’t
have a single bitmap index in your database. Unfortunately, because of the implicit packing
assumption that the optimizer uses for bitmap indexes, this will sometimes be a very bad idea.
As a related issue, this change can make it worth using the minimize_records_per_block
option on all your important tables.

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ONLYONE_HTLIU/archive/2010/07/23/5758128.aspx