It’s called the CPU Costing model because among other things, it includes the time associated with performing CPU operations.
The CPU Costing model formula once again:
(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
So the portion detailing the sum of all required CPU cycles divided by the CPU cycles per second can potentially contribute a significant proportion of the overall costs.
When I previously discussed the costs associated with the CPU model between using an index and a FTS, the FTS example I used had an overall cost of 70 but I calculated that the I/O component costs were only 67. Therefore the costs directly related to CPU operations with the FTS example was 3.
However, these CPU specific costs in this example may vary from database to database, although the FTS might be identical as might the required CPU cycles. However, a variable in all this is the CPU cycles per second system statistic (CPUSPEED) associated with a particular database environment.
Obviously, the faster the CPUs, the quicker it can perform the necessary CPU operations associated with the FTS (or any operation for that matter). Conversely, the slower the CPUs, the longer it will take to complete the necessary CPU related operations. The CPU costing model formula automatically takes all this into consideration.
In the previous example, the CPUSPEED system statistic was 1745.
Let’s now run the identical FTS but this time with a faster and a slower CPU and see how this might adjust the overall related costs when using the CPU costing model.
One can simulate a “faster” CPU by simply adjusting the CPUSPEED system statistic. Let’s make the CPUs appear 10 times faster:
SQL> exec dbms_stats.set_system_stats(pname=>’cpuspeed’, pvalue=>17450);
PL/SQL procedure successfully completed.
OK, let’s now see how this impacts the cost of the FTS:
SQL> SELECT * FROM bowie_stuff WHERE id = 420;
1000 rows selected.
Execution Plan
———————————————————-
Plan hash value: 910563088
——————————————————————-
|Id|Operation |Name |Rows|Bytes|Cost (%CPU)|Time |
——————————————————————-
| 0|SELECT STATEMENT | |1000|18000| 67 (0)|00:00:01|
|*1| TABLE ACCESS FULL|BOWIE_STUFF|1000|18000| 67 (0)|00:00:01|
——————————————————————-
We notice that the overall cost has reduced from 70 down to 67. The cost of 3 that was previously attributed to just the CPU related costs have all disappeared and the costs are now just the 67 in relation to the I/O component.
The CPU is now so fast that it can effectively perform all the necessary operations in a negligible amount of time. An even faster CPU will not further improve the costs associated with this FTS as the costs now only include the I/O related components.
The (%CPU) value of (0) gives us this information if you didn’t follow how I derived the I/O cost of 67 in my previous post.
If we go the other way and now make the CPU about 1/10 the speed of the original example:
SQL> exec dbms_stats.set_system_stats(pname=>’cpuspeed’, pvalue=>175);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM bowie_stuff WHERE id = 420;
1000 rows selected.
Execution Plan
———————————————————-
Plan hash value: 910563088
——————————————————————-
|Id|Operation |Name |Rows|Bytes|Cost (%CPU)|Time |
——————————————————————-
| 0|SELECT STATEMENT | |1000|18000| 93 (28)|00:00:01|
|*1| TABLE ACCESS FULL|BOWIE_STUFF|1000|18000| 93 (28)|00:00:01|
——————————————————————-
We now notice the overall costs have jumped up considerably up from 70 up 93.
The costs associated directly with CPU activities have now increased up from 3 to 26. The CPU component is in the ballpark of 10 times as expensive/significant when you take into account rounding errors (the original 3 value was rounded accordingly). Remember also that these figures are times expressed in units of time it takes to perform a single block I/O.
The CPUs are now so slow that it takes a considerable amount of time to complete all the required CPU operations.
Note that the (%CPU) value is now a significant (28%) of the overall costs as derived from the following formula:
round(cpu related cost/total cost) x 100 = round(26/93 x 100) = 28.
So having a faster (or possibly slower) CPU when performing a hardware upgrade/change can result in potentially different execution plan costings (and as such different execution plans) when using the CPU CBO costing model.
It’s called the CPU costing model for a reason and as one would indeed hope, the speed of said CPU(s) can directly impact the associated costs and decisions made by the CBO.
如有错误,欢迎指正
邮箱: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 ...
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 ...
A clustered index is like a telephone directory in which all of the rows for customers with the same last name are clustered together in the same part of the book. Just as the organization of a ...
Chapter 3: A Study of Function Point Analysis Chapter 4: Data Functions Chapter 5: Transactional Functions Chapter 6: General System Characteristics Chapter 7: Size, Effort, and Scheduling of Projects...
the document is used for oracle project costing implementation
Tasks that traditionally took weeks of work, costing thousands of dollars, can be completed in minutes for a fraction of a penny. This book is a resource for using Microsoft's powerful scripting ...
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 ...
The business owner escalates the urgency of the issue but cannot get past the process boundaries of the enterprise. Ultimately, the business owner establishes an independent IT department funded by ...
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. 工作节奏超过了我们的跟上能力。人工智能有望创造一种全新的工作方式。 微软工作趋势指数年度报告( ...
SAP 内置TM管理 ,比较实用 ,没有那么多的无用理论,直接截图,简单粗暴。SAP 内置TM管理 ,比较实用 ,没有那么多的无用理论,直接截图,简单粗暴。SAP 内置TM管理 ,比较实用 ,没有那么多的无用理论,直接截图,...
The INS is capable of providing continuous estimates of a vehicle’s position and orientation. Typically IMU’s are very expensive sensors, however this INS will use a “low cost” version costing ...
活动成本及活动管理activity based costing and activity based management
Beginning STM32 provides the professional, student, or hobbyist a way to learn about ARM without costing an arm! What You'll Learn Initialize and use the libopencm3 drivers and handle interrupts ...
Oracle Project Costing User Guide Release 11i Part No. B10855-02
QAD Costing QAD Costing QAD Costing
10.5 Sum of the angles in a polygon 95 10.6 The circle 96 11 Areas (1) 101 11.1 Introduction 101 11.2 Area of triangles 102 11.3 Area of quadrilaterals 103 11.4 Area of circles 104 11.5 Application of...
预订工作卡,分配员工以记录在工作上花费的时间,该时间转移到时间表中,并添加成本,例如内置库存系统中的物料。 最后创建与XERO在线会计集成的发票或打印PDF发票。
很好的资料,详细介绍成本核算单的原理与后台配置!