程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> Optimizer統計信息管理介紹

Optimizer統計信息管理介紹

編輯：DB2教程

Optimizer統計信息管理介紹

1. 前言

在我們的日常維護中受理一些一直以來運行得很好的系統，突然有一天用戶反饋沒有做任何操作，系統的某個功能模塊或者是某個報表以前只需要幾秒，但現在需要幾分鐘或更長的時間都沒有返回結果。在這樣的情況下，我們通常會分析SQL語句，會發現這個SQL的執行計劃已經發生改變，在硬件環境未發生變化的情況下，執行計劃發生變化多數原因是由於表的統計信息發生了變化，原本使用的某個索引突然間沒有被使用，或者使用了較差的索引，這都是由於統計信息不准備確引起的，因此我們有必要了解統計信息的管理和維護，更好的優化SQL和性能問題處理。

2. 什麼是統計信息

統計信息是描述該數據庫中的數據和數據庫中的對象的集合，這些統計數據所使用的優化選擇對每個SQL語句的最佳執行計劃。統計信息存儲在數據字典，並且可以使用數據字典視圖來訪問諸如USER_TAB_STATISTICS。優化統計數據是從通過V $視圖中顯示的性能統計信息不同。在V$視圖中的信息涉及該系統的狀態和SQL負載上執行它。

Oracle 10g之後，Query Optimizer就已經將CBO作為默認優化器，並且Oracle官方不再支持10G以前的RBO服務。但是，通過優化器參數OPTIMIZER_MODE，我們可以控制Oracle優化器生成不同模式下的執行計劃。數據庫中的對象可以是不斷變化的，統計數據必須定期更新，以便准確地描述這些數據庫對象，統計都是由Oracle自動地或手動使用DBMS_STATS包完成，DBMS_STATS還提供了用於管理統計程序。可以保存和恢復副本統計。可以從一個系統導出統計數據到另一個系統。例如，你可以從一個生產數據庫將統計信息導出到一個測試系統。此外，我們還可以鎖定統計信息，防止這些統計數據的變化統計。

圖1存儲統計信息字典視圖

2.1. 表統計信息

表統計信息包括表中行的數量信息，表使用數據塊的數量，以及行在表中的平均行長度。優化器使用這些信息，結合其它統計信息，以計算各種操作的執行計劃成本，並估計行的操作將產生的數量。例如，一個表存取的成本是使用數據塊和參數DB_FILE_MULTIBLOCK_READ_COUNT的值進行計算，表的統計信息可以在字典視圖USER_TAB_STATISTICS查看。

2.2. 列統計信息

列統計信息包括列上不同值的數量，以及在該列中的最小和最大值。可以在字典視圖USER_TAB_COL_STATISTICS查看列統計信息。優化器使用列統計信息和表統計信息的行數估計SQL操作返回的結果。例如，如果一個表有100條記錄，並且訪問表列有10個不同的相等謂詞，那麼優化器，假定數據分布是均勻的，估計的基數為表中的行數不同的值除以該列，即100 / 10 = 10。

圖2使用基本表和列統計基數計算

2.3. 索引統計信息

索引統計信息提供索引中不同值的數據信息，葉塊中的索引數，索引的深度和集群因子數目的數量。優化程序會使用這些信息連同其他統計信息來確定索引訪問的成本。例如優化器使用B樹索引，將會使用葉塊數和表統計中的num_rows確定索引范圍掃描的成本。

2.4. 直方圖統計信息

直方圖是一種對被管理對象某一方面質量進行管理的描述工具，在Oracle中自然它也是對Oracle中某個對象質量的描述工具，這個對象就是Oracle中最重要的東西——“數據”。

在Oracle中直方圖是一種對數據分布質量情況進行描述的工具。它會按照某一列不同值出現數量多少，以及出現的頻率高低來繪制數據的分布情況，以便能夠指導優化器根據數據的分布做出正確的選擇。在某些情況下，表的列中的數值分布將會影響優化器使用索引還是執行全表掃描的決策。當WHERE子句的值具有不成比例數量的數值時，將出現這種情況，使得全表掃描比索引訪問的成本更低。這種情況下如果WHERE子句的過濾謂詞列之上有一個合理的，正確的直方圖，將會對優化器使用索引還是全表掃描發揮巨大的作用，使得SQL語句執行成本最低從而提升性能。

