tag:blogger.com,1999:blog-5124641802818980374.post3867978810099838643..comments2015-06-30T18:01:30.807+02:00Comments on Oracle related stuff: Understanding the different modes of System Statistics aka. CPU Costing and the effects of multiple blocksizes - part 2Randolfnoreply@blogger.comBlogger5125tag:blogger.com,1999:blog-5124641802818980374.post-34311731742579817702009-05-06T16:04:00.000+02:002009-05-06T16:04:00.000+02:00Randolf,
I now agree with the fact that my "Estim...Randolf,<br /><br />I now agree with the fact that my "Estimated MBRC" is incorrect i.e. there is no such thing. I think I read 2 different documents and got them mixed up a bit. What I have labelled "Estimated MBRC" is really MBRC adjusted to be in units of single block reads. Which is exactly what you said in your last comment - that all I/Os are costed in units of single block reads. As I said I think I read 2 different documents, one of which had this MRT / SRT adjustment in it, and somehow combined the two together in my mind.<br /><br />I think I now have this straight, and have changed my own notes to remove this "Estimated MBRC" thing.<br /><br />Thanks for taking the time to respond to my comments and helping clarify what we are both talking about.<br /><br />JohnJohn Bradyhttp://www.blogger.com/profile/08719865814813032071noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-34032676534312164492009-05-06T12:53:00.000+02:002009-05-06T12:53:00.000+02:00John,
thanks for the clarification.
I didn't use...John,<br /><br />thanks for the clarification.<br /><br />I didn't use brackets since I was under the impression it is then clear that the evaluation is supposed to be strictly from left to right and I don't see why this shouldn't be the case given the operators of the formula.<br /><br />I'm not sure what you attempt to express using your "estimated MBRC" value.<br /><br />The meaning of the formula used to calculate the I/O cost of a full table scan with system statistics available is the following as outlined in part 1:<br /><br />number of multi-block reads * MREADTIM / SREADTIM<br /><br />Using the first part only:<br /><br />number of multi-block reads * MREADTIM<br /><br />We can already deduce the estimated time in ms it's going to take to complete the full table scan.<br /><br />The "number of multi-block reads" is simply "blocks below HWM" divided by the MBRC (And by the way this is where in traditional I/O costing the "adjusted MBRC" comes into the picture and the calculation simply ends at that point for traditional I/O costing).<br /><br />The time is then divided by SREADTIM to get back to the "traditional" unit used so far for costing in Oracle, which is units of single block read requests, see e.g. Jonathan Lewis quick <A HREF="http://jonathanlewis.wordpress.com/2006/12/11/cost-is-time/" REL="nofollow">note</A> on this.<br /><br />So given this meaning I'm not sure what your "estimated MBRC" is supposed to represent.<br /><br />I brought this MREADTIM / SREADTIM factor up because according to the formula it describes the expected difference in costs of a full table scan between traditional I/O costing and CPU costing, but as outlined in part 1, you have to consider the "adjusted MBRC" used for traditional I/O costing to understand why the difference between traditional and CPU I/O cost is not consistent with simply MREADTIM / SREADTIM.<br /><br />Regards,<br />RandolfRandolfhttp://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-89060583840018445042009-05-06T11:59:00.000+02:002009-05-06T11:59:00.000+02:00Randolf,
I think we do agree on the formula, and ...Randolf,<br /><br />I think we do agree on the formula, and just disagree on how best to write it down. That is why I often use brackets to make such formula more explicitly clear.<br /><br />Your calculation of 1402 is the same as mine - so we do agree. Maybe you would consider modifying how your formula is written.<br /><br />Your 3 step calculation can be written as:<br />1. Blocks / MBRC<br />2. (Blocks / MBRC) * MREADTIM<br />3. ((Blocks / MBRC) * MREADTIM) / SREADTIM<br /><br />I am using the brackets for clarity. Expanding this it comes out to be:<br /><br />(Blocks * MREADTIM) / (MBRC * SREADTIM)<br /><br />(10000 * 16.241) / (8 * 14.478) = 162410 / 115.824 = 1402.21<br /><br />And you can see how my "Estimated MBRC" formula is divided into Blocks, resulting in the expanded formula just shown. So we do agree on the formula and calculation, but maybe not on the best way to write it down.<br /><br />In Christian's book on page 177 is the following formula. I am modifying it to include brackets, because in the book it is formatted over two lines i.e. blocks appears over mbrc, and mreadtim appears over sreadtim.<br /><br />io_cost ~= (blocks / mbrc) * (mreadtim / sreadtim)<br /><br />It is just that when I see mathematical formulae it is sometimes difficult to know if the calculation is performed in a strictly left to right fashion, which is what I think you meant, or whether things group in a certain way i.e. the value of that before the '/' is divided by the value of everything after it.<br /><br />Hope that all makes sense.<br /><br />JohnJohn Bradyhttp://www.blogger.com/profile/08719865814813032071noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-71660943024470384682009-05-05T18:14:00.000+02:002009-05-05T18:14:00.000+02:00John,
as already mentioned in the part 1 comments...John,<br /><br />as already mentioned in the part 1 comments, we're talking here about different things. The "adjusted" MBRC refers to the traditional I/O based costing.<br /><br />I can't follow your example why the calculation you've mentioned should be wrong:<br /><br />Doing step by step and not inversing the meaning by introducing brackets:<br /><br />10,000 / 8 = 1,250<br /><br />1,250 * 16.241 = 20,301.25<br /><br />20,301.25 / 14.478 = 1,402.2<br /><br />which shown as 1,403 in the cost.<br /><br />Regarding your "estimated MBRC", can you quote where you have this from Christian's book?<br /><br />Best Regards,<br />RandolfRandolfhttp://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-6300055230368538682009-05-05T16:46:00.000+02:002009-05-05T16:46:00.000+02:00Randolf,
As I said in a comment on your first pos...Randolf,<br /><br />As I said in a comment on your first post I think your "adjusted MBRC" formula is wrong. It should be:<br />Estimated MBRC = MBRC * SREADTIM / MREADTIM<br /><br />In your post you have:<br />FTS cost = Blocks below HWM / MBRC * MREADTIM / SREADTIM<br /><br />I assume the double divide by SREADTIM means that it ends up being a multiply i.e. when you divide Blocks by (MBRC * MREADTIM / SREADTIM), the SREADTIM ends up on top, multiplying against Blocks.<br /><br />Your calculation seems wrong to me. You state:<br />FTS cost = 10,000 / 8 * 16.241 / 14.478 = 1,403<br /><br />But breaking it into steps I get a completely different result:<br />10000 / (8 * 16.241) = 10000 / 129.928 = 76.966<br />76.966 * 14.478 = 1114.31<br /><br />This is not 1403 as you state.<br /><br />Using my formula instead for the Estimated MBRC, then:<br />EMBRC = 8 * 14.478 / 16.241 = 7.132<br />FTS Cost = 10000 / 7.132 = 1402.13<br /><br />Which is pretty close to the reported cost of 1403 by Oracle itself. In fact I did get 1403 when I used a calculator, and did not round to 3 decimal places.<br /><br />I hope this helps with your understanding of how the optimiser does some of its costs. I'll try and get around to posting my findings sometime.<br /><br />John<br /><A HREF="http://databaseperformance.blogspot.com" REL="nofollow">Database Performance Blog</A>John Bradyhttp://www.blogger.com/profile/08719865814813032071noreply@blogger.com