---

---

系統統計資訊主要描述了與系統硬體相關的某些特性，例如cpu和io系統的效能和利用率等。這些資訊對於查詢優化器來說是非常重要的，在選擇執行計劃的過程中，oracle優化器會利用系統統計資訊來評估執行計劃的成本，因此，準確無誤的系統統計資訊可以幫助優化器做出正確的選擇。

我們可以通過DBMS_STATS.GATHER_SYSTEM_STATS過程來收集系統統計資訊，收集的方式有兩種：負載統計（WORKLOAD STATISTICS）在具有真實系統負載的資料庫系統上，收集某一時間段內的系統資訊；非負載統計（NOWORKLOAD STATISTICS）oracle資料庫基於某種方式（如持續讀磁碟）模擬系統負載來獲取系統統計資訊。

通各方資料和oracle官方文件中可以看出，我們最好採用系統負載的方式來收集系統統計資訊，收集的時間間隔至少要30分鐘，條件允許的情況下，最好多次收集，擇優使用。處理使用DBMS_STATS.GATHER_SYSTEM_STATS來收集系統統計資訊，我們也可以手工設定某些資訊選項，這一點在後面會有示例。

oracle的系統統計資訊最終是儲存在aux_stats$資料基表中的，首先來看一下該表的結構：
SQL> desc aux_stats$
名稱                是否為空? 型別
----------------------------- -------- --------------------
SNAME                 NOT NULL VARCHAR2(30)
PNAME                 NOT NULL VARCHAR2(30)
PVAL1                  NUMBER
PVAL2                  VARCHAR2(255)

SQL> select * from aux_stats$;

SNAME            PNAME             PVAL1 PVAL2
-------------------- -------------------- ---------- ------------------------------
SYSSTATS_INFO        STATUS              COMPLETED
SYSSTATS_INFO        DSTART              09-17-2011 10:21
SYSSTATS_INFO        DSTOP               09-17-2011 10:21
SYSSTATS_INFO        FLAGS             1
SYSSTATS_MAIN        CPUSPEEDNW       1751.75879
SYSSTATS_MAIN        IOSEEKTIM            10
SYSSTATS_MAIN        IOTFRSPEED         4096
SYSSTATS_MAIN        SREADTIM
SYSSTATS_MAIN        MREADTIM
SYSSTATS_MAIN        CPUSPEED
SYSSTATS_MAIN        MBRC

SNAME            PNAME             PVAL1 PVAL2
-------------------- -------------------- ---------- ------------------------------
SYSSTATS_MAIN        MAXTHR
SYSSTATS_MAIN        SLAVETHR

已選擇13行。


sname欄位的取值有：SYSSTATS_INFO,SYSSTATS_MAIN,SYSSTATS_TEMP

SYSSTATS_INFO：系統統計資訊的狀態

SYSSTATS_MAIN：系統統計資訊的內容

SYSSTATS_TEMP：在收集系統統計資訊期間，用於臨時存放中間資料

對於pname欄位的含義，oracle官方文件給出了詳細的解釋：

align="left" valign="top">align="left" valign="top">align="left" valign="top">align="left" valign="top">align="left" valign="top">align="left" valign="top">align="left" valign="top">align="left" valign="top">align="left" valign="top">align="left" valign="top">

