[20180112]11g关闭bloom filter.txt

[20180112]11g关闭bloom filter.txt

--//11Gsql语句执行计划偶尔出现bloom filte，我上午调式生产系统sql语句,执行计划经现bloom filter,无论我使用什么提示我发现执
--//行计划都选择执行bloom filter,浪费许多时间。下午仔细想一下有几种方法关闭bloom filter.

1.环境：
xxxx> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

xxxx> @ &r/hide bloom
NAME                                 DESCRIPTION                                                        DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------------------------ ------------------------------------------------------------------ ------------- ------------- ------------
_bloom_filter_debug                  debug level for bloom filtering                                    TRUE          0             0
_bloom_filter_enabled                enables or disables bloom filter                                   TRUE          TRUE          TRUE
_bloom_folding_density               bloom filter folding density lower bound                           TRUE          16            16
_bloom_folding_enabled               Enable folding of bloom filter                                     TRUE          TRUE          TRUE
_bloom_folding_min                   bloom filter folding size lower bound                              TRUE          131072        131072
_bloom_max_size                      bloom filter maximum size in bytes                                 TRUE          268435456     268435456
_bloom_minmax_enabled                enable or disable bloom min max filtering                          TRUE          TRUE          TRUE
_bloom_predicate_enabled             enables or disables bloom filter predicate pushdown                TRUE          TRUE          TRUE
_bloom_predicate_pushdown_to_storage enables or disables bloom filter predicate pushdown to storage     TRUE          TRUE          TRUE
_bloom_pruning_enabled               Enable partition pruning using bloom filtering                     TRUE          TRUE          TRUE
_bloom_pushing_max                   bloom filter pushing size upper bound                              TRUE          512           512
_bloom_pushing_total_max             bloom filter combined pushing size (DOP x filter size) upper bound TRUE          262144        262144
_bloom_serial_filter                 enable serial bloom filter on exadata                              TRUE          on            on
_bloom_sm_enabled                    enable bloom filter optimization using slave mapping               TRUE          FALSE         FALSE
_bloom_vector_elements               number of elements in a bloom filter vector                        TRUE          0             0
15 rows selected.

2.方法1：
--//在会话级别设置：
alter session set "_bloom_filter_enabled"=false;
--//或者在sql语句中加入如下：
OPT_PARAM('_bloom_filter_enabled' 'false')

3.方法2：
alter session set optimizer_features_enable='10.2.0.5';

--//这样可以绕过执行计划选择bloom filter，做一个记录避免以后遗忘。关于bloom filter可以参考我以前链接：
--//http://blog.itpub.net/267265/viewspace-1384617/