Oracle如何收集優化統計數據

這篇文章主要介紹了Oracle如何收集優化統計數據，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

介紹
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性能,來在執行計劃中對每一個步驟獲得更精確的成本值.系統統計信息缺省情況下是啟用的,它使用缺省值自動初始化,這些值對于大多數系統來說是有代表性的.

感謝你能夠認真閱讀完這篇文章，希望小編分享的“Oracle如何收集優化統計數據”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!