Parameter Name	Description	Initialization	Options for Gathering or Setting Statistics	Unit
cpuspeedNW	Represents noworkload CPU speed. CPU speed is the average number of CPU cycles in each second. CPU速率	At system startup	Set gathering_mode = NOWORKLOAD or set statistics manually.	Millions/sec.
ioseektim	I/O seek time equals seek time + latency time + operating system overhead time. 尋道時間	At system startup 10 (default)	Set gathering_mode = NOWORKLOAD or set statistics manually.	ms
iotfrspeed	I/O transfer speed is the rate at which an Oracle database can read data in the single read request. 資料傳輸速率	At system startup 4096 (default)	Set gathering_mode = NOWORKLOAD or set statistics manually.	Bytes/ms
cpuspeed	Represents workload CPU speed. CPU speed is the average number of CPU cycles in each second. CPU速率	None	Set gathering_mode = NOWORKLOAD, INTERVAL, orSTART|STOP, or set statistics manually.	Millions/sec.
maxthr	Maximum I/O throughput is the maximum throughput that the I/O subsystem can deliver. 輸入輸出最大吞吐量	None	Set gathering_mode = NOWORKLOAD, INTERVAL, orSTART|STOP, or set statistics manually.	Bytes/sec.
slavethr	Slave I/O throughput is the average parallel slave I/O throughput. 從屬程式的平均吞吐量	None	Set gathering_mode = INTERVAL or START|STOP, or set statistics manually.	Bytes/sec.
sreadtim	Single block read time is the average time to read a single block randomly. 單塊讀時間	None	Set gathering_mode = INTERVAL or START|STOP, or set statistics manually.	ms
mreadtim	Multiblock read is the average time to read a multiblock sequentially. 多塊讀時間	None	Set gathering_mode = INTERVAL or START|STOP, or set statistics manually.	ms
mbrc	Multiblock count is the average multiblock read count sequentially. 一次多塊讀的資料庫塊數量	None	Set gathering_mode = INTERVAL or START|STOP, or set statistics manually.	blocks

有一點需要注意：在新版本的oracle（10g)中，非工作量統計資訊總是可用的，如果不存在非工作量統計資訊，會在例項初始化時自動收集。

當我們更新系統統計資訊後，oracle並不會講已解析的sql語句置為無效，但是新的sql會使用資訊系統統計資訊。

我們先來看一下非工作量統計資訊

非工作量統計資訊包括IO TRASFER SPEED, IO SEEK TIME 和CPU SPEED，即上表中的藍色標識區域。

非工作量統計資訊是在系統空閒時段收集的，它通過隨機讀取所有的資料檔案來測試系統IO效能並測試CPU的速度。在預設情況下，oracle使用非工作量統計資訊，但是如果我們收集了工作量統計資訊，oracle就會優先使用工作量統計資訊。

oracle會在第一次啟動時自動收集非工作量統計資訊，如果我們需要手工收集非工作量統計資訊，可以使用不帶引數的DBMS_sTATS.GATHER_SYSTEM_STATS過程。收集非工作量統計資訊會對系統產生負載，其執行時間的長短也依賴於資料庫伺服器的效能。由於oracle內部的檢測機制，非工作量統計資訊的收集可能不會覆蓋預設值，這時我們可以多試幾次或者手工來設定統計資訊。

我們再來看一下工作量統計資訊

工作量統計資訊包括單塊讀時間和多塊讀時間，mbrc等，即上表中紅色標識區域。

oracle在收集工作量統計資訊時，不會對資料庫系統造成額外的系統負載，因為oracle是通過查詢統計時間段內的統計資訊如v$sysstat v$sesstat等來得到工作量系統統計資訊的。

oracle計算工作量統計資訊是通過某些計數器來實現的，這些計數器只有在oracle buffer cach完成某些操作時才會改變，因此這些計數器不僅反映了磁碟輸入輸出的延遲，同時也反應了與latch和任務轉換相關的等待事件。由此我們可以推斷出，如果一個系統的瓶頸在io系統，那麼oracle在根據工作量統計資訊生成執行計劃時，會優先選擇對io依賴較小的執行計劃。

我們可以通過如下兩種方式來收集工作量系統統計資訊

Run the DBMS_STATS.GATHER_SYSTEM_STATS('start') procedure at the beginning of the workload window, then the DBMS_STATS.GATHER_SYSTEM_STATS('stop')procedure at the end of the workload window.

Run DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval=>N) where N is the number of minutes when statistics gathering is stopped automatically.

當我們收集工作量統計資訊時，oracle會收集MBRC的值，但是如果在收集時間段內，oracle沒有能過收集到mbrc和MREADTIM的值（如，在時間段內沒有發生序列化的全表掃描操作），在這種情況下，oracle會使用初始化引數DB_FILE_MULTIBLOCK_READ_COUNT來評估全表掃描的成本，如果該初始化引數沒有指定，那麼oracle會使用8來代替。關於DB_FILE_MULTIBLOCK_READ_COUNT，我會專門來介紹。

