There’s plenty more I could talk about regarding the CBO CPU costing model and system statistics but I’ll make this my final little comment on this subject for now.
As previously discussed, the CPU costing model basically takes the time it takes to perform the all necessary I/O related activities and all the time it takes to perform all necessary CPU related activities and adds them together to get the overall time to complete a task. The CBO then takes this total and divides it by the average time to perform a single block I/O so that it expresses the overall costs in units of single block I/Os.
There are two advantages with expressing CBO costs in this manner.
Firstly, it makes the move from the old I/O costing model a little easier in that the “units” of cost under both CBO costing models is very similar.
With the I/O costing model, the unit of cost was also basically the number of I/Os. It’s just that the CBO made no (automatic) distinction between the I/O costs associated with single and multiblock reads. The cost was simply the expected total number of I/Os for a given execution plan, with single block and multiblock I/Os being consider the same (unless the optimiser_index_cost_adj parameter kicked in).
With the CPU costing modelling, the costs are expressed specifically in units of single block I/Os. However, the CBO automatically takes into consideration and differentiates the relative costs associated with multiblock I/Os (and CPU operations) and incorporates them automatically into the final cost.
The other nice advantage is that one can use the actual cost values as an indication of how long an operation or execution plan is likely to take. The overall execution times of the plan are divided by the average time of a single block I/O when using the CPU costing formula. Therefore by multiplying these cost values out again by the average time of a single block I/O (SREADTIM system statistic), one can have an indicative idea of the overall expected execution time.
The overall execution times as estimated by the CBO using the CPU costing model is therefore basically = cost of execution plan multiplied by SREADTIM system statistic.
Using my previous example with the FTS where the overall cost of the execution plan was 70, and the SREADTIM system statistic was 5:
the overall execution time as estimated by the CBO is approximately 70 x 5 = 350 ms.
Now this of course is only an indicative value. As all system related statistics are simply averages, there could obviously be discrepancies with how long specific I/Os take to actually perform, the size and number of specific multiblock read operations, etc. There may also be caching characteristics of objects that may influence the actual number of physical reads and associated wait times, it doesn’t take into consideration time taken to actually return data to the “client”, etc. etc. etc.
However, it provides one with a rough “ballpark figure”. If the actual executions times in the above example were (say) 20 seconds, then it’s a strong indication that the CBO may have got it wrong, that it may have calculated the wrong cost and maybe as a result the wrong execution plan. Somewhere, something such as the segment statistics, the system statistics, optimizer parameters, etc. may be inaccurate and is causing the CBO to get its costings incorrect.
The CBO cost value doesn’t compare well to reality and so is perhaps worthy of further investigation.
The cost values associated with CPU costing model is not some random, ambiguous, mysterious number but a value that can often be derived and which can be most useful in determining and resolving problematic SQL statements and execution plans.
参考至:http://richardfoote.wordpress.com/2010/01/13/the-cpu-costing-model-a-few-thoughts-part-v-reality/
如有错误,欢迎指正
邮箱:czmcj@163.com
相关推荐
Title: Dynamic Costing Author: Troels Troelsen Subjects: Business, Economics Key words: business, finance, value Education Level: Higher Education License: All Rights Reserved - Standard ...
the document is used for oracle project costing implementation
Visual C# Homework Projects: A Computer Programming Tutorial by Philip Conrod English | 7 Aug. 2017 | ASIN: B074N8QQG3 | 920 Pages | AZW3 | 23.48 MB Visual C# programming concepts are taught while ...
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 ...
SAP 内置TM管理 ,比较实用 ,没有那么多的无用理论,直接截图,简单粗暴。SAP 内置TM管理 ,比较实用 ,没有那么多的无用理论,直接截图,简单粗暴。SAP 内置TM管理 ,比较实用 ,没有那么多的无用理论,直接截图,...
活动成本及活动管理activity based costing and activity based management
FP法软件评测的经典书籍,由印度软件巨头公司InfoSys的副总裁执笔。行文生动,图表并茂,理论实践... A Certified Quality Analyst (QAI), he has twice been honored with the prestigious Infosys Excellence Award.
QAD Costing QAD Costing QAD Costing
Oracle Project Costing User Guide Release 11i Part No. B10855-02
很好的资料,详细介绍成本核算单的原理与后台配置!
预订工作卡,分配员工以记录在工作上花费的时间,该时间转移到时间表中,并添加成本,例如内置库存系统中的物料。 最后创建与XERO在线会计集成的发票或打印PDF发票。
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,...
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...
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 ...
I see these scenarios on a daily basis, and I don’t see a clear solution to the problem unless the entire process and structure in which these organizations operate is revamped, or technology like ...
CoP Cost Management_Standard Costing,CoP Cost Management_Standard Costing,CoP Cost Management_Standard Costing
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 ...
The pace of work is outpacing our ability to keep up. AI is poised to create a whole new way of working. 工作节奏超过了我们的跟上能力。人工智能有望创造一种全新的工作方式。 微软工作趋势指数年度报告( ...
PP Product Costing To learn about SAP PP product costing