禁用优化器动态采样

优化器动态采样有关的参数是：optimizer_dynamic_sampling

1、参数的官方文档描述如下：

OPTIMIZER_DYNAMIC_SAMPLING
 
 

  
  
Property
Description
Parameter type
Integer
Default value
If OPTIMIZER_FEATURES_ENABLE
 is set to 10.0.0 or higher, then 2 
If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1
If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0
Modifiable
ALTER SESSION, ALTER SYSTEM
Range of values
0 to 10

 
 
OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed by the optimizer.

2、从文档的描述可以看出10g的默认值为2：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> show parameter optimizer_dyna

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
SQL>

3、在平时的环境中，如果统计分析工作做得好，可以直接将该参数的值 设置为0，这样就避免了语句执行时进行动态采样，但通常不会这样设置，如果有需要可以在SQL语句中通过hint的方式来关闭动态采样：

select /*+ dynamic_sampling(0) */ * from t1 where object_id=30;