jump to navigation

The CPU Costing Model – A Few Thoughts Part II December 14, 2009

Posted by Richard Foote in CBO, OPTIMIZER_INDEX_COST_ADJ, Oracle Cost Based Optimizer, Oracle Indexes, System Statistics.
trackback

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 the mbrc 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.

About these ads

Comments»

1. Daniel - December 15, 2009

Richard,

What are these little white dots falling through your page?

Are they supposed to be snow flakes?

It really made me think I was still under a hangover from saturday night or that I had some problem with my LCD lol.

2. Dion Cho - December 15, 2009

Yes, the snow falls are interesting. They even follow my mouse cursor!

And a little hacking showed that it’s the wordpress plugin called “snowstorm”. Good for Christmas season. :)

3. Richard Foote - December 15, 2009

Hi Guys

Yes, it’s my little touch of Xmas sparkle, even though the odds of it snowing here in Canberra are not that that good with temps of 37C in the next day or two.

You can spend hours of fun watching the snow chase your cursor :)

Brian Tkatch - December 17, 2009

Well, can you send us in the states some of that (warm!) wonderful sunshine?

Richard Foote - December 18, 2009

Hi Brian

It’s not warm sunshine, it’s hot, really really hot:

http://canberra.iprime.com.au/index.php/news/prime-news/flash-bushfires-break-out-in-3-areas,237951

The Michelago fires are not too far from where I live !!

4. Joze Senegacnik - December 18, 2009

Richard,
Another Oakie Charles Hooper has recently posted a nice collection of faulty quotes about OICA on his new blog. And of course, I agree and share your thoughts about OICA and system stats. That’s exactly what I’m doing for last 5 years.

I started using system statistics back in 2004. At that time we had an interesting discussion about the same topic on Oracle-L with Wolfgang Breitling, see http://www.freelists.org/post/oracle-l/optimizer-index-cost-adj-and-optimizer-index-caching. Since then I have probably never set the OICA to a non-default value. Now, when I can use OPT_PARAM hint I would do it only on statement level.

Richard Foote - December 18, 2009

Hi Joze

Interesting discussion on Oracle-L, thank-you for the link. Charles certainly writes some really good material.

I think the message is certainly getting through that having to tune by globally changing so-called “silver bullet” parameters means one is doing fundamental things wrong in the database to begin with.

5. Blogroll Report 11/12/2009-18/12/2009 « Coskan’s Approach to Oracle - January 3, 2010

[...] 1-Why not to set optimizer_index_cost_adj with CPU costing Richard Foote-The CPU Costing Model – A Few Thoughts Part II [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,703 other followers

%d bloggers like this: