Oracle性能分析11：系統統計信息

早期Oracle查詢優化器的開銷計算是基於執行SQL語句所需要的物理讀，這個方法被叫做I/O開銷模式（I/O cost model），這個方法的主要缺點是認為單塊讀和多塊讀開銷相當。在Oracle 8i中，初始化參數optimizer_index_caching和optimizer_index_cost_adj解決了這個問題，但參數的默認值僅適用於報告系統和數據倉庫。直到Oracle 9i，ORacle引入了一種新的CPU開銷模式（CPU cost model），消除了這個缺陷，但必須提供數據庫引擎所在的系統的性能信息，即系統統計信息。這篇文章將介紹系統統計信息和收集系統統計信息的方法。

系統統計信息

系統統計信息放在表aux_stats$中，可以通過下面的方法查看該視圖中的數據（需要DBA用戶或者有該表的查看權限）：

select * from aux_stats$;

SNAME			PNAME		PVAL1				PVAL2
---------------------------------------------------------------------
SYSSTATS_INFO	STATUS							COMPLETED
SYSSTATS_INFO	DSTART							08-30-2005 15:04
SYSSTATS_INFO	DSTOP							08-30-2005 15:04
SYSSTATS_INFO	FLAGS		1
SYSSTATS_MAIN	CPUSPEEDNW	484.974958263773
SYSSTATS_MAIN	IOSEEKTIM	10
SYSSTATS_MAIN	IOTFRSPEED	4096
SYSSTATS_MAIN	SREADTIM
SYSSTATS_MAIN	MREADTIM
SYSSTATS_MAIN	CPUSPEED
SYSSTATS_MAIN	MBRC
SYSSTATS_MAIN	MAXTHR
SYSSTATS_MAIN	SLAVETHR

表中的數據包括3個部分，使用SNAME區分：

1）SYSSTATS_INFO表示系統統計信息狀態和時間
STATUS在正確收集時為COMPLETED；如果出現問題，顯示為BADSTATS，在這種情況下查詢優化器將不使用統計信息；該字段還可以為：MANUALGATHERING和AUTOGATHERING。

2）SYSSTATS_MAIN表示系統統計信息結果集
該部分的信息分為非工作量統計信息（noworkload statistics）和工作量統計信息（workload statistics），兩者的主要差別是度量I/O子系統性能的方法有所不同，後面具體討論。
非工作量統計在Oracle 10g之後總是提供，包括：

CPUSPEEDNW	一個CPU一秒鐘能處理的操作數，單位百萬次/秒
IOSEEKTIM	平均磁盤尋道時間，單位毫秒，默認10
IOTFRSPEED	平均每毫秒從磁盤傳輸的字節數，默認為4096

工作量統計信息必須顯示地收集後才可用，包括：

SREADTIM	單塊數據平均讀取時間，單位毫秒
MREADTIM	多塊數據平均讀取時間，單位毫秒
CPUSPEED	一個CPU一秒鐘能處理的操作數，單位百萬次/秒
MBRC		一次多塊讀操作的平均讀取數據塊數
MAXTHR		整個系統的最大I/O吞吐量（字節/秒）
SLAVETHR	並行處理中從屬線程的平均I/O吞吐量（字節/秒）

3）SYSSTATS_TEMP用來計算系統統計信息，只有收集工作量統計信息時才可用

收集系統統計信息

使用工具包dbms_stats中的存儲過程gather_system_stats收集系統統計信息，可以收集非工作量統計信息或者工作量統計信息。

非工作量統計信息

從Oracle 10g開始，總是提供默認的非工作量統計信息，如果顯式地刪除，下次數據庫啟動時會自動收集。收集非工作量統計信息要在空閒的系統上進行，使用那個下面的語句：

begin
  dbms_stats.gather_system_stats(gathering_mode => 'noworkload');
end;

收集非工作量統計信息只需要很短的時間，但有時需要重復收集多次才能生效。

工作量統計信息

收集工作量統計信息，不能使用空閒的系統，以為數據庫引擎要利用正常的數據庫負載來評估I/O子系統的性能。
工作量統計信息的收集包含下面的步驟：

1）執行快照並存儲各種性能指標的初始值到數據字典表aux_stats$（列SNAME設定為SYSSTATS_TEMP）。使用下面的方式來啟動：

begin
  dbms_stats.gather_system_stats(gathering_mode => 'start');
end;

2）等待足夠的時間產生一個有代表性的負載時候再進行另一次快照，等待時間通常不少於30分鐘；

3）進行第二次快照，使用下面的語句：

begin
  dbms_stats.gather_system_stats(gathering_mode => 'stop');
end;

4）Oracle根據兩次快照之間的差值，計算出系統統計信息，如果無法計算的話，將被設置為空。

你也可以指定gathering_mode為interval，並指定間隔時間來收集工作量統計時間，這樣就不用手工停止快照，下面的命令指定30分鐘後進行第二次快照：

begin
  dbms_stats.gather_system_stats(gathering_mode => 'interval',
                                 interval       => 30);
end;

需要注意上面的語句並不會延續30分鐘，它只是啟動一個作業調度後返回。
使用工作量統計信息需要盡量避免收集到沒有代表性的工作量信息，可以收集多天的工作量統計信息，然後將mbrc、mreadtim、sreadtim和cpuspeed指標取平均值，maxthr和slavethr取最大值，再使用delete_system_stats過程刪除舊的系統統計信息，然後使用set_system_stats設定新的系統統計信息，存儲過程如下：

begin
  dbms_stats.delete_system_stats();
  dbms_stats.set_system_stats(pname => 'CPUSPEED', pvalue => 772);
  dbms_stats.set_system_stats(pname => 'SREADTIM', pvalue => 5.5);
  dbms_stats.set_system_stats(pname => 'MREADTIM', pvalue => 19.4);
  dbms_stats.set_system_stats(pname => 'MBRC', pvalue => 53);
  dbms_stats.set_system_stats(pname => 'MAXTHR', pvalue => 1136136192);
  dbms_stats.set_system_stats(pname => 'SLAVETHR', pvalue => 16870400);
end;