ORACLE 系統統計資料和CPU開銷模型

原創 轉載請註明出處

學習感悟

       10G以後預設都是使用CPU開銷模型，除非在SQL語句級別指定NO_CPU_COSTING，也就是說除了IO_COST以外，還需要計算CPU_COST，而系統的統計資料就是影響COST計算的一些基數。
系統統計資訊一般是存放在aux_stats$裡面如下:
 select * FROM AUX_STATS$;
 SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------------------------------------------------------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    01-06-2010 23:25
SYSSTATS_INFO                  DSTOP                                     01-06-2010 23:55
SYSSTATS_INFO                  FLAGS                                   0
SYSSTATS_MAIN                  CPUSPEEDNW                        864.957
SYSSTATS_MAIN                  IOSEEKTIM                          19.887
SYSSTATS_MAIN                  IOTFRSPEED                      10580.471
SYSSTATS_MAIN                  SREADTIM                            0.512
SYSSTATS_MAIN                  MREADTIM                            0.606
SYSSTATS_MAIN                  CPUSPEED                             1011
SYSSTATS_MAIN                  MBRC                                   11
SYSSTATS_MAIN                  MAXTHR                                   
SYSSTATS_MAIN                  SLAVETHR                                 
SYSSTATS_INFO表示是一些基本資訊。

SYSSTATS_MAIN                  CPUSPEEDNW                        864.957
SYSSTATS_MAIN                  IOSEEKTIM                          19.887
SYSSTATS_MAIN                  IOTFRSPEED                      10580.471

表示的是非工作量統計資訊（noworkload statistics)

SYSSTATS_MAIN                  SREADTIM                            0.512
SYSSTATS_MAIN                  MREADTIM                            0.606
SYSSTATS_MAIN                  CPUSPEED                             1011
SYSSTATS_MAIN                  MBRC                                   11
SYSSTATS_MAIN                  MAXTHR                                   
SYSSTATS_MAIN                  SLAVETHR                                 
表示的是工作量統計資訊（workload statistics)

所謂noworkload statistics和workload statistics簡單的說就是前者是模擬負載得出的資訊，後者是在應用系統真正負載的高的時候得到的真實資訊
收集noworkload statistics使用命令，（其實在10G預設情況下有一組noworkload statistics資訊，但是我看了好像很不準確，所以自己收集了下）。
execute dbms_stats.gather_system_stats(gathering_mode => 'noworkload');
收集workload statistics使用命令如下
1、啟動
execute dbms_stats.gather_system_stats(gathering_mode => 'start');
2、經過一段時間的負載
3、停止
execute dbms_stats.gather_system_stats(gathering_mode => 'stop');
透過計算差值會得出workload statistics
接下來是說明這些資料到底有何用
在noworkload statistics下
多塊讀的IO_COST=blocks/1.6765*db_file_multiblock_read_count^0.6581
而cost=io_cost+cpu_cost/cpuspeednw*(ioseektim+db_block_size/iotfrspeed)*1000
而在workload statistic下
多塊讀的IO_COST=blocks/mbrc*mreadtim/sreadtim
而COST=IO_COST+cpu_cost/cpuspeed*sreadtim*1000
可以看到在noworkload statistics和workload statistic下計算COST的公式是不一樣的，所以系統統計資訊實際會影響計算COST的值。
當然最好收集workload statistic，如果不收集起碼要更新noworkload statistics，讓系統統計資訊更加準確。
這裡所有的=其實為約等於，所有公式來源於ORACLE效能診斷藝術。