在分析表或索引時，直方圖用於記錄數據的分布。通過獲得該信息，基於成本的優化器就可以決定使用將返回少量行的索引，而避免使用基於限制條件返回許多行的索引。直方圖的使用不受索引的限制，可以在表的任何列上構建直方圖。

構造直方圖最主要的原因就是幫助優化器在表中數據嚴重偏斜時做出更好的規劃：例如，如果一到兩個值構成了表中的大部分數據(數據偏斜)，相關的索引就可能無法幫助減少滿足查詢所需的I/O數量。創建直方圖可以讓基於成本的優化器知道何時使用索引才最合適，或何時應該根據WHERE子句中的值返回表中80％的記錄。

通常情況下在以下場合中建議使用直方圖：

1) 當Where子句引用了列值分布存在明顯偏差的列時：當這種偏差相當明顯時，以至於 WHERE子句中的值將會使優化器選擇不同的執行計劃。這時應該使用直方圖來幫助優化器來修正執行路徑。（注意：如果查詢不引用該列，則創建直方圖沒有意義）

2) 當列值導致不正確的判斷時：這種情況通常會發生在多表連接時，例如，假設我們有一個五項的表聯接，其結果集只有 10行。Oracle 將會以一種使第一個聯接的結果集（集合基數）盡可能小的方式將表聯接起來。通過在中間結果集中攜帶更少的負載，查詢將會運行得更快。為了使中間結果最小化，優化器嘗試在 SQL執行的分析階段評估每個結果集的集合基數。在偏差的列上擁有直方圖將會極大地幫助優化器做出正確的決策。如優化器對中間結果集的大小做出不正確的判斷，它可能會選擇一種未達到最優化的表聯接方法，因此向該列添加直方圖經常會向優化器提供使用最佳聯接方法所需的信息。

Oracle利用直方圖來提高非均勻數據分布的選擇率和技術的計算精度。但是實際上Oracle會采用另種不同的策略來生成直方圖：其中一種是針對包含很少不同值的數據集；另一種是針對包含很多不同的數據集。Oracle會針對第一種情況生成頻率直方圖，針對第二種情況生成高度均衡直方圖。通常情況下當BUCTET < 表的NUM_DISTINCT值得到的是HEIGHT BALANCED（高度平衡）直方圖，而當BUCTET > 表的NUM_DISTINCT值的時候得到的是FREQUENCY（頻率）直方圖。

3. 管理統計信息

Oracle 建議啟用自動優化程序統計信息收集。在這種情況下，數據庫會自動收集沒有被收集過的統計信息或過時的統計信息。如果一個新表需要收集統計信息，數據庫會收集表和關聯的索引。

自動優化統計信息收集程序運行DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC調用，數據統計信息收集過程DBMS_STATS.GATHER_DATABASE_STATS，該過程使用GATHER AUTO選項收集數據庫表的信息和索引信息，使統計信息為最新狀態。GATHER_DATABASE_STATS_JOB_PROC過程進行統計信息收集，過程會優先數據庫對象統計收集，所以在收集統計信息期間要對數據庫的對象更新操作時，需要關閉自動統計收集作業。

3.1. 啟用和禁用自動統計信息收集

Oracle自動維護任務基礎結構（稱為 AutoTask）調度程序會在維護窗口中自動運行該任務。默認情況下，每周六的晚上，自動優化程序統計信息收集作為 AutoTask 的一部分運行，默認啟用的在所有的預定義的維護窗口中運行。

如果對於一些原因自動優化程序統計信息收集被禁用，可以使用DBMS_AUTO_TASK_ADMIN包手動啟用：

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE(

client_name => 'auto optimizer stats collection'

, operation => NULL

, window_name => NULL

);

END;

如果采用手動管理方式收集統計信息，同樣可以使用可以使用DBMS_AUTO_TASK_ADMIN包手動禁用作業：

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'auto optimizer stats collection'

, operation => NULL

, window_name => NULL

);

END;

3.2. DBMS_STATS統計信息管理