---

---

1. 什麼是系統統計資訊？

我們知道在CBO環境中，Oracle依賴於物件的統計估算成本，以選擇正確的SQL執行計劃。從Oracle9i開始CBO計算成本(cost)的演算法有了變化。Oracle9i以前更多考慮IO（多塊讀與單塊讀）成本;9i以後，強化了cpu速度對成本估算的影響。

Oracle提供了dbms_stats.gather_system_stats來收集系統統計資訊。系統統計資訊讓優化器考慮伺服器的IO與CPU效能及其利用率，作為計算成本的依據；為每一個可選的執行計劃估算IO與CPU成本。因而對於CBO來說，獲得準確的系統統計資訊對於正確估計成本是非常重要的。Oracle收集的系統統計資訊主要內容說明如下：

--cpuspeedNW 表示非負載情況下的cpu速度,在系統啟動時自動蒐集

--ioseektim IO查詢時間，以毫秒錶示；預設為10ms,非負載模式或可以手動設定。

--iotfrspeed IO傳輸速度，表示Oracle資料庫單次讀資料的傳輸速率，單位為bytes/ms，在系統啟動時自動收集；預設為4096 bytes/ms

--cpuspeed 表示負載情況下的cpu速度,以平均每秒可提供的cpu週期表示

--maxthr　　　最大IO吞吐量,單位為bytes/s

--slavethr 從屬IO吞吐量，表示並行程式時，從屬程式的IO吞吐量，單位為bytes/s

--sreadtim 單塊讀時間(如索引讀取)，表示隨機讀一個Oracle資料塊的時間，以ms計算

--mreadtim 多塊讀時間(主要是指全表掃描),表示連續讀取多個Oracle資料庫的平均時間，以ms計算　

--mbrc 多塊讀計數，表示一次多塊讀的讀取的Oracle資料塊數量

系統統計資訊儲存在sys.aux_stats$表中：

SQL> select * from sys.aux_stats$;

SNAME PNAME PVAL1 PVAL2

-------------------- -------------------- ---------- --------------------

SYSSTATS_INFO STATUS COMPLETED

SYSSTATS_INFO DSTART 01-24-2011 18:06

SYSSTATS_INFO DSTOP 01-24-2011 18:06

SYSSTATS_INFO FLAGS 1

SYSSTATS_MAIN CPUSPEEDNW 1970.048

SYSSTATS_MAIN IOSEEKTIM 11.132

SYSSTATS_MAIN IOTFRSPEED 4096

SYSSTATS_MAIN SREADTIM 6

SYSSTATS_MAIN MREADTIM 24

SYSSTATS_MAIN CPUSPEED 1800

SYSSTATS_MAIN MBRC 6

SNAME PNAME PVAL1 PVAL2

SYSSTATS_MAIN MAXTHR

SYSSTATS_MAIN SLAVETHR

2. 系統統計資訊的收集

Dbms_stats.gather_system_stats的引數如下：

SQL> desc dbms_stats.gather_system_stats;

Parameter Type Mode Default?

-------------- -------- ---- --------

GATHERING_MODE VARCHAR2 IN Y

INTERVAL NUMBER IN Y

STATTAB VARCHAR2 IN Y

STATID VARCHAR2 IN Y

STATOWN VARCHAR2 IN Y

STATTAB、STATID、STATOWN與其他收集統計資訊的引數一樣，不多做說明。系統統計資訊有工作負載與無工作負載兩種型別; ioseektim、iotrfspeed、cpuspeednw是無負載的統計資訊，也就是說不需要系統有工作負載，可以系統空閒時進行收集。Oracle為在系統啟動時間重新設定，或重置為預設值。要手動收集非工作負載統計資訊，使用dbms_stats.gather_system_stats(gathering_mode => 'NOWORKLOAD')。當使用dbms_stats.delete_system_stats()刪除系統統計資訊時間，將只保留非負載時的統計資訊:

SQL> exec dbms_stats.delete_system_stats();

PL/SQL 過程已成功完成。

