[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/
