一条sql添加分区过滤条件后cost反而上升(二)
------
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