SQL> select * from sys.aux_stats$;

SNAME PNAME PVAL1 PVAL2

-------------------- -------------------- ---------- --------------------

SYSSTATS_INFO STATUS COMPLETED

SYSSTATS_INFO DSTART 01-25-2011 11:37

SYSSTATS_INFO DSTOP 01-25-2011 11:37

SYSSTATS_INFO FLAGS 0

SYSSTATS_MAIN CPUSPEEDNW 2030.679

SYSSTATS_MAIN IOSEEKTIM 10

SYSSTATS_MAIN IOTFRSPEED 4096

SYSSTATS_MAIN SREADTIM

SYSSTATS_MAIN MREADTIM

SYSSTATS_MAIN CPUSPEED

SYSSTATS_MAIN MBRC

SNAME PNAME PVAL1 PVAL2

-------------------- -------------------- ---------- --------------------

SYSSTATS_MAIN MAXTHR

SYSSTATS_MAIN SLAVETHR

已選擇13行。

不同壓力與不同型別的應用，甚至同一系統的不同時間，cpu與io的能力都是不一樣的。比如ZLHIS在８點到11點的壓力，明顯於大於下午的壓力；這時候包括多塊讀時間、單塊讀時間的效率都會有差異。理想的情況是，收集不同系統負載下的系統統計資訊，存放到特定的統計資訊表中，然後在負載發生變化的時候匯入到Oracle中，但在類似ZLHIS這種要求高可用的系統，頻繁的變更系統統計資訊不太現實。大多數情況下，只需要採集系統高峰時段或典型時段的系統統計資訊即可。

收集負載情況下的統計資訊有兩種方式，一種是手工指定收集時段的開始與結束：

--啟動收集

exec dbms_stats.gather_system_stats(gathering_mode => 'START');

.............

--等待系統執行一段時間，等待時間長短根據情況做調整

.............

--停止收集

exec dbms_stats.gather_system_stats(gathering_mode => 'STOP');

另一種方式就是使用間隔模式，指定一個間隔時段，Oracle自動開始與結束資訊收集：

--以未來10分鐘的系統負載，收集系統統計資訊。

exec dbms_stats.gather_system_stats(gathering_mode => 'INTERVAL',interval => 10);

　

需要說明的是收集系統統計資訊，並不影響已經快取的sql語句，只會影響新解析的SQL語句，如果要已經快取的SQL語句也按新的系統統計資訊生成執行計劃，只有清空共享池，但這在生產系統上是比較危險的操作。另外需要注意的就是，如果在收集時段內沒有相應操作，將不會收集對應的系統統計資訊；例如，如果收集時段內沒有產生全表掃描的多塊讀，mbrc(多塊讀計數)將不會收集。

2. 系統統計資訊對CBO成本計算的影響

雖然CBO計算的成本只是對生成何種執行計劃有關，並不對真正執行SQL語句的真實代價產生什麼影響，但作為CBO估算成本的基礎要素，系統統計資訊要儘量保證準確。我們通過實驗來說明系統統計資訊對sql語句成本估算的影響：

　　 使用dba_objects檢視建立一個測試表:

SQL> --建立測試表

SQL> create table test as select * from dba_objects;

表已建立。

SQL> insert into test select * from test;

已建立10212行。

SQL> --收集測試表的統計資訊

SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => 'test',cascade => true);

PL/SQL 過程已成功完成。

然後刪除刪除工作負載的統計資訊，只保留非工作負載的統計資訊:

exec dbms_stats.delete_system_stats();

我們來看此時的對test全表掃描估算的成本：

SQL> explain plan for select count(*) from test;

已解釋。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------

Plan hash value: 1950795681

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 71 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 20424 | 71 (0)| 00:00:01 |

可以看到Oracle在沒有負載情況下的系統統計資訊時，估算的成本為71。接下為，我們使用匯入一些系統統計資訊。由於測試環境，沒有什麼負載，我們使用dbms_stats.set_system_stats過程來手工修改統計資訊：

SQL> --建立統計資訊表

