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