ORA FAQ 性能调整系列之——Oracle 9与Oracle 8中CPU_COSTING有什么变化？

答案：
What is the difference between cpu_costing in Oracle 9 and the old costing of Oracle 8 ?
Oracle 9与Oracle 8中CPU_COSTING有什么变化？

Author's name: Jonathan Lewis
Author's Email: Jonathan@jlcomp.demon.co.uk
Date written: 15th Dec 2002

Oracle version(s): 9
What is the difference between cpu_costing in Oracle 9 and the previous costing methods of Oracle 7 and 8 ?
Oracle 9与之前的Oracle 7和8中CPU_COSTING有什么变化？


Oracle 9 introduces a more subtle, and thorough costing mechansim. It's a great improvement on the Oracle 7/8 version, but I think the change-over is going to be a bit like the change-over from rule-based to cost-based. If you don't understand how it works you may see some strange events.
Oracle 9 引入了一套更精细和全面的代价机制。这是对Oracle 7/8版的重大改进，但我认为这一改变类似从基于规则转变至基于代价。如果你不理解它是如何运作的，你会看到奇怪的事件。

You can enable cpu_costing simply by collecting system_statistics for an appropriate period of time with the dbms_stats package. This records in the table sys.aux_stats$ values for:
你可以用dbms_stats包收集一段适当的时间内的系统统计，以此来激活CPU_COSTING。

 assumed CPU speed in MHz                       假定的CPU速度（MHz）
 single block read time in milliseconds         单块读时间（ms）
 multiblock read time in milliseconds           多块读时间（ms）
 typical achieved multiblock read.              一般达到的多块读

These figures are used to produce three main effects.
这些数字可产生三个主要效用：

Instead of Oracle assuming that single block reads are just as expensive as multiblock reads, Oracle knows the relative speed. This is roughly equivalent to setting the parameter optimizer_index_cost_adj according to the db file xxxx read average wait times - it will encourage Oracle to use indexed access paths instead of tablescans because Oracle now understands that tablescans are more expensive than it used to think.
Oracle并不假定单块读与多块读一样昂贵，它知道相对速度。这大致与根据数据库文件xxxx读取的平均等待时间设置参数optimizer_index_cost_adj相同——由于Oracle现在认为表扫描比原先理解更昂贵，这将鼓励Oracle使用索引访问路径而不是表扫描。

Secondly, Oracle will use the 'achieved' average multiblock read count to calculate the number of read requests required to scan a table, instead of using an adjusted value of db_file_multiblock_read_count. Since many people use a value of db_file_multiblock_read_count that is inappropriate, the result of this is that Oracle is likely to increase the cost of, and therefore decrease the probability of, doing tablescans (and index fast full scans). Note - the recorded value is used in the calculations, but Oracle tries to use the init.ora value when actually running a tablescan.
其次，Oracle将使用“达到的”（achieved）平均多块读取数来计算扫描一张表所需的读请求数，而不是使用一个db_file_multiblock_read_count的调整值。由于很多人使用的db_file_multiblock_read_count值并不合适，这样就造成Oracle可能增加表扫描（和索引快速全扫描）的代价，并由此减少表扫描（和索引快速全扫描）的可能性。注意——记录的值是用于计算的，当真正进行扫描时，Oracle会尝试使用init.ora中的值。

Finally, Oracle knows that (e.g.) to_char(date_col) = 'character value' costs a lot more CPU than number_col = number_value, so it may change its choice of index to use a less selective index if the consequential cost of using that index is lower on CPU usage. (Also, Oracle will re-arrange the order of non-access predicates to minimise CPU costs, but the difference in performance from this is not likely to be visible in most cases).
最后，Oracle知道（例如）to_char(日期列)='字符值'的CPU代价比数字列=数值要打，所以Oracle可能修改索引的选择，若使用一个有更小选择性的索引的连续读代价（consequential cost）低于CPU的使用代价，则会选择这一索引。（另外，Oracle将重新安排非访问（non-access）的谓词顺序来减小CPU代价，但在多数情况下不会察觉性能的差异。）

Overall - it's definitely a good thing. In practice, you may see a much stronger bias towards indexed access paths which may impact performance.
总之——这肯定是一个好事。事实上，你可以发现对索引访问路径更强的偏好，这将影响性能。

Further reading: Oracle 9.2 Performance Tuning Guide and Reference
进一步阅读：Oracle 9.2 性能调整指南和参考

--------------------------------------------------------------------------------
本文翻译自http://www.jlcomp.demon.co.uk/faq/  译者仅保留翻译版权

上一个：Oracle9i中一个特殊等待事件--null event
下一个：ORACLE中BFILE字段的使用研究