一条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 and use dynamic sampling
RELATED BUGS:
-------------
REPRODUCIBILITY:
----------------
reproduced in-house on 10.2.0.4 and 11.1.0.7