對於不斷變化的數據庫對象，必須定期收集統計數據以便他們准確地描述數據庫對象。Oracle推薦使用DBMS_STATS過程包收集統計信息，並取代現在已經過時的統計信息收集命令ANALYZE 。DBMS_STATS 包包含超過 50 不同的過程，用於收集和管理統計，而且最重要的過程是 GATHER_ * _STATS 程序。這些過程可以用於收集和管理表、列和索引的統計信息，必須使用對象的所有者或有任何具有系統特權的DBA 角色運行這些程序，以下是DBMS_STATS包中收集統計信息涉及到的過程。

名稱

用途描述

GATHER_INDEX_STATS

收集特定用戶下指定索引列的統計信息

GATHER_TABLE_STATS

收集特定用戶指定表上表行，列和索引列的統計信息

GATHER_SCHEMA_STATS

收集特定用戶所有對像的統計信息

GATHER_DICTIONARY_STATS

收集數據庫所有數據字典統計信息

GATHER_DATABASE_STATS

收集數據庫所有對象統計信息

3.2.1. 手動采集表統計信息

這些程序使用的參數是幾乎相同，因此這裡列舉 GATHER_TABLE_STATS 過程的參數作為說明，GATHER_TABLE_STATS包過程用於收集表，分區，索引和列的統計信息。這個過程擁有15個不同的參數。我們在收集表的統計信息時，只需指定ownname和tabname這兩個參數，過程包就可以運行。如果表是分區表還需要指分區名稱。例如我們對住院費用記錄表進行統計信息進行收集，使用以下方式就可以收集到該表的統計信息。

SQL> begin

2 dbms_stats.gather_table_stats(ownname => 'ZLHIS',

3 tabname => '藥品庫存');

4 end;

5 /

PL/SQL procedure successfullycompleted

在進行數據采集時我們有時會使用到其它輸入參數，在這裡我們對過程中的其它輸入參數介紹。

l ESTIMATE_PERCENT

ESTIMATE_PERCENT參數確定用來計算統計信息行數的百分比，最准確的統計信息收集處理是收集表中的所有行。Oracle 11g使用一種新的采樣算法，基於哈希值並提供准確的統計信息。這種新方法精度接近所有行（100%）樣品，但頂多消耗10%樣品的成本。ESTIMATE_PERCENT的默認值設置為AUTO_SAMPLE_SIZE，將使用這種新算法。GATHER_ * _STATS 程序。我們在對ZLHIS對象收集統計信息時，將ESTIMATE_PRECENT參數設置為較低的值，通常是10%的方式收集，這樣做以確保將收集統計數據的結果迅速。當然為了數據庫得到更准備統計信息。Oracle強烈建議從 Oracle 11g 起使用ESTIMATE_PRECENT參數的默認值收集統計信息，該參數的取值范圍為取值范圍[0.000001-100]。

示例：參數ESTIMATE_PERCENT=10以病人醫囑發送數據表數據10%的比例進行數據收集。

begin

dbms_stats.gather_table_stats(ownname => 'ZLHIS',

tabname => '病人醫囑發送',

estimate_percent=> 10,

method_opt => 'for all columns size skewonly',

force => true,

cascade => true,

degree => 4);

end;

l METHOD_OPT

這個參數最常見的功能就是控制直方圖的收集方式，但實際上它的功能遠不及此，它的實際功能如下所示：

ü 控制哪些列收集基本的統計信息

ü 收集直方圖,

ü 收集擴展的統計信息

Method_opt 參數用法分為兩個部分,如下圖所示：

