MyException - 我的异常网
当前位置:我的异常网» 数据库 » The CBO and Indexes: Introduction Continues

The CBO and Indexes: Introduction Continues

www.MyException.Cn  网友分享于:2014-06-29  浏览:0次
The CBO and Indexes: Introduction Continues …

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

 

文章评论

做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
旅行,写作,编程
旅行,写作,编程
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
代码女神横空出世
代码女神横空出世
为什么程序员都是夜猫子
为什么程序员都是夜猫子
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
漫画:程序员的工作
漫画:程序员的工作
如何成为一名黑客
如何成为一名黑客
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
10个调试和排错的小建议
10个调试和排错的小建议
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
 程序员的样子
程序员的样子
程序员都该阅读的书
程序员都该阅读的书
程序员必看的十大电影
程序员必看的十大电影
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
中美印日四国程序员比较
中美印日四国程序员比较
每天工作4小时的程序员
每天工作4小时的程序员
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
我是如何打败拖延症的
我是如何打败拖延症的
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
我的丈夫是个程序员
我的丈夫是个程序员
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
程序员和编码员之间的区别
程序员和编码员之间的区别
程序员应该关注的一些事儿
程序员应该关注的一些事儿
那些争议最大的编程观点
那些争议最大的编程观点
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
总结2014中国互联网十大段子
总结2014中国互联网十大段子
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
一个程序员的时间管理
一个程序员的时间管理
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有