在执行计划的中CPU COSTING究竟代表什么呢？最近在看"Cost-Based Oracle Fundamentals"时对此有了比较满意的了解。因此决定在此分享给大家。实际上Costing的单位就是时间，成本表示（也总是表示）优化器对执行语句所用时间的最优估计。

在Oracle9i中，优化器引入了一个新的功能，称为CPU成本计算（CPU costing）。为什么我们如此的确信成本应该可以用等价的时间表示呢？
在"Performance Tuning Guide and Reference(9.2).pdf"中的原文解释如下：

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//viewspace-/，如需转载，请注明出处，否则将追究法律责任。