Oracle系統統計資訊

StudyCow發表於2011-01-25
Oracle

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 |

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7839206/viewspace-1045163/，如需轉載，請註明出處，否則將追究法律責任。

相關文章