jump to navigation

The CPU Costing Model: A Few Thoughts Part V (Reality) January 13, 2010

Posted by Richard Foote in CBO, Multiblock Reads, Oracle Cost Based Optimizer, Richard's Musings, System Statistics.
trackback

There’s plenty more I could talk about regarding the CBO CPU costing model and system statistics but I’ll make this my final little comment on this subject for now.
 
As previously discussed, the CPU costing model basically takes the time it takes to perform the all necessary I/O related activities and all the time it takes to perform all necessary CPU related activities and adds them together to get the overall time to complete a task. The CBO then takes this total and divides it by the average time to perform a single block I/O so that it expresses the overall costs in units of single block I/Os.
 
There are two advantages with expressing CBO costs in this manner.
 
Firstly, it makes the move from the old I/O costing model a little easier in that the “units” of cost under both CBO costing models is very similar.
 
With the I/O costing model, the unit of cost was also basically the number of I/Os. It’s just that the CBO made no (automatic) distinction between the I/O costs associated with single and multiblock reads. The cost was simply the expected total number of I/Os for a given execution plan, with single block and multiblock I/Os being consider the same (unless the optimiser_index_cost_adj parameter kicked in).
 
With the CPU costing modelling, the costs are expressed specifically in units of single block I/Os. However, the CBO automatically takes into consideration and differentiates the relative costs associated with multiblock I/Os (and CPU operations) and incorporates them automatically into the final cost.

The other nice advantage is that one can use the actual cost values as an indication of how long an operation or execution plan is likely to take. The overall execution times of the plan are divided by the average time of a single block I/O when using the CPU costing formula. Therefore by multiplying these cost values out again by the average time of a single block I/O (SREADTIM system statistic), one can have an indicative idea of the overall expected execution time.
 
The overall execution times as estimated by the CBO using the CPU costing model is therefore basically = cost of execution plan multiplied by SREADTIM system statistic.
 
Using my previous example with the FTS where the overall cost of the execution plan was 70, and the SREADTIM system statistic was 5:
 
the overall execution time as estimated by the CBO is approximately 70 x 5 = 350 ms.
 
Now this of course is only an indicative value. As all system related statistics are simply averages, there could obviously be discrepancies with how long specific I/Os take to actually perform, the size and number of specific multiblock read operations, etc. There may also be caching characteristics of objects that may influence the actual number of physical reads and associated wait times, it doesn’t take into consideration time taken to actually return data to the “client”, etc. etc. etc.
 
However, it provides one with a rough “ballpark figure”. If the actual executions times in the above example were (say) 20 seconds, then it’s a strong indication that the CBO may have got it wrong, that it may have calculated the wrong cost and maybe as a result the wrong execution plan. Somewhere, something such as the segment statistics, the system statistics, optimizer parameters, etc. may be inaccurate and is causing the CBO to get its costings incorrect.
 
The CBO cost value doesn’t compare well to reality and so is perhaps worthy of further investigation.
 
The cost values associated with CPU costing model is not some random, ambiguous, mysterious number but a value that can often be derived and which can be most useful in determining and resolving problematic SQL statements and execution plans.

About these ads

Comments»

1. Illiyaz Mohammad - January 19, 2010

How do you know so much Richard????? i am always spell bound with the content that you provide … i am seriously amazed by the amount of knowledge you ( includes Jonathan, tanel,cary,anjo kolk, tom kyte) guys have…why cant you let us know the secret of it ???????????

Richard Foote - January 24, 2010

HI Illiyaz

Thank-you very much for the compliment :)

Having had the pleasure of meeting each of the folk you’ve mention, I can assure you I don’t know anywhere near as much as those guys.

I know what I know by having the opportunity to work with Oracle for nearly 14 years and having the practical curiosity of wanting to know how Oracle actually works so I can do my DBA activities to the best of my ability.

There’s no secret really, it just takes that curiosity to investigate and research how the database actual works. I can’t “fix” it unless I know why it’s broken and guessing and hoping something will work is no way of making a living as a DBA :)

2. illiyaz mohammad - January 27, 2010

Bowled over with your humble response…well i cant guarantee that i will be anywhere near your level after a few years but i will keep your advice on the top of my head always…..just blessed to have got hold of your blog and learn some of the best stuff…keep up the good work richard…and i am waiting to see how you explain read consistency from an index perspective…

Richard Foote - February 3, 2010

Hi Illiyaz

I’m getting there. Just been on a week’s holiday and I still have one white paper to go.

Please be patient !!

3. Blogroll Report 08/01/2009 – 15/01/2010 « Coskan’s Approach to Oracle - February 3, 2010

[...] 15-CPU cost and execution time relationship Richard Foote-The CPU Costing Model: A Few Thoughts Part V (Reality) [...]


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: