ORA FAQ 性能調整系列之——Oracle 9與Oracle 8中CPU_COSTING有什么變化？
2024-08-29 13:48:06
供稿：網友
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/ 譯者僅保留翻譯版權
