共計(jì) 22506 個(gè)字符，預(yù)計(jì)需要花費(fèi) 57 分鐘才能閱讀完成。
這篇文章主要介紹了 Oracle 如何收集優(yōu)化統(tǒng)計(jì)數(shù)據(jù)，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。
介紹
oracle 優(yōu)化器對(duì)一個(gè) sql 語句檢測所有可能的執(zhí)行計(jì)劃并選擇一個(gè)成本值最小的, 這里的成本代表了一個(gè)特定執(zhí)行計(jì)劃的資源使用情況. 為了讓優(yōu)化器能準(zhǔn)確的判斷一個(gè)執(zhí)行計(jì)劃的成本它必須要關(guān)于 sql 語句要訪問的所有對(duì)象(表或索引) 的信息同時(shí)還要有運(yùn)行 sql 語句的系統(tǒng)信息.
這此必要的信息通常稱為優(yōu)化器統(tǒng)計(jì)信息. 理解和管理優(yōu)化器統(tǒng)計(jì)信息是優(yōu)化 sql 執(zhí)行的關(guān)鍵. 知道何時(shí)以及如何收集統(tǒng)計(jì)信息對(duì)于維護(hù)可以接受的性能來說至關(guān)重要.
這里將介紹在常見的 oracle 數(shù)據(jù)庫場景中何時(shí)以及如何來收集統(tǒng)計(jì)信息. 它包含以下內(nèi)容:
怎樣收集統(tǒng)計(jì)信息
何時(shí)收集統(tǒng)計(jì)信息
提高收集統(tǒng)計(jì)信息的效率
何時(shí)不收集統(tǒng)計(jì)信息
收集處理的統(tǒng)計(jì)信息
怎樣收集統(tǒng)計(jì)信息
在 oracle 中收集統(tǒng)計(jì)信息的首選方法是使用提供了自動(dòng)統(tǒng)計(jì)信息收集 job.
自動(dòng)統(tǒng)計(jì)信息收集 job
對(duì)運(yùn)行 oracle autotask 任務(wù)的一個(gè)預(yù)定義維護(hù)窗口期間對(duì)哪些丟失統(tǒng)計(jì)信息或統(tǒng)計(jì)信息過期的所有數(shù)據(jù)庫對(duì)象收集統(tǒng)計(jì)信息,oracle 內(nèi)部很重視數(shù)據(jù)庫對(duì)象的統(tǒng)計(jì)信息因此這此對(duì)象在進(jìn)行處理前需要更新統(tǒng)計(jì)信息.
自動(dòng)統(tǒng)計(jì)信息收集 job 是使用 dbms_stats.gather_database_stats_job_proc 過程來實(shí)現(xiàn)的, 它與 dbms_stats.gather_*_stats 過程使用相同的缺省參數(shù). 這個(gè)缺省值在大多數(shù)情況下是有效的. 然而偶爾也需要改變這些
統(tǒng)計(jì)信息收集參數(shù)的缺省值, 可以通過 dbms_stats.set_*_pref 過程來進(jìn)行修改. 例如設(shè)置一個(gè)表中有 5% 的數(shù)據(jù)發(fā)生了改變而不是缺省值 10% 時(shí)就認(rèn)會(huì)它的統(tǒng)計(jì)信息失效了. 如果想要改變這個(gè)統(tǒng)計(jì)信息失效的閾值, 可以
使用 dbms_stats.set_table_prefs 過程來修改 stale_percent 參數(shù).
sys@JINGYONG begin
2 dbms_stats.set_table_prefs(SH , SALES , STALE_PERCENT , 5
3 end;
4 /
PL/SQL 過程已成功完成。
使用 dbms_stats.set_table_prefs 過程將表統(tǒng)計(jì)信息失效的閾值 stale_percent 改變了 5%.
手動(dòng)統(tǒng)計(jì)信息收集
如果已經(jīng)有一個(gè)完善的統(tǒng)計(jì)信息收集過程或者因?yàn)槟承┰蛳胍獙?duì)特定用戶方案禁用自動(dòng)統(tǒng)計(jì)信息收集而只保留收集數(shù)據(jù)字典的統(tǒng)計(jì)信息. 可以使用 dbms_stats.set_global_prefs 過程來改變 autostats_target 參數(shù)
為 oracle 來替代 auto.
sys@JINGYONG begin
2 dbms_stats.set_global_prefs(AUTOSTATS_TARGET , ORACLE
3 end;
4 /
PL/SQL 過程已成功完成。
用上面的代碼改變自動(dòng)統(tǒng)計(jì)信息收集 job 只自動(dòng)收集數(shù)據(jù)字典統(tǒng)計(jì)信息.
為了手動(dòng)收集統(tǒng)計(jì)信息你應(yīng)該使用 dbms_stats 包, 用它來替找過時(shí)的 analyze 命令.dbms_stats 包提供多個(gè) dbms_stats.gather_*_stats 過程來收集用戶方案對(duì)象, 數(shù)據(jù)字典和固定對(duì)象的統(tǒng)計(jì)信息. 理想情況下應(yīng)該讓這些
過程除了用戶方案名和對(duì)象名參數(shù)之外其它的參數(shù)值都使用缺省值. 缺省參數(shù)值和自適應(yīng)參數(shù)設(shè)置在大多數(shù)情況下已經(jīng)足夠了.
sys@JINGYONG begin
2 dbms_stats.gather_table_stats(SH , SALES
3 end;
4 /
PL/SQL 過程已成功完成。
兩個(gè)修改最頻繁的參數(shù)是 ESTIMATE_PERCENT 和 METHOD_OPT
ESTIMATE_PERCENT
在收集統(tǒng)計(jì)信息的過程最常見的問題是 使用什么樣的抽樣大小 與這個(gè)問題相關(guān)的設(shè)置是 dbms_stats.gather_*_stats 過程中的 ESTIMATE_PERCENT 參數(shù). 這個(gè) ESTIMATE_PERCENT 參數(shù)判斷用來計(jì)算統(tǒng)計(jì)信息所使用的行數(shù)
百份比. 當(dāng)表中的所有行被處理時(shí)收集的統(tǒng)計(jì)信息是最準(zhǔn)確的(比如 100% 抽樣). 然而抽樣的樣本越大收集操作的時(shí)間越長. 因此使用怎樣的抽樣大小來提供及時(shí)準(zhǔn)確的統(tǒng)計(jì)信息.
oracle11G 之前的 ESTIMATE_PERCENT
在 oracle10g 中,ESTIMATE_PERCENT 的缺省值從 100% 變成了 AUTO_SAMPLE_SIZE. 這個(gè) AUTO_SAMPLE_SIZE 的目的是讓 oracle 在每次收集統(tǒng)計(jì)信息時(shí)來判斷每一個(gè)表的合適的抽樣大小. 這將允許 oracle 自動(dòng)地對(duì)每一個(gè)表改變
其抽樣大小但仍然能確保及時(shí)有效的收集統(tǒng)計(jì)信息. 這種方法對(duì)于大多數(shù)表來說是一種可取的方法但是對(duì)于數(shù)據(jù)存在傾斜的表來說存在問題. 當(dāng)表中數(shù)據(jù)出現(xiàn)傾斜 AUTO_SAMPLE_SIZE 算法通常選擇的抽樣大小太小, 在這種
情況下最好的方法還是手動(dòng)指定 ESTIMATE_PERCENT 參數(shù)的大小.
oracle11g 中的 ESTIMATE_PERCENT
oracle11g 中引入一種新的 hash-based 抽樣算法來提供精確的統(tǒng)計(jì)數(shù)據(jù)解決了精確和速度兩個(gè)關(guān)鍵問題. 它的精確度接近 100% 抽樣大小的水平但是成本與 10% 抽樣大小相當(dāng). 這種新的算法只有當(dāng)任何
dbms_stats.gather_*_stats 過程中的 ESTIMATE_PERCENT 參數(shù)設(shè)置為 AUTO_SAMPLE_SIZE 時(shí)才會(huì)使用.
下面的表格顯示了一個(gè)早前使用 1% 抽樣,100% 抽樣和 AUTO_SAMPLE_SIZE 抽樣收集統(tǒng)計(jì)信息的結(jié)查. 第一行比較運(yùn)行的時(shí)間, 后繼的行將顯示每次運(yùn)行計(jì)算出來的 L_ORDERKDY 和 L_COMMENT 兩個(gè)列不同值的數(shù)量 (NDV)
———————————————————————————————–
1% sample auto_sample_size 100% sample
———————————————————————————————–
Elapse time (sec) 797 1908 18772
NDV for L_ORDERKEY Column 225000000 450000000 450000000
NDV for L_COMMENT Column 7244885 177499684 181122127
———————————————————————————————–
在這種情況下新的 auto_sample_size 算法比 100% 抽樣執(zhí)行的時(shí)間要快 9 倍且只比 1% 抽樣執(zhí)行的時(shí)間慢 2.4 倍, 而提供的統(tǒng)計(jì)信息質(zhì)量幾乎接近 100% 抽樣的水平(不足以改變執(zhí)行計(jì)劃).
從 oracle11g 開始強(qiáng)烈建議你使用 estimate_percent 參數(shù)的缺省值. 如果你手動(dòng)設(shè)置 estimate_percent 參數(shù)盡管你將它設(shè)置為 100% 它還是會(huì)使用舊的收集算法.
method_opt
在 dbms_stats.gather_*_stats 過程中到目前為止最有爭議的參數(shù)就是 method_opt. 這個(gè) method_opt 參數(shù)控制著在收集統(tǒng)計(jì)信息過程是否創(chuàng)建直方圖. 直方圖是一種特殊類型的列統(tǒng)計(jì)信息提供關(guān)于表中列數(shù)據(jù)分布的
詳細(xì)信息. 所以這就是為什么直方圖存在爭議的問題
直方圖的創(chuàng)建會(huì)增加統(tǒng)計(jì)收集的時(shí)間和系統(tǒng)資源的使用但最大的問題是直方圖與 bind peeking 功能的相互作用以及對(duì) near popular values 基數(shù)評(píng)估的影響.
直方圖與 bind peeking
bind peeking 對(duì)直方圖的不利影響已經(jīng)在 oracle11g 中通過引入自適應(yīng)游標(biāo)共享被淡化了但是直到今天仍然感受到它的影響. 為了說明自適應(yīng)游標(biāo)共享是怎樣解決這個(gè)問題的先來檢查一個(gè)這個(gè)問題的原因.
oracle11g 之前的直方圖與 bind peeking
在 oracle11g 之前當(dāng)優(yōu)化一個(gè)在 where 子句中包含綁定變量的語句時(shí)優(yōu)化在第一次執(zhí)行這個(gè)語句時(shí)會(huì)窺視這些綁定變量的值 (在硬解析階段). 優(yōu)化器然后會(huì)基于這個(gè)初始化的綁定變量值來判斷執(zhí)行計(jì)劃. 對(duì)于后續(xù)執(zhí)行
這個(gè)查詢不會(huì)執(zhí)行綁定變量窺視(沒有硬解析發(fā)生), 所以對(duì)于后面的這個(gè)語句的所有執(zhí)行即使綁定變量發(fā)生了改變也會(huì)決定使用第一次設(shè)置的綁定變量值所產(chǎn)生的執(zhí)行計(jì)劃
對(duì)于在表達(dá)式中使用綁定變量的列存在直方圖這將有助于判斷初始化綁定變量值的最優(yōu)執(zhí)行計(jì)劃. 因此對(duì)于相同的語句依賴于初始化硬解析時(shí)綁定變量的值執(zhí)行計(jì)劃可能會(huì)有所不同.
有兩種方法可以避免這個(gè)問題: 刪除直方圖并在將業(yè)停止收集直方圖或禁用 bind peeking 綁定變量窺視. 根據(jù)所有的 sql 是否都在使用綁定變量你可以判斷哪一種方法更適合你的數(shù)據(jù)庫.
禁止直方圖的創(chuàng)建
如果你的環(huán)境中所有 sql 語句使用了綁定變量那么最好是刪除存在的直方圖并在將來的收集統(tǒng)計(jì)信息過程中禁止創(chuàng)建直方圖. 禁上直方圖的創(chuàng)建能確保依賴于綁定變量的值的執(zhí)行計(jì)劃不會(huì)發(fā)生改變也會(huì)減少收集統(tǒng)計(jì)
信息的時(shí)間. 沒有直方圖優(yōu)化器會(huì)假設(shè)列中不相同的值是均勻分布在所有行中的并當(dāng)窺視 sql 語句中初始化綁定變量值時(shí)使用 NDV(number distinct values)來判斷基數(shù)的評(píng)估.
可以使用 dbms_stats.delete_table_stats 過程來刪除統(tǒng)計(jì)信息中存在的直方圖信息.
sys@JINGYONG begin
2 dbms_stats.delete_table_stats(SH , SALES
3 end;
4 /
PL/SQL 過程已成功完成。
接下來可以通過使用 dbms_stats.set_param 過程來改變 method_opt 參數(shù)的缺省值來阻止將來生成直方圖. 這能確保 dbms_stats.gather_*_stats 過程和自動(dòng)統(tǒng)計(jì)信息收集 job 在將來都不會(huì)收集直方圖信息.
sys@JINGYONG begin
2 dbms_stats.set_param(pname= METHOD_OPT ,pval= FOR ALL COLUMNS SIZE 1
3 end;
4 /
PL/SQL 過程已成功完成。
最后可以使用 dbms_stats.gather_table_stats 過程來對(duì)受影響的對(duì)象重新收集統(tǒng)計(jì)信息.
注意在 oracle11g 中通過使用 dbms_stats.delete_column_stats 和對(duì)直方圖設(shè)置 col_stat_type 可以刪除不想要的直方圖而不用刪除所有的直方圖信息. 也可以對(duì)單個(gè)表或者使用 dbms_stats.set_table_prefs 過程來
對(duì)列禁止直方圖的創(chuàng)建.
你知道直方圖也用于某些連接謂詞而且刪除直方圖對(duì)連接謂詞的基數(shù)評(píng)估會(huì)有影響. 在這種情況下更安全的方法是禁用綁定變量窺視.
禁用綁定變量窺視
如果你的環(huán)境中有一些 sql 語句是使用綁定變量而有一些 sql 語句使用了 literal values 那么你應(yīng)該禁用綁定變量窺視. 通過禁用綁定變量窺視它將阻止優(yōu)化器窺初始綁定變量值且將不使用直方圖來進(jìn)行基數(shù)評(píng)估.
相反優(yōu)化器將列中的不相同值是均勻分布在行中并使用 NDV(number distinct values)來進(jìn)行基數(shù)評(píng)估操作. 這將對(duì)于使用綁定變量的語句使用一致的執(zhí)行計(jì)劃. 但是如果 sql 語句使用 literal values 那么仍然能
利用直方圖來得到最優(yōu)的執(zhí)行計(jì)劃. 可以通過設(shè)置隱含參數(shù)_optim_peek_user_binds 為 false 來禁用綁定變量窺視.
oracle11g 中的直方圖與綁定變量窺視
在 oracle11g 中優(yōu)化器已經(jīng)增強(qiáng)了允許多個(gè)版本的執(zhí)行計(jì)劃用于使用綁定變量的單個(gè) sql 語句. 這個(gè)功能就叫作自適應(yīng)游標(biāo)共享且依賴于對(duì)執(zhí)行統(tǒng)計(jì)的監(jiān)控來確保每一個(gè)綁定變量值使用正確的執(zhí)行路徑.
在第一次執(zhí)行時(shí)優(yōu)化器將窺視綁定變量值且基于綁定變量值的選擇性來判斷其執(zhí)行計(jì)劃, 與 oracle11g 之前的版本一樣. 如果優(yōu)化器認(rèn)為最佳的執(zhí)行計(jì)劃可能依賴于綁定變量的值 (例如, 列上的直方圖或者一個(gè)范圍謂詞,
or, , ) 這個(gè)游標(biāo)將會(huì)被標(biāo)記為 bind sensitive. 當(dāng)一個(gè)游標(biāo)被標(biāo)記為 bind sensitive.oracle 將監(jiān)控游標(biāo)使用不同綁定值的行為來確定是否要使用一個(gè)不同的執(zhí)行計(jì)劃.
如果一個(gè)不同的綁定變量值在后繼的執(zhí)行中使用, 優(yōu)化器將使用相同的執(zhí)行計(jì)劃因?yàn)?oracle 一開始會(huì)假設(shè)游標(biāo)能被共享. 然而新的綁定變量的執(zhí)行統(tǒng)計(jì)會(huì)被記錄并與之前綁定變量值的執(zhí)行統(tǒng)計(jì)進(jìn)行比較. 如果 oracle
判斷新的綁定變量值造成了操作的數(shù)據(jù)量明顯不同那么對(duì)于新的綁定變量值在下一次執(zhí)行時(shí)會(huì)進(jìn)行硬解析且這個(gè)游標(biāo)會(huì)被標(biāo)記為 bind-aware. 每一個(gè) bind_aware 游標(biāo)與綁定變量的選擇性范圍有關(guān)因此游標(biāo)只有在這個(gè)
語句的綁定變量值在一個(gè)被認(rèn)為可共享的范圍之內(nèi)才能被共享.
當(dāng)另一個(gè)新的綁定變量值被使用時(shí), 優(yōu)化器將會(huì)基于綁定變量值的選擇性的相似度來找到一個(gè)它認(rèn)為最好的一個(gè)游標(biāo). 如果它不能找到一個(gè)游標(biāo), 它將創(chuàng)建一個(gè)新的. 如果執(zhí)行計(jì)劃的一個(gè)新的游標(biāo)與一個(gè)已經(jīng)存在的游標(biāo)
一樣, 那么兩個(gè)游標(biāo)將會(huì)在共享池中合并從而節(jié)省空間. 游標(biāo)的選擇性范圍為了包含新綁定變量值的選擇性將會(huì)有所增加.
通過允許對(duì)單個(gè) sql 語句存在多個(gè)執(zhí)行計(jì)劃, 在 oracle11g 中直方圖對(duì)于使用綁定變量的語句不再有負(fù)面影響.
直方圖和 near popular values
當(dāng)優(yōu)化器遇到一個(gè) where 子句中謂詞列上有直方圖, 它將基于 literal value 的出現(xiàn)頻率來進(jìn)行基數(shù)評(píng)估. 例如假設(shè)在 sh 用戶下的 customers 表中的 cust_city_di 列上有一個(gè)高度平衡的直方圖且有一個(gè)使用
cust_city_id=51806 的查詢. 優(yōu)化器首先會(huì)檢查這個(gè)直方圖有 51806 作為它的 end point 有多少個(gè)桶. 在這種情況下,endpint 是 51806 的桶有 136,137,138 和 139(可以查看 user_histograms). 因?yàn)?endpoint 的值有兩個(gè)或多個(gè)桶要被考慮為出現(xiàn)頻繁的優(yōu)化器將使用下面的公式來進(jìn)行基數(shù)評(píng)估:
(Number of bucket endpoints / total number of buckets) * number of rows in the table
在這種情況下:4/254*55500=874
sys@JINGYONG set autotrace traceonly
sys@JINGYONG select count(*) from sh.customers where cust_city_id=51806;
執(zhí)行計(jì)劃
———————————————————-
Plan hash value: 296924608
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 382 (3)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 874 | 4370 | 382 (3)| 00:00:04 |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – filter(CUST_CITY_ID =51806)
然而如果謂詞是 cust_city_id=52500, 它對(duì)于任何桶來說都不是一個(gè) endpoint 那么優(yōu)化器會(huì)使用一個(gè)同的公式來進(jìn)行基數(shù)評(píng)估. 對(duì)于 endpoint 值只在一個(gè)桶出現(xiàn)或者任何桶中都沒有這個(gè) endpoint 時(shí)優(yōu)化器會(huì)使用下面的計(jì)算公式:
density * number of rows in the table,
density 的值可以在 user_tab_col_statistics 中看到, 它的值從 oracle10.2.0.4 以后優(yōu)化器將不再使用. 記錄這個(gè)值是為了向后兼容, 在 oracle9i 和 oracle10g 前期的版本中會(huì)使用這個(gè)值. 此外如果參數(shù) optimizer_features_enable 設(shè)置的版本小于 10.2.0.4, 那么視圖中的 density 仍然會(huì)被使用.
sys@JINGYONG select column_name,density from dba_tab_col_statistics where owner
= SH and table_name= CUSTOMERS
COLUMN_NAME DENSITY
—————————— ———-
CUST_ID .000018018
CUST_FIRST_NAME .000769231
CUST_LAST_NAME .001101322
CUST_GENDER .5
CUST_YEAR_OF_BIRTH .013333333
CUST_MARITAL_STATUS .090909091
CUST_STREET_ADDRESS .000019629
CUST_POSTAL_CODE .001605136
CUST_CITY .001612903
CUST_CITY_ID .002179391
CUST_STATE_PROVINCE .006896552
CUST_STATE_PROVINCE_ID .000009009
COUNTRY_ID .000009009
CUST_MAIN_PHONE_NUMBER .000019608
CUST_INCOME_LEVEL .083333333
CUST_CREDIT_LIMIT .125
CUST_EMAIL .000588582
CUST_TOTAL 1
CUST_TOTAL_ID .000009009
CUST_SRC_ID 0
CUST_EFF_FROM 1
CUST_EFF_TO 0
CUST_VALID .5
已選擇 23 行。
sys@JINGYONG select column_name,num_buckets,histogram from dba_tab_col_statisti
cs where owner= SH and table_name= CUSTOMERS and column_name= CUST_CITY_ID
COLUMN_NAME NUM_BUCKETS HISTOGRAM
—————————— ———– —————
CUST_CITY_ID 254 HEIGHT BALANCED
sys@JINGYONG show parameter optimzer_features_enable
sys@JINGYONG show parameter optimizer_features_enable
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_features_enable string 11.2.0.1
sys@JINGYONG set autotrace traceonly
sys@JINGYONG select count(*) from sh.customers where cust_city_id=52500;
執(zhí)行計(jì)劃
———————————————————-
Plan hash value: 296924608
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 382 (3)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 66 | 330 | 382 (3)| 00:00:04 |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – filter(CUST_CITY_ID =52500)
現(xiàn)在將 optimizer_features_enable 設(shè)置為 10.2.0.3
sys@JINGYONG alter session set optimizer_features_enable= 10.2.0.3
會(huì)話已更改。
sys@JINGYONG set autotrace traceonly
sys@JINGYONG select count(*) from sh.customers where cust_city_id=52500;
執(zhí)行計(jì)劃
———————————————————-
Plan hash value: 296924608
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 382 (3)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 121 | 605 | 382 (3)| 00:00:04 |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – filter(CUST_CITY_ID =52500)
現(xiàn)在的基數(shù)是 121=55500*.002179391,CUST_CITY_ID 的 density 為.002179391
這些 nearly popular 值被歸類為 non-popular values 使用與 non-popular values 相同的計(jì)算公式. 例如, 如果謂詞是 cust_city_id=52114, 那么它的評(píng)估基數(shù)將是 66 行. 與 non-popular 值 52500 的基數(shù)一樣, 但是 cust_city_id=52114 實(shí)際上有 227 行記錄.
sys@JINGYONG select count(*) from sh.customers where cust_city_id=52114;
執(zhí)行計(jì)劃
———————————————————-
Plan hash value: 296924608
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 382 (3)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 66 | 330 | 382 (3)| 00:00:04 |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – filter(CUST_CITY_ID =52114)
sys@JINGYONG select count(*) from sh.customers where cust_city_id=52114;
COUNT(*)
———-
227
唯一能讓優(yōu)化器意識(shí)到這些 near popular values 的方法是使用動(dòng)態(tài)抽樣. 動(dòng)態(tài)抽樣在優(yōu)化一個(gè) sql 語句時(shí)會(huì)收集額外的 statement-specific 對(duì)象統(tǒng)計(jì)信息. 在這個(gè)例子中, 動(dòng)態(tài)抽樣提示加入到了查詢中且優(yōu)化器會(huì)得到一個(gè)更準(zhǔn)確的基數(shù)評(píng)估值.
sys@JINGYONG select /*+ dynamic_sampling(a 2) */ count(a.cust_id) from sh.custo
mers a where a.cust_city_id=52114;
執(zhí)行計(jì)劃
———————————————————-
Plan hash value: 296924608
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 382 (3)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 246 | 410 | 382 (3)| 00:00:04 |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – filter(A . CUST_CITY_ID =52114)
Note
—–
– dynamic sampling used for this statement (level=2)
使用動(dòng)態(tài)抽樣可以提高高度平衡直方圖中 non-popular value 的基數(shù)評(píng)估
在上面已經(jīng)討論了在 oracle10g 中使用直方圖可能存的問題和可能的解決方法. 建議從 oracle11g 開始收集統(tǒng)計(jì)信息時(shí)使用參數(shù) METHOD_OPT 的缺省值且利用自適應(yīng)游標(biāo).
如果想手動(dòng)設(shè)置 method_opt 參數(shù)值不使用缺省值要確保只對(duì)需要直方圖的列進(jìn)行設(shè)置. 將 method_opt 設(shè)置為 for all columns size 254 將會(huì)使 oracle 對(duì)每一個(gè)列都收集直方圖信息. 這對(duì)于收集統(tǒng)計(jì)信息來說是不必要的會(huì)增加運(yùn)行時(shí)間和浪費(fèi)系統(tǒng)資源, 也會(huì)增加存儲(chǔ)這些統(tǒng)計(jì)信息的空間.
還要避免將 method_opt 設(shè)置為 for all index columns size 254 它使 oracle 對(duì)存過索引的每一個(gè)列收集直方圖信息, 也會(huì)浪費(fèi)系統(tǒng)資源. 這個(gè)設(shè)置還有一個(gè)副作用就是會(huì)阻止 oracle 對(duì)哪些不存在索引的列收集基本的列統(tǒng)計(jì)信息.
pending statistics
當(dāng)決定改變 dbms_stats_gather_*_stats 過程的參數(shù)缺省值時(shí)強(qiáng)烈建議在生產(chǎn)系統(tǒng)中修改之前先驗(yàn)證這些改變. 如果沒有一個(gè)完整的測試環(huán)境應(yīng)該使用 pending statistics. 使用 pending statistics 代替常用的數(shù)據(jù)字典表, 存儲(chǔ)在 pending 表中的統(tǒng)計(jì)信息在它們被發(fā)和被系統(tǒng)使用之前可以以一種受控的方式來啟用和測試. 為了激活 pending 統(tǒng)計(jì)信息的收集需要對(duì)希望創(chuàng)建 pending 統(tǒng)計(jì)信息的對(duì)象使用 dbms_stats.set_*_prefs 過程將參數(shù) publish 從缺省值 true 改變 false. 下面的例子中對(duì) sh 用戶下的 sales 表啟用 pending 統(tǒng)計(jì)信息并對(duì) sales 表收集統(tǒng)計(jì)信息.
sys@JINGYONG begin
2 dbms_stats.set_table_prefs(SH , SALES , PUBLISH , FALSE
3 end;
4 /
PL/SQL 過程已成功完成。
通過將 publish 設(shè)置為 false 來啟用 pending 統(tǒng)計(jì)信息
正常的收集對(duì)象統(tǒng)計(jì)信息
sys@JINGYONG begin
2 dbms_stats.gather_table_stats(SH , SALES
3 end;
4 /
PL/SQL 過程已成功完成。
對(duì)于這些對(duì)象收集的統(tǒng)計(jì)信息可以查詢 *_tab_pending_stats 視圖來顯示:
sys@JINGYONG select * from dba_tab_pending_stats where owner= SH
OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
—————————— —————————— —————————— —————————— ———- ———- ———– ———– ————-
SH SALES 918843 1769 29 918843 2013-12-18 22
SH SALES SALES_1995 0 0 0 0 2013-12-18 22
SH SALES SALES_1996 0 0 0 0 2013-12-18 22
SH SALES SALES_H1_1997 0 0 0 0 2013-12-18 22
SH SALES SALES_H2_1997 0 0 0 0 2013-12-18 22
SH SALES SALES_Q1_1998 43687 90 29 43687 2013-12-18 22
SH SALES SALES_Q1_1999 64186 121 29 64186 2013-12-18 22
SH SALES SALES_Q1_2000 62197 119 29 62197 2013-12-18 22
SH SALES SALES_Q1_2001 60608 119 30 60608 2013-12-18 22
SH SALES SALES_Q1_2002 0 0 0 0 2013-12-18 22
SH SALES SALES_Q1_2003 0 0 0 0 2013-12-18 22
SH SALES SALES_Q2_1998 35758 76 29 35758 2013-12-18 22
SH SALES SALES_Q2_1999 54233 103 29 54233 2013-12-18 22
SH SALES SALES_Q2_2000 55515 109 30 55515 2013-12-18 22
SH SALES SALES_Q2_2001 63292 119 30 63292 2013-12-18 22
SH SALES SALES_Q2_2002 0 0 0 0 2013-12-18 22
SH SALES SALES_Q2_2003 0 0 0 0 2013-12-18 22
SH SALES SALES_Q3_1998 50515 95 29 50515 2013-12-18 22
SH SALES SALES_Q3_1999 67138 120 29 67138 2013-12-18 22
SH SALES SALES_Q3_2000 58950 110 30 58950 2013-12-18 22
SH SALES SALES_Q3_2001 65769 124 29 65769 2013-12-18 22
SH SALES SALES_Q3_2002 0 0 0 0 2013-12-18 22
SH SALES SALES_Q3_2003 0 0 0 0 2013-12-18 22
SH SALES SALES_Q4_1998 48874 108 29 48874 2013-12-18 22
SH SALES SALES_Q4_1999 62388 114 29 62388 2013-12-18 22
SH SALES SALES_Q4_2000 55984 106 30 55984 2013-12-18 22
SH SALES SALES_Q4_2001 69749 136 29 69749 2013-12-18 22
SH SALES SALES_Q4_2002 0 0 0 0 2013-12-18 22
SH SALES SALES_Q4_2003 0 0 0 0 2013-12-18 22
29 rows selected
可以通過一個(gè) alter session 命令來設(shè)置初始化參數(shù) optimizer_use_pending_stats 為 true 來使用 pending 統(tǒng)計(jì)信息. 在啟用 pending 統(tǒng)計(jì)信息之后任何在該會(huì)話運(yùn)行的 sql 將使用這些新的沒有發(fā)布的統(tǒng)計(jì)信息. 對(duì)于在工作負(fù)載下的所訪問的表沒有 pending 統(tǒng)計(jì)信息時(shí)優(yōu)化器將使用標(biāo)準(zhǔn)數(shù)據(jù)字典表中的當(dāng)前統(tǒng)計(jì)信息. 當(dāng)你驗(yàn)證這些 pending 統(tǒng)計(jì)信息后可以使用 dbms_stats.publish_pending_stats 過程來發(fā)布.
何時(shí)收集統(tǒng)計(jì)信息
為了選擇一個(gè)最佳的執(zhí)行計(jì)劃優(yōu)化器必須要有有代表性的統(tǒng)計(jì)信息, 有代表性的統(tǒng)計(jì)信息并不是最新的統(tǒng)計(jì)信息但是這組統(tǒng)計(jì)信息能幫助優(yōu)化器判斷在執(zhí)行計(jì)劃中每一個(gè)操作步驟所期待的正確的行記錄數(shù).
自動(dòng)統(tǒng)計(jì)信息收集 job
在一個(gè)預(yù)定義的維護(hù)窗口中 oracle 會(huì)自動(dòng)對(duì)哪些丟失統(tǒng)計(jì)信息或者統(tǒng)計(jì)信息失效的所有對(duì)象收集統(tǒng)計(jì)信息(每個(gè)工作日的晚上 10 點(diǎn)到零晨 2 點(diǎn)和每個(gè)周末的 6 點(diǎn)到零晨 2 點(diǎn)).
可以使用企業(yè)管理器或使用 dbms_scheduler 和 dbms_auto_task_admin 包來改變這個(gè)維護(hù)窗口.
如果已經(jīng)有一個(gè)完善的統(tǒng)計(jì)信息收集過程或者如果因?yàn)槟承┰蛳胍米詣?dòng)統(tǒng)計(jì)信息收集可以禁用收集任務(wù):
sys@JINGYONG begin
2 dbms_auto_task_admin.disable(
3 client_name= auto optimizer stats collection ,
4 operation= null,
5 window_name= null);
6 end;
7 /
PL/SQL 過程已成功完成。
手動(dòng)統(tǒng)計(jì)信息收集
如果計(jì)劃手動(dòng)維護(hù)優(yōu)化器統(tǒng)計(jì)信息將需要判斷何時(shí)進(jìn)行收集.
基于失效統(tǒng)計(jì), 自動(dòng)收集 job 或者系統(tǒng)中加載新數(shù)據(jù)的時(shí)間你能判斷何時(shí)來收集統(tǒng)計(jì)信息. 如果基本數(shù)據(jù)沒有發(fā)生明顯的改變不建議不斷的重新收集統(tǒng)計(jì)信息這樣只會(huì)浪費(fèi)系統(tǒng)資源.
如果數(shù)據(jù)在一個(gè)預(yù)定義的 ETL 或 ELT job 只加載到系統(tǒng)中那么統(tǒng)計(jì)信息收集操作應(yīng)該作為這個(gè)過程的一部分被調(diào)度. 注意如果使用分區(qū)交換加載并希望利用增量統(tǒng)計(jì)信息將需要在交換過程完成后收集統(tǒng)計(jì)信息.
然而如果系統(tǒng)中有大量的聯(lián)機(jī)事務(wù)只插入少量的數(shù)據(jù)但是這些操作每天都會(huì)發(fā)生, 你將需要判斷何時(shí)你的統(tǒng)計(jì)信息將會(huì)失效然后觸發(fā)統(tǒng)計(jì)信息收集 job. 如果你計(jì)劃依賴 user_tab_statistics 中的 stale_stats 列來判斷統(tǒng)計(jì)信息是否失效你應(yīng)該能意識(shí)到這些信息每天及時(shí)更新. 如果需要更多更及時(shí)的信息比如你的表什么時(shí)候執(zhí)行過 DML 操作你將需要查看 user_tab_modifications 視圖, 它會(huì)顯示每一個(gè)表上執(zhí)行的 insert,update,delete 操作, 表是否執(zhí)行過 truncated 并計(jì)算自己是否已經(jīng)失效. 需要注意這些信息是否定時(shí)的從內(nèi)存中自動(dòng)更新. 如果需要最新的信息需要使用 dbms_stats.flush_database_monitoring_info 函數(shù)來手動(dòng)刷新.
阻止超出范圍的條件
不管你是使用自動(dòng)統(tǒng)計(jì)信息收集 job 還是手動(dòng)收集統(tǒng)計(jì)信息, 如果終端用戶在統(tǒng)計(jì)信息收集之前開始查詢新插入的數(shù)據(jù), 即使只有不到 10% 的數(shù)據(jù)發(fā)生了變化也可能由于失效的統(tǒng)計(jì)信息得到一個(gè)次優(yōu)的執(zhí)行計(jì)劃. 發(fā)生這種問題最常見的原因是 where 子句中謂詞提供的值超出了最小 / 最大列統(tǒng)計(jì)信息所能表示的范圍. 這通常稱為超出范圍的錯(cuò)誤.
這種情況在分區(qū)表中很常見. 一個(gè)新分區(qū)剛添加到一個(gè)存在的范圍分區(qū)表中且記錄剛被插入到分區(qū)中. 在對(duì)這個(gè)新分區(qū)收集統(tǒng)計(jì)信息之前終端用戶就開始查詢這些新的數(shù)據(jù). 對(duì)于分區(qū)表, 可以使用 dbms_stats.copy_table_stats 過程 (從 oracle10.2.0.4 開始可以使用) 來阻止超出范圍的條件表達(dá)式. 這個(gè)過程將復(fù)制原分區(qū)數(shù)據(jù)的統(tǒng)計(jì)信息為新創(chuàng)建分區(qū)的統(tǒng)計(jì)信息. 它能復(fù)制依賴對(duì)象的統(tǒng)計(jì)信息: 列, 本地 (分區(qū)) 索引等等. 直到對(duì)分區(qū)收集統(tǒng)計(jì)信息之前復(fù)制的統(tǒng)計(jì)信息只能作為臨時(shí)的解決方法來使用. 復(fù)制的統(tǒng)計(jì)信息不能代替真實(shí)收集的統(tǒng)計(jì)信息.
注意通常 dbms_stats.copy_table_stats 只能調(diào)整分區(qū)統(tǒng)計(jì)信息不能調(diào)整全局或表級(jí)別的統(tǒng)計(jì)信息. 如果想在復(fù)制統(tǒng)計(jì)信息時(shí)對(duì)分區(qū)列進(jìn)行全局級(jí)別的更新需要將 dbms_stats.copy_table_stats 中的 flags 參數(shù)設(shè)置為 8.
對(duì)于非分區(qū)表你能通過 dbms_stats.set_column_stats 過程來手動(dòng)設(shè)置列的最大值. 通常這種方法不建議它并不能代替真實(shí)的收集的統(tǒng)計(jì)信息.
提高收集統(tǒng)計(jì)信息的效率
隨著數(shù)據(jù)量的增長和維護(hù)窗口的縮短能及時(shí)的收集統(tǒng)計(jì)信息是很重要的.oracle 提供了多種方法來提高統(tǒng)計(jì)信息收集的速度.
使用并行
用于收集統(tǒng)計(jì)信息的幾種并行方法
內(nèi)部對(duì)象并行
外部對(duì)象并行
內(nèi)部對(duì)象并行與外部對(duì)象并行的組合
內(nèi)部對(duì)象并行
內(nèi)部對(duì)象并行是由 dbms_stats.gather_*_stats 過程的 degree 參數(shù)來控制的.degree 參數(shù)控制著用于收集統(tǒng)計(jì)信息的并行服務(wù)器進(jìn)程的數(shù)量.
通常 oracle 使用數(shù)據(jù)字典表中并行屬性的值作為指定并行服務(wù)器進(jìn)程的參數(shù)值. 在 oracle 數(shù)據(jù)庫中所有的表都有一個(gè) degree 屬性缺省值為 1. 對(duì)要收集統(tǒng)計(jì)信息的大表設(shè)顯示地設(shè)置這個(gè)參數(shù)能提高統(tǒng)計(jì)信息收集的速度.
你也可以設(shè)置 degree 為 auto_degree.oracle 將基于一個(gè)對(duì)象的大小自動(dòng)判斷一個(gè)合適的并行服務(wù)進(jìn)程個(gè)數(shù)來收集統(tǒng)計(jì)信息. 這個(gè)值的范圍在 1 - 小對(duì)象 (串行操作) 到大對(duì)象的 default_degree((PARALLEL_THREADS_PER_CPU X CPU_COUNT)之間.
你將會(huì)注意到對(duì)一個(gè)分區(qū)表設(shè)置 degree 這意味著對(duì)每一個(gè)分區(qū)使用多個(gè)并行服務(wù)器進(jìn)程來收集統(tǒng)計(jì)信息但是不能同時(shí)對(duì)不同的分區(qū)收集統(tǒng)計(jì)信息. 統(tǒng)計(jì)信息只能在一個(gè)分區(qū)收集完之后才能收集下一個(gè)分區(qū).
外部對(duì)象并行
在 oracle11.2.0.2 中, 外部對(duì)象并行被引入且由 global statistics gathering preference concurrent 來控制. 當(dāng) concurrent 設(shè)置為 true 時(shí),oracle 將使用 oracle job 作業(yè)調(diào)度和高級(jí)隊(duì)列組來創(chuàng)建和管理多個(gè)統(tǒng)計(jì)信息收集 job 并發(fā)執(zhí)行. 通過 oracle 來完全利用多個(gè) cpu 來對(duì)多個(gè)表和 (子) 分區(qū)并發(fā)的收集統(tǒng)計(jì)信息來減小整個(gè)統(tǒng)計(jì)信息收集的時(shí)間.
活動(dòng)并行統(tǒng)計(jì)信息收集 job 的最大個(gè)數(shù)是由 job_queue_processes 參數(shù)來控制的.job_queue_processes 缺省值設(shè)置為 1000. 這通常對(duì)于并行統(tǒng)計(jì)信息收集操作來說太高了尤其是在并行執(zhí)行也在使用時(shí)更是如此. 一個(gè)最有效的值應(yīng)該是總 cpu 核數(shù)的 2 倍 (在 rac 中這是每一個(gè)節(jié)點(diǎn)的參數(shù)值). 你需要確在系統(tǒng)級(jí)別設(shè)置這個(gè)參數(shù)(alter system 命令或 init.ora 文件) 而不是在會(huì)話級(jí)別(alter session).
內(nèi)部和外部并行的組合
在一個(gè)并行統(tǒng)計(jì)收集操作中的每一個(gè)統(tǒng)計(jì)信息收集 job 都能以并行的方式來執(zhí)行. 將并行統(tǒng)計(jì)收集和并行執(zhí)行組合起來能大大減小收集統(tǒng)計(jì)信息的時(shí)間.
當(dāng)使用并行執(zhí)行作為一個(gè)并行統(tǒng)計(jì)信息收集操作的一部分時(shí)你應(yīng)該禁用 parallel_adaptive_multi_user 初始化參數(shù)來阻止并行 job 被降級(jí)為串行操作. 它應(yīng)該在系統(tǒng)級(jí)別來禁用而不是在會(huì)話級(jí)別禁用這個(gè)參數(shù):
sys@JINGYONG alter system set parallel_adaptive_multi_user=false;
系統(tǒng)已更改。
增量統(tǒng)計(jì)信息
分區(qū)表的統(tǒng)計(jì)信息收集是由表級(jí)別 (global statistics) 和(子)分區(qū)級(jí)別的統(tǒng)計(jì)信息收集操作組成的. 如果一個(gè)分區(qū)表的 incremental preference 設(shè)置為 true,dbms_stats.gather_*_stats 中參數(shù) granularity 的值包含 global 和 estimate_percent 設(shè)置為 auto_sample_size,oracle 將會(huì)通過掃描這些已經(jīng)被添加或被修改的分區(qū)來獲得全局級(jí)別的統(tǒng)計(jì)信息而不是整個(gè)表的.
增量全局統(tǒng)計(jì)信息是由表中每個(gè)分區(qū)存儲(chǔ)的概要計(jì)算出來的. 一個(gè)概要是這個(gè)分區(qū)和分區(qū)中列的統(tǒng)計(jì)信息的元數(shù)據(jù). 聚合分區(qū)級(jí)的統(tǒng)計(jì)信息和每個(gè)分區(qū)的概要信息將能精確的生成全局級(jí)別的統(tǒng)計(jì)信息因此消除了需要掃描整個(gè)表的操作. 當(dāng)一個(gè)新的分區(qū)添加到表中, 你僅僅需要對(duì)這個(gè)新的分區(qū)進(jìn)行統(tǒng)計(jì)信息收集而已. 表級(jí)別的統(tǒng)計(jì)信息將會(huì)使用新分區(qū)的概要信息和已經(jīng)存的分區(qū)的概要信息來自動(dòng)和精確的計(jì)算出來.
注意當(dāng)增量統(tǒng)計(jì)信息被啟用時(shí)分區(qū)統(tǒng)計(jì)信息不從子分區(qū)統(tǒng)計(jì)信息中進(jìn)行聚合操作.
何時(shí)不收集統(tǒng)計(jì)信息
盡管 oracle 需要精確的統(tǒng)計(jì)信息來選擇一個(gè)最優(yōu)的執(zhí)行計(jì)劃, 有些情況下收集統(tǒng)計(jì)信息是很困難的, 很昂貴的或者是不能及時(shí)完成的所以要有一和睦替代的策略.
volatile 表
一個(gè) volatile 表是隨著時(shí)間的變化數(shù)據(jù)量會(huì)發(fā)生很大改變的表. 例如, 一個(gè)訂單隊(duì)列表, 這個(gè)表在一天開始的時(shí)候是空的, 隨著時(shí)間的推移訂單將會(huì)填滿這個(gè)表. 當(dāng)被處理的一個(gè)訂單從表中刪除時(shí)所以這一天結(jié)束時(shí)這個(gè)表會(huì)被再次清空.
如果你依賴自動(dòng)統(tǒng)計(jì)信息收集 job 來維護(hù)象這樣的表的統(tǒng)計(jì)信息那么這些表顯示的統(tǒng)計(jì)信息總是空的因?yàn)槭占?job 是在晚上. 然而在當(dāng)天工作期間這個(gè)表可能有成千上萬行記錄.
對(duì)于這樣的表最好是在白天收集一組有代表性的統(tǒng)計(jì)信息并鎖定這些信息. 鎖定這些統(tǒng)計(jì)信息將阻止自動(dòng)統(tǒng)計(jì)信息收集 job 來覆蓋它們. 優(yōu)化器在優(yōu)化 sql 語句之前在編譯 sql 語句時(shí)會(huì)使用動(dòng)態(tài)抽樣對(duì)表收集基本的統(tǒng)計(jì)信息. 盡管通過動(dòng)態(tài)抽樣產(chǎn)生的統(tǒng)計(jì)信息質(zhì)量不高或者不象使用 dbms_stats 包收集的統(tǒng)計(jì)信息那樣完整但在大多數(shù)情況下已經(jīng)夠用了.
全局臨時(shí)表
全局臨時(shí)表在應(yīng)用程序上下文中經(jīng)常用來存儲(chǔ)中間結(jié)果. 一個(gè)全局臨時(shí)表對(duì)于有合理權(quán)限的所有用戶共享它的定義, 但是數(shù)據(jù)只在各自的會(huì)話中可見. 直到數(shù)據(jù)被插入表中之前是不分配物理存儲(chǔ)的. 一個(gè)全局臨時(shí)表可能是 transaction specific(提交時(shí)刪除行 (或 session-specific(提交時(shí)保存行). 對(duì)一個(gè) transaction specific 的表收集統(tǒng)計(jì)信息將導(dǎo)致對(duì)這個(gè)表進(jìn)行 truncate 操作. 相反,
可以對(duì)全局臨時(shí)表收集統(tǒng)計(jì)信息. 然而統(tǒng)計(jì)信息的收集將僅僅基于 session-private 數(shù)據(jù)內(nèi)容進(jìn)行收集但是這些統(tǒng)計(jì)信息將能被訪問這個(gè)表的所有會(huì)話使用.
如果有一個(gè)全局臨時(shí)表持續(xù)有行數(shù)據(jù)且每一個(gè)會(huì)話將有相同的數(shù)據(jù)量和相同的數(shù)值那么應(yīng)該在一個(gè)會(huì)話中收集一組有代表性的統(tǒng)計(jì)信息并鎖定它們防止其它會(huì)話將其覆蓋. 注意自動(dòng)統(tǒng)計(jì)信息收集 job 是不會(huì)收集全局臨時(shí)表的統(tǒng)計(jì)信息.
中間工作表
中間工作表是典型的一個(gè) ELT 過程或者一個(gè)復(fù)雜事務(wù)的一部分. 這些表只會(huì)寫一次, 讀一次然后 truncate 或者 delete. 在這種情況下
收集統(tǒng)計(jì)信息成本超過了它所帶來的好處, 因?yàn)榻y(tǒng)計(jì)信息只能用一次. 相反動(dòng)態(tài)抽樣在這種情況下更有用. 建議鎖定中間工作表的統(tǒng)計(jì)信息來持久的阻止自動(dòng)統(tǒng)計(jì)信息收集 job 來對(duì)它們收集統(tǒng)計(jì)信息.
收集其它類型的統(tǒng)計(jì)信息
因?yàn)楝F(xiàn)在只支持基于成本的優(yōu)化器, 數(shù)據(jù)庫中所有的表需要有統(tǒng)計(jì)信息, 包含所有的數(shù)據(jù)字典表 (sys,system 用戶所擁有的表和內(nèi)置在 system 和 sysaux 表空間中的表) 和通過動(dòng)態(tài) v$ 性能視圖使用的 x$ 表.
數(shù)據(jù)字典統(tǒng)計(jì)信息
數(shù)據(jù)字典表的統(tǒng)計(jì)信息是由自動(dòng)統(tǒng)計(jì)信息收集 job 在晚維護(hù)窗口進(jìn)行收集的. 強(qiáng)烈建議你允許 oracle 自動(dòng)統(tǒng)計(jì)信息收集 job 來維護(hù)數(shù)據(jù)字典統(tǒng)計(jì)信息即使在你關(guān)閉對(duì)主應(yīng)用程序方案關(guān)閉自動(dòng)統(tǒng)計(jì)信息收集的情況下. 可以使用 dbms_stats.set_global_prefs 過程將 autostats_target 從 auto 改成 oracle
sys@JINGYONG begin
2 dbms_stats.set_global_prefs(AUTOSTATS_TARGET , ORACLE
3 end;
4 /
PL/SQL 過程已成功完成。
固定對(duì)象統(tǒng)計(jì)信息
自動(dòng)統(tǒng)計(jì)信息收集 job 不會(huì)收集固定對(duì)象的統(tǒng)計(jì)統(tǒng)計(jì)信息. 當(dāng)優(yōu)化統(tǒng)計(jì)信息丟失時(shí)不象其它的數(shù)據(jù)庫表對(duì)于 sql 語句中調(diào)用 X$ 表是不能自動(dòng)使用動(dòng)態(tài)抽樣的. 如果它們的統(tǒng)計(jì)信息丟失優(yōu)化器會(huì)使用預(yù)先定義的缺省統(tǒng)計(jì)信息. 這些缺省的統(tǒng)計(jì)信息可能沒有代表性且可能導(dǎo)致選擇次優(yōu)的執(zhí)行計(jì)劃, 在系統(tǒng)中可能會(huì)導(dǎo)致嚴(yán)重的性能問題. 如果是這個(gè)原因造成性能問題強(qiáng)烈建議你手動(dòng)收集固定對(duì)象的統(tǒng)計(jì)信息.
可以使用 dbms_stats.gather_fixed_objects_stats 過程來收集固定對(duì)象的統(tǒng)計(jì)信息. 因?yàn)樵谙到y(tǒng)如果存在一個(gè)有代表性的工作負(fù)載收集 x$ 這些固定對(duì)象的統(tǒng)計(jì)信息是很重要的. 在大型系統(tǒng)中由于收集固定對(duì)象統(tǒng)計(jì)信息需要額外的資源所以對(duì)固定對(duì)象收集統(tǒng)計(jì)信息不總是可行. 如果不能在負(fù)載高峰期間收集固定對(duì)象的統(tǒng)計(jì)信息那么應(yīng)該在系統(tǒng)負(fù)載降低之后對(duì)三種關(guān)鍵類型的固定對(duì)象表收集統(tǒng)計(jì)信息:
structural data– 比如 controlfile contents
Session based data – 比如 v$session, v$access
Workload data - 比如 v$sql, v$sql_plan
建議當(dāng)主數(shù)據(jù)庫或應(yīng)用程序升級(jí)后, 實(shí)現(xiàn)新的模塊或者改變數(shù)據(jù)庫的配置后重新收集固定對(duì)象統(tǒng)計(jì)信息. 例如, 如果增加 SGA 的大小包含緩沖區(qū)緩存和共享池信息的 x$ 表會(huì)顯著的發(fā)生改變, 比如 v$buffer_pool 或 v$shared_pool_advice 視圖使用的 x$ 表.
系統(tǒng)統(tǒng)計(jì)信息
系統(tǒng)統(tǒng)計(jì)信息能讓優(yōu)化器通過使用執(zhí)行這個(gè)語句相關(guān)的實(shí)際系統(tǒng)硬件信息, 比如,cpu 速度和 IO 性能, 來在執(zhí)行計(jì)劃中對(duì)每一個(gè)步驟獲得更精確的成本值. 系統(tǒng)統(tǒng)計(jì)信息缺省情況下是啟用的, 它使用缺省值自動(dòng)初始化, 這些值對(duì)于大多數(shù)系統(tǒng)來說是有代表性的.
感謝你能夠認(rèn)真閱讀完這篇文章，希望丸趣 TV 小編分享的“Oracle 如何收集優(yōu)化統(tǒng)計(jì)數(shù)據(jù)”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持丸趣 TV，關(guān)注丸趣 TV 行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來學(xué)習(xí)!
