Oracle系統(tǒng)統(tǒng)計信息
1. 什么是系統(tǒng)統(tǒng)計信息,？
我們知道在CBO環(huán)境中，Oracle依賴于對象的統(tǒng)計估算成本,，以選擇正確的SQL執(zhí)行計劃,。從Oracle9i開始CBO計算成本(cost)的算法有了變化。Oracle9i以前更多考慮IO（多塊讀與單塊讀）成本;9i以后,，強化了cpu速度對成本估算的影響,。
Oracle提供了dbms_stats.gather_system_stats來收集系統(tǒng)統(tǒng)計信息。系統(tǒng)統(tǒng)計信息讓優(yōu)化器考慮服務器的IO與CPU性能及其利用率,，作為計算成本的依據(jù)；為每一個可選的執(zhí)行計劃估算IO與CPU成本,。因而對于CBO來說,，獲得準確的系統(tǒng)統(tǒng)計信息對于正確估計成本是非常重要的。Oracle收集的系統(tǒng)統(tǒng)計信息主要內容說明如下：
--cpuspeedNW 表示非負載情況下的cpu速度,在系統(tǒng)啟動時自動搜集
--ioseektim IO查找時間,，以毫秒表示,；缺省為10ms,非負載模式或可以手動設置。
--iotfrspeed IO傳輸速度,，表示Oracle數(shù)據(jù)庫單次讀數(shù)據(jù)的傳輸速率,，單位為bytes/ms，在系統(tǒng)啟動時自動收集,；默認為4096 bytes/ms
--cpuspeed 表示負載情況下的cpu速度,以平均每秒可提供的cpu周期表示
--maxthr 最大IO吞吐量,單位為bytes/s
--slavethr 從屬IO吞吐量,，表示并行進程時，從屬進程的IO吞吐量,，單位為bytes/s
--sreadtim 單塊讀時間(如索引讀取),，表示隨機讀一個Oracle數(shù)據(jù)塊的時間,，以ms計算
--mreadtim 多塊讀時間(主要是指全表掃描),表示連續(xù)讀取多個Oracle數(shù)據(jù)庫的平均時間，以ms計算
--mbrc 多塊讀計數(shù),，表示一次多塊讀的讀取的Oracle數(shù)據(jù)塊數(shù)量
系統(tǒng)統(tǒng)計信息存儲在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. 系統(tǒng)統(tǒng)計信息的收集
Dbms_stats.gather_system_stats的參數(shù)如下：
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與其他收集統(tǒng)計信息的參數(shù)一樣,，不多做說明,。系統(tǒng)統(tǒng)計信息有工作負載與無工作負載兩種類型; ioseektim、iotrfspeed,、cpuspeednw是無負載的統(tǒng)計信息,，也就是說不需要系統(tǒng)有工作負載，可以系統(tǒng)空閑時進行收集,。Oracle為在系統(tǒng)啟動時間重新設置,，或重置為默認值。要手動收集非工作負載統(tǒng)計信息,，使用dbms_stats.gather_system_stats(gathering_mode => 'NOWORKLOAD'),。當使用dbms_stats.delete_system_stats()刪除系統(tǒng)統(tǒng)計信息時間，將只保留非負載時的統(tǒng)計信息:
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行,。
不同壓力與不同類型的應用，甚至同一系統(tǒng)的不同時間,，cpu與io的能力都是不一樣的,。比如ZLHIS在８點到11點的壓力，明顯于大于下午的壓力,；這時候包括多塊讀時間,、單塊讀時間的效率都會有差異。理想的情況是,，收集不同系統(tǒng)負載下的系統(tǒng)統(tǒng)計信息,，存放到特定的統(tǒng)計信息表中，然后在負載發(fā)生變化的時候導入到Oracle中,，但在類似ZLHIS這種要求高可用的系統(tǒng),，頻繁的變更系統(tǒng)統(tǒng)計信息不太現(xiàn)實。大多數(shù)情況下,，只需要采集系統(tǒng)高峰時段或典型時段的系統(tǒng)統(tǒng)計信息即可,。
收集負載情況下的統(tǒng)計信息有兩種方式，一種是手工指定收集時段的開始與結束：
--啟動收集
exec dbms_stats.gather_system_stats(gathering_mode => 'START');
.............
--等待系統(tǒng)運行一段時間,，等待時間長短根據(jù)情況做調整
.............
--停止收集
exec dbms_stats.gather_system_stats(gathering_mode => 'STOP');
另一種方式就是使用間隔模式,，指定一個間隔時段，Oracle自動開始與結束信息收集：
--以未來10分鐘的系統(tǒng)負載，收集系統(tǒng)統(tǒng)計信息,。
exec dbms_stats.gather_system_stats(gathering_mode => 'INTERVAL',interval => 10);
需要說明的是收集系統(tǒng)統(tǒng)計信息,，并不影響已經緩存的sql語句，只會影響新解析的SQL語句,，如果要已經緩存的SQL語句也按新的系統(tǒng)統(tǒng)計信息生成執(zhí)行計劃,，只有清空共享池，但這在生產系統(tǒng)上是比較危險的操作,。另外需要注意的就是,，如果在收集時段內沒有相應操作，將不會收集對應的系統(tǒng)統(tǒng)計信息,；例如,，如果收集時段內沒有產生全表掃描的多塊讀，mbrc(多塊讀計數(shù))將不會收集,。
2. 系統(tǒng)統(tǒng)計信息對CBO成本計算的影響
雖然CBO計算的成本只是對生成何種執(zhí)行計劃有關,，并不對真正執(zhí)行SQL語句的真實代價產生什么影響，但作為CBO估算成本的基礎要素,，系統(tǒng)統(tǒng)計信息要盡量保證準確,。我們通過實驗來說明系統(tǒng)統(tǒng)計信息對sql語句成本估算的影響：
使用dba_objects視圖創(chuàng)建一個測試表:
SQL> --創(chuàng)建測試表
SQL> create table test as select * from dba_objects;
表已創(chuàng)建。
SQL> insert into test select * from test;
已創(chuàng)建10212行,。
SQL> --收集測試表的統(tǒng)計信息
SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => 'test',cascade => true);
PL/SQL 過程已成功完成,。
然后刪除刪除工作負載的統(tǒng)計信息，只保留非工作負載的統(tǒng)計信息:
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在沒有負載情況下的系統(tǒng)統(tǒng)計信息時,，估算的成本為71。接下為,，我們使用導入一些系統(tǒng)統(tǒng)計信息,。由于測試環(huán)境，沒有什么負載,，我們使用dbms_stats.set_system_stats過程來手工修改統(tǒng)計信息：
SQL> --創(chuàng)建統(tǒng)計信息表
SQL> exec dbms_stats.create_stat_table(ownname => user,stattab => 'SYSTEM_STATS');
PL/SQL 過程已成功完成,。
SQL> --設置相關的統(tǒng)計信息值
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> --導入相應統(tǒng)計信息
SQL> exec dbms_stats.import_system_stats(stattab => 'system_stats',statown => user);
PL/SQL 過程已成功完成,。
現(xiàn)在來重新查看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,在收集了系統(tǒng)信息后,，CBO估算的成本發(fā)生了變化,。我們知道Oracle提供了db_file_multiblock_read_count參數(shù)，來控制Oracle一次多塊讀的Oracle數(shù)據(jù)塊數(shù)量，也將影響Oracle對全表掃描成本的估算,。Oracle既然收集了多塊讀IO速度(mreadtim),、多塊讀計數(shù)(mbrc)等信息，那db_file_multiblock_read_count的設置與這些統(tǒng)計信息是什么關系呢,？答案是：如果存在負載情況下的多塊讀的相關統(tǒng)計信息,，將會忽略db_file_multiblock_read_count的設置，如果不存在相應的系統(tǒng)統(tǒng)計信息,，將使用db_file_multiblock_read_count的值對全表掃描成本進行估算,。
首先，我們測試一下,，不存在相關系統(tǒng)統(tǒng)計信息時,，全表掃描的成本：
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)又回到了未收集系統(tǒng)統(tǒng)計信息時的71,而不是收集后的33,這個時候,，我們修改db_file_multiblock_read_count參數(shù)，來看看相應的成本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行。
可以看到在沒有系統(tǒng)統(tǒng)計信息的情況下,，設置db_file_multiblock_read_count,，可以影響SQL的成本估算，現(xiàn)在變成了57?，F(xiàn)在重新導入系統(tǒng)統(tǒng)計信息,，看設置db_file_multiblock_read_count能否影響執(zhí)行計劃：
--重新導入系統(tǒng)統(tǒng)計信息
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行。
可以看到,，導入系統(tǒng)統(tǒng)計信息后,，成本又變成了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 |
