一条sql添加分区过滤条件后cost反而上升

一条sql添加分区过滤条件后cost反而上升

 

      在10.2.0.1.0版本上查询分区表上加了一个过滤条件后，cost反而增加了，原因是这是oracle的bug，下面来做个试验：

 

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> drop table test purge;
表已删除。

SQL> create table test(cl varchar2(8), r  integer)
  2          partition by list(cl) (
  3             partition big values('big'),
  4             partition small values ('small'),
  5             partition empty values (default)
  6          );
表已创建。

SQL> insert /*+ append */ into test(cl,r)
            select case when level between 1 and 4 then 'small' else 'big' end,
                     dbms_random.value(1,9000)
           from dual connect by level<=10000;
已创建10000行。

SQL> commit;
提交完成。

SQL> create index i_tr on test(r);
索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'test', cascade=>true);
PL/SQL 过程已成功完成。

SQL> select count(*) from test where cl='big';
  COUNT(*)
----------
      9996

SQL> select count(*) from test where cl='small';
  COUNT(*)
----------
         4
         
SQL> set autotrace traceonly
SQL> select /*+index(test)*/ * from test where cl='small' and r=549;
执行计划
----------------------------------------------------------
Plan hash value: 397514736

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |     1 |    10 |  2359   (1)| 00:00:29 |       |    |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TEST |     1 |    10 |  2359   (1)| 00:00:29 |     2 |  2 |
|*  2 |   INDEX RANGE SCAN                 | I_TR |  2500 |       |     7   (0)| 00:00:01 |       |    |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("CL"='small')
   2 - access("R"=549)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        460  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select /*+index(test)*/ * from test where r=549;
执行计划
----------------------------------------------------------
Plan hash value: 885212093
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |     2 |    14 |     3   (0)| 00:00:01 |       |    |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TEST |     2 |    14 |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | I_TR |     2 |       |     1   (0)| 00:00:01 |       |    |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
   2 - access("R"=549)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        511  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

 

          

解决方法：删除统计信息，进行动态采集

SQL> exec dbms_stats.delete_table_stats(user,'test');
PL/SQL 过程已成功完成。

SQL> select /*+index(test)*/ * from test where cl='small' and r=549;
执行计划
----------------------------------------------------------
Plan hash value: 397514736
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |     1 |    19 |     2   (0)| 00:00:01 |       |    |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TEST |     1 |    19 |     2   (0)| 00:00:01 |     2 |  2 |
|*  2 |   INDEX RANGE SCAN                 | I_TR |     2 |       |     1   (0)| 00:00:01 |       |    |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("CL"='small')
   2 - access("R"=549)
Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        460  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed





[Bug 8971829: WRONG GLOBAL INDEX SELECTIVITY/COST ON SMALL PARTITIONS WITH FILTER ON PART KEY         
________________________________________
Bug Attributes
________________________________________
Type        B - Defect        Fixed in Product Version        -
Severity        2 - Severe Loss of Service        Product Version        10.2.0.4
Status        11 - Code Bug (Response/Resolution)        Platform        212 - IBM AIX on POWER Systems (64-bit)
Created        29-Sep-2009        Platform Version        5.3
Updated        21-Oct-2009        Base Bug        -
Database Version        10.2.0.4                
Affects Platforms        Generic                
Product Source        Oracle                
Related Products
________________________________________
Line        Oracle Database Products        Family        Oracle Database
Area        Oracle Database        Product        5 - Oracle Server - Enterprise Edition
Hdr: 8971829 10.2.0.4 RDBMS 10.2.0.4 QRY OPTIMIZER PRODID-5 PORTID-212
Abstract: WRONG GLOBAL INDEX SELECTIVITY/COST ON SMALL PARTITIONS WITH FILTER ON PART KEY

*** 09/29/09 07:24 am ***

PROBLEM:
--------
For small partitions, the computed selectivity and cost of global index 
access path are wrong - when a filter on partitioning key is present.


This causes CBO to choose a full scan of an unsuitable index over a very
    selective range scan of the correct index which results in performance 
degradation.

WORKAROUND:
-----------
set optimizer_features_enable='9.2.0'
or
delete statistics on base table an