In the coming days, I’ll post a series of little entries highlighting a specific point in relation to the use of system statistics and the CPU cost model. In myprevious post, we looked at how the cost of a FTS is calculated using the CPU costing model and how this generally results in an increase in the associated FTS cost over the I/O costing model.
The table in my demo though had an index with an appalling clustering factor and even though the cost of the FTS increased substantially from 33 to 70, this cost was still significantly less than the large cost associated with using such an inefficient index. So in that specific example, the change of FTS costs as introduced with the CPU costing model made no difference to the final execution plan.
The key point I want to emphasise with this post, is that by increasing FTS costs as is common with the CPU costing model over the I/O costing model, this can of course potentially result in entirely different execution plans, especially if a candidate index has a reasonable clustering factor. Substantially increasing the associated costs of a FTS can be very significant, especially where the difference in costs between a FTS and an index can be much narrower for well clustered indexes.
In this previous I/O Costing Model example using the BOWIE_STUFF2 table, the index had an excellent clustering factor. However the query resulted in a FTS as the cost of 65 was just a little less than using an associated index:
SQL> select * from bowie_stuff2 where id in (20, 30, 40, 50, 60);
10000 rows selected.
Execution Plan
———————————————————-
Plan hash value: 573616353
——————————————————————
| Id | Operation | Name | Rows | Bytes | Cost |
——————————————————————
| 0 | SELECT STATEMENT | | 10000 | 175K| 65 |
|* 1 | TABLE ACCESS FULL| BOWIE_STUFF2 | 10000 | 175K| 65 |
——————————————————————
Remember, this was “addressed” and the CBO started using the index, by manually adjusting the optimizer_index_cost_adj parameter from its default value to a value of 75 as explained in this previous post on the effects of the optimizer_index_cost_adj parameter.
However, with system stats and the use of the CPU costing model, the extra FTS cost can have a direct impact on the resultant execution plan. Running the same query again, but this time without changing any optimizer parameters and using the same system stats as in my last post on the CPU Costing Model:
PNAME PVAL1 -------- ----- SREADTIM 5 MREADTIM 10 CPUSPEED 1745 MBRC 10
SQL> select * from bowie_stuff2 where id in (20, 30, 40, 50, 60);
10000 rows selected.
Execution Plan
———————————————————-
Plan hash value: 2964430066
———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 10000 | 175K| 69(2)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2 | 10000 | 175K| 69(2)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | BOWIE_STUFF2_I | 10000 | | 25(0)| 00:00:01 |
———————————————————————————————–
We notice that the CBO has now chosen the index automatically, without having to make any changes to the optimizer_index_cost_adj parameter at all.
Previously, the FTS costs were 65. However, the current costs for a FTS are at least:
(ceil(blocks/mbrc) x mreadtime) / sreadtime = (ceil(659/10) x 10) / 5 = 132.
132 is already way greater than the 69 cost associated with using the above index and the 132 cost doesn’t even take into consideration any additional costs related to CPU usage.
So in general, using the CPU costing model will likely increase the associated costs of FTS, making indexes automatically more “attractive” to the CBO as a result. This change alone in how the FTS in particular is costed using the CPU costing model can have a major impact in execution plans chosen by the CBO. This is but one of the key reasons why things can change so dramatically when moving from 9i to 10g where the CPU costing model is the default.
参考至:http://richardfoote.wordpress.com/2009/12/08/the-cpu-costing-model-a-few-thoughts-part-i/
如有错误,欢迎指正
邮箱: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
To grasp the concepts presented in VISUAL C# HOMEWORK PROJECTS, you should possess a working knowledge of Windows and have had some exposure to Visual C# programming (or some other programming ...
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 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 ...
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...
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