Oracle peeking关闭动态采样导致走错索引-dingjun123-ChinaUnix博客

如果动态采样，且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)