Oracle peeking关闭动态采样导致走错索引

如果动态采样，且peeking关闭，则不管列的数据分布，会按照默认选择率计算，很容易走错索引，
需要收集统计信息。

在Oracle数据库中，优化器计算等值条件选择率时，
通常优先使用DBA_TAB_COL_STATISTICS.NUM_DISTINCT/DENSITY/HISTOGRAM（列的统计信息）

没有关闭peeking,走索引正确，如下所示：

create table default_t as select * from dba_objects;
exec dbms_stats.delete_table_stats(ownname=>user,tabname=>'default_t');


create index idx_default_t on default_t(object_id);
create index idx1_default_t on default_t(status);


var v1 number;
var v2 varchar2(100);
exec :v1 := 1;
exec :v2 := 'VALID';


select * from default_t 
where object_id = :v1 
and status = :v2;


SQL_ID  gfqhn6rwb15hn, child number 0
-------------------------------------
select * from default_t where object_id = :v1 and status = :v2


Plan hash value: 865985874


----------------------------------------------------------------------
| Id  | Operation                           | Name          | E-Rows |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |        |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEFAULT_T     |      7 |
|*  2 |   INDEX RANGE SCAN                  | IDX_DEFAULT_T |     16 |
----------------------------------------------------------------------


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


   1 - filter("STATUS"=:V2)
   2 - access("OBJECT_ID"=:V1)

关闭peeking,动态采样，两个索引列估算都是按照0.004，估算返回行数都是265，这样很容易走错索引，没有使用动态采样的num distinct：

alter system flush shared_pool;
alter session set "_optim_peek_user_binds"=false;


var v1 number;
var v2 varchar2(100);
exec :v1 := 1;
exec :v2 := 'VALID';


select * from default_t 
where object_id = :v1 
and status = :v2;


Plan hash value: 865985874


---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |      1 |        |      0 |00:00:00.01 |       2 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEFAULT_T     |      1 |      7 |      0 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN                  | IDX_DEFAULT_T |      1 |    265 |      0 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------


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


   1 - filter("STATUS"=:V2)
   2 - access("OBJECT_ID"=:V1)


Note
-----
   - dynamic statistics used: dynamic sampling (level=2)




select/*+index(default_t(status) )*/ * from default_t where object_id = :v1 and status = :v2;




Plan hash value: 3060018666


----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |      1 |        |      0 |00:00:00.02 |    1607 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEFAULT_T      |      1 |      7 |      0 |00:00:00.02 |    1607 |
|*  2 |   INDEX RANGE SCAN                  | IDX1_DEFAULT_T |      1 |    265 |  73749 |00:00:00.01 |     176 |
----------------------------------------------------------------------------------------------------------------


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


   1 - filter("OBJECT_ID"=:V1)
   2 - access("STATUS"=:V2)


Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

所以，绑定变量peeking{BANNED}最佳好不要关闭。如果收集了统计信息，则会利用列统计信息计算选择率，如果收集统计信息，就算peeking关闭，也会利用num distinct,如果某个分布均匀，选择性好，不会走错

exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'default_t',no_invalidate=>false);




var v1 number;
var v2 varchar2(100);
exec :v1 := 1;
exec :v2 := 'VALID';


select * from default_t 
where object_id = :v1 
and status = :v2;


-- 这个索引估算的行数就是1/num_distinct 计算选择率=1/73752


select * from default_t where object_id = :v1 and status = :v2


Plan hash value: 865985874


---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |      1 |        |      0 |00:00:00.01 |       2 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEFAULT_T     |      1 |      1 |      0 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN                  | IDX_DEFAULT_T |      1 |      1 |      0 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------


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


   1 - filter("STATUS"=:V2)
   2 - access("OBJECT_ID"=:V1)






-- 这个索引估算的行数就是50%,按照1/num_distinct 计算选择率=1/2
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1rh5an6btwgd6, child number 1
-------------------------------------
select/*+index(default_t(status) )*/ * from default_t where object_id =
:v1 and status = :v2


Plan hash value: 3060018666


----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |      1 |        |      0 |00:00:00.03 |    1607 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEFAULT_T      |      1 |      1 |      0 |00:00:00.03 |    1607 |
|*  2 |   INDEX RANGE SCAN                  | IDX1_DEFAULT_T |      1 |  36877 |  73749 |00:00:00.01 |     176 |
----------------------------------------------------------------------------------------------------------------


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


   1 - filter("OBJECT_ID"=:V1)
   2 - access("STATUS"=:V2)