ORACLE CBO 默认选择率及其问题解决方案_PART3

接：ORACLE CBO 默认选择率及其问题解决方案_PART2

本部分主要研究下默认选择率出现问题的解决方案。

3.默认选择率导致执行计划不准的解决方案

遇到CBO使用默认选择率，计算的cardinality与实际差别很大，让执行计划走错的tuning方法
如果使用默认选择率，计算的cardinality与实际差别很大，则不准确。可能导致后续的join method,join order选择错误，这时候需要根据具体情况去调整选择率变得更准确。


1）如果是有统计信息，但是因为使用绑定变量，peeking关闭的，则需要打开peeking才可以
按照实际传入的值计算准确selectivity
如果全局peeking是关闭的，可以针对特定SQL打开， /*+opt_param('_optim_peek_user_binds', 'false')*/ 放到SQL PROFILE里绑定。


2)表有统计信息，但是列未收集统计信息的
  这时候都是使用默认选择率，列无统计信息，则需要收集缺失的列统计信息：
  不正确的收集方法，只收集索引列统计信息：  

exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'sel_test',estimate_percent=>10,-
method_opt=>'for all indexed columns size skewonly',no_invalidate=>false);

建议收集语句如下：

exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'sel_test',estimate_percent=>dbms_stats.auto_sample_size,-
method_opt=>'for all columns size auto',no_invalidate=>false);

如果分区表，建议增加partname,避免无数据的分区num_rows=0.

当然，如果统计信息是自动收集的，则没有这种问题。

3）有统计信息，但是对列使用了函数
因为函数不存储统计信息，这时候会使用默认统计信息，函数等值1%，函数范围5%选择率

动态采样：
如果没有统计信息，动态采样，大部分时候也是准确的,可以对函数条件采样。
有统计信息谓词复杂，就不会动态采样，函数条件按默认选择率，可以采用高级别动态采样，dynamic_sampling>=3。

对于这种情况可以使用cardinality/estimate_rows hints传入较准确值、高级别动态采样dynamic_sampling(4),扩展列统计信息，SQL PROFILE绑定等

对于这种方案还是建议收集扩展列统计信息。

sample:

explain plan for
select * from sel_test
where substr(object_name,1,1) = 'D' and SUBSTR(OWNER,1,3)='SYS';

select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 747827318

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 924 | 396 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SEL_TEST | 7 | 924 | 396 (1)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter(SUBSTR("OBJECT_NAME",1,1)='D' AND
              SUBSTR("OWNER",1,3)='SYS')

这个SQL条件是substr(object_name,1,1) = 'D' and SUBSTR(OWNER,1,3)='SYS'，因此sel=0.01*0.01,
对应的返回行数=num_rows*0.01*0.01=round(72800*0.01*0.01)=7

但是实际情况是2631行：

select count(*) from sel_test
where substr(object_name,1,1) = 'D' and SUBSTR(OWNER,1,3)='SYS';
  COUNT(*)
----------
      2631

很显然，不够准确，可以用cardinality/estimate_rows hints(table函数默认8168也不准，常用cardinality hints)：

3.1)使用cardinality hints

使用cardinality hints,这是undocuments hints,只能指定表返回的行数:

explain plan for
select/*+cardinality(sel_test 3000)*/ * from sel_test
where substr(object_name,1,1) = 'D' and SUBSTR(OWNER,1,3)='SYS';

select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 747827318

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3000 | 386K| 396 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SEL_TEST | 3000 | 386K| 396 (1)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter(SUBSTR("OBJECT_NAME",1,1)='D' AND
              SUBSTR("OWNER",1,3)='SYS')

3.2）使用opt_estimate hints

使用opt_estimate,这个更强大，可以控制索引访问，索引过滤，回表，JOIN等返回行数估算，可以指定行数指定原估算倍数等：
比如下面用/*+opt_estimate(table sel_test scale_rows=100)*/，原估算7行，现在扩大100倍：

explain plan for
select/*+opt_estimate(table sel_test scale_rows=100)*/ * from sel_test
where substr(object_name,1,1) = 'D' and SUBSTR(OWNER,1,3)='SYS';
select * from table(dbms_xplan.display);

现在的rows=728,原先的7是round结果，实际上=round(72800*0.01*0.01)*100=728

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 747827318

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 728 | 96096 | 396 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SEL_TEST | 728 | 96096 | 396 (1)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUBSTR("OBJECT_NAME",1,1)='D' AND
              SUBSTR("OWNER",1,3)='SYS')

3.3）使用高级别动态采样dynamic_sampling(3-5)

