Merry Christmas (Peace On Earth) December 23, 2009
Posted by Richard Foote in Christmas, David Bowie, Richard's Musings.7 comments
Wow, yet another year almost behind us and what looks like being yet another busy year ahead. Can you believe that The Noughties are nearly all gone, I remember being all excited at the dawn of The Eighties !!
I must say I’m really looking forward to finally saying “Twenty Ten” rather than the more long-winded “Two Thousand and Nine” 🙂
I just want to wish everyone a very very Merry Christmas and a fantastic, fun-filled, index rebuild free, very very happy New Year in 2010.
Hopefully next year, Australia will win the Football World Cup, Crystal Palace will finally get promotion to the Premier League, England will be 4-0 down in The Ashes (again) with just one match to go, David Bowie will release a new album and the world will be just that little bit cooler and less polluted.
Here’s hoping anyways, as I’m sure are all the Polar Bears as well; certainly the Aussie, Crystal Palace supporting ones with good taste in music 😉
As is my custom, no Christmas would be complete without a bit of David Bowie and Bing Crosby to bring some good cheer. This year, a lovely fully restored version of their classic performance for your viewing pleasure:
“Our finest gifts we bring, pa rum pum pum pum” indeed 🙂
The CPU Costing Model: A Few Thoughts Part III (Bang Bang) December 21, 2009
Posted by Richard Foote in CBO, Multiblock Reads, System Statistics.add a comment
One of the advantages of system statistics and the CPU costing model is in how the CBO deals with multiblock reads and the impact of the db_file_multiblock_read_count parameter.
When performing a full table (or fast full index) scan, the CBO needs to determine just how many multiblock read operations are likely to be performed so the associated operation can be costed correctly. The CBO simply takes the number of blocks to be read (e.g. for a table, BLOCKS in dba_tables), and divides this by the “effective” multiblock read count.
With the I/O costing model, the effective multiblock read count value is derived directly from the db_file_multiblock_read_count parameter. However, the CBO doesn’t use the actual db_file_multiblock_read_count value as the CBO knows that most multiblock read operations are unlikely to read the maximum possible number of blocks.
This is due to a number of factors. For example a multiblock read operation can’t span across extent boundaries. The more common reason however is that Oracle will not read a block into the buffer cache that is already cached and so will split a multiblock read operation at the point where a block is already in cache. Therefore, if the db_file_multiblock_read_count is set to (say) 16 blocks, many of the actual multiblock read operations may read something less than the maximum 16 blocks if some of the blocks in the table are already cached. So if within the next 16 possible blocks to be read, the 6th block is already in cache, the multiblock read will only consist of the 5 uncached blocks and the next mutliblock read will commence from the 7th block.
The CBO takes this into consideration and therefore doesn’t simply divide the blocks in the table by the full db_file_multiblock_read_count value. It uses a “fudged” or an “adjusted” multiblock read value in its calculations. Jonathan Lewis discusses this very well in his Cost-Based Oracle Fundamentals book and lists how the adjusted multiblock read value for the following examples is adjusted:
db_file_multiblock_read_count of 8 is adjusted to 6.59
db_file_multiblock_read_count of 16 is adjusted to 10.40
db_file_multiblock_read_count of 32 is adjusted to 16.39
db_file_multiblock_read_count of 64 is adjusted to 25.84
Now these adjusted values are nothing more than globally implemented “guesses”, adjusted with the assumption that the larger the multiblock read operation, the more likely a block will actually be cached and the less likely the maximum possible multiblock read is going to be actually performed. Not only are these adjusted values simply guesses, but they’re derived directly from the db_file_multiblock_read_count parameter. By increasing this parameter, you directly impact the actual costs associated with FTS as the value by which to divide the number of blocks in the segment is impacted. Adjusting this parameter needs to be very carefully considered as it not only changes the number of blocks that might be read per multiblock read but the associated CBO costs as well. Simply increasing the db_file_multiblock_read_count blindly might lead Oracle to start favouring FTS operations inappropriately, as the increase might not actually result in fewer, more efficient multiblock reads but it will result in lower FTS costs.
System Statistics and the CPU costing model simplifies and addresses some of these issues.
The first point is that the CBO no longer uses a blind guess based on the size of the db_file_multiblock_read_count parameter, but rather an “educated” guess based on the actual average size of multiblock read operations in the specific database environment. The MBRC system statistic is the actual average size of a multiblock read operation during the period in which system statistics are collected.
Like any average or single generic figure, it will not always be right but at least it’s based on the actual average multiblock read size in the specific database environment. You can’t really ask more from an average figure than to use the actual average figure in your environment.
However, the other important point is that simply increasing the value of the db_file_multiblock_read_count parameter is no longer quite so dangerous as it will not now directly impact the costs of multiblock read operations if indeed increasing the parameter has no such or very minimal effect. Only if the average MBRC is actually changed and reflected in any updated system statistics will the subsequent CBO costs of FTS be adjusted.
Therefore, you can potentially apply one of 2 strategies when setting the db_file_multiblock_read_count parameter with the CPU costing model:
1) Simply set it to the highest supported value in your environment and get the “biggest bang for your buck” while the actual average MBRC is automatically captured by the system statistics and used for costing purposes by the CBO, or
2) Simply leave the parameter unset and let Oracle automatically determine and set the parameter to the largest physical read size supported by your environment (recommended)
Either way, you allow the multiblock reads to be as large and efficient as possible, you allow the actual average multiblock read size to be as large and efficient as possible, but the CBO will only use the actual average multiblock read that is achieved in your database environment when determining the cost of FTS operations. This means multiblock reads will be as large and as efficient as possible but will likely be costed appropriately by the CBO.
Not only are the I/O costs relating to multiblock reads likely to be more accurate in general as a result of using system statistics, but just as importantly, they also take into consideration the actual CPU related costs of such operations and automatically incorporate these into the final costs as well.
To be discussed next.
So in summary, when using CPU costing model, don’t set the db_file_multiblock_read_count parameter, let Oracle determine the maximum optimal size for you and ensure the system statistics are accurate and reflect the actual average MBRC size in your database environment.
Collaborate 2010: Penny Lane December 18, 2009
Posted by Richard Foote in Collaborate 10, Richard's Musings.5 comments
Well, so much for my plans to not travel as much next year …
Found out earlier in the week I’ve now been selected to present at next year’s Collaborate 10 event in Las Vegas. I’ll be presenting a new version of my Indexing Tricks and Traps presentation on Monday, 19 April from 10:45am to 11:45am (Session 302).
I’ve been to Las Vegas a number of times but this will be my first Collaborate conference. Back in 1989, I even managed to leave Las Vegas with more money than when I arrived 🙂
So if you don’t get the chance to catch me at Hotsos Symposium 2010, perhaps I’ll see you at Collaborate 10 !!
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.8 comments
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.
2 Year Anniversary !! December 14, 2009
Posted by Richard Foote in Oracle Indexes.21 comments
A little anniversary slipped by unnoticed this past weekend. Which isn’t really too surprising considering how busy things have been lately, with Christmas just around the corner.
I’ve now been at this blogging lark for 2 whole years !!
Hopefully, there are a few people out there just that little bit wiser now regarding how indexes work in Oracle and a little bit wiser regarding just what a brilliant album Radiohead’s “In Rainbows” really is.
150 posts later and 300,000 plus visits, things are still going strong. Well still going anyways 😉
Thanks to everyone for their comments, contributions and continuing support over the past 2 years.
Doesn’t time fly …
🙂
Hotsos Symposium 2010 December 9, 2009
Posted by Richard Foote in Hotsos Symposium, Richard's Musings.1 comment so far
I’ve attended and presented at numerous Oracle conferences and events over the years but one conference I’ve never had the opportunity to attend is the Hotsos Symposium, held each year in Dallas, Texas, USA. It’s one of the premier Oracle events, especially for those of us DBAs with a special interest in performance tuning and management and so is a conference I’ve always wanted to attend one day.
Therefore, it’s with great pleasure and anticipation that I’ve been invited to present at next years event to be held on 7-11 March 2010. I’ll be presenting 2 presentations, Oracle Indexing Myths and Oracle Indexing Tips and Tricks.
As usual, the list of presenters next year reads like a who’s who of Oracle Performance Tuning experts from all around the world, with Tom Kyte being the Keynote Speaker and Tanel Poder conducting the optional (but highly recommended) training day. Don’t let the fact Tanel looks in the photo on the Hotsos Symposium website very much like Count von Count from Sesame Street put you off 🙂 Having had the pleasure of attending his Advanced Oracle Troubleshooting Seminar, Tanel really knows his stuff.
I look forward to catching up with all of you lucky enough to attend the Hotsos Symposium next year.
The CPU Costing Model: A Few Thoughts Part I December 8, 2009
Posted by Richard Foote in CBO, Oracle Cost Based Optimizer, System Statistics.4 comments
In the coming days, I’ll post a series of little entries highlighting a specific point in relation to the use of system statistics and the CPU cost model. In my previous post, we looked at how the cost of a FTS is calculated using the CPU costing model and how this generally results in an increase in the associated FTS cost over the I/O costing model.
The table in my demo though had an index with an appalling clustering factor and even though the cost of the FTS increased substantially from 33 to 70, this cost was still significantly less than the large cost associated with using such an inefficient index. So in that specific example, the change of FTS costs as introduced with the CPU costing model made no difference to the final execution plan.
The key point I want to emphasise with this post, is that by increasing FTS costs as is common with the CPU costing model over the I/O costing model, this can of course potentially result in entirely different execution plans, especially if a candidate index has a reasonable clustering factor. Substantially increasing the associated costs of a FTS can be very significant, especially where the difference in costs between a FTS and an index can be much narrower for well clustered indexes.
In this previous I/O Costing Model example using the BOWIE_STUFF2 table, the index had an excellent clustering factor. However the query resulted in a FTS as the cost of 65 was just a little less than using an associated index:
SQL> select * from bowie_stuff2 where id in (20, 30, 40, 50, 60);
10000 rows selected.
Execution Plan
———————————————————-
Plan hash value: 573616353
——————————————————————
| Id | Operation | Name | Rows | Bytes | Cost |
——————————————————————
| 0 | SELECT STATEMENT | | 10000 | 175K| 65 |
|* 1 | TABLE ACCESS FULL| BOWIE_STUFF2 | 10000 | 175K| 65 |
——————————————————————
Remember, this was “addressed” and the CBO started using the index, by manually adjusting the optimizer_index_cost_adj parameter from its default value to a value of 75 as explained in this previous post on the effects of the optimizer_index_cost_adj parameter.
However, with system stats and the use of the CPU costing model, the extra FTS cost can have a direct impact on the resultant execution plan. Running the same query again, but this time without changing any optimizer parameters and using the same system stats as in my last post on the CPU Costing Model:
PNAME PVAL1 -------- ----- SREADTIM 5 MREADTIM 10 CPUSPEED 1745 MBRC 10
SQL> select * from bowie_stuff2 where id in (20, 30, 40, 50, 60);
10000 rows selected.
Execution Plan
———————————————————-
Plan hash value: 2964430066
———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 10000 | 175K| 69(2)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2 | 10000 | 175K| 69(2)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | BOWIE_STUFF2_I | 10000 | | 25(0)| 00:00:01 |
———————————————————————————————–
We notice that the CBO has now chosen the index automatically, without having to make any changes to the optimizer_index_cost_adj parameter at all.
Previously, the FTS costs were 65. However, the current costs for a FTS are at least:
(ceil(blocks/mbrc) x mreadtime) / sreadtime = (ceil(659/10) x 10) / 5 = 132.
132 is already way greater than the 69 cost associated with using the above index and the 132 cost doesn’t even take into consideration any additional costs related to CPU usage.
So in general, using the CPU costing model will likely increase the associated costs of FTS, making indexes automatically more “attractive” to the CBO as a result. This change alone in how the FTS in particular is costed using the CPU costing model can have a major impact in execution plans chosen by the CBO. This is but one of the key reasons why things can change so dramatically when moving from 9i to 10g where the CPU costing model is the default.
Richard Foote's Oracle Blog 
