收集最佳化統計資料(Optimizer Statistics)的最佳實踐方法
介紹
oracle最佳化器對一個sql語句檢測所有可能的執行計劃並選擇一個成本值最小的,這裡的成本代表了一個特定執行計劃的資源使用情況.為了讓最佳化器能準確的判斷一個執行計劃的成本它必須要關於sql語句要訪問的所有物件(表或索引)的資訊同時還要有執行sql語句的系統資訊.
這些必要的資訊通常稱為最佳化器統計資訊.理解和管理最佳化器統計資訊是最佳化sql執行的關鍵.知道何時以及如何收集統計資訊對於維護可以接受的效能來說至關重要.
這裡將介紹在常見的oracle資料庫場景中何時以及如何來收集統計資訊.它包含以下內容:
怎樣收集統計資訊
何時收集統計資訊
提高收集統計資訊的效率
何時不收集統計資訊
收集處理的統計資訊
怎樣收集統計資訊
在oracle中收集統計資訊的首選方法是使用提供了自動統計資訊收集job.
自動統計資訊收集job
對執行oracle autotask任務的一個預定義維護視窗期間對哪些丟失統計資訊或統計資訊過期的所有資料庫物件收集統計資訊,oracle內部很重視資料庫物件的統計資訊因此這此物件在進行處理前需要更新統計資訊.
自動統計資訊收集job是使用dbms_stats.gather_database_stats_job_proc過程來實現的,它與dbms_stats.gather_*_stats過程使用相同的預設引數.這個預設值在大多數情況下是有效的.然而偶爾也需要改變這些統計資訊收集引數的預設值,可以透過dbms_stats.set_*_pref過程來進行修改.例如設定一個表中有5%的資料發生了改變而不是預設值10%時就認會它的統計資訊失效了.如果想要改變這個統計資訊失效的閾值,可以使用dbms_stats.set_table_prefs過程來修改stale_percent引數.
sys@JINGYONG> begin
2 dbms_stats.set_table_prefs('SH','SALES','STALE_PERCENT','5');
3 end;
4 /
PL/SQL 過程已成功完成。
使用dbms_stats.set_table_prefs過程將表統計資訊失效的閾stale_percent改變了5%.
手動統計資訊收集
如果已經有一個完善的統計資訊收集過程或者因為某些原因想要對特定使用者方案禁用自動統計資訊收集而只保留收集資料字典的統計資訊.可以使用dbms_stats.set_global_prefs過程來改變autostats_target引數為oracle來替代auto.
sys@JINGYONG> begin
2 dbms_stats.set_global_prefs('AUTOSTATS_TARGET','ORACLE');
3 end;
4 /
PL/SQL 過程已成功完成。
用上面的程式碼改變自動統計資訊收集job只自動收集資料字典統計資訊.
為了手動收集統計資訊你應該使用dbms_stats包,用它來替找過時的analyze命令.dbms_stats包提供多個dbms_stats.gather_*_stats過程來收集使用者方案物件,資料字典和固定物件的統計資訊.理想情況下應該讓這些過程除了使用者方案名和物件名引數之外其它的引數值都使用預設值.預設引數值和自適應引數設定在大多數情況下已經足夠了.
sys@JINGYONG> begin
2 dbms_stats.gather_table_stats('SH','SALES');
3 end;
4 /
PL/SQL 過程已成功完成。
兩個修改最頻繁的引數是ESTIMATE_PERCENT和METHOD_OPT
ESTIMATE_PERCENT
在收集統計資訊的過程最常見的問題是'使用什麼樣的抽樣大小'與這個問題相關的設定是dbms_stats.gather_*_stats過程中的ESTIMATE_PERCENT引數.這個ESTIMATE_PERCENT引數判斷用來計算統計資訊所使用的行數百份比.當表中的所有行被處理時收集的統計資訊是最準確的(比如100%抽樣).然而抽樣的樣本越大收集操作的時間越長.因此使用怎樣的抽樣大小來提供及時準確的統計資訊.
oracle11G之前的ESTIMATE_PERCENT
在oracle10g中,ESTIMATE_PERCENT的預設值從100%變成了AUTO_SAMPLE_SIZE.這個AUTO_SAMPLE_SIZE的目的是讓oracle在每次收集統計資訊時來判斷每一個表的合適的抽樣大小.這將允許oracle自動地對每一個表改變其抽樣大小但仍然能確保及時有效的收集統計資訊.這種方法對於大多數表來說是一種可取的方法但是對於資料存在傾斜的表來說存在問題.當表中資料出現傾斜
AUTO_SAMPLE_SIZE演算法通常選擇的抽樣大小太小,在這種情況下最好的方法還是手動指定ESTIMATE_PERCENT引數的大小.
oracle11g中的ESTIMATE_PERCENT
oracle11g中引入一種新的hash-based抽樣演算法來提供精確的統計資料解決了精確和速度兩個關鍵問題.它的精確度接近100%抽樣大小的水平但是成本與10%抽樣大小相當.這種新的演算法只有當任何dbms_stats.gather_*_stats過程中的ESTIMATE_PERCENT引數設定為AUTO_SAMPLE_SIZE時才會使用.
下面的表格顯示了一個早前使用1%抽樣,100%抽樣和AUTO_SAMPLE_SIZE抽樣收集統計資訊的結查.第一行比較執行的時間,後繼的行將顯示每次執行計算出來的L_ORDERKDY和L_COMMENT兩個列不同值的數量(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%抽樣執行的時間要快9倍且只比1%抽樣執行的時間慢2.4倍,而提供的統計資訊質量幾乎接近100%抽樣的水平(不足以改變執行計劃).
從oracle11g開始強烈建議你使用estimate_percent引數的預設值.如果你手動設定estimate_percent引數儘管你將它設定為100%它還是會使用舊的收集演算法.
method_opt
在dbms_stats.gather_*_stats過程中到目前為止最有爭議的引數就是method_opt.這個method_opt引數控制著在收集統計資訊過程是否建立直方圖.直方圖是一種特殊型別的列統計資訊提供關於表中列資料分佈的詳細資訊.所以這就是為什麼直方圖存在爭議的問題
直方圖的建立會增加統計收集的時間和系統資源的使用但最大的問題是直方圖與bind peeking功能的相互作用以及對near popular values基數評估的影響.
直方圖與bind peeking
bind peeking對直方圖的不利影響已經在oracle11g中透過引入自適應遊標共享被淡化了但是直到今天仍然感受到它的影響.為了說明自適應遊標共享是怎樣解決這個問題的先來檢查一個這個問題的原因.
oracle11g之前的直方圖與bind peeking
在oracle11g之前當最佳化一個在where子句中包含繫結變數的語句時最佳化在第一次執行這個語句時會窺視這些繫結變數的值(在硬解析階段).最佳化器然後會基於這個初始化的繫結變數值來判斷執行計劃.對於後續執行這個查詢不會執行繫結變數窺視(沒有硬解析發生),所以對於後面的這個語句的所有執行即使繫結變數發生了改變也會決定使用第一次設定的繫結變數值所產生的執行計劃對於在表示式中使用繫結變數的列存在直方圖這將有助於判斷初始化繫結變數值的最優執行計劃.因此對於相同的語句依賴於初始化硬解析時繫結變數的值執行計劃可能會有所不同.
有兩種方法可以避免這個問題:刪除直方圖並在將業停止收集直方圖或禁用bind peeking繫結變數窺視.根據所有的sql是否都在使用繫結變數你可以判斷哪一種方法更適合你的資料庫.
禁止直方圖的建立
如果你的環境中所有sql語句使用了繫結變數那麼最好是刪除存在的直方圖並在將來的收集統計資訊過程中禁止建立直方圖.禁上直方圖的建立能確保依賴於繫結變數的值的執行計劃不會發生改變也會減少收集統計資訊的時間.沒有直方圖最佳化器會假設列中不相同的值是均勻分佈在所有行中的並當窺視sql語句中初始化繫結變數值時使用NDV(number distinct values)來判斷基數的評估.
可以使用dbms_stats.delete_table_stats過程來刪除統計資訊中存在的直方圖資訊.
sys@JINGYONG> begin
2 dbms_stats.delete_table_stats('SH','SALES');
3 end;
4 /
PL/SQL 過程已成功完成。
接下來可以透過使用dbms_stats.set_param過程來改變method_opt引數的預設值來阻止將來生成直方圖.這能確保dbms_stats.gather_*_stats過程和自動統計資訊收集job在將來都不會收集直方圖資訊.
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過程來對受影響的物件重新收集統計資訊.
注意在oracle11g中透過使用dbms_stats.delete_column_stats和對直方圖設定col_stat_type可以刪除不想要的直方圖而不用刪除所有的直方圖資訊.也可以對單個表或者使用dbms_stats.set_table_prefs過程來對列禁止直方圖的建立.
你知道直方圖也用於某些連線謂詞而且刪除直方圖對連線謂詞的基數評估會有影響.在這種情況下更安全的方法是禁用繫結變數窺視.
禁用繫結變數窺視
如果你的環境中有一些sql語句是使用繫結變數而有一些sql語句使用了literal values那麼你應該禁用繫結變數窺視.透過禁用繫結變數窺視它將阻止最佳化器窺初始繫結變數值且將不使用直方圖來進行基數評估.相反最佳化器將列中的不相同值是均勻分佈在行中並使用NDV(number distinct values)來進行基數評估操作.這將對於使用繫結變數的語句使用一致的執行計劃.但是如果sql語句使用literal values那麼仍然能利用直方圖來得到最優的執行計劃.可以透過設定隱含引數_optim_peek_user_binds為false來禁用繫結變數窺視.
oracle11g中的直方圖與繫結變數窺視
在oracle11g中最佳化器已經增強了允許多個版本的執行計劃用於使用繫結變數的單個sql語句.這個功能就叫作自適應遊標共享且依賴於對執行統計的監控來確保每一個繫結變數值使用正確的執行路徑.在第一次執行時最佳化器將窺視繫結變數值且基於繫結變數值的選擇性來判斷其執行計劃,與oracle11g之前的版本一樣.如果最佳化器認為最佳的執行計劃可能依賴於繫結變數的值(例如,列上的直方圖或者一個範圍謂詞,or,<,>)這個遊標將會被標記為bind sensitive.當一個遊標被標記為bind sensitive.oracle將監控遊標使用不同繫結值的行為來確定是否要使用一個不同的執行計劃.
如果一個不同的繫結變數值在後繼的執行中使用,最佳化器將使用相同的執行計劃因為oracle一開始會假設遊標能被共享.然而新的繫結變數的執行統計會被記錄並與之前繫結變數值的執行統計進行比較.如果oracle判斷新的繫結變數值造成了操作的資料量明顯不同那麼對於新的繫結變數值在下一次執行時會進行硬解析且這個遊標會被標記為bind-aware.每一個bind_aware遊標與繫結變數的選擇性範圍有關因此遊標只有在這個語句的繫結變數值在一個被認為可共享的範圍之內才能被共享.當另一個新的繫結變數值被使用時,最佳化器將會基於繫結變數值的選擇性的相似度來找到一個它認為最好的一個遊標.如果它不能找到一個遊標,它將建立一個新的.如果執行計劃的一個新的遊標與一個已經存在的遊標一樣,那麼兩個遊標將會在共享池中合併從而節省空間.遊標的選擇性範圍為了包含新繫結變數值的選擇性將會有所增加.
透過允許對單個sql語句存在多個執行計劃,在oracle11g中直方圖對於使用繫結變數的語句不再有負面影響.
直方圖和near popular values
當最佳化器遇到一個where子句中謂詞列上有直方圖,它將基於literal value的出現頻率來進行基數評估.例如假設在sh使用者下的customers表中的cust_city_di列上有一個高度平衡的直方圖且有一個使用cust_city_id=51806的查詢.最佳化器首先會檢查這個直方圖有51806作為它的end point有多少個桶.在這種情況下,endpint是51806的桶有136,137,138和139(可以檢視user_histograms).因為endpoint的值有兩個或多個桶要被考慮為出現頻繁的最佳化器將使用下面的公式來進行基數評估:
(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;
執行計劃
----------------------------------------------------------
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,它對於任何桶來說都不是一個endpoint那麼最佳化器會使用一個同的公式來進行基數評估.對於endpoint值只在一個桶出現或者任何桶中都沒有這個endpoint時最佳化器會使用下面的計算公式:
density * number of rows in the table,
density的值可以在user_tab_col_statistics中看到,它的值從oracle10.2.0.4以後最佳化器將不再使用.記錄這個值是為了向後相容,在oracle9i和oracle10g前期的版本中會使用這個值.此外如果引數optimizer_features_enable設定的版本小於10.2.0.4,那麼檢視中的density仍然會被使用.
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;
執行計劃
----------------------------------------------------------
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)
現在將optimizer_features_enable設定為10.2.0.3
sys@JINGYONG> alter session set optimizer_features_enable='10.2.0.3';
會話已更改。
sys@JINGYONG> set autotrace traceonly
sys@JINGYONG> select count(*) from sh.customers where cust_city_id=52500;
執行計劃
----------------------------------------------------------
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)
現在的基數是121=55500*.002179391,CUST_CITY_ID的density為.002179391
這些nearly popular值被歸類為non-popular values使用與non-popular values相同的計算公式.例如,如果謂詞是cust_city_id=52114,那麼它的評估基數將是66行.與non-popular值52500的基數一樣,但是cust_city_id=52114實際上有227行記錄.
sys@JINGYONG> select count(*) from sh.customers where cust_city_id=52114;
執行計劃
----------------------------------------------------------
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
唯一能讓最佳化器意識到這些near popular values的方法是使用動態抽樣.動態抽樣在最佳化一個sql語句時會收集額外的
statement-specific物件統計資訊.在這個例子中,動態抽樣提示加入到了查詢中且最佳化器會得到一個更準確的基數評估值.
sys@JINGYONG> select /*+ dynamic_sampling(a 2) */ count(a.cust_id) from sh.custo
mers a where a.cust_city_id=52114;
執行計劃
----------------------------------------------------------
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)
使用動態抽樣可以提高高度平衡直方圖中non-popular value的基數評估
在上面已經討論了在oracle10g中使用直方圖可能存的問題和可能的解決方法.建議從oracle11g開始收集統計資訊時使用引數METHOD_OPT的預設值且利用自適應遊標.如果想手動設定method_opt引數值不使用預設值要確保只對需要直方圖的列進行設定.將method_opt設定為for all columns size 254將會使oracle對每一個列都收集直方圖資訊.這對於收集統計資訊來說是不必要的會增加執行時間和浪費系統資源,也會增加儲存這些統計資訊的空間.還要避免將method_opt設定為for all index columns size 254它使oracle對存過索引的每一個列收集直方圖資訊,也會浪費系統資源.這個設定還有一個副作用就是會阻止oracle對哪些不存在索引的列收集基本的列統計資訊.
pending statistics
當決定改變dbms_stats_gather_*_stats過程的引數預設值時強烈建議在生產系統中修改之前先驗證這些改變.如果沒有一個完整的測試環境應該使用pending statistics.使用pending statistics代替常用的資料字典表,儲存在pending表中的統計資訊在它們被髮和被系統使用之前可以以一種受控的方式來啟用和測試.為了啟用pending統計資訊的收集需要對希望建立pending統計資訊的物件使用dbms_stats.set_*_prefs過程將引數publish從預設值true改變false.下面的例子中對sh使用者下的sales表啟用pending統計資訊並對sales表收集統計資訊.
sys@JINGYONG> begin
2 dbms_stats.set_table_prefs('SH','SALES','PUBLISH','FALSE');
3 end;
4 /
PL/SQL 過程已成功完成。
透過將publish設定為false來啟用pending統計資訊
正常的收集物件統計資訊
sys@JINGYONG> begin
2 dbms_stats.gather_table_stats('SH','SALES');
3 end;
4 /
PL/SQL 過程已成功完成。
對於這些物件收集的統計資訊可以查詢*_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
可以透過一個alter session命令來設定初始化引數optimizer_use_pending_stats為true來使用pending統計資訊.在啟用pending統計資訊之後任何在該會話執行的sql將使用這些新的沒有釋出的統計資訊.對於在工作負載下的所訪問的表沒有pending統計資訊時最佳化器將使用標準資料字典表中的當前統計資訊.當你驗證這些pending統計資訊後可以使用
dbms_stats.publish_pending_stats過程來發布.
何時收集統計資訊
為了選擇一個最佳的執行計劃最佳化器必須要有有代表性的統計資訊,有代表性的統計資訊並不是最新的統計資訊但是這組統計資訊能幫助最佳化器判斷在執行計劃中每一個操作步驟所期待的正確的行記錄數.
自動統計資訊收集job
在一個預定義的維護視窗中oracle會自動對哪些丟失統計資訊或者統計資訊失效的所有物件收集統計資訊(每個工作日的晚上10點到零晨2點和每個週末的6點到零晨2點).可以使用企業管理器或使用dbms_scheduler和dbms_auto_task_admin包來改變這個維護視窗.
如果已經有一個完善的統計資訊收集過程或者如果因為某些原因想要禁用自動統計資訊收集可以禁用收集任務:
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 過程已成功完成。
手動統計資訊收集
如果計劃手動維護最佳化器統計資訊將需要判斷何時進行收集.基於失效統計,自動收集job或者系統中載入新資料的時間你能判斷何時來收集統計資訊.如果基本資料沒有發生明顯的改變不建議不斷的重新收集統計資訊這樣只會浪費系統資源.如果資料在一個預定義的ETL或ELT job只載入到系統中那麼統計資訊收集操作應該作為這個過程的一部分被排程.注意如果使用分割槽交換載入並希望利用增量統計資訊將需要在交換過程完成後收集統計資訊.然而如果系統中有大量的聯機事務只插入少量的資料但是這些操作每天都會發生,你將需要判斷何時你的統計資訊將會失效然後觸發統計資訊收集job.如果你計劃依賴user_tab_statistics中的stale_stats列來判斷統計資訊是否失效你應該能意識到這些資訊每天及時更新.如果需要更多更及時的資訊比如你的表什麼時候執行過DML操作你將需要檢視user_tab_modifications檢視,它會顯示每一個表上執行的insert,update,delete操作,表是否執行過truncated並計算自己是否已經失效.需要注意這些資訊是否定時的從記憶體中自動更新.如果需要最新的資訊需要使用dbms_stats.flush_database_monitoring_info函式來手動重新整理.
阻止超出範圍的條件
不管你是使用自動統計資訊收集job還是手動收集統計資訊,如果終端使用者在統計資訊收集之前開始查詢新插入的資料,即使只有不到10%的資料發生了變化也可能由於失效的統計資訊得到一個次優的執行計劃.發生這種問題最常見的原因是where子句中謂詞提供的值超出了最小/最大列統計資訊所能表示的範圍.這通常稱為超出範圍的錯誤.這種情況在分割槽表中很常見.一個新分割槽剛新增到一個存在的範圍分割槽表中且記錄剛被插入到分割槽中.在對這個新分割槽收集統計資訊之前終端使用者就開始查詢這些新的資料.對於分割槽表,可以使用dbms_stats.copy_table_stats過程(從oracle10.2.0.4開始可以使用)來阻止超出範圍的條件表示式.這個過程將複製原分割槽資料的統計資訊為新建立分割槽的統計資訊.它能複製依賴物件的統計資訊:列,本地(分割槽)索引等等.直到對分割槽收集統計資訊之前複製的統計資訊只能作為臨時的解決方法來使用.複製的統計資訊不能代替真實收集的統計資訊.
注意通常dbms_stats.copy_table_stats只能調整分割槽統計資訊不能調整全域性或表級別的統計資訊.如果想在複製統計資訊時對分割槽列進行全域性級別的更新需要將dbms_stats.copy_table_stats中的flags引數設定為8.
對於非分割槽表你能透過dbms_stats.set_column_stats過程來手動設定列的最大值.通常這種方法不建議它並不能代替真實的收集的統計資訊.
提高收集統計資訊的效率
隨著資料量的增長和維護視窗的縮短能及時的收集統計資訊是很重要的.oracle提供了多種方法來提高統計資訊收集的速度.使用並行用於收集統計資訊的幾種並行方法
內部物件並行
外部物件並行
內部物件並行與外部物件並行的組合
內部物件並行
內部物件並行是由dbms_stats.gather_*_stats過程的degree引數來控制的.degree引數控制著用於收集統計資訊的並行伺服器程式的數量.通常oracle使用資料字典表中並行屬性的值作為指定並行伺服器程式的引數值.在oracle資料庫中所有的表都有一個degree屬性預設值為1.對要收集統計資訊的大表設顯示地設定這個引數能提高統計資訊收集的速度.你也可以設定degree為auto_degree.oracle將基於一個物件的大小自動判斷一個合適的並行服務程式個數來收集統計資訊.這個值的範圍在1-小物件(序列操作)到大物件的default_degree((PARALLEL_THREADS_PER_CPU X CPU_COUNT)之間.
你將會注意到對一個分割槽表設定degree這意味著對每一個分割槽使用多個並行伺服器程式來收集統計資訊但是不能同時對不同的分割槽收集統計資訊.統計資訊只能在一個分割槽收集完之後才能收集下一個分割槽.
外部物件並行
在oracle11.2.0.2中,外部物件並行被引入且由global statistics gathering preference concurrent來控制.當concurrent設定為true時,oracle將使用oracle job作業排程和高階佇列組來建立和管理多個統計資訊收集job併發執行.透過oracle來完全利用多個cpu來對多個表和(子)分割槽併發的收集統計資訊來減小整個統計資訊收集的時間.
活動並行統計資訊收集job的最大個數是由job_queue_processes引數來控制的.job_queue_processes預設值設定為1000.這通常對於並行統計資訊收集操作來說太高了尤其是在並行執行也在使用時更是如此.一個最有效的值應該是總cpu核數的2倍(在rac中這是每一個節點的引數值).你需要確在系統級別設定這個引數(alter system命令或init.ora檔案)而不是在會話級別(alter session).
內部和外部並行的組合
在一個並行統計收集操作中的每一個統計資訊收集job都能以並行的方式來執行.將並行統計收集和並行執行組合起來能大大減小收集統計資訊的時間.
當使用並行執行作為一個並行統計資訊收集操作的一部分時你應該禁用parallel_adaptive_multi_user初始化引數來阻止並行job被降級為序列操作.它應該在系統級別來禁用而不是在會話級別禁用這個引數:
sys@JINGYONG> alter system set parallel_adaptive_multi_user=false;
系統已更改。
增量統計資訊
分割槽表的統計資訊收集是由表級別(global statistics)和(子)分割槽級別的統計資訊收集操作組成的.如果一個分割槽表的incremental preference設定為true,dbms_stats.gather_*_stats中引數granularity的值包含global和estimate_percent設定為auto_sample_size,oracle將會透過掃描這些已經被新增或被修改的分割槽來獲得全域性級別的統計資訊而不是整個表的.
增量全域性統計資訊是由表中每個分割槽儲存的概要計算出來的.一個概要是這個分割槽和分割槽中列的統計資訊的後設資料.聚合分割槽級的統計資訊和每個分割槽的概要資訊將能精確的生成全域性級別的統計資訊因此消除了需要掃描整個表的操作.當一個新的分割槽新增到表中,你僅僅需要對這個新的分割槽進行統計資訊收集而已.表級別的統計資訊將會使用新分割槽的概要資訊和已經存的分割槽的概要資訊來自動和精確的計算出來.
注意當增量統計資訊被啟用時分割槽統計資訊不從子分割槽統計資訊中進行聚合操作.
何時不收集統計資訊
儘管oracle需要精確的統計資訊來選擇一個最優的執行計劃,有些情況下收集統計資訊是很困難的,很昂貴的或者是不能及時完成的所以要有一和睦替代的策略.
volatile表
一個volatile表是隨著時間的變化資料量會發生很大改變的表.例如,一個訂單佇列表,這個表在一天開始的時候是空的,隨著時間的推移訂單將會填滿這個表.當被處理的一個訂單從表中刪除時所以這一天結束時這個表會被再次清空.如果你依賴自動統計資訊收集job來維護象這樣的表的統計資訊那麼這些表顯示的統計資訊總是空的因為收集job是在晚上.然而在當天工作期間這個表可能有成千上萬行記錄.對於這樣的表最好是在白天收集一組有代表性的統計資訊並鎖定這些資訊.鎖定這些統計資訊將阻止自動統計資訊收集job來覆蓋它們.最佳化器在最佳化sql語句之前在編譯sql語句時會使用動態抽樣對錶收集基本的統計資訊.儘管透過動態抽樣產生的統計資訊質量不高或者不象使用dbms_stats包收集的統計資訊那樣完整但在大多數情況下已經夠用了.
全域性臨時表
全域性臨時表在應用程式上下文中經常用來儲存中間結果.一個全域性臨時表對於有合理許可權的所有使用者共享它的定義,但是資料只在各自的會話中可見.直到資料被插入表中之前是不分配物理儲存的.一個全域性臨時表可能是transaction specific(提交時刪除行(或session-specific(提交時儲存行).對一個transaction specific的表收集統計資訊將導致對這個表進行truncate操作.相反,可以對全域性臨時表收集統計資訊.然而統計資訊的收集將僅僅基於session-private資料內容進行收集但是這些統計資訊將能被訪問這個表的所有會話使用.
如果有一個全域性臨時表持續有行資料且每一個會話將有相同的資料量和相同的數值那麼應該在一個會話中收集一組有代表性的統計資訊並鎖定它們防止其它會話將其覆蓋.注意自動統計資訊收集job是不會收集全域性臨時表的統計資訊.
中間工作表
中間工作表是典型的一個ELT過程或者一個複雜事務的一部分.這些表只會寫一次,讀一次然後truncate或者delete.在這種情況下收集統計資訊成本超過了它所帶來的好處,因為統計資訊只能用一次.相反動態抽樣在這種情況下更有用.建議鎖定中間工作表的統計資訊來持久的阻止自動統計資訊收集job來對它們收集統計資訊.
收集其它型別的統計資訊
因為現在只支援基於成本的最佳化器,資料庫中所有的表需要有統計資訊,包含所有的資料字典表(sys,system使用者所擁有的表和內建在system和sysaux表空間中的表)和透過動態v$效能檢視使用的x$表.
資料字典統計資訊
資料字典表的統計資訊是由自動統計資訊收集job在晚維護視窗進行收集的.強烈建議你允許oracle自動統計資訊收集job來維護資料字典統計資訊即使在你關閉對主應用程式方案關閉自動統計資訊收集的情況下.可以使用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 過程已成功完成。
固定物件統計資訊
自動統計資訊收集job不會收集固定物件的統計統計資訊.當最佳化統計資訊丟失時不象其它的資料庫表對於sql語句中呼叫X$表是不能自動使用動態抽樣的.如果它們的統計資訊丟失最佳化器會使用預先定義的預設統計資訊.這些預設的統計資訊可能沒有代表性且可能導致選擇次優的執行計劃,在系統中可能會導致嚴重的效能問題.如果是這個原因造成效能問題強烈建議你手動收集固定物件的統計資訊.可以使用dbms_stats.gather_fixed_objects_stats過程來收集固定物件的統計資訊.因為在系統如果存在一個有代表性的工作負載收集x$這些固定物件的統計資訊是很重要的.在大型系統中由於收集固定物件統計資訊需要額外的資源所以對固定物件收集統計資訊不總是可行.如果不能在負載高峰期間收集固定物件的統計資訊那麼應該在系統負載降低之後對三種關鍵型別的固定物件表收集統計資訊:
structural data--比如controlfile contents
Session based data - 比如 v$session, v$access
Workload data -比如 v$sql, v$sql_plan
建議當主資料庫或應用程式升級後,實現新的模組或者改變資料庫的配置後重新收集固定物件統計資訊.例如,如果增加SGA的大小包含緩衝區快取和共享池資訊的x$表會顯著的發生改變,比如v$buffer_pool或v$shared_pool_advice檢視使用的x$表.系統統計資訊系統統計資訊能讓最佳化器透過使用執行這個語句相關的實際系統硬體資訊,比如,cpu速度和IO效能,來在執行計劃中對每一個步驟獲得更精確的成本值.系統統計資訊預設情況下是啟用的,它使用預設值自動初始化,這些值對於大多數系統來說是有代表性的.
小結
為了讓最佳化器準確的判斷執行計劃中的成本它必須有這個語句所訪問的所物件的精確的統計資訊和執行sql語句所在系統的系統統計資訊.這裡介紹了為什麼必須要有統計資訊,怎樣使用統計資訊和各種收集統計資訊的方法.
透過使用自動統計資訊收集job和其它收集技術相結合能讓DBA維護一組精確的統計資訊來確保最佳化器總是有必要的資訊來選擇最優的執行計劃.一旦一個統計資訊收集策略已經在使用時,要改變這個策略應該以一種受控的方式來利用一些關鍵特性比如pending統計資訊來確保不會對程式效能造成影響.
oracle最佳化器對一個sql語句檢測所有可能的執行計劃並選擇一個成本值最小的,這裡的成本代表了一個特定執行計劃的資源使用情況.為了讓最佳化器能準確的判斷一個執行計劃的成本它必須要關於sql語句要訪問的所有物件(表或索引)的資訊同時還要有執行sql語句的系統資訊.
這些必要的資訊通常稱為最佳化器統計資訊.理解和管理最佳化器統計資訊是最佳化sql執行的關鍵.知道何時以及如何收集統計資訊對於維護可以接受的效能來說至關重要.
這裡將介紹在常見的oracle資料庫場景中何時以及如何來收集統計資訊.它包含以下內容:
怎樣收集統計資訊
何時收集統計資訊
提高收集統計資訊的效率
何時不收集統計資訊
收集處理的統計資訊
怎樣收集統計資訊
在oracle中收集統計資訊的首選方法是使用提供了自動統計資訊收集job.
自動統計資訊收集job
對執行oracle autotask任務的一個預定義維護視窗期間對哪些丟失統計資訊或統計資訊過期的所有資料庫物件收集統計資訊,oracle內部很重視資料庫物件的統計資訊因此這此物件在進行處理前需要更新統計資訊.
自動統計資訊收集job是使用dbms_stats.gather_database_stats_job_proc過程來實現的,它與dbms_stats.gather_*_stats過程使用相同的預設引數.這個預設值在大多數情況下是有效的.然而偶爾也需要改變這些統計資訊收集引數的預設值,可以透過dbms_stats.set_*_pref過程來進行修改.例如設定一個表中有5%的資料發生了改變而不是預設值10%時就認會它的統計資訊失效了.如果想要改變這個統計資訊失效的閾值,可以使用dbms_stats.set_table_prefs過程來修改stale_percent引數.
sys@JINGYONG> begin
2 dbms_stats.set_table_prefs('SH','SALES','STALE_PERCENT','5');
3 end;
4 /
PL/SQL 過程已成功完成。
使用dbms_stats.set_table_prefs過程將表統計資訊失效的閾stale_percent改變了5%.
手動統計資訊收集
如果已經有一個完善的統計資訊收集過程或者因為某些原因想要對特定使用者方案禁用自動統計資訊收集而只保留收集資料字典的統計資訊.可以使用dbms_stats.set_global_prefs過程來改變autostats_target引數為oracle來替代auto.
sys@JINGYONG> begin
2 dbms_stats.set_global_prefs('AUTOSTATS_TARGET','ORACLE');
3 end;
4 /
PL/SQL 過程已成功完成。
用上面的程式碼改變自動統計資訊收集job只自動收集資料字典統計資訊.
為了手動收集統計資訊你應該使用dbms_stats包,用它來替找過時的analyze命令.dbms_stats包提供多個dbms_stats.gather_*_stats過程來收集使用者方案物件,資料字典和固定物件的統計資訊.理想情況下應該讓這些過程除了使用者方案名和物件名引數之外其它的引數值都使用預設值.預設引數值和自適應引數設定在大多數情況下已經足夠了.
sys@JINGYONG> begin
2 dbms_stats.gather_table_stats('SH','SALES');
3 end;
4 /
PL/SQL 過程已成功完成。
兩個修改最頻繁的引數是ESTIMATE_PERCENT和METHOD_OPT
ESTIMATE_PERCENT
在收集統計資訊的過程最常見的問題是'使用什麼樣的抽樣大小'與這個問題相關的設定是dbms_stats.gather_*_stats過程中的ESTIMATE_PERCENT引數.這個ESTIMATE_PERCENT引數判斷用來計算統計資訊所使用的行數百份比.當表中的所有行被處理時收集的統計資訊是最準確的(比如100%抽樣).然而抽樣的樣本越大收集操作的時間越長.因此使用怎樣的抽樣大小來提供及時準確的統計資訊.
oracle11G之前的ESTIMATE_PERCENT
在oracle10g中,ESTIMATE_PERCENT的預設值從100%變成了AUTO_SAMPLE_SIZE.這個AUTO_SAMPLE_SIZE的目的是讓oracle在每次收集統計資訊時來判斷每一個表的合適的抽樣大小.這將允許oracle自動地對每一個表改變其抽樣大小但仍然能確保及時有效的收集統計資訊.這種方法對於大多數表來說是一種可取的方法但是對於資料存在傾斜的表來說存在問題.當表中資料出現傾斜
AUTO_SAMPLE_SIZE演算法通常選擇的抽樣大小太小,在這種情況下最好的方法還是手動指定ESTIMATE_PERCENT引數的大小.
oracle11g中的ESTIMATE_PERCENT
oracle11g中引入一種新的hash-based抽樣演算法來提供精確的統計資料解決了精確和速度兩個關鍵問題.它的精確度接近100%抽樣大小的水平但是成本與10%抽樣大小相當.這種新的演算法只有當任何dbms_stats.gather_*_stats過程中的ESTIMATE_PERCENT引數設定為AUTO_SAMPLE_SIZE時才會使用.
下面的表格顯示了一個早前使用1%抽樣,100%抽樣和AUTO_SAMPLE_SIZE抽樣收集統計資訊的結查.第一行比較執行的時間,後繼的行將顯示每次執行計算出來的L_ORDERKDY和L_COMMENT兩個列不同值的數量(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%抽樣執行的時間要快9倍且只比1%抽樣執行的時間慢2.4倍,而提供的統計資訊質量幾乎接近100%抽樣的水平(不足以改變執行計劃).
從oracle11g開始強烈建議你使用estimate_percent引數的預設值.如果你手動設定estimate_percent引數儘管你將它設定為100%它還是會使用舊的收集演算法.
method_opt
在dbms_stats.gather_*_stats過程中到目前為止最有爭議的引數就是method_opt.這個method_opt引數控制著在收集統計資訊過程是否建立直方圖.直方圖是一種特殊型別的列統計資訊提供關於表中列資料分佈的詳細資訊.所以這就是為什麼直方圖存在爭議的問題
直方圖的建立會增加統計收集的時間和系統資源的使用但最大的問題是直方圖與bind peeking功能的相互作用以及對near popular values基數評估的影響.
直方圖與bind peeking
bind peeking對直方圖的不利影響已經在oracle11g中透過引入自適應遊標共享被淡化了但是直到今天仍然感受到它的影響.為了說明自適應遊標共享是怎樣解決這個問題的先來檢查一個這個問題的原因.
oracle11g之前的直方圖與bind peeking
在oracle11g之前當最佳化一個在where子句中包含繫結變數的語句時最佳化在第一次執行這個語句時會窺視這些繫結變數的值(在硬解析階段).最佳化器然後會基於這個初始化的繫結變數值來判斷執行計劃.對於後續執行這個查詢不會執行繫結變數窺視(沒有硬解析發生),所以對於後面的這個語句的所有執行即使繫結變數發生了改變也會決定使用第一次設定的繫結變數值所產生的執行計劃對於在表示式中使用繫結變數的列存在直方圖這將有助於判斷初始化繫結變數值的最優執行計劃.因此對於相同的語句依賴於初始化硬解析時繫結變數的值執行計劃可能會有所不同.
有兩種方法可以避免這個問題:刪除直方圖並在將業停止收集直方圖或禁用bind peeking繫結變數窺視.根據所有的sql是否都在使用繫結變數你可以判斷哪一種方法更適合你的資料庫.
禁止直方圖的建立
如果你的環境中所有sql語句使用了繫結變數那麼最好是刪除存在的直方圖並在將來的收集統計資訊過程中禁止建立直方圖.禁上直方圖的建立能確保依賴於繫結變數的值的執行計劃不會發生改變也會減少收集統計資訊的時間.沒有直方圖最佳化器會假設列中不相同的值是均勻分佈在所有行中的並當窺視sql語句中初始化繫結變數值時使用NDV(number distinct values)來判斷基數的評估.
可以使用dbms_stats.delete_table_stats過程來刪除統計資訊中存在的直方圖資訊.
sys@JINGYONG> begin
2 dbms_stats.delete_table_stats('SH','SALES');
3 end;
4 /
PL/SQL 過程已成功完成。
接下來可以透過使用dbms_stats.set_param過程來改變method_opt引數的預設值來阻止將來生成直方圖.這能確保dbms_stats.gather_*_stats過程和自動統計資訊收集job在將來都不會收集直方圖資訊.
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過程來對受影響的物件重新收集統計資訊.
注意在oracle11g中透過使用dbms_stats.delete_column_stats和對直方圖設定col_stat_type可以刪除不想要的直方圖而不用刪除所有的直方圖資訊.也可以對單個表或者使用dbms_stats.set_table_prefs過程來對列禁止直方圖的建立.
你知道直方圖也用於某些連線謂詞而且刪除直方圖對連線謂詞的基數評估會有影響.在這種情況下更安全的方法是禁用繫結變數窺視.
禁用繫結變數窺視
如果你的環境中有一些sql語句是使用繫結變數而有一些sql語句使用了literal values那麼你應該禁用繫結變數窺視.透過禁用繫結變數窺視它將阻止最佳化器窺初始繫結變數值且將不使用直方圖來進行基數評估.相反最佳化器將列中的不相同值是均勻分佈在行中並使用NDV(number distinct values)來進行基數評估操作.這將對於使用繫結變數的語句使用一致的執行計劃.但是如果sql語句使用literal values那麼仍然能利用直方圖來得到最優的執行計劃.可以透過設定隱含引數_optim_peek_user_binds為false來禁用繫結變數窺視.
oracle11g中的直方圖與繫結變數窺視
在oracle11g中最佳化器已經增強了允許多個版本的執行計劃用於使用繫結變數的單個sql語句.這個功能就叫作自適應遊標共享且依賴於對執行統計的監控來確保每一個繫結變數值使用正確的執行路徑.在第一次執行時最佳化器將窺視繫結變數值且基於繫結變數值的選擇性來判斷其執行計劃,與oracle11g之前的版本一樣.如果最佳化器認為最佳的執行計劃可能依賴於繫結變數的值(例如,列上的直方圖或者一個範圍謂詞,or,<,>)這個遊標將會被標記為bind sensitive.當一個遊標被標記為bind sensitive.oracle將監控遊標使用不同繫結值的行為來確定是否要使用一個不同的執行計劃.
如果一個不同的繫結變數值在後繼的執行中使用,最佳化器將使用相同的執行計劃因為oracle一開始會假設遊標能被共享.然而新的繫結變數的執行統計會被記錄並與之前繫結變數值的執行統計進行比較.如果oracle判斷新的繫結變數值造成了操作的資料量明顯不同那麼對於新的繫結變數值在下一次執行時會進行硬解析且這個遊標會被標記為bind-aware.每一個bind_aware遊標與繫結變數的選擇性範圍有關因此遊標只有在這個語句的繫結變數值在一個被認為可共享的範圍之內才能被共享.當另一個新的繫結變數值被使用時,最佳化器將會基於繫結變數值的選擇性的相似度來找到一個它認為最好的一個遊標.如果它不能找到一個遊標,它將建立一個新的.如果執行計劃的一個新的遊標與一個已經存在的遊標一樣,那麼兩個遊標將會在共享池中合併從而節省空間.遊標的選擇性範圍為了包含新繫結變數值的選擇性將會有所增加.
透過允許對單個sql語句存在多個執行計劃,在oracle11g中直方圖對於使用繫結變數的語句不再有負面影響.
直方圖和near popular values
當最佳化器遇到一個where子句中謂詞列上有直方圖,它將基於literal value的出現頻率來進行基數評估.例如假設在sh使用者下的customers表中的cust_city_di列上有一個高度平衡的直方圖且有一個使用cust_city_id=51806的查詢.最佳化器首先會檢查這個直方圖有51806作為它的end point有多少個桶.在這種情況下,endpint是51806的桶有136,137,138和139(可以檢視user_histograms).因為endpoint的值有兩個或多個桶要被考慮為出現頻繁的最佳化器將使用下面的公式來進行基數評估:
(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;
執行計劃
----------------------------------------------------------
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,它對於任何桶來說都不是一個endpoint那麼最佳化器會使用一個同的公式來進行基數評估.對於endpoint值只在一個桶出現或者任何桶中都沒有這個endpoint時最佳化器會使用下面的計算公式:
density * number of rows in the table,
density的值可以在user_tab_col_statistics中看到,它的值從oracle10.2.0.4以後最佳化器將不再使用.記錄這個值是為了向後相容,在oracle9i和oracle10g前期的版本中會使用這個值.此外如果引數optimizer_features_enable設定的版本小於10.2.0.4,那麼檢視中的density仍然會被使用.
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;
執行計劃
----------------------------------------------------------
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)
現在將optimizer_features_enable設定為10.2.0.3
sys@JINGYONG> alter session set optimizer_features_enable='10.2.0.3';
會話已更改。
sys@JINGYONG> set autotrace traceonly
sys@JINGYONG> select count(*) from sh.customers where cust_city_id=52500;
執行計劃
----------------------------------------------------------
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)
現在的基數是121=55500*.002179391,CUST_CITY_ID的density為.002179391
這些nearly popular值被歸類為non-popular values使用與non-popular values相同的計算公式.例如,如果謂詞是cust_city_id=52114,那麼它的評估基數將是66行.與non-popular值52500的基數一樣,但是cust_city_id=52114實際上有227行記錄.
sys@JINGYONG> select count(*) from sh.customers where cust_city_id=52114;
執行計劃
----------------------------------------------------------
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
唯一能讓最佳化器意識到這些near popular values的方法是使用動態抽樣.動態抽樣在最佳化一個sql語句時會收集額外的
statement-specific物件統計資訊.在這個例子中,動態抽樣提示加入到了查詢中且最佳化器會得到一個更準確的基數評估值.
sys@JINGYONG> select /*+ dynamic_sampling(a 2) */ count(a.cust_id) from sh.custo
mers a where a.cust_city_id=52114;
執行計劃
----------------------------------------------------------
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)
使用動態抽樣可以提高高度平衡直方圖中non-popular value的基數評估
在上面已經討論了在oracle10g中使用直方圖可能存的問題和可能的解決方法.建議從oracle11g開始收集統計資訊時使用引數METHOD_OPT的預設值且利用自適應遊標.如果想手動設定method_opt引數值不使用預設值要確保只對需要直方圖的列進行設定.將method_opt設定為for all columns size 254將會使oracle對每一個列都收集直方圖資訊.這對於收集統計資訊來說是不必要的會增加執行時間和浪費系統資源,也會增加儲存這些統計資訊的空間.還要避免將method_opt設定為for all index columns size 254它使oracle對存過索引的每一個列收集直方圖資訊,也會浪費系統資源.這個設定還有一個副作用就是會阻止oracle對哪些不存在索引的列收集基本的列統計資訊.
pending statistics
當決定改變dbms_stats_gather_*_stats過程的引數預設值時強烈建議在生產系統中修改之前先驗證這些改變.如果沒有一個完整的測試環境應該使用pending statistics.使用pending statistics代替常用的資料字典表,儲存在pending表中的統計資訊在它們被髮和被系統使用之前可以以一種受控的方式來啟用和測試.為了啟用pending統計資訊的收集需要對希望建立pending統計資訊的物件使用dbms_stats.set_*_prefs過程將引數publish從預設值true改變false.下面的例子中對sh使用者下的sales表啟用pending統計資訊並對sales表收集統計資訊.
sys@JINGYONG> begin
2 dbms_stats.set_table_prefs('SH','SALES','PUBLISH','FALSE');
3 end;
4 /
PL/SQL 過程已成功完成。
透過將publish設定為false來啟用pending統計資訊
正常的收集物件統計資訊
sys@JINGYONG> begin
2 dbms_stats.gather_table_stats('SH','SALES');
3 end;
4 /
PL/SQL 過程已成功完成。
對於這些物件收集的統計資訊可以查詢*_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
可以透過一個alter session命令來設定初始化引數optimizer_use_pending_stats為true來使用pending統計資訊.在啟用pending統計資訊之後任何在該會話執行的sql將使用這些新的沒有釋出的統計資訊.對於在工作負載下的所訪問的表沒有pending統計資訊時最佳化器將使用標準資料字典表中的當前統計資訊.當你驗證這些pending統計資訊後可以使用
dbms_stats.publish_pending_stats過程來發布.
何時收集統計資訊
為了選擇一個最佳的執行計劃最佳化器必須要有有代表性的統計資訊,有代表性的統計資訊並不是最新的統計資訊但是這組統計資訊能幫助最佳化器判斷在執行計劃中每一個操作步驟所期待的正確的行記錄數.
自動統計資訊收集job
在一個預定義的維護視窗中oracle會自動對哪些丟失統計資訊或者統計資訊失效的所有物件收集統計資訊(每個工作日的晚上10點到零晨2點和每個週末的6點到零晨2點).可以使用企業管理器或使用dbms_scheduler和dbms_auto_task_admin包來改變這個維護視窗.
如果已經有一個完善的統計資訊收集過程或者如果因為某些原因想要禁用自動統計資訊收集可以禁用收集任務:
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 過程已成功完成。
手動統計資訊收集
如果計劃手動維護最佳化器統計資訊將需要判斷何時進行收集.基於失效統計,自動收集job或者系統中載入新資料的時間你能判斷何時來收集統計資訊.如果基本資料沒有發生明顯的改變不建議不斷的重新收集統計資訊這樣只會浪費系統資源.如果資料在一個預定義的ETL或ELT job只載入到系統中那麼統計資訊收集操作應該作為這個過程的一部分被排程.注意如果使用分割槽交換載入並希望利用增量統計資訊將需要在交換過程完成後收集統計資訊.然而如果系統中有大量的聯機事務只插入少量的資料但是這些操作每天都會發生,你將需要判斷何時你的統計資訊將會失效然後觸發統計資訊收集job.如果你計劃依賴user_tab_statistics中的stale_stats列來判斷統計資訊是否失效你應該能意識到這些資訊每天及時更新.如果需要更多更及時的資訊比如你的表什麼時候執行過DML操作你將需要檢視user_tab_modifications檢視,它會顯示每一個表上執行的insert,update,delete操作,表是否執行過truncated並計算自己是否已經失效.需要注意這些資訊是否定時的從記憶體中自動更新.如果需要最新的資訊需要使用dbms_stats.flush_database_monitoring_info函式來手動重新整理.
阻止超出範圍的條件
不管你是使用自動統計資訊收集job還是手動收集統計資訊,如果終端使用者在統計資訊收集之前開始查詢新插入的資料,即使只有不到10%的資料發生了變化也可能由於失效的統計資訊得到一個次優的執行計劃.發生這種問題最常見的原因是where子句中謂詞提供的值超出了最小/最大列統計資訊所能表示的範圍.這通常稱為超出範圍的錯誤.這種情況在分割槽表中很常見.一個新分割槽剛新增到一個存在的範圍分割槽表中且記錄剛被插入到分割槽中.在對這個新分割槽收集統計資訊之前終端使用者就開始查詢這些新的資料.對於分割槽表,可以使用dbms_stats.copy_table_stats過程(從oracle10.2.0.4開始可以使用)來阻止超出範圍的條件表示式.這個過程將複製原分割槽資料的統計資訊為新建立分割槽的統計資訊.它能複製依賴物件的統計資訊:列,本地(分割槽)索引等等.直到對分割槽收集統計資訊之前複製的統計資訊只能作為臨時的解決方法來使用.複製的統計資訊不能代替真實收集的統計資訊.
注意通常dbms_stats.copy_table_stats只能調整分割槽統計資訊不能調整全域性或表級別的統計資訊.如果想在複製統計資訊時對分割槽列進行全域性級別的更新需要將dbms_stats.copy_table_stats中的flags引數設定為8.
對於非分割槽表你能透過dbms_stats.set_column_stats過程來手動設定列的最大值.通常這種方法不建議它並不能代替真實的收集的統計資訊.
提高收集統計資訊的效率
隨著資料量的增長和維護視窗的縮短能及時的收集統計資訊是很重要的.oracle提供了多種方法來提高統計資訊收集的速度.使用並行用於收集統計資訊的幾種並行方法
內部物件並行
外部物件並行
內部物件並行與外部物件並行的組合
內部物件並行
內部物件並行是由dbms_stats.gather_*_stats過程的degree引數來控制的.degree引數控制著用於收集統計資訊的並行伺服器程式的數量.通常oracle使用資料字典表中並行屬性的值作為指定並行伺服器程式的引數值.在oracle資料庫中所有的表都有一個degree屬性預設值為1.對要收集統計資訊的大表設顯示地設定這個引數能提高統計資訊收集的速度.你也可以設定degree為auto_degree.oracle將基於一個物件的大小自動判斷一個合適的並行服務程式個數來收集統計資訊.這個值的範圍在1-小物件(序列操作)到大物件的default_degree((PARALLEL_THREADS_PER_CPU X CPU_COUNT)之間.
你將會注意到對一個分割槽表設定degree這意味著對每一個分割槽使用多個並行伺服器程式來收集統計資訊但是不能同時對不同的分割槽收集統計資訊.統計資訊只能在一個分割槽收集完之後才能收集下一個分割槽.
外部物件並行
在oracle11.2.0.2中,外部物件並行被引入且由global statistics gathering preference concurrent來控制.當concurrent設定為true時,oracle將使用oracle job作業排程和高階佇列組來建立和管理多個統計資訊收集job併發執行.透過oracle來完全利用多個cpu來對多個表和(子)分割槽併發的收集統計資訊來減小整個統計資訊收集的時間.
活動並行統計資訊收集job的最大個數是由job_queue_processes引數來控制的.job_queue_processes預設值設定為1000.這通常對於並行統計資訊收集操作來說太高了尤其是在並行執行也在使用時更是如此.一個最有效的值應該是總cpu核數的2倍(在rac中這是每一個節點的引數值).你需要確在系統級別設定這個引數(alter system命令或init.ora檔案)而不是在會話級別(alter session).
內部和外部並行的組合
在一個並行統計收集操作中的每一個統計資訊收集job都能以並行的方式來執行.將並行統計收集和並行執行組合起來能大大減小收集統計資訊的時間.
當使用並行執行作為一個並行統計資訊收集操作的一部分時你應該禁用parallel_adaptive_multi_user初始化引數來阻止並行job被降級為序列操作.它應該在系統級別來禁用而不是在會話級別禁用這個引數:
sys@JINGYONG> alter system set parallel_adaptive_multi_user=false;
系統已更改。
增量統計資訊
分割槽表的統計資訊收集是由表級別(global statistics)和(子)分割槽級別的統計資訊收集操作組成的.如果一個分割槽表的incremental preference設定為true,dbms_stats.gather_*_stats中引數granularity的值包含global和estimate_percent設定為auto_sample_size,oracle將會透過掃描這些已經被新增或被修改的分割槽來獲得全域性級別的統計資訊而不是整個表的.
增量全域性統計資訊是由表中每個分割槽儲存的概要計算出來的.一個概要是這個分割槽和分割槽中列的統計資訊的後設資料.聚合分割槽級的統計資訊和每個分割槽的概要資訊將能精確的生成全域性級別的統計資訊因此消除了需要掃描整個表的操作.當一個新的分割槽新增到表中,你僅僅需要對這個新的分割槽進行統計資訊收集而已.表級別的統計資訊將會使用新分割槽的概要資訊和已經存的分割槽的概要資訊來自動和精確的計算出來.
注意當增量統計資訊被啟用時分割槽統計資訊不從子分割槽統計資訊中進行聚合操作.
何時不收集統計資訊
儘管oracle需要精確的統計資訊來選擇一個最優的執行計劃,有些情況下收集統計資訊是很困難的,很昂貴的或者是不能及時完成的所以要有一和睦替代的策略.
volatile表
一個volatile表是隨著時間的變化資料量會發生很大改變的表.例如,一個訂單佇列表,這個表在一天開始的時候是空的,隨著時間的推移訂單將會填滿這個表.當被處理的一個訂單從表中刪除時所以這一天結束時這個表會被再次清空.如果你依賴自動統計資訊收集job來維護象這樣的表的統計資訊那麼這些表顯示的統計資訊總是空的因為收集job是在晚上.然而在當天工作期間這個表可能有成千上萬行記錄.對於這樣的表最好是在白天收集一組有代表性的統計資訊並鎖定這些資訊.鎖定這些統計資訊將阻止自動統計資訊收集job來覆蓋它們.最佳化器在最佳化sql語句之前在編譯sql語句時會使用動態抽樣對錶收集基本的統計資訊.儘管透過動態抽樣產生的統計資訊質量不高或者不象使用dbms_stats包收集的統計資訊那樣完整但在大多數情況下已經夠用了.
全域性臨時表
全域性臨時表在應用程式上下文中經常用來儲存中間結果.一個全域性臨時表對於有合理許可權的所有使用者共享它的定義,但是資料只在各自的會話中可見.直到資料被插入表中之前是不分配物理儲存的.一個全域性臨時表可能是transaction specific(提交時刪除行(或session-specific(提交時儲存行).對一個transaction specific的表收集統計資訊將導致對這個表進行truncate操作.相反,可以對全域性臨時表收集統計資訊.然而統計資訊的收集將僅僅基於session-private資料內容進行收集但是這些統計資訊將能被訪問這個表的所有會話使用.
如果有一個全域性臨時表持續有行資料且每一個會話將有相同的資料量和相同的數值那麼應該在一個會話中收集一組有代表性的統計資訊並鎖定它們防止其它會話將其覆蓋.注意自動統計資訊收集job是不會收集全域性臨時表的統計資訊.
中間工作表
中間工作表是典型的一個ELT過程或者一個複雜事務的一部分.這些表只會寫一次,讀一次然後truncate或者delete.在這種情況下收集統計資訊成本超過了它所帶來的好處,因為統計資訊只能用一次.相反動態抽樣在這種情況下更有用.建議鎖定中間工作表的統計資訊來持久的阻止自動統計資訊收集job來對它們收集統計資訊.
收集其它型別的統計資訊
因為現在只支援基於成本的最佳化器,資料庫中所有的表需要有統計資訊,包含所有的資料字典表(sys,system使用者所擁有的表和內建在system和sysaux表空間中的表)和透過動態v$效能檢視使用的x$表.
資料字典統計資訊
資料字典表的統計資訊是由自動統計資訊收集job在晚維護視窗進行收集的.強烈建議你允許oracle自動統計資訊收集job來維護資料字典統計資訊即使在你關閉對主應用程式方案關閉自動統計資訊收集的情況下.可以使用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 過程已成功完成。
固定物件統計資訊
自動統計資訊收集job不會收集固定物件的統計統計資訊.當最佳化統計資訊丟失時不象其它的資料庫表對於sql語句中呼叫X$表是不能自動使用動態抽樣的.如果它們的統計資訊丟失最佳化器會使用預先定義的預設統計資訊.這些預設的統計資訊可能沒有代表性且可能導致選擇次優的執行計劃,在系統中可能會導致嚴重的效能問題.如果是這個原因造成效能問題強烈建議你手動收集固定物件的統計資訊.可以使用dbms_stats.gather_fixed_objects_stats過程來收集固定物件的統計資訊.因為在系統如果存在一個有代表性的工作負載收集x$這些固定物件的統計資訊是很重要的.在大型系統中由於收集固定物件統計資訊需要額外的資源所以對固定物件收集統計資訊不總是可行.如果不能在負載高峰期間收集固定物件的統計資訊那麼應該在系統負載降低之後對三種關鍵型別的固定物件表收集統計資訊:
structural data--比如controlfile contents
Session based data - 比如 v$session, v$access
Workload data -比如 v$sql, v$sql_plan
建議當主資料庫或應用程式升級後,實現新的模組或者改變資料庫的配置後重新收集固定物件統計資訊.例如,如果增加SGA的大小包含緩衝區快取和共享池資訊的x$表會顯著的發生改變,比如v$buffer_pool或v$shared_pool_advice檢視使用的x$表.系統統計資訊系統統計資訊能讓最佳化器透過使用執行這個語句相關的實際系統硬體資訊,比如,cpu速度和IO效能,來在執行計劃中對每一個步驟獲得更精確的成本值.系統統計資訊預設情況下是啟用的,它使用預設值自動初始化,這些值對於大多數系統來說是有代表性的.
小結
為了讓最佳化器準確的判斷執行計劃中的成本它必須有這個語句所訪問的所物件的精確的統計資訊和執行sql語句所在系統的系統統計資訊.這裡介紹了為什麼必須要有統計資訊,怎樣使用統計資訊和各種收集統計資訊的方法.
透過使用自動統計資訊收集job和其它收集技術相結合能讓DBA維護一組精確的統計資訊來確保最佳化器總是有必要的資訊來選擇最優的執行計劃.一旦一個統計資訊收集策略已經在使用時,要改變這個策略應該以一種受控的方式來利用一些關鍵特性比如pending統計資訊來確保不會對程式效能造成影響.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2127849/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 微課sql最佳化(3)、統計資訊收集(2)-如何收集統計資訊SQL
- 最佳實踐：解讀GaussDB(DWS) 統計資訊自動收集方案
- 微課sql最佳化(8)、統計資訊收集(6)-統計資訊查詢SQL
- 資料統計查詢最佳化
- 前端效能最佳化實踐方向與方法前端
- 微課sql最佳化(2)-為什麼需要收集統計資訊SQL
- 網易互娛資料成本最佳化治理實踐
- Android 中的升級資料庫最佳方法實踐Android資料庫
- 資料庫設計的十個最佳實踐資料庫
- 微課sql最佳化(7)、統計資訊收集(5)-關於直方圖SQL直方圖
- 最佳化您的部署：Docker 映象最佳實踐Docker
- 8個雲成本最佳化的最佳實踐
- TypeScript 資料模型層程式設計的最佳實踐TypeScript模型程式設計
- 從 ClickHouse 到 ByteHouse：實時資料分析場景下的最佳化實踐
- 異常值檢測！最佳統計方法實踐（程式碼實現）！⛵
- Golang效能最佳化實踐Golang
- 攜程酒店基於血緣後設資料的資料流程最佳化實踐
- 微課sql最佳化(6)、統計資訊收集(4)-關於動態取樣SQL
- 京東雲TiDB SQL最佳化的最佳實踐TiDBSQL
- C#開源、功能強大、免費的Windows系統最佳化工具 - OptimizerC#Windows
- 微課sql最佳化(5)、統計資訊收集(3)-關於預設取樣率SQL
- 收集 Kubernetes 資源統計資料的新工具
- 頁面CLS 最佳化實踐
- 6 收集資料庫統計資訊資料庫
- 千萬級資料深分頁查詢SQL效能最佳化實踐SQL
- Apache Kafka最佳化部署的十大最佳實踐ApacheKafka
- 資料治理：管理資料資產的最佳實踐框架框架
- 研發團隊資源成本最佳化實踐
- 乾貨 | 攜程酒店基於血緣後設資料的資料流程最佳化實踐
- 資料安全治理及審計合規的最佳實踐XX
- 資料庫設計中的6個最佳實踐步驟資料庫
- Mysql事務原理與最佳化最佳實踐MySql
- MySQL 資料庫最佳化的具體方法說明MySql資料庫
- Github Statistics 一個基於 React 的 GitHub 資料統計工具GithubReact
- IBM SPSS Statistics 26 for Mac(spss資料統計分析工具)IBMSPSSMac
- 資料庫系統概述之資料庫最佳化資料庫
- 數字水印在資料安全保護中的應用實踐和最佳化
- 微服務的【資料庫管理】最佳實踐微服務資料庫
- 記某百億級mongodb叢集資料過期效能最佳化實踐MongoDB