FOR ALL [indexed " hidden] columns這一部分控制著哪些列將會收集列的基本統計信息，目標列上的最小值，最大值，列上不同值的數量，空值的數量等等。系統默認值為FOR ALL COLUMNS，它將收集表上所有列(包括隱藏列)的基本的統計信息。指定FOR ALL INDEXED COLUMNS 只收集含有索引字段列的基本統計信息。一般不推薦使用這個選項值，因為在數據庫環境中的所有 SQL語句所使用的字段，比如SELECT 後面的字段，WHERE後面字段，GROUP BY中的字段，並不只是會引用含有索引的字段。指定FOR ALL HIDDEN COLUMNS收集所有不可見字段基本統計信息，同樣在收集統計信息時不推薦使用這個選項值。這個選項值通常只用於在一個所有列的統計信息都是准確的表中新增了一個或幾個不可見或者說是虛擬的列，只需要收集這個或者這幾個不可見列的統計信息，而不再重復去其他列的統計信息。

Size [size_clause]這一部分控制收集直方圖的方式，SIZE 後面可以有以下選項：

AUTO Oracle自己決定根據列的統計信息(sys.col_usage$)以及列的數據傾斜程度(均勻分布程度)決定哪些列需要收集直方圖。

INTEGER 指定收集直方圖的桶數，桶數最小為 1最大為 254 (針對 11g及以前的版本， 12c後沒有這個限制)。注意如果桶數為 1，即SIZE 1 意味著不建立直方圖，如果已經有直方圖的列則會刪除該列的直方圖。

REPEAT只在已經有直方圖的列上重新收集直方圖。REPEAT會確保在全局級別上對已經存在直方圖的列重新收集直方圖。一般不推薦使用這個選項，因為新的直方圖使用的桶數將不能超過舊的直方圖中的桶數。假設當前直方圖中桶數為 5，當使用SIZE REPEAT重新收集直方圖時，新的直方圖使用的桶數將不能超過 5，這鐘方式可能不會取得好的效果。

SKEWONLY 只在數據不均勻分布的列上收集直方圖。

示例：參數METHOD_OPT = 'FOR ALL COLUMNS SIZESKEWONLY'收集病人醫囑分布不均勻列的直方圖統計信息。

begin

dbms_stats.gather_table_stats(ownname => 'ZLHIS',

tabname => '病人醫囑發送',

estimate_percent => 10,

method_opt => 'for all columns size skewonly',

force => true,

cascade => true,

degree => 4);

end;

l DEGREE

DEGREE參數控制服務器並行收集統計數據的進程數。默認情況下，Oracle數據庫中的所有表的DEGREE屬性為1，我們可以更改這個參數值，加快統計數據的收集。當DEGREE設置值為DMBS_STATS.AUTO_DEGREE，Oracle根據並行服務器進程數參數（PARALLEL_MAX_SERVERS）值自動分配進程數收集統計信息。對一個數據量較小的對象，使用缺省值1即可。對大數據對象的可以使用DBMS_STAT.DEFAULT_DEGREE參數由數據庫自動分配並行度。

示例：DEGREE=4以4個進程收集病人醫囑發送記錄的統計信息。

begin

dbms_stats.gather_table_stats(ownname => 'ZLHIS',

tabname => '病人醫囑發送',

estimate_percent => 10,

method_opt => 'for all columns size skewonly',

force => true,

cascade => true,

degree => 4);

end;

l CASCADE

在系統上沒有執行過索引統計信息收集。使用CASCADE選項相當於在除了收集表和列統計並同時運行 GATHER_INDEX_STATS收集索引統計信息，使用參數DBMS_STATS.AUTO_CASCADE由ORACLE確定是否收集索引統計信息要，參數設置為TRUE強制收集所有索引統計信息，在缺省情況下CASCADE參數值為FALSE。

示例：強制收集病人醫囑發送上的所有索引統計信息。

begin

dbms_stats.gather_table_stats(ownname => 'ZLHIS',

tabname => '病人醫囑發送',

estimate_percent => 10,

method_opt => 'for all columns size skewonly',

force => true,

cascade => true,

degree => 4);

end;

3.2.2. 鎖定和解鎖一個表統計信息

在某些情況下我們需要鎖定一個特定表的統計息不被更新，以保證執行計劃的准確性，我們需要使用DBMS_STATS.LOCK_TABLE_STATS鎖定統計信息。要鎖定一個表的統計信息我們只需要傳入表的擁有者和表名就可以鎖定該用戶表的統計信息。反之我們要解鎖一個鎖定的用戶對象使用DBMS_STAT.UNLOCK_TABLE_STATS解鎖鎖定的統計信息。

示例:鎖定ZLHIS用戶下藥品收發記錄統計信息。

begin

DBMS_STATS.LOCK_TABLE_STATS(ownname =>'ZLHIS' , tabname =>'藥品收發記錄' );

end;

示例:解鎖被鎖定的ZLHIS用戶下藥品收發記錄統計信息。

begin

DBMS_STATS.UNLOCK_TABLE_STATS(ownname =>'ZLHIS' , tabname =>'藥品收發記錄' );

end;