可以对表有多列复杂条件采样，5比4使用更多数据块采样，可能更准确
指定不准的表动态采样，避免过度采样：dynamic_sampling(alias 4)

explain plan for
select/*+dynamic_sampling(4)*/ * from sel_test
where substr(object_name,1,1) = 'D' and SUBSTR(OWNER,1,3)='SYS';
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 747827318

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3916 | 504K| 396 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SEL_TEST | 3916 | 504K| 396 (1)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter(SUBSTR("OBJECT_NAME",1,1)='D' AND
              SUBSTR("OWNER",1,3)='SYS')

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

指定表动态采样，建议：/*+dynamic_sampling(a 5)*/，但是Note:dynamic statistics used: dynamic sampling (level=2),
不影响优化效果。

explain plan for
select/*+dynamic_sampling(a 5)*/ * from sel_test a
where substr(object_name,1,1) = 'D' and SUBSTR(OWNER,1,3)='SYS';
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 747827318

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2474 | 362K| 396 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SEL_TEST | 2474 | 362K| 396 (1)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter(SUBSTR("OBJECT_NAME",1,1)='D' AND
              SUBSTR("OWNER",1,3)='SYS')

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

采用dynamic_sampling(5)更准确，现在估算的行数=2474，比4更接近真实行数2631.

3.4）使用扩展列统计信息

只采集组合列不采集单列的好像用不上扩展列统计信息,而组合的报错：
ORA-20001: Invalid Extension: Column group can contain only columns seperated by comma


收集扩展统计信息如下：

--收集单列扩展
exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'sel_test',-
method_opt=>'for columns size auto (substr(object_name,1,1)),(SUBSTR(OWNER,1,3)),',no_invalidate=>false);
--收集组合扩展
exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'sel_test',-
method_opt=>'for columns size auto (SUBSTR("OBJECT_NAME",1,1),SUBSTR("OWNER",1,3))',no_invalidate=>false);

查看扩展列情况：

elect extension_name,extension from dba_stat_extensions where table_name='SEL_TEST';
EXTENSION_NAME                 EXTENSION
------------------------------ --------------------------------------------------------------------------------
SYS_STU5NQYW0T0Y__CUOUS30XU1JP (SUBSTR("OBJECT_NAME",1,1))
SYS_STUBTK8R9URO9QAAU#1LY00SHP (SUBSTR("OWNER",1,3))
SYS_STU7DGJ0XNRQN13E0KA_E698Y3 (SUBSTR("OBJECT_NAME",1,1),SUBSTR("OWNER",1,3))

column group会自动创建分组，删除统计信息不会删除分组，需要使用：

exec dbms_stats.drop_extended_stats(ownname=>user,tabname=>'sel_test',extension=>'(SUBSTR("OBJECT_NAME",1,1))');
exec dbms_stats.drop_extended_stats(ownname=>user,tabname=>'sel_test',extension=>'(SUBSTR("OWNER",1,3))');
exec dbms_stats.drop_extended_stats(ownname=>user,tabname=>'sel_test',extension=>'(SUBSTR("OBJECT_NAME",1,1),SUBSTR("OWNER",1,3))');

扩展列统计信息：

select column_name,num_distinct,histogram
from dba_tab_col_statistics
where table_name='SEL_TEST'
and column_name='SYS_STU7DGJ0XNRQN13E0KA_E698Y3';
COLUMN_NAME          NUM_DISTINCT HISTOGRAM
-------------------- ------------ ------------------------------
SYS_STU7DGJ0XNRQN13E          244 FREQUENCY
0KA_E698Y3

SQL如下：

explain plan for
select * from sel_test
where substr(object_name,1,1) = 'D' and SUBSTR(OWNER,1,3)='SYS';
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 747827318

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 298 | 44700 | 396 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SEL_TEST | 298 | 44700 | 396 (1)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter(SUBSTR("OBJECT_NAME",1,1)='D' AND
              SUBSTR("OWNER",1,3)='SYS')

返回行数=round(72800*(1/244))=298;貌似扩展统计信息没有动态采样dynamic_sampling(5)准确。

先创建扩展列，然后在收集全表的，更准：

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

explain plan for
select * from sel_test
where substr(object_name,1,1) = 'D' and SUBSTR(OWNER,1,3)='SYS';
select * from table(dbms_xplan.display);

现在估算是2631行，和真实行数2631行一样：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 747827318

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2631 | 385K| 396 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SEL_TEST | 2631 | 385K| 396 (1)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter(SUBSTR("OBJECT_NAME",1,1)='D' AND
              SUBSTR("OWNER",1,3)='SYS')

下一篇：ORACLE CBO 默认选择率及其问题解决方案_PART4