当前位置:首页 > 开发 > 数据库 > 正文

The CPU Costing Model – A Few Thoughts Part II

发表于: 2014-06-28   作者:czmmiao   来源:转载   浏览次数:
摘要: 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 m

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 CPU Costing Model – A Few Thoughts Part II

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
二、ASP.NET Runtime Pipeline(续ASP.NET Http Runtime Pipeline - Part I) 现在我们真正进入ASP.NE
导读:       二、ASP.NET Runtime Pipeline(续ASP.NET Http Runtime Pipeline - Part I )   
[原文链接:http://engineering.richrelevance.com/recommendations-thompson-sampling/。] [本文链
Previous Article New Features of WCF 4.0: Part I Introduction Microsoft.NET 4.0 and Visual St
Part-I of this article covered cryptography in the context of encryption. This part continues
Playing with ptrace, Part II by Pradeep Padala p_padala@yahoo.com http://www.cise.ufl.edu/~pp
让我们先从一张图片说起: 这幅画是由德国大画家丢勒(Albrecht Dürer)所画,其中布满了数学符号。
Hive Architecture 1. Metastore service, 提供元数据服务,存储可以选择Derby,Mysql, 等其他数据库
ASP.NET Internals – IIS and the Process Model Published: 03 May 2007 By: Simone Busoli ASP.N
LINQ to SQL系列Part 2 - Defining our Data Model Classes 本文转载自: http://www.cnblogs.com/h
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号