最近遇到一个动态采样带来的性能问题,让我着实有点不可理解,通过查看文档以及做实验,现总结如下:
我们先来看看Online Document关于动态采用的解释
Oracle 10GR2 documentation:
This dynamic sampling feature is controlled by the OPTIMIZER_DYNAMIC_SAMPLING parameter.
For dynamic sampling to automatically gather the necessary statistics, this parameter should be set to a value of 2 or higher.
The default value is 2. See "Dynamic Sampling Levels" for information about the sampling levels that can be set.
Oracle 11GR2 documentation:
When the Optimizer Uses Dynamic Sampling
During compilation, the optimizer decides whether to use dynamic sampling based on a number of factors,
including whether the statements use parallel processing or serial processing.
For parallel statements, the optimizer automatically decides whether to use dynamic sampling and which level to use.
The decision depends on the size of the tables and the complexity of the predicates. The optimizer expects parallel statements to be
resource-intensive,so the additional overhead at compile time is worth it to ensure the best plan. The database ignores the OPTIMIZER_DYNAMIC_SAMPLING setting
unless set to a nondefault value, in which case the value is honored.
For serially processed SQL statements, the dynamic sampling level depends on the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter and
is not triggered automatically by the optimizer. Serial statements are typically short-running, so that any overhead at compile time
could have a huge impact on their performance.
注:大家注意到没有11.2.0.*中如果语句是并行执行,那么是否采用dynamic sampling以及level将以表的大小,sql中table join的复杂度来决定,也就是又CBO来决定采样的level值。
CBO将忽略OPTIMIZER_DYNAMIC_SAMPLING的默认值。对于Oracle的各种特性,如果有自动的功能,多多少少都不太稳定,总会出问题的。当然，对于串行执行的语句依旧动态采样的level 将仍然有OPTIMIZER_DYNAMIC_SAMPLING决定,其实大部分时候level 2已经完全足够。
下面来看看11.2.0.3中动态采用的情况
SQL> create table leo(id number,
2 leo_date date,
3 value varchar2(20),
4 name varchar2(30)) tablespace users;
Table created.
用随机数生成100w数据，插入表中。
SQL> insert into leo
2 select rownum id,sysdate-dbms_random.value(1,500) leo_create,
3 dbms_random.string('1',15) value,
4 dbms_random.string('1',20) name
5 from dual
6* connect by level<=1e6;
1000000 rows created.
SQL> commit;
Commit complete
注:不要收集统计信息
SQL> show parameter optimizer NAME TYPE VALUE ------------------------------------ ------------- ------------ optimizer_dynamic_sampling integer 2 optimizer_features_enable string 11.2.0.3 SQL> explain plan for select*from leo; Explained. SQL> @explain PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------- Plan hash value: 1174476904 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 897K| 43M| 2105 (2)| 00:00:26 | | 1 | TABLE ACCESS FULL| LEO | 897K| 43M| 2105 (2)| 00:00:26 | -------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) 12 rows selected. SQL> explain plan for select/*+parallel*/* from leo; Explained. SQL> @explain PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------- Plan hash value: 3240177498 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 897K| 43M| 1166 (2)| 00:00:14 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM)| :TQ10000 | 897K| 43M| 1166 (2)| 00:00:14 | Q1,00 | P->S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 897K| 43M| 1166 (2)| 00:00:14 | Q1,00 | PCWC | | | 4 | TABLE ACCESS FULL| LEO | 897K| 43M| 1166 (2)| 00:00:14 | Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=6) - - automatic DOP: skipped because of IO calibrate statistics are missing 16 rows selected.
看看10.2.0.3的情况：
SQL> alter session set optimizer_features_enable='10.2.0.3'; Session altered. SQL> explain plan for select/*+parallel*/* from leo; Explained. SQL> @explain PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------- Plan hash value: 3240177498 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2583K| 125M| 2330 (2)| 00:00:28 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM)| :TQ10000 | 2583K| 125M| 2330 (2)| 00:00:28 | Q1,00 | P->S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 2583K| 125M| 2330 (2)| 00:00:28 | Q1,00 | PCWC | | | 4 | TABLE ACCESS FULL| LEO | 2583K| 125M| 2330 (2)| 00:00:28 | Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) - automatic DOP: Computed Degree of Parallelism is 2 because of degree limit 16 rows selected.
如上说明当OFE为11.2.0.3的时候,sql语句并行执行自动调整level值(默认为2).如果OLAP系统中出现这样的情况,那么如果是大对象,数据库性能
将可能会很恶劣。
所以对于OFE为11.2.0.*，出现这种情况的时候，如何控制他的level级别呢，通过optimizer bugfixes。
SQL> col sql_feature for a30
SQL> @bugfix dynamic
old 2: where (sql_feature like upper('%&1%') or upper(description) like '%&1%')
new 2: where (sql_feature like upper('%dynamic%') or upper(description) like '%dynamic%')
BUGNO OFE SQL_FEATURE DESCRIPTION VALUE
---------- ---------- ------------------------------------- ---------------------------------------------------------------- -------
12399886 11.2.0.3 QKSFM_DYNAMIC_SAMPLING_12399886 update the index clustering factor (DS) if statistics do not exi 1
6408301 11.2.0.2 QKSFM_DYNAMIC_SAMPLING_6408301 use recursive idx selectivity for partitioned table as well 1
8767442 11.2.0.2 QKSFM_DYNAMIC_SAMPLING_8767442 compute NDV for all columns in a multi-column join key using DS 1
9272549 11.2.0.2 QKSFM_DYNAMIC_SAMPLING_9272549 do not sample columns which have statistics 1
7452863 11.2.0.1 QKSFM_DYNAMIC_SAMPLING_7452863 adjust DS level for large parallel tables based on the size 1
6766962 11.1.0.7 QKSFM_DYNAMIC_SAMPLING_6766962 sample size in DS is at most equal to the partition sample size 1
6956212 10.2.0.5 QKSFM_DYNAMIC_SAMPLING_6956212 allow dynamic sampling when OR-expansion is used 1
7592673 10.2.0.5 QKSFM_DYNAMIC_SAMPLING_7592673 do not use dyn sampling index selectivity if not all keys sampl 1
6608941 QKSFM_DYNAMIC_SAMPLING_6608941 allow dynamic sampling on fixed tables 0
6708183 QKSFM_DYNAMIC_SAMPLING_6708183 allow dynamic sampling on table functions 0
10 rows selected.
我们可以看见7452863,11.2.0.*描述：adjust DS level for large parallel tables based on the size.好吧，我们来试试：
SQL> alter session set optimizer_features_enable='11.2.0.3';
Session altered.
SQL> alter session set "_fix_control"='7452863:OFF';
Session altered.
SQL> explain plan for select/*+parallel*/* from leo
Explained.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Plan hash value: 3240177498
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2583K| 125M| 2330 (2)| 00:00:28 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 2583K| 125M| 2330 (2)| 00:00:28 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 2583K| 125M| 2330 (2)| 00:00:28 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| LEO | 2583K| 125M| 2330 (2)| 00:00:28 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: skipped because of IO calibrate statistics are missing
16 rows selected.
以上测试是在正式的生产环境中完成,呵呵,以后做实验还是到测试库上安全。
关于动态采样的一些知识:MOS-Different Level for Dynamic Sampling used than the Specified Level [ID 1102413.1]