1.5 引導(dǎo)SQL語句使用智能掃描

在針對(duì)數(shù)據(jù)庫中的單獨(dú)SQL語句進(jìn)行優(yōu)化時(shí)，可能常會(huì)遇到一些特定的SQL語句未使用智能掃描操作。如果經(jīng)過測試可以確認(rèn)該SQL語句智能掃描比傳統(tǒng)的索引掃描更加高效，那么可能需要使用一些特殊的手段，讓這類SQL語句進(jìn)行智能掃描操作。

常見的手段有如下幾種。

1．使用parallel提示

當(dāng)SQL語句并行運(yùn)行時(shí)，基本上都會(huì)使用智能掃描，因?yàn)椴⑿胁樵儠r(shí)，并行的子進(jìn)程默認(rèn)會(huì)選擇直接路徑讀取的方式來訪問數(shù)據(jù)塊。同樣，使用并行的SQL語句，一般情況下也會(huì)選擇使用全表掃描方式。

下面通過示例進(jìn)行說明，見代碼清單1.23。

從以上示例可以看出，當(dāng)SQL語句中涉及的表test.middle在內(nèi)存中已經(jīng)緩存了66%的數(shù)據(jù)塊時(shí)，該SQL語句無法使用串行的直接路徑讀取方式來進(jìn)行數(shù)據(jù)掃描，而相同的SQL語句，僅僅是添加了parallel提示之后，就可以進(jìn)行直接路徑讀取，最終也能夠進(jìn)行智能掃描。

2．使用full提示

當(dāng)SQL語句中帶有查詢條件并且表中存在索引時(shí)，Oracle優(yōu)化器很可能會(huì)優(yōu)先使用索引進(jìn)行數(shù)據(jù)查詢。此時(shí)可以使用／?+full（a）?／提示強(qiáng)制該SQL語句進(jìn)行全表掃描，因?yàn)橹挥腥頀呙璨庞袡C(jī)會(huì)觸發(fā)智能掃描。

3．將索引置于invisible

當(dāng)SQL語句中帶有查詢條件并且表中存在索引時(shí)，Oracle優(yōu)化器很可能會(huì)優(yōu)先使用索引進(jìn)行數(shù)據(jù)查詢。此時(shí)如果確定全表掃描后智能掃描效率高于索引掃描，可以選擇刪除索引。在刪除索引之前，通常先將索引置于invisible狀態(tài)。

4．忽略提示

很多應(yīng)用程序開發(fā)人員喜歡在SQL語句中包含一些提示，以此來控制SQL語句的執(zhí)行計(jì)劃，但往往是這種人為的提示，可能導(dǎo)致智能掃描無法執(zhí)行。如果無法修改SQL語句中的代碼，可以在會(huì)話級(jí)別設(shè)置隱含參數(shù)_optimizer_ignore_hints來忽略SQL語句中的提示，見代碼清單1.24。

5．減小db_cache_size大小

減小db_cache_size的大小，其實(shí)也就是間接地調(diào)整_small_table_threshold隱含參數(shù)，使SQL語句更容易進(jìn)行串行的直接路徑讀取。

6．設(shè)置“特大表”與“小表”參數(shù)

直接調(diào)整_small_table_threshold和_very_large_object_threshold隱含參數(shù)，使SQL語句更容易進(jìn)行串行的直接路徑讀取。

7．調(diào)整統(tǒng)計(jì)信息中的數(shù)據(jù)塊個(gè)數(shù)

使用DBMS_STATS.SET_TABLE_STATS函數(shù)或DBMS_STATS.SET_INDEX_STATS函數(shù)手動(dòng)修改表或索引的統(tǒng)計(jì)信息中的數(shù)據(jù)塊個(gè)數(shù)。

該方式必須配合參數(shù)_direct_read_decision_statistics_driven一起使用，具體原因可參考前面章節(jié)。

8．設(shè)置_serial_direct_read隱含參數(shù)

該隱含參數(shù)用來控制是否允許串行的直接路徑讀取，參數(shù)值可設(shè)置為auto、always和never，具體含義如表1.4所示。

查詢數(shù)據(jù)庫當(dāng)前_serial_direct_read隱含參數(shù)設(shè)置，代碼如下。

如果需要強(qiáng)制會(huì)話進(jìn)行直接路徑讀取，可執(zhí)行如下語句。

如果需要強(qiáng)制會(huì)話不進(jìn)行直接路徑讀取，可執(zhí)行如下語句。

下面通過示例來驗(yàn)證在會(huì)話級(jí)別設(shè)置_serial_direct_read隱含參數(shù)為always時(shí)，SQL語句的性能表現(xiàn)，見代碼清單1.25。

SQL語句訪問的表對(duì)象介于“小表”與“特大表”之間，當(dāng)內(nèi)存中緩存的數(shù)據(jù)塊個(gè)數(shù)大于數(shù)據(jù)塊總數(shù)的50%時(shí)，在會(huì)話級(jí)別設(shè)置_serial_direct_read隱含參數(shù)為always，同時(shí)收集該SQL語句的10046事件，以便觀察_serial_direct_read隱含參數(shù)對(duì)直接路徑讀取的影響。

對(duì)生成的10046事件使用tkprof工具進(jìn)行格式化后，從輸出可以看出，該SQL語句的主要等待事件為cell smart table scan，這也間接說明了該SQL語句進(jìn)行了直接路徑讀取，而正常情況下，如果不設(shè)置該參數(shù)，根據(jù)串行直接路徑讀取算法，該SQL語句是不可能使用直接路徑讀取的方式來訪問數(shù)據(jù)塊的。

下面繼續(xù)觀察如果清空內(nèi)存中的數(shù)據(jù)塊，然后設(shè)置_serial_direct_read隱含參數(shù)會(huì)有什么表現(xiàn)。

使用alter system flush buffer_cache語句清空緩存中已經(jīng)緩存的數(shù)據(jù)塊后，在會(huì)話級(jí)別設(shè)置_serial_direct_read隱含參數(shù)為always，同時(shí)收集該SQL語句的10046事件，來觀察_serial_direct_read隱含參數(shù)對(duì)直接路徑讀取的影響。

對(duì)生成的10046事件使用tkprof工具進(jìn)行格式化后，從輸出可以看出，該SQL語句同樣進(jìn)行了cell smart table scan操作，也即進(jìn)行了直接路徑讀取。出現(xiàn)這個(gè)結(jié)果是在情理之中，因?yàn)楦鶕?jù)串行直接路徑讀取算法，當(dāng)表對(duì)象緩存在內(nèi)存中的數(shù)據(jù)塊比例比較低時(shí)，該SQL語句本來就應(yīng)該使用直接路徑讀取的方式來訪問數(shù)據(jù)塊。

同樣，當(dāng)表對(duì)象為“特大表”或“小表”時(shí)，在會(huì)話級(jí)別設(shè)置_serial_direct_read隱含參數(shù)為always，則無論內(nèi)存中緩存的數(shù)據(jù)塊有多少，該SQL語句都會(huì)使用直接路徑讀取的方式來訪問數(shù)據(jù)塊。