oracle bloom过滤,[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/