在執行計劃的中CPU COSTING究竟代表什么呢？最近在看"Cost-Based Oracle Fundamentals"時對此有了比較滿意的了解。因此決定在此分享給大家。實際上Costing的單位就是時間，成本表示（也總是表示）優化器對執行語句所用時間的最優估計。

在Oracle9i中，優化器引入了一個新的功能，稱為CPU成本計算（CPU costing）。為什么我們如此的確信成本應該可以用等價的時間表示呢？
在"Performance Tuning Guide and Reference(9.2).pdf"中的原文解釋如下：

[@more@]

CPU Costing Model

Every database operation uses the CPU. In most cases, CPU utilization is as important as I/O; often it is the only contribution to the cost (in cases of in-memory sort, hash, predicate evaluation, and cached I/O). In Oracle9i the optimizer introduces a new model, which includes the cost of CPU utilization. Including CPU utilization in the cost model helps generate better plans.

According to the CPU costing model:

Cost = (#SRds * sreadtim +#MRds * mreadtim +#CPUCycles / cpuspeed ) / sreadtim

where:

• #SRDs is the number of single block reads

• #MRDs is the number of multi block reads
• #CPUCycles is the number of CPU Cycles *)
• sreadtim is the single block read time
• mreadtim is the multi block read time
• cpuspeed is the CPU cycles per second

CPUCycles includes CPU cost of query processing (pure CPU cost) and CPU cost
of data retrieval (CPU cost of the buffer cache get).
This model is straightforward for serial execution. For parallel execution, necessary adjustments are made while computing estimates for #SRD, #MRD, and #CPUCycles.

翻譯過來上述代碼的含義如下：
成本指的是花費在單塊讀取上的時間，加上花費在多塊兒讀取上的時間，再加上所需要的CPU處理的時間，然后將總和除以單塊單塊讀取所花費的時間。也就是說，成本是語句的預計執行時間的總和，以單塊讀取時間為單位的形式來表示。

收集完的相關信息會記錄在sys.aux_stats$表中，這個通過如下查詢可以得知：
select pname,pval1 from sys.aux_stats$
Where Sname = 'SYSSTATS_MAIN'
PNAME PVAL1
--------------- ----------
CPUSPEEDNW 913.641 -- speed in millions of operations per second
IOSEEKTIM 10 -- disk seek time in milliseconds
IOTFRSPEED 4096 -- disk transfer time in bytes per millisecond

當然通過如下過程，也可以自定以上過程：
begin
dbms_stats.set_system_stats('CPUSPEED',500);
dbms_stats.set_system_stats('SREADTIM',5.0);
dbms_stats.set_system_stats('MREADTIM',30.0);
dbms_stats.set_system_stats('MBRC',12);
end;
/

在Oracle10g的10053跟蹤事件中，也能看到這些信息，如下案例所示：
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 485 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)

根據以上信息，可以推算出如下：
? MBRC= db_file_multiblock_read_count.
? sreadtim= ioseektim + db_block_size/iotrfrspeed.
? mreadtim=ioseektim + db_file_multiblock_read_count * db_block_size/iotftspeed.

關于#CPUCycles ，它在plan_table中cpu_cost所對應的值，而這個值來源于如下于CPU花在如下過程的時間集合的大概值：

? Cost of acquiring a block = X

? Cost of locating a row in a block = Y

? Cost of acquiring the Nth (in our case the 2nd) column in a row = (N – 1) * Z

? Cost of comparing a numeric column with a numeric constant = A

來自 “ ITPUB博客 ” ，鏈接：http://blog.itpub.net/18841027/viewspace-1054424/，如需轉載，請注明出處，否則將追究法律責任。

轉載于:http://blog.itpub.net/18841027/viewspace-1054424/

總結

以上是生活随笔為你收集整理的成本（CPU Costing）的含义的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。