Announcing Finale (The End) March 31, 2008
Posted by Richard Foote in Humour, Richard's Musings.18 comments
SORRY TO DISAPPOINT, BUT PLEASE NOTE THIS WAS SIMPLY AN APRIL FOOLS JOKE !!!
After much thought, I’ve decided to finally retire from being an Oracle DBA.
Previously, I had hoped to delay this difficult decision, but I’m sure the time is now right.
Recent events have convinced me that I’m just not suited to this role and all this Oracle tuning stuff is too damn hard.
In fact, if you read some of the stuff I’ve written here, it’s clearly not right that I should continue.
Little by little I’ve come to the realisation that it’s best if I simply just call it a day.
Fortunately, some of the recent comments here have really helped me to see the light and for that I sincerely thank them.
Ordinarily, I would try and battle on, but it’s just got to be all too hard for me.
Only time will tell if I’ve made the right decision.
Let me however say a big thank you to all of you who have supported me over the years, it’s been much appreciated.
So that’s it folks, I going back to working in the adult film industry, all the very best for the future …
SORRY TO DISAPPOINT, BUT PLEASE NOTE THIS WAS SIMPLY AN APRIL FOOLS JOKE !!!
Larger Block Index Tablespace and Small Index Scans – Performance Improvement ? (Let Down) March 31, 2008
Posted by Richard Foote in Index Block Size, Oracle General, Oracle Indexes, Oracle Myths, Performance Tuning, Tablespace Management.12 comments
Thought it might be worth looking at the impact on the performance of Unique and small index range scans as typical in OLTP environments, when an index is rebuilt in a larger block tablespace.
Warning, this discussion will again primarily be an exercise in simple mathematics. However, a few little details to start the ball rolling.
I’ve already discussed how in many scenarios, by increasing the index block size, the height of the index can remain unchanged. One can’t simply assume a larger block index results in an index with a lesser height. However, as we shall see, even when indexes do reduce in height, the so-called performance benefits can be somewhat “exaggerated”.
Note also in OLTP environments, the vast majority of index related access paths are either unique scans or small index scans. We’re only interested in a specific customer, in a specific order, in the bank balances of specific accounts, with specific customers buying specific products with specific credit cards, etc. Remember also that say a block size of 8K can generally store many hundreds of index entries per index leaf block and that even a humble little 2K leaf block can often store a three figure number of index entries …
So let’s say in our first example we currently have an 8K index with a height of 3 which we rebuild in a 16K tablespace and the index height remains the same. What are the comparative costs of performing a simple index look-up.
In the 8K index, we need to read 3 x 8K (root, branch and leaf block) to read the index entry of interest for a total of 24K. However, in the new 16K index, we now need to read 3 x 16K (again, a root, branch and index leaf block) to read the index entry of interest, for a total of 48K.
That’s double the potential physical I/O if the blocks are not currently cached, that’s double the memory that needs to be used in the buffer cache to store the index blocks and that’s potentially more CPU we need to use in order to pin and process the larger index blocks.
Hummm, these larger blocks aren’t too impressive so far …
But what if we actually reduce the height after moving an index to a larger block tablespace ?
In the 8K index, we still have our 3 x 8K = 24K. If we rebuild in an 16K block tablespace and reduce it’s height by 1, we now only need to read 2 x 16K = 32K. That’s 3 consistent reads at 24K vs. 2 consistent reads at 32K. Yes, we reduce the number of consistent reads which is a good thing but we still have a larger index footprint which again means possibly more data off disk, more memory and more CPU resources to process the index blocks.
Let’s hope our 2K block index with a height of 2 reduces its height somewhat if we decide to rebuild it in 32K blocks because that’s 2 x 2K = 4K vs 2 x 32K = 64K otherwise. But even if we did, although we reduce the consistent reads, which is a good thing, that’s still 2 x 2K = 4K vs 1 x 32K = 32K.
But we can potentially reduce the height of a 2K index if rebuild in a much larger block size (in specific cases) by more than 1 level, right ?
Yes, a 4 level 2K index, 4 x 2K = 8K, might very well now only require 2 levels in a 32K index, 2 x 32K = 64K. But that’s still 64K of index data we need to access vs. 8K in the smaller block. Consistent reads reduce but our footprint for small index range scans is still larger, sometimes by a considerably amount depending on the change of block size.
You begin to see the issue …
It’s a bit like someone saying they’re going to improve the skyline somewhat and only build appartment buildings that have fewer numbers of floors than previously. But if the floors they build are 2 times or 4 times or even 16 times higher than the previous floors and they only reduce the number of floors by a moderate amount, is the building really lower ?
Have we really reduced the height of the building ?
Yes, the lift only has to stop at fewer floors which can be more “efficient” but it takes longer each time as it goes from one floor to the next. So is it really that much quicker to get to the top, or more specifically in our discussion, from the top to the bottom of the building ?
Surely though, things must run faster, performance must actually improve, things must be more efficient by having larger sized index blocks else why bother ? Why indeed …
This demo on the Performance Impact Of Small Index Scans In A Larger Index Block Size shows how performance may actually worsen, not improve if we move indexes into a larger block size. It creates an index on a well clustered column (so the index is at it’s most efficient and potentially impacts performance the most), first with an 8K block size and then with a 16K block size. The size of the index was carefully chosen so that the larger index block size did indeed reduce the height of the index (although the range of values when this was possible was actually quite limited with these block sizes). A simple PL/SQL procedure then performs a massive number of single row look-ups and the CPU and elapsed times are monitored. The results show when the associated blocks are either cached or not cached, the larger block index incurs larger CPU related costs and results in overall slower response times, despite the fact it actually has a lower height than the smaller block index.
It’s not precisely the same as a large scale environment as PL/SQL has subtle little differences and efficiencies when compared to multiple, separate transactions. Also, it’s not the specific results that are important here but the overall general approach. You can pick any index you want to investigate, you can pick whatever block sizes you may be interested in, you can monitor and benchmark by checking out specific session details or by tracing the specific sessions, you can determine what the comparative costs and response times may be and you can determine what may cause any performance differences by digging deeper into the session statistics or trace files.
No matter what index or block size you select, you will likely come to the same conclusion. The overall performance benefits of your OLTP transactions when you move indexes to a larger block tablespace will likely be “disappointing”.
If a real estate developer comes knocking on your door with a promise that the empty block around the corner will have an apartment building with only a few floors and it shouldn’t ruin the view too much, you may just want to ask them quietly exactly how many floors they’re planning to build and exactly how high each floor will be.
Else the resulting view may turn out more disappointing than you’re led to believe 😉
Store Indexes In A Larger Block Tablespace: Height Reduction 1/2 Myth (Five Foot One) March 26, 2008
Posted by Richard Foote in Index Block Size, Index Height, Index Internals, Oracle General, Oracle Indexes, Oracle Myths, Tablespace Management.14 comments
A common misconception with using a larger block tablespace specifically for indexes is that this will result in a reduction in the height of indexes and hence “flatten” index structures.
However, this is only partly true.
A few little generalisations to begin with.
First, most databases out there have a default block size of 8K. I won’t go into a big discussion on what the database block size should be set to (maybe another time), however I will say most databases these days have a default block size of 8K and that it’s certainly questionable to have the database block size set to 2K.
Note also in many common platforms (e.g. Windows, Linux) the maximum block size limit is 16K. So in many environments, when we talk about moving indexes into a bigger sized block, it specifically involves moving from an 8K to a 16K block size.
Yes, in theory you could move an index from (say) a 2K block size up as high as a 32K block size but you would need to question why the default is so low to begin with and whether the upper value is actually supported in your environment.
I make this point because the difference between block sizes makes a huge difference in the probability of the index height actually being reduced.
So let’s start with an example of moving indexes between an 8K default block size to a 16K block tablespace, not least because the arithmetic is easier and extrapolate out as we go along.
So in our example, the new block size is double or 2 times that of the default one. By doubling the block size, we effectively 1/2 the number of necessary leaf blocks in the index structure. By having fewer leaf blocks we also therefore reduce the overall associated block level overheads so the actual reduction in leaf blocks could be a tad more, but we’ll say a 1/2 reduction to keep the numbers nice and simple.
Note the reduction in leaf blocks in therefore simply 1 / the ratio of block increase (1/2). Moving from a 2K block to a 32K block is 16 times larger so we’ll have approximately 1/16 the number of leaf blocks.
So how does (say) halving the number of leaf blocks impact the overall height of the index ?
We obviously can’t reduce the height of an index with a height of just 1. The index consists of just the one block so a larger block would simply mean the block having more free space.
To reduce the height of an index with a height of 2 (back to 1), we therefore must be able to store all index entries within a single block. Therefore, in the 8K to 16K example, the index can only have 2 full leaf blocks for this to be possible. If an index has 3 or more “filled” leaf blocks, the index must remain at a height of 2 as we can’t fit all the index entries into the single larger index block.
Importantly therefore, all indexes with a height of 2 with more than 2 full leaf blocks would not reduce in height by simply doubling the block size. This could very well be the vast majority of indexes at this level.
For an index with a height of 2, the index must have less full leaf blocks than the ratio of block increase for a height reduction to be possible. In our best case scenario, 2K block to 32K block, any index with more than 16 full leaf blocks would not reduce in height.
To reduce the height of an index with a height of 3 (back to 2), we must therefore be able to store all intermediate branch blocks into the one branch (root) block. When we double the block size, we therefore 1/2 the leaf blocks and 1/2 again the necessary branch blocks. Therefore the necessary branch blocks is 1/(2×2) = 1/4 that of the default block size. Therefore any index with a height of 3 that has more than 4 full intermediate branch blocks will again not reduce in height as again all the necessary branch information would not fit in one root block.
Importantly therefore, all indexes with a height of 3 with more than 4 full intermediate branch blocks would not reduce in height by simply doubling the block size. Again, this could very well be a significant proportion of all indexes at this level. Note also in many databases, the vast majority of indexes have a height of 3 or less so by simply doubling the index block size, most indexes would not reduce in height …
For an index with a height of 3, the index must have less full intermediate branch blocks than the ratio of block increase to the power of 2 for a height reduction to be possible. In our best case scenario, the 2K block to 32K block, only those indexes with more than 16×16=256 full intermediate branch blocks will reduce in height. This is therefore likely to be a far higher proportion of all such indexes.
You see the pattern …
To reduce the height of an index with a height of 4 (back to 3), we must therefore store all first level intermediate branch blocks into the one branch (root) block. When we double the block size, we therefore 1/2 the leaf blocks, 1/2 again the second level intermediate branch blocks and 1/2 again the first level intermediate branch blocks. Therefore the necessary first level intermediate branch blocks is 1/(2x2x2) = 1/8 that of the default block size. Therefore any index with a height of 4 that has more than 8 full intermediate first level branch blocks would again not reduce in height as again all the necessary first level branch information would not fit in the one root block.
Importantly therefore, all indexes with a height of 4 with more than 8 full intermediate branch blocks will not reduce in height by simply doubling the block size. However, as the index height increases, the ratio of indexes where this is likely to be the case decreases.
For our best case scenario, 2K to 32K, we now start hitting very large numbers 16x16x16=4096 so the likelihood of a index height reduction is very very high.
And so on …
The important point being that by simply doubling the index block size, in most databases, the vast majority of indexes are actually quite unlikely to reduce in height as the index needs to be within very limited size boundaries for the index height to reduce. The greater the index height however, the greater the index size boundaries whereby an index height reduction is possible.
Also, the greater the index block increase, proportionally the fewer the index blocks and so greater the likelihood of an index height reduction.
This demo on the Impact Of Block Size On Index Height illustrates that by simply doubling the index block size, the height of an index (in various sizes) rarely decreases.
One final point. With our height 4 index example, note the index can only have a maximum of 8 first level branch blocks for the height to reduce. Therefore, in effect, we’re replacing a maximum of 9 x 8K branch blocks with 1 x 16K block. If this index is frequently accessed, these 9 branch blocks are likely cached and we only need to read two of these blocks anyways for an index range scan (for a total of 16K). After the rebuild, we still need to read this block (16K again) anyways so from a purely performance perspective with regard to just simply reducing the index height, the so-called performance benefits are often very much exaggerated.
As we’ll see in the next epic episode of this series, performance can actually decrease 😦
Next time someone claims moving indexes into a larger block size will decrease the height and flatten an index, remember it really does depend. In many databases, especially when the index block size is just doubled, it’s actually quite surprising just how unlikely it is for an index to actually decrease in height.
Happy Easter Message !! March 20, 2008
Posted by Richard Foote in Uncategorized.1 comment so far
To all of you who celebrate Easter, I hope you have a truly wonderful Easter break.
Try not to eat too much chocolate and don’t waste too much of your precious time rebuilding indexes 🙂
Have fun !!
Store Indexes In A Larger Block Tablespace: The Multiblock Read Myth Part II (The Fly) March 20, 2008
Posted by Richard Foote in Index Block Size, Index statistics, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Myths, Performance Tuning, Tablespace Management.67 comments
Thought I might begin by mentioning a lovely little story by Billy Verreynne in this OTN Forum Thread.
Basically a scientist is doing research on the behaviour of flies. He notes when he opens the jar lid of a trapped fly and claps his hands, the fly takes off and flies away. One day, he decides to pull the wings off the fly. When he claps his hands, the fly just sits there. No matter how loud he claps, other than a slight rocking motion, the fly just doesn’t budge.
Excitedly, he writes in his journal his latest discovery. When you pull the wings off a fly, it goes stone deaf !!
This is soooo funny because this sort of thing happens all the time in the Oracle world (and elsewhere). I see it time after time after time, we’ve all done it.
Hey, I just compressed a segment into one extent and performance improved. Conclusion, storing a segment in one extent improves performance.
Hey, I just separated my indexes from my tables and performance improved. Conclusion, separating indexes from tables improves performance.
Hey, I just moved my indexes into a larger block sized tablespace and my Index Fast Full Scan performance improved. Conclusion, a larger index block size improves Index Fast Full Scan performance.
Lots of flies with no wings. Lots of people thinking flies go deaf when they don’t have wings. An action appears to cause an effect, but does it really …
As Billy himself suggests, the fundamental reason why so many people think “flies with no wings go deaf” is that many don’t understand what’s actually going on. Many simply don’t understand the basic workings, the fundamental processes and mechanisms involved in how Oracle functions or how Oracle performs a specific operation. Tuning by observation, tuning by making change “A” without understanding all the implications of such a change and subsequently making suppositions on the results of such a change, ultimately means we have lots of people thinking flies without wings are deaf.
As I discussed in Part 1, Oracle performs exactly the same sized I/O during a multiblock read, regardless of the block size of the segment. Exactly the same. Without understanding this simple fact, one could very easily come to a wrong conclusion regarding the ramification of block sizes on multiblock reads. Without understanding flies don’t have ears or sound sensors in their wings, one could very well come to a wrong conclusion regarding the ramifications of removing the wings from a fly.
If we perform an Index Fast Full Scan and performance improves, it can’t be because associated multiblock I/Os are more efficient. A fly doesn’t have sound sensors in its wings. There must be another explanation. Conversely, in my example in Part I of this discussion, performance went worse with a larger index block size (as it did in Greg Rahn’s example on this OTN Forum Thread), but again not as a result of multiblock read performance.
So how could the performance of an Index Fast Full Scan change (for better or worse), if one simply rebuilds the index within a larger block size tablespace ? Well there are of course many possible reasons, with the two more obvious explanations being the following:
1) Most randomly inserted indexes have a PCT_USED value ranging between 70-75% as these indexes perform random 50-50 block splits that are subsequently in differing stages of being filled. By rebuilding an index (say back to a default PCTFREE of 10%) one might increase index compactness by say 15% and hence decrease the overall index size (note reduced block overheads may also reduce the index a little as well, depending on index size and differences in block sizes). The Fast Full Index Scan is the access path that potentially benefits most by defragmenting an index as the associated costs are proportional to the overall size of the index. Reducing the size of an index could therefore impact subsequent performance. However, rebuilding the index in the current block size would likely achieve a similar result (plus block overheads), compacting the index and resulting in potentially better performance (although once the index blocks begin to split again, the index would eventually return back to its previous state). Therefore, it’s not the bigger block size but the resultant defragmentation of the index that’s improved matters. The fly isn’t deaf, it just needs its wings to fly …
2) By storing an index in a larger block tablespace, the index must physically be stored on a different database file. This file could be on a faster disk, improving performance, this file could be on a faster part of the disk, improving performance, this file could be on a disk with far less disk contention, improving performance, etc. etc. It’s not the larger block size that’s improved (or worsened) performance, it’s the new physical characteristics of where the index is now stored. If one were to rebuild the index with the current block size and use the same physical characteristics of the larger block index, subsequent performance would likewise increase (or decrease). The fly isn’t deaf, it just needs its wings to fly …
There are many other possible reasons, the system was less busy when using the larger block index, more of the index was physically cached when using the larger block index, etc. etc.
Of course, an Index Fast Full Scan is rarely a scalability issue anyways. Do we really want our applications to perform hundreds of large, concurrent Index Fast Full Scans ? Tuning the application to avoid these overheads should be the focus rather than moving indexes into a larger block tablespace in the vain hope it will improve things dramatically. But that’s the topic of another discussion …
Can the performance of an Index Fast Full scan change after moving the index to a larger block size tablespace. Absolutely. However, it doesn’t necessarily mean such a change in performance is a direct result of the index having a larger block size and that multiblock read performance has improved.
It doesn’t mean moving indexes to larger block size tablespaces suddenly makes Oracle go deaf …
Bzzzzzzzzzzzzzzzzzz
UPDATE: I’ve added this simple little demo that illustrates how performance improves when an index is rebuilt in a larger block tablespace. This will of course suggest to some folk that the larger block tablespace improved the performance but what actually improved things was rebuilding the fragmented index to be a more efficient structure. The larger block tablespace was not the fix, rebuilding the index was the important factor. In fact, by rebuilding the index in the original smaller block tablespace, not only do we also improve performance, but things are further improved as we reduce CPU overheads incurred by the larger block tablespace and as a result elapsed times are further improved.
Marcel Kratochvil: New Oracle Multimedia Blog (Good DBA / Bad DBA) March 19, 2008
Posted by Richard Foote in Oracle Blog, Oracle General.add a comment
Just wanted to quickly mention an excellent new blog, the Oracle Multimedia Blog, that might be of interest to some of you.
It’s run by fellow Canberra resident, Marcel Kratochvil, a well known Oracle identity, who is almost as well known as myself but nowhere near as good looking 😉
Marcel and I go way back, having worked together at Oracle Corporation in the mid 1990’s. Marcel was also my partner in crime when we won the runner’s up award for best paper at Oracle Openworld in Brisbane in 1999 with our paper / theatre production called “Good DBA / Bad DBA”. For those who might remember that fateful day, he was the over-acting “Cowboy”, I was the understated and somewhat professional looking “Airline Pilot” 🙂
Marcel is a most knowledgeable and clever fellow who among his many achievements won the Oracle PL/SQL Developer Of the Year award in 2004 and has recently been made an Oracle Ace.
I wish Marcel the very best with his new Blog and encourage everyone to check it out.
Store Indexes In A Larger Block Tablespace: The Multiblock Read Myth (Karma Police) March 18, 2008
Posted by Richard Foote in Index Block Size, Index statistics, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Myths, Performance Tuning, Tablespace Management.8 comments
One of the great myths surrounding the use of differing block sizes is that storing indexes in larger block sizes somehow dramatically improves the performance of index related multiblock reads.
Oracle performs index multiblock reads when performing an Index Fast Full Scan, when it basically treats the index structure as a skinny version of the table. It reads the entire index structure, multiple blocks at a time, “throwing away” any non leaf blocks as it stumbles across them.
The theory goes that by storing indexes in larger size blocks, we would obviously have fewer index related blocks. If we need to read the entire index, as we do with an Index Fast Full Scan, surely it must be more efficient if we have fewer, larger index blocks.
The evidence looks convincing. Here’s a link to an extract from a book by Robin Schumacher where he clearly shows a dramatic “improvement” by using an index tablespace with double the block size. In one query using an 8K block index, the consistent reads during an Index Fast Full Scan was 421. However, when the same index was recreated as a 16K block index, the same query only used 211 consistent gets, 1/2 of what it was previously.
Conclusive “proof” that the 16K block index improved performance wouldn’t you say ?
Well actually, it’s only conclusive proof that the number of consistent gets has dropped, whether it actually improves “performance” is another thing entirely.
There are a couple of little “details” that many don’t quite appreciate. The devil is always in the details …
The first point to note is that when Oracle performs a multiblock read, it uses the value in the db_file_multiblock_read_count parameter to determine how many blocks to read per multiblock read (with system statistics, Oracle itself can determine how best to set this value).
So if the db_file_multiblock_read_count value were set to say 16, Oracle will attempt to read as many as 16 blocks at a time during a multiblock read operation.
Note this value is based on the default block size of the database. So if the default block size is 8K and the db_file_multiblock_read_count is 16, Oracle will try and read 16 x 8K blocks at a time during a multiblock read operation.
However, if there’s a non-default block sized segment (say 16K), Oracle will adjust the number of blocks that are actually read during a multiblock read operation so that the maximum size of the overall multiblock read is identical to that of the default block size.
So if the db_file_multiblock_read_count is 16 and the default block size is 8K, a multiblock read of an object in a 16K tablespace will only read 8 blocks at a time (and not 16). A multiblock read of an object in a 2K tablespace will read 64 blocks at a time.
The actual size of a multiblock read therefore is identical regardless of the block size of an object within a database.
An easy way to highlight this is to simply trace a session and see the specific size of corresponding multiblock read operations.
A sample from a trace on an 8K block index (with the db_file_multiblock_read_count set to 16), performing an Index Fast Full Scan looks like this:
WAIT #1: nam=’db file scattered read’ ela= 1487 file#=8 block#=1050 blocks=16 obj#=78294 tim=615409554677
WAIT #1: nam=’db file scattered read’ ela= 1377 file#=8 block#=1066 blocks=16 obj#=78294 tim=615409557777
WAIT #1: nam=’db file scattered read’ ela= 1143 file#=8 block#=1082 blocks=16 obj#=78294 tim=615409561563
Note that Oracle is reading 16 x 8K blocks (128K) per multiblock read operation.
However, when the index is recreated in a 16K block size tablespace, the Fast Full Index Scan looks like this:
WAIT #1: nam=’db file scattered read’ ela= 1413 file#=6 block#=14 blocks=8 obj#=78296 tim=626802128684
WAIT #1: nam=’db file scattered read’ ela= 1447 file#=6 block#=22 blocks=8 obj#=78296 tim=626802131649
WAIT #1: nam=’db file scattered read’ ela= 2014 file#=6 block#=30 blocks=8 obj#=78296 tim=626802135222
Note that Oracle is now only reading 8 x 16K blocks (128K) per multiblock operation.
Both indexes are effectively doing exactly the same work, both are effectively reading up to 128K of data per multiblock read …
It’s like paying someone $50 per 1/2 hour of work and then deciding to make things more “efficient” by paying them $100 per hour of work instead. In the end, you’re still just paying them $800 for an 8 hour day’s work regardless …
Note a larger block size will have less associated block overheads with there being less actual blocks so the overall size of an index may reduce a little, depending on index size and differences in block sizes. Therefore any possible improvements will only be restricted to the potential savings in the overall index size. With many databases having default block sizes of 8k and a maximum block size restricted to 16k, these savings may be minimum or non-existent.
This demo on the impact of different block sizes on multiblock read operations shows how Oracle actually performs the same sized reads when performing multiblock reads from differing block sized tablespaces, with the performance of the index in the larger block size tablespace being somewhat worse in this specific example.
With Oracle effectively performing identical work behind the scenes, the performance between different block size tablespaces is likely to be similar. You’re still paying $800 a day regardless …
Although it’s often claimed that multiblock reads is one of the key areas where larger index block sizes are beneficial, a claim based generally on the simplistic fact the number of consistent reads is reduced, the reality of the situation is somewhat different …
Store Indexes In a Larger Block Tablespace: Some Thoughts (Big Brother) March 16, 2008
Posted by Richard Foote in Index Block Size, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Myths, Performance Tuning, Richard's Musings, Tablespace Management.17 comments
A suggestion that seems to pop up on a routine basis on various forums and discussion boards is that we should be storing our Indexes in a larger block-size tablespace. For example, if our database block size is set to 8K, we should be creating separate (say) 16K block tablespaces specifically for our indexes. Doing so will improve performance as the index will have a flatter, more efficient structure. Multiblock reads will also be more efficient (or so the theory goes) as we would be reading fewer index blocks during such scans.
Oracle introduced the concept of having different tablespaces in a database with different block sizes back in 9i Release 1 in order to make transportable tablespaces between databases with differing block sizes possible. However, there’s nothing preventing one creating a new tablespace with a non-default block size and assigning objects to these tablespaces.
In principle, storing indexes (in particular) in a larger block size sounds like a really good idea doesn’t it ?
I’ll be discussing the pros and cons of this approach in future postings but just some initial thoughts to get everyone thinking about it:
-
All tablespaces with a non-default block size requires a separate, non-default block size buffer cache to be manually configured
-
Non-Default buffer caches are not automatically sized as part of Oracle’s automatic memory management and must be manually tuned and sized, potentially increasing administrative overheads
-
Non-Default buffer caches do not have an associated KEEP or RECYCLED pool and so all objects with the same non-default block size must reside in the same buffer cache
-
The possibility of unnecessarily caching blocks from an infrequently accessed object and wasting memory is therefore likely to increase
-
The possibility of unnecessarily aging out blocks from a more frequently accessed object is also likely to increase, thus increasing I/O related overheads
-
Although the height of an index may reduce if stored in a larger block size, in many cases it may not actually change at all
-
In those cases when the height of an index is actually reduced, the actual performance benefit of such a height reduction is often overstated
-
The reduction of index leaf blocks (a much more telling possible advantage) is only beneficial to very specific types of queries
-
Larger blocks often have the disadvantage of greater contention, which can lead to performance related issues
-
Indexes with larger block sizes have a significantly greater I/O and memory related footprint in relation to most OLTP related index scans
-
Index related multiblock reads on larger block sized segments actually have no real benefit when compared to multiblock reads on smaller block sized segments
-
Most databases out there in the “real world” only use default block size tablespaces so the risks associated with finding bugs, CBO anomalies, etc. increase once non-default block sizes are introduced
Although in specific scenarios with specific applications, there may be some potential benefits of using non-default blocks sizes, in general, the disadvantages of using non-default block sizes usually out weigh these potential benefits.
As we shall see …
Index Skip Scan – Does Index Column Order Matter Any More ? (Warning Sign) March 10, 2008
Posted by Richard Foote in Index Access Path, Index Skip Scan, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Performance Tuning.35 comments
I’ve already written a few posts regarding concatenated indexes and things to consider (and not consider) when deciding the column order of a concatenated (composite) index.
A comment I see from time to time is that the whole question of column order within an index is now somewhat redundant anyways as Oracle since 9i has introduced the Index Skip Scan access path. Prior to 9i, if the leading column of an index wasn’t specified in a predicate, the index was effectively ignored by the CBO. However, if the leading column isn’t referenced now, Oracle can use the index anyways via an Index Skip Scan access path.
So the column order in a concatenated index doesn’t matter that much now, right ?
Well, not quite ….
An Index Skip Scan can only actually be used and considered by the CBO in very specific scenarios and is often an indicator there’s either a missing index or an exisiting index has the columns in the wrong order.
If the leading column of an index is missing, it basically means the values in subsequently referenced columns in the index can potentially appear anywhere within the index structure as the index entries are sorted primarily on the leading indexed column. So if we have column A with 100,000 distinct values and column B with 100,000 distinct values and an index based on (A,B), all index entries are sorted primarily on column A and within a specific value of column A, sorted by column B. Therefore if we attempt a search on just Column B = 42, these values could potentially appear anywhere within the index structure and so the index can not generally be effectively used.
However, what if the leading column actually contained very few distinct values ? Yes, the subsequent column(s) values could appear anywhere within the index structure BUT if these subsequent columns have relatively high cardinality, once we’ve referenced the required index entries for a specific occurrence of a leading column value, we can ignore all subsequent index row entries with the same leading column value. If the leading column has few distinct values, this means we can potentially “skip” several/many leaf blocks until the leading column value changes again, where we can again “probe” the index looking for the subsequent indexed column values of interest.
So if we have a leading column with few distinct values, we may be able to use the index “relatively” efficiently by probing the index as many times as we have distinct leading column values.
On the other hand, if the leading column has relatively high cardinality then an Index Skip Scan is not a viable option. An index can generally store many hundreds of index entries per index leaf block, depending on the block size and the average size of the index row entries of course. So if the leading column were to change just once on average within the subsequent index leaf block, Oracle would be forced to scan the next index leaf block anyways as it may contain the required index row entry.
For Oracle to be able to “skip” an index leaf block, the leaf block must contain nothing but the same leading column value as last changed in a preceding leaf block. Hopefully, there are many leaf blocks where the leading column value doesn’t change and so hopefully there are many leaf blocks that can potentially be “skipped”.
Therefore, the cardinality of the leading column is crucial for an Index Skip Scan to be viable. In the example above where we had 100,000 distinct values for columns A and B, unless the table is massive, it’s unlikely an Index Skip Scan will be viable regardless of which column is the first column in the index. However, if column B only had 10 distinct values, then an index based on (B,A) may very well be able to use an Index Skip Scan whereas an index on (A,B) would not.
Note though that an Index Skip Scan must probe the index at least as many times as there are distinct values of the leading column. This will not be as efficient as an index that only requires the one index probe. Therefore although a query with a predicate based on A=42 could use an Index Skip Scan with an index on (B,A) assuming column B had few distinct values, an index on (A,B) or (A) would be more efficient as it would only require the one index probe.
However, if the performance of index (B,A) were “good enough” and/or a search on just A=42 was uncommon, then the index on (B,A) may be quite adequate and an index on (A,B) may be unnecessary. The index on (B,A) would also be able to handle queries based on columns A and B and queries based on just column B (providing the CBO determined the selectivity acceptable, which it might for unevenly distributed rare values of column B).
See this Index Skip Scan demo to see when it all may prove useful.
No, an Index Skip Scan doesn’t mean we don’t need to consider the column order of an index. If anything, it’s something else that needs to be considered and along with index compression, is another reason why low cardinality leading index columns have advantages.
OT: Darwin – Australian Northern Territory March 5, 2008
Posted by Richard Foote in Travel.4 comments
Just returned last night from a brief work related visit to the city of Darwin, Capital of the Australian Northern Territory.
For those of you that don’t know Australia very well, Darwin is located in the middle of the very top end of Australia. It basically has two types of weather, hot and wet or hot and dry. Sometimes it might be hot and a little overcast and sometimes it’s hot and slightly windy. But always hot.
In fact a big news item while I was up there was someone had built a chimney on their house. Now this may not sound particularly news worthy but it’s actually believed to be the only house in Darwin with a chimney !! Another major story was a somewhat famous woman who had previously survived a rather vicious Crocodile attack a few years ago had just been killed by a poisonous snake. Yes, the local wildlife can be a bit of an issue up there in Darwin …
I actually really like the place. It’s nice and relaxed and people generally have a very easy and friendly attitude. But the heat man. I went for an hour run one evening and I would have lost about 5kgs in sweat, easy. No wonder Darwin is regarded as the beer drinking capital of the world, with an average beer consumption of some 230 litres per person. Another reason I like the place 😉
I managed to grab a free hour to feed the fish at Doctor’s Gully. Basically all these wild fish turn up at high tide and get a free hand feed. Milkfish, Mullet, Bream, Catfish, Cod, Mangrove Jack, Rays, you name it, all turn up in massive numbers and just eat out of your hand. No, you’re not allow to catch them !!
I had dinner at the Char Restaurant on my last night there, where I basically had the nicest, most perfect steak, ever. I would visit Darwin just to eat there again. Highly recommended.
If any Oracle DBA out there is looking for a change of scenery, I was told that Darwin is really short of quality Oracle DBAs at the moment. I believe the pay may not be the best but the fishing is great !! If anyone is interested (must be an Aussie I’m afraid), drop me a line and I’ll put you in touch with someone.
Richard Foote's Oracle Blog 