As previously discussed, the formula used by the CBO using the CPU costing model is basically:
(sum of all the single block I/Os x average wait time for a single block I/O +
sum of all the multiblock I/Os x average wait time for a multiblock I/O +
sum of all the required CPU cycles / CPU cycles per second)
/
average wait time for a single block I/O
When determining the multiblock I/Os costs associated with a FTS, the CBO basically:
- determines the number of multiblock operations (blocks in dba_tables /mbrc system statistic)
- then multiplies this out by the average wait time of a multiblock I/O (mreadtim system statistic)
to determine the total wait time for all expected multiblock read operations.
-This total wait time of all multiblock read operations is then finally divided by the average wait time for a single block I/O (sreadtim system statistic) to express the final cost in units of single block I/Os.
Remember these average wait times associated with both single and multiblock I/Os are actual wait times for these events as experienced in the specific database environment and captured during the collection of system statistics.
Therefore, the formula automatically takes into consideration and incorporates into the calculations any discrepancies and differences in wait times between a single and a multiblock I/O.
For example, if a multiblock I/O actually takes (say) 10ms to perform on average, while a single block I/O only takes (say) 5ms to perform on average, then the formula will automatically make the costs of performing multiblock reads to be twice as expensive as the costs associated with performing the single block reads as performed by index scans.
These discrepancies in costs and trying to make a level playing field when comparing the multiblock I/Os costs associated with FTS vs. the single block I/Os costs associated with index scan is precisely what the optimizer_index_cost_adj parameter was designed to addressed.
Rather than treat both types of I/Os as being the same, which is the default behaviour with the I/O costing model, the optimizer_index_cost_adj parameter is designed to adjust the single block read costs to ensure that they are indeed costed as being (say) 1/2 the cost as that of a typical multiblock I/O.
However, when using the CPU costing model, the optimizer_index_cost adj parameter is effectively redundant as the necessary adjustments are already incorporated into the final costs. The total time required to perform a multiblock read operation is divided by the time it takes on average to perform a single block read operation. Using the optimizer_index_cost_adj parameter, although supported and permissible, will likely result in the final CBO costs being adjusted inappropriately as the index related single block I/Os will “double-dip” and potentially reduce both as a result of the system statistic differences between sreadtim and mreadtim and also as a result of the optimizer_index_cost_adj parameter as well.
The system stats are much preferred provided they’re accurate and kept reasonably up to date, because one doesn’t need to “manually” change any associated database parameter.
Not only are the comparative differences between sreadtim and mreadtim maintained, but so are other useful system statistics such as thembrc statistic to be discussed next.
So in summary, when using the CPU costing model, do not set the optimizer_index_cost_adj parameter at all. Leave it alone, collect representative system statistics and let the system statistics look after the comparative costs between single and multiblock I/Os for you automatically.
参考至:http://richardfoote.wordpress.com/2009/12/14/the-cpu-costing-model-a-few-thoughts-part-ii/
如有错误,欢迎指正
邮箱:czmcj@163.com
相关推荐
the document is used for oracle project costing implementation
Title: Dynamic Costing Author: Troels Troelsen Subjects: Business, Economics Key words: business, finance, value Education Level: Higher Education License: All Rights Reserved - Standard ...
SAP 内置TM管理 ,比较实用 ,没有那么多的无用理论,直接截图,简单粗暴。SAP 内置TM管理 ,比较实用 ,没有那么多的无用理论,直接截图,简单粗暴。SAP 内置TM管理 ,比较实用 ,没有那么多的无用理论,直接截图,...
The trees are managed, and branches are grafted as necessary, so that navigating down the tree to find a value and locate a specific record takes only a few page accesses. Because the trees are ...
QAD Costing QAD Costing QAD Costing
Oracle Project Costing User Guide Release 11i Part No. B10855-02
很好的资料,详细介绍成本核算单的原理与后台配置!
• Consumer Loan Assistant – Helps you see just how much those credit cards are costing you. • Flash Card Math Quiz – Lets you practice basic addition, subtraction, multiplication and division ...
the perspective of a software developer, but rather that of a Web-page author. I don’t spend a lot of time discussing BNF grammars or parsing element trees. Instead, I show you how you can use XML ...
Research has shown that if a customer arrives and there are no beds available, the customer will turn around and leave, thus costing the company a sale. Your task is to write a program that tells the...
活动成本及活动管理activity based costing and activity based management
CoP Cost Management_Standard Costing,CoP Cost Management_Standard Costing,CoP Cost Management_Standard Costing
The cloud offers information technology workers significant cost savings and agility unimaginable even just a few years ago. Tasks that traditionally took weeks of work, costing thousands of dollars,...
Product_Costing
In the wake of the indie game development scene, game development tools are no longer luxury items costing up to millions of dollars but are now affordable by smaller teams or even individual ...
PP Product Costing To learn about SAP PP product costing
录像61_costing by period3
录像60_costing by period2
录像59_Costing by period1
录像62_costing by period4