OK, I previously briefly covered how the CBO calculates the basic cost of an index range scan. Yes, those cardinality/rows values in the execution plans are vitally important as they highlight whether or not the CBO has used the appropriate selectivity values in the index costing formula. And yes, the associated cost values are meaningful and potentially useful as they determine the actual costs associated with the execution plan in terms of the expected number of I/Os the CBO estimates will be required (when using the IO costing model and often the CPU costing model as well).
I’m just going to look at another example now using the same table setup as before, but this time running an SQL query that has 5 distinct values in an IN list predicate on our demo table (again, follow the link to see the query and formatted execution plan).
The first thing we notice in this example, is that Oracle has decided to use a FTS rather than use the index on the ID column. Considering we’re only after 5 values out of the possible 100 values, some may not see this as expected behaviour, especially considering the index has such a good Clustering Factor. Basically Oracle is deciding to access each and every block below the HWM of the table, retrieving all 100% of the rows in the table, only to ultimately discard 95% of them.
It certainly appears at first glance to be a more “costly” option than using the index to directly access just the 5% of rows we’re interested in …
The first thing to check is the estimated cardinality figures, to see if the CBO has miscalculated the expected number of rows it needs to retrieve. However, as the statistics have just been fully computed and that the ID column has perfectly even distributed values, we notice the cardinality figures are again spot on. The query returns 10,000 rows and indeed the rows estimate in the execution plan is exactly10,000 rows. The calculation is simply 0.01 (density of column) x 200,000 (rows) x 5 (values in select list) = 10,000.
Let’s now calculate the cost of using the index using our index costing formula, using the CEIL function this time
index blevel + ceil(index selectivity x leaf blocks) + ceil(table selectivity x clustering factor)
2 + 5 x ceil(0.01 x 602) + ceil(0.05 x 854) = 2 + 5 x 7 + 43 = 37 + 43 = 80.
So the cost of using an index range scan to retrieve 5% of the rows in our example comes to a total of 80.
If we look at the cost of the FTS in our explain plan in the above link, we notice the cost is just 65. 65 is less than 80, so the FTS wins.
So how did the CBO come to a cost of just 65 when it has to read all 659 blocks in the table ?
Well, the first missing piece of information is the value of the db_file_multiblock_read_count parameter because this governs how many blocks Oracle will attempt to read within a single logical multiblock I/O call. Remember, when performing a FTS, Oracle knows it has to read all the table related blocks below the HWM and so rather than reading one tiny little block at a time, it does so more efficiently by reading multiple blocks at a time. This is the fundamental advantage of the FTS over the index range scan which can only ever access the one block at a time.
SQL> show parameter db_file_multi
NAME TYPE VALUE ----------------------------- ------- ----- db_file_multiblock_read_count integer 16
So the db_file_multiblock_read_count is 16.
The next thing to note is that it’s very unlikely that Oracle will actually read the full 16 blocks at a time as there are a number of factors that prevents this from occurring. Extent boundaries is one classic example (a multiblock read can not span across extent boundaries) but the more common issue is a block within the table already being stored in the buffer cache. Rather than storing the same block at the same consistent point twice in memory, Oracle breaks up the multiblock read and only reads up to the block that is already cached in the buffer cache. Therefore, for Oracle to actually read the entire table using the full 16 block multiblock I/Os, it would mean there are no cached blocks from the table currently in the buffer cache, an unlikely event.
Therefore, Oracle doesn’t use the full 16 value when determining the number of expected multiblock I/Os, but a modified “fudge” value which equates to approximately 10.4. for a MBRC of 16. Again, Jonathan Lewis in his excellent “Cost-Based Oracle Fundamentals” book discusses all this is some detail.
Remember also that Oracle needs to access the segment header as part of a FTS as I explained is some detail in my “Indexes and Small Table” series. So that’s an additional single block I/O on top of the multiblock I/Os.
Therefore the cost of performing a FTS is:
segment header I/O + ceil(table blocks/fudged mbrc value) = 1 + ceil(659/10.4) = 1 + 64 = 65.
The 65 cost for the FTS does make sense when one understands a little how this value is derived by the CBO …
As the FTS can read big chunks of the table at a time whereas the index range scan can only read each necessary block one at a time, the FTS can indeed read the table and retrieve the required 5% of data in fewer LIOs and so has the lesser associated cost than the index.
Now there are a few issues with all of this. Firstly, is the db_file_multiblock_read_count actually a valid and correct setting as this directly impacts not only the actual size of the multiblock read operations but critically, the associated costs relating to FTS operations (and indeed Fast Full Index Scans as well) ?
Also, is it really correct and valid to assume the cost of a multiblock I/O to be the same and equal to the cost of a single block I/O ? Surely, the process of performing a single block I/O is likely to be “cheaper” than that of a multiblock I/O and yet the CBO treats both types of I/Os as having the same fundamental “cost”.
Also the CPU overheads of having to access each and every row in each and every block is likely going to be more significant than the CPU required to access just specific data from specific blocks when using an index.
Perhaps, the more “expensive” index range scan might actually be a better alternative than the FTS if these factors were taken into consideration ?
Now this may indeed be true, if these factors were correctly taken into consideration. However, this may also indeed be quite false and the FTS may really truly be the better and more efficient alternative and attempts to force the use of the index may be inappropriate and ultimately more expensive.
I’ll next discuss some really bad (although still very common) methods of making the CBO favour indexes, using generally inappropriate so-called “Silver Bullets” …
参考至:http://richardfoote.wordpress.com/2009/06/15/the-cbo-and-indexes-introduction-continues/
如有错误,欢迎指正
邮箱:czmcj@163.com
相关推荐
CBO中Nd:YLF红色激光倍频的全固态330nm紫外激光
A Look under the Hood of CBO - the 10053 Event
matlab巴特沃斯代码CBO4过滤器 使用称为碰撞体优化 (CBO) 的元启发式优化技术,根据整数阶有理近似设计分数阶低通巴特沃斯滤波器的 MATLAB 代码 分数阶滤波器比整数阶滤波器具有更好的滚降频率。 但是实现分数阶...
CBO学习代码 Jonathan Lewis编写
cbooracle cbo
关于oracle的CBO下的HINT总结!
Oracle CBO 学习笔记之(1) : 深入理解Oracle Hash Join的代价模型及其执行流程:word,pdf,图例
cbo数据库优化,数据性能,oracle性能分析优化
cbo CBO-巴西杯足球俱乐部从2020年第1季度开始至在CBO-CBO-巴西巴西杯比赛之前,截止到2020年)。 O Fato de aVERSÃOser 2002não意义上的替代品,algumasocupaçõesforaminclídas。 Apenas o FORMATO de ...
ORACLE CBO RBO 优化
how_cbo_works
Oracle的优化器有两种优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO),在Oracle8及以后的版本,Oracle强列推荐用CBO的方式 ...
平安Oracle CBO 资料.doc
【Maclean Liu技术分享】拨开Oracle CBO优化器迷雾,探究Histogram直方图之秘_0321.pdf
For example, combo box usually begin with cbo (i.e., cboNames) and text boxes start with txt (i.e. txtAddress). When these conventions are used it makes managing the code associated with the controls...
CBO,RBO在ORACLE中的应用
asp.net cbo实现多行选中,里面有案例和dll..可以直接套用。。非常好用
基于CBO的Oracle的成本分析.pdf
Things_You_Should_Know_About_11g_CBO
asp.net cbo实现多行选中,里面有案例和dll..可以直接套用。。非常好用