SQL> exec dbms_stats.create_stat_table(ownname => user,stattab => 'SYSTEM_STATS');

PL/SQL 過程已成功完成。

SQL> --設定相關的統計資訊值

SQL> exec dbms_stats.set_system_stats(pname => 'SREADTIM',pvalue => '6' ,stattab => 'system_stats');

PL/SQL 過程已成功完成。

SQL> exec dbms_stats.set_system_stats(pname => 'MREADTIM',pvalue => '12',stattab => 'system_stats');

PL/SQL 過程已成功完成。

SQL> exec dbms_stats.set_system_stats(pname => 'CPUSPEED',pvalue => '1800' ,stattab => 'system_stats');

PL/SQL 過程已成功完成。

SQL> exec dbms_stats.set_system_stats(pname => 'MBRC',pvalue => '16',stattab => 'system_stats');

PL/SQL 過程已成功完成。

SQL> --匯入相應統計資訊

SQL> exec dbms_stats.import_system_stats(stattab => 'system_stats',statown => user);

PL/SQL 過程已成功完成。

現在來重新檢視sql語句的估算成本：

SQL> explain plan for select count(*) from test;

已解釋。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------

Plan hash value: 1950795681

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 33 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 20424 | 33 (0)| 00:00:01 |

這次估算的成本(cost)為33,在收集了系統資訊後，CBO估算的成本發生了變化。我們知道Oracle提供了db_file_multiblock_read_count引數，來控制Oracle一次多塊讀的Oracle資料塊數量，也將影響Oracle對全表掃描成本的估算。Oracle既然收集了多塊讀IO速度(mreadtim)、多塊讀計數(mbrc)等資訊，那db_file_multiblock_read_count的設定與這些統計資訊是什麼關係呢？答案是：如果存在負載情況下的多塊讀的相關統計資訊，將會忽略db_file_multiblock_read_count的設定，如果不存在相應的系統統計資訊，將使用db_file_multiblock_read_count的值對全表掃描成本進行估算。

首先，我們測試一下，不存在相關係統統計資訊時，全表掃描的成本：

SQL> explain plan for select count(*) from test;

已解釋。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------

Plan hash value: 1950795681

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 71 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 20424 | 71 (0)| 00:00:01 |

-------------------------------------------------------------------

已選擇9行。

可以看成本(cost)又回到了未收集系統統計資訊時的71,而不是收集後的33,這個時候，我們修改db_file_multiblock_read_count引數，來看看相應的成本cost是否會起變化：

SQL> show parameter db_file_multiblock_read_count;

NAME TYPE VALUE

------------------------------------ ----------- ---------------------

db_file_multiblock_read_count integer 8

SQL> alter session set db_file_multiblock_read_count=16;

會話已更改。

SQL> explain plan for select count(*) from test;

已解釋。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------

--------------------

Plan hash value: 1950795681

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 57 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 20424 | 57 (0)| 00:00:01 |

-------------------------------------------------------------------

已選擇9行。

可以看到在沒有系統統計資訊的情況下，設定db_file_multiblock_read_count，可以影響SQL的成本估算，現在變成了57。現在重新匯入系統統計資訊，看設定db_file_multiblock_read_count能否影響執行計劃：

--重新匯入系統統計資訊

SQL> exec dbms_stats.import_system_stats(stattab => 'system_stats',statown => user);

PL/SQL 過程已成功完成。

SQL> --還原db_file_multiblock_read_count

SQL> alter session set db_file_multiblock_read_count=8;

會話已更改。

SQL> explain plan for select count(*) from test;

已解釋。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------

Plan hash value: 1950795681

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 33 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 20424 | 33 (0)| 00:00:01 |

-------------------------------------------------------------------

已選擇9行。

可以看到，匯入系統統計資訊後，成本又變成了33，我們再設定db_file_multiblock_read_count，再重新解析sql語句：

SQL> --再設定db_file_multiblock_read_count值

SQL> alter session set db_file_multiblock_read_count=16;

會話已更改。

SQL> explain plan for select count(*) from test;

已解釋。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------

Plan hash value: 1950795681

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 33 (0)| 00:00:01 |

| 1 |