系統統計資訊system statitics小結

什麼是system statistics

System statistics describe the system's hardware characteristics, such as I/O and CPU performance and utilization, to the query optimizer. When choosing an execution plan, the optimizer estimates the I/O and CPU resources required for each query. System statistics enable the query optimizer to more accurately estimate I/O and CPU costs, enabling the query optimizer to choose a better execution plan.

--描述系統硬體特性，比如I/O和CPU的效能，當選擇執行計劃時，最佳化器需要評估每個query所需的I/O和CPU資源，system statistics能幫助最佳化器選擇最佳執行計劃；

System statistics又分為workload和noworkload，透過執行dbms_stats.gather_system_stats完成，但使用者必須具備DBA或者gather_system_statistics許可權以更新相應資料字典；

Oracle收集完system statistics後不會invalidate已經解析的sql，這一點不同於常見的統計資訊，但新解析的sql會使用到新的統計資訊；

 

什麼是workload statistics

Workload statistics, introduced in Oracle 9i, gather single and multiblock read times, mbrc, CPU speed (cpuspeed), maximum system throughput, and average slave throughput. The sreadtim, mreadtim, and mbrc are computed by comparing the number of physical sequential and random reads between two points in time from the beginning to the end of a workload. These values are implemented through counters that change when the buffer cache completes synchronous read requests. Since the counters are in the buffer cache, they include not only I/O delays, but also waits related to latch contention and task switching. Workload statistics thus depend on the activity the system had during the workload window. If system is I/O bound—both latch contention and I/O throughput—it will be reflected in the statistics and will therefore promote a less I/O intensive plan after the statistics are used. Furthermore, workload statistics gathering does not generate additional overhead.

--9i引入，主要是收集系統的I/O和CPU效能資訊用以調節系統效能；假如現在系統I/O比較繁忙，資訊收集完成後系統則傾向於生存消耗I/O較少的執行計劃，且此操作不會對系統產生額外壓力；

如何收集

--有兩種方法

1  呼叫dbms_stats.gather_system_stats(‘start’)開始收集，dbms_stats.gather_system_stats(‘stop’)手工結束

2  呼叫dbms_stats.gather_system_stats(‘interval’, interval => N)，指定執行時間到時會自動結束

--可呼叫dbms_stats.delete_system_stats()刪除

多塊讀和MBRC

在10.2中，最佳化器使用mbrc來預測全表掃描，而資料庫一次I/O所能讀取的最大塊數目則由db_file_mutiblock_read_count決定；

但在實際情形中，至少以下三種情況會導致oracle無法讀取最大塊

1 段頭需要單塊讀

2  物理讀不能跨越多個extent

3  若資料塊已在buffer cache中，除非direct path read，否則不會再次從磁碟讀取

因為並非每次多塊讀都會讀取db_file_multiblock_read_count定義的數值，故oracle引入mbrc替代其計算IO cost，

其公式為io_cost =(blocks/mbrc)*(mreadtim/sreadtim)；

只有當workload statistics不可用時，才使用db_file_multiblock_read_count，公式大致為io_cost = blocks/(1.6765*db_file_multiblock_read_count(0.6581))，

此時該引數值越大，系統則越傾向使用全表掃描；

 

還有一點需要注意，在收集工作量統計資訊的時候如果系統沒有全表掃描，則mbrc和mreadtim可能不被收集，oracle則可能只用cpuspeed和sreadtim計算costing，

全表掃描時會沿用以前版本的演算法(10R2之前)。

 

什麼是noworkload statistics

非工作量統計資訊包含I/O transfer speed, I/O seek time和CPU speed(cpuspeednw)，與工作量統計資訊最主要的差別在於收集方法的不同，應在系統沒有負載的時候進行；

Noworkload statistics gather data by submitting random reads against all data files, while workload statistics uses counters updated when database activity occurs.

Isseektim即磁碟尋道時間，一般為5-15ms，取決於磁碟轉速和儲存效能；

i/o transfer speed為系統程式從I/O子系統讀取資料的速度，其範圍波動比較大，從幾兆到幾百兆不等；

非工作量統計資訊在例項啟動時會自動被初始化；

Ioseektim = 10ms

Iotrfspeed = 4096 bytes/ms

Cpuspeednw = gatherer value, varied based on system

當工作量統計資訊被收集後，非工作量統計資訊則會被忽略；

 

如何收集noworkload statistics

直接呼叫dbms_stats.gather_system_stats()(不帶任何引數)，將會佔有一定的I/O開銷，也可手工設定dbms_stats.set_system_stats；

 

當系統統計資訊可用時，oracle會計算I/O cost和CPU cost，上面列出了I/O cost的計算公式，關於cpu cost，Joze Senegacnik 給出過訪問一個列的cost

Cpu_cost = column_position * 20，當列位置越靠後其cpu cost就越高；

由於I/O cost和CPU cost的計算單位不同，不能直接相加，可用以下公式作參考

 Cost = io_cost + cpu_cost/(cpuspeed * sreadtim * 1000)—非工作量統計資訊則把cpuspeed替換為cpuspeednw

 

 透過查詢AUX_STATS$可以看出當前系統統計資訊

SQL> select * from AUX_STATS$;

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    07-16-2010 07:46
SYSSTATS_INFO                  DSTOP                                     07-16-2010 07:46
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                     1899.72752
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

 

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.	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.	None	Set gathering_mode = NOWORKLOAD, INTERVAL, or START|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, or START|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