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.trackback
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.
Richard Foote's Oracle Blog 
Hi Richard,
>> As I discussed previously, Oracle performs exactly the same sized I/O during a multiblock read, regardless of the block size of the segment.
Ok, but the real question is about “response time”, where credible people have observed differences using different block sizes.
Will you be looking at response time?
>> I just moved my indexes into a larger block sized tablespace and my Fast Full Index Scan performance improved. Conclusion, a larger index block size improves Index Fast Full Scan performance.
That’s a tautology.
If act A causes reaction B, how can you say that act A does not cause action B?
>> If we perform an Index Fast Full Scan and performance improves, it can’t be because associated multiblock I/Os are more efficient.
OK, enlighten us. A real scientist would investigate this, starting by confirming that there are indded differences in response time using different blocksizes. Then, you are in a position to dive-in and explain the reason for the phenomenon.
>> 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.
Ah, OK Richard, I think that I understand you now.
You argue that “act A” precpitates “act B’, which is the root-cause of the performance improvement. Is this right?
But it DOES not mean that the causation chain is broken.
– Act A results in Action B.
– Does this mean that Act A “caused” Action B? Yes.
– Does this ean that there may be many interviening factors? Absolutely.
I’ve done lots of predictive analytics, and the correlation is always more important than the cause, whihc may never be fully understood. Just look at clinical drug trials. It’s scarey, but many of the drugs on the market today reply solely on clinical evidence, and the underlying mechanisms are not well understood:
http://www.dba-oracle.com/t_prediction_causation_correlation.htm
In the world of science, root cause analysis is interesting, but NOT essential. Have you ever heard the Beer & Diapers story? It’s often cited in graduate school statistics classes (I have two kids in Grad School, and they think that they know it all. . . . . )
http://www.dba-oracle.com/oracle_tips_beer_diapers_data_warehouse.htm
BTW, of you read that “fly” story carefully, you will see that it DOES NOT make your point at all.
LikeLike
Hi Don
The whole point of the Blog entry was to state that rebuilding an index in a 16K tablespace (from a 8K block) might improve performance.
But simply rebuilding the index in the same 8K tablespace could have achieved potentially exactly the same result.
It was the rebuild of the index that improved the index, not the size of the block in which the index was built.
The larger 16K block size didn’t improve matters, it was the rebuild, the rebuild.
Or the disk in which the index now lives on. It’s the new disk, not the 16K block size.
Or the reduction in disk contention. The 8K block index would have improved performance just as well if it lived on the newer, faster, less contention disk.
Etc. Etc.
Do you see what I mean?. The suggestion that is was the 16K block tablespace that improved matters is like the scientist who thought the loss of wings reduced a fly’s hearing capacity 😉
LikeLike
Hi Richard,
>> The suggestion that is was the 16K block tablespace that improved matters is like the scientist who thought the loss of wings reduced a fly’s hearing capacity
Now that’s different, the person did two separate and distinct actions.
I was hoping that you were planning to explore the counterintuitative reports of different response times with different block sizes, reproduce them, and offer insights . . . .
BTW, have you seen the drivel that an index, after a rebuild, is somehow suboptimal (i.e. if the DBA did not set PCTFREE to accommodate growth)?
This would imply that the pristine state of an index is sub-optimal!
LikeLike
Hi Don
I prefer to stay on topic for now.
Answer this basic question. Why does an Index Fast Full Scan display the “largest measurable benefit”, what benefit are we talking about ?
And what environment has so many Index Fast Full scans that it makes moving indexes into a larger block tablespace worthwhile ?
LikeLike
I fixed the link to the OTN thread that contains Billy Verreynne’s excellent analogy.
LikeLike
Don
In many other threads I see you call for respect. Even on this blog How is this respectful?
12. Donald K. Burleson – March 20, 2008
Hi Brian,
…
Debate is good, so long as people treat each other with respect.
but this quote from your comment above:
BTW, have you seen the drivel that an index, after a rebuild, is somehow suboptimal (i.e. if the DBA did not set PCTFREE to accommodate growth)?
when I look up drivel, I find
1. saliva flowing from the mouth, or mucus from the nose; slaver.
2. childish, silly, or meaningless talk or thinking; nonsense; twaddle.
3. to let saliva flow from the mouth or mucus from the nose; slaver.
4. to talk childishly or idiotically.
5. Archaic. to issue like spittle.
6. to utter childishly or idiotically.
7. to waste foolishly.
that seems rather disrespectful in a debate doesn’t it? Especially when you provide no context for discussion. You are saying that “anyone that says an index rebuild might not do more harm than good speaks drivel”
Where do you see this drivel however? Maybe the author provided some information that makes a technical point, rather than trying to just ridicule?
LikeLike
Richard,
As it happens the issue of larger blocksizes was reviewed late last year – at least that’s the date at the top of the article. You can find the review at http://www.dba-oracle.com/t_multiple_blocksizes_summary.htm
In short though you find a number of different claims and counter claims, the people who setup multiple blocksizes for TPC-C sometimes put indexes in larger block sizes, sometimes in smaller blocksizes. The experts (oracle, the hardware vendors etc) express either straight doubt or considerable caveats around the implementation, noting it *can* be a performance impediment and *is* extra administrative headache.
In short difficult to read through all that and consider that using multiple blocksizes is an especially useful option for any but a very few particular sites, especially as the evidence given so far is for correlation rather than causation. post hoc ergo proctor hoc indeed.
LikeLike
Hi Richard,
>> And what environment has so many Index Fast Full scans that it makes moving indexes into a larger block tablespace worthwhile ?
Well, I’ve seen loads of FFS operations in DW batch jobs, especially MV’s and re-aggregations, that comes to mind.
BTW, your multi-block read “myth” is well-taken, but there are a few issues with it. The double-sized, one-scoop analogy omits the external influences . . .
In sum, differences in response time have been observed for different block sizes, and if you wish to “debunk” the multi-block read issue you need to start by reproducing the phenomenon. Only then, will you be able to proffer an alternative explaination.
A single negative test case DOES NOT disprove anything, on the contrary. . . . . Just because someone says “I did not see it happen on my PC”, does not make the phenomenon any less real, and certainly it does not make it a myth, right?
Please don’t fixate on the multi-block read issue, keep an open mind!
Remember, much of the “work” in a disk read is external to Oracle and even though you and Greg present convincing theory on how it “should” make no difference, evidence suggests otherwise, especially when we measure it as “response time”, the metric used in the contrary observations.
Remember, stripe size changes with block size, and external influences (SAN, NAS, channel contention, RAID level, TCP/IP settings), all can have an impact on the real-world observations.
As Greg notes here:
http://structureddata.org/2008/03/21/awareness-test/
It’s easy to miss something that you aren’t looking for . . . . .
http://structureddata.org/2008/03/21/awareness-test/
LikeLike
Hi Guy,
>> that seems rather disrespectful in a debate doesn’t it?
You are right, I was way too harsh, I won’t use that word again.
Thanks for pointing that out.
LikeLike
Hi Niall,
>> In short difficult to read through all that and consider that using multiple blocksizes is an especially useful option for any but a very few particular sites
Yes, I completely agree! I’ve tried to make this super-clear on my web pages, that it’s only for specialized cases of super high-volume databases, where it’s well-worth the effort to get a 6% performance boost. . . .
>> evidence given so far is for correlation rather than causation
Well, the empitical evidence says, “I changed block sizes and witnessed a change in response time:
http://www.dba-oracle.com/t_multiple_blocksizes_summary.htm#dba
What am I missing here? Is there something wrong with that?
Can’t you correctly say that their block size change “caused” their observed performance change? (assuming, of course, that block size was the only thing that they changed!)
>> The experts (oracle, the hardware vendors etc) express either straight doubt or considerable caveats
Not all, though. The TPC benchmarks seem to favor multiple blocksizes for squezing-out the last drop of performance, and they test and re-test to ensure thatthey have an optimal database:
http://www.dba-oracle.com/oracle_tips_benchmarks.htm
LikeLike
Hi Richard,
>> But simply rebuilding the index in the same 8K tablespace could have achieved potentially exactly the same result.
Oh, OK, now I get your point (I must be dense today).
Can we isolate this discussion somehow? I think that David rebuilt before-and-after in his test. . . . .
Would it be helpful to you to talk with some of my engineers who have witnessed this first-hand? If so, shoot me an e-mail.
BTW, what’s your take on the folks who claimed to have seen a response time difference? Since you are not suggesting that they are inexperienced DBA’s, and that they are not deliberately decieving people, I was hoping that you might enlighten me as to the other reasons that might explain their observations?
LikeLike
From comment 10: Donald K Burleson
Not all, though. The TPC benchmarks seem to favor multiple blocksizes for squezing-out the last drop of performance, and they test and re-test to ensure thatthey have an optimal database:
An important point you are forgetting about the TPC benchmarks is the limited run time requirement.
You only have to look at the use of single table hash clusters in some of HP’s TPC-C benchmarks to realise that the design could not work in a live production system (unless you had the down time to rebuild the database every 8 hours or so).
Designing for a benchmark is a very different game from designing for a production system that has to run 24 x 7.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
LikeLike
Hi Jonathan,
>> You only have to look at the use of single table hash clusters in some of HP’s TPC-C benchmarks to realise that the design could not work in a live production system (unless you had the down time to rebuild the database every 8 hours or so).
Ah, but they do it anyway . . .
Poor response time translates into lost productoivity for thousands of their end-users, and pay will pay a high price for good performance. I have clients with nightly rebuild jobs on sorted hash clusters, and it’s worth it to them because of the huge response time benefits.
What’s the cost? If they want a human DBA, well, they are on salary, so there is no cost. I have a techniques that has NEVER failed, where I rebuild fragmented clusters automatically.
Of course, this is predicated on a system that has regular evening downtime, like a typical OLTP, where end-users only use the system during business hours . . . .
LikeLike
Jonathan wrote “Somewhere I have a script that emulates the Schumacher test, but shows better timing from smaller block sizes.
So you saw a difference was well, great!
>> I wrote it a few years ago, but I’ll see if I can dig it out”
Yes, please do. I’m hoping that someone will explore this issue . . . .
>> But when you start doing block scans, the amount of CPU time spent actually using the block can become the important bit.
Exactly! It’s all-about “external” issues, CPU, disk controller scheduling, SAN, NAS, ETC.
I postulate that the answer to Richard’s question lies in the external influences on multiple block size reads. Do you agree?
http://www.dba-oracle.com/t_physical_io_disk_metrics.htm
LikeLike
Re Comment 14:
>> So you saw a difference as well, great!
Yes, but if you look carefully, you will notice that it says you get better TIMING from SMALLER block sizes, despite the INCREASE this gives in the number of block GETS. Yet again showing why pushing the Schumacher result as a reason for building indexes in larger block sizes is a bad idea.
I’ll be posting some recent results in a reply to Val on another thread.
>> Yes, please do. I’m hoping that someone will explore this issue
Does this mean you’ve been telling people to build indexes in bigger blocks for years WITHOUT first exploring the issue ?
>> I postulate that the answer to Richard’s question lies in the external influences on multiple block size reads. Do you agree?
Which question of Richard’s specifically ? He’s just making the point that you don’t change the read request size when changing the Oracle block size – so if you’ve configured your hardware reasonably you’re not going to get a significant (real-world) benefit from messing with the block size.
Notice, by the way, that your article about the Schumacher result eliminates any “external influences on multiple block size reads”- the test caches the index and takes its results from the second execution.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
LikeLike
Hi Don
Please please please read this post carefully again, as it answers most of the questions you’ve asked.
Just because you rebuild an index in a larger block tablespace does not mean it’s the larger block size that’s improved things (if indeed things have improved). You change many other things other than just the block size don’t you, surely you realise this ? You change the structure of the index, you place the index on another part of the file system, you change the caching characteristics of the index, etc. etc. etc. any of which could significantly change the performance of the index, none of which have anything to do with the tablespace block size.
To help you and others illustrate the point, I’ve added a little demo to the above post:
Click to access 16k-block-not-real-solution.pdf
I have an index with performance issues. I rebuild the index in a larger block tablespace and performance improves. You no doubt would jump up and down at this point in jubilation that the larger block tablespace has improved matters. Update a web page or two and highlight this as yet more proof that a larger block size improves index performance.
BUT BUT BUT, the improvement had nothing to do with the fact the index was built in a larger tablespace. Yes performance improved BUT it was as a result of the index no longer being so fragmented. It was the REBUILD, not the larger block tablespace that made the improvement.
If in fact one had just rebuilt the index in the original smaller block tablespace, performance would have improved by even more as it would have incurred far less CPU overheads than the larger block tablespace.
Don, please read through the demo, read it carefully and hopefully, at long last, you will see finally that just because someone claims a performance improvement with a larger block tablespace doesn’t mean it was actually the larger block that improved matters.
In David’s example, how on earth can you tell that the index wasn’t built on a fast part of a disk, or on a faster disk, or on a disk with less contention, or that the index wasn’t better cached or his system wasn’t less busy, etc. etc. etc.
Get it now ?
LikeLike
Hi Don
I’m really confused, when discussing the (so-called) benefits of larger block sizes, why do you continually quote from the performance tuning manuals the discussion on the KEEP and RECYCLE pools:
http://www.dba-oracle.com/t_multiple_blocksizes_summary.htm#benefits
http://forums.oracle.com/forums/message.jspa?messageID=2302993#2302993
http://forums.oracle.com/forums/thread.jspa?messageID=2373798�
http://forums.oracle.com/forums/thread.jspa?threadID=608337&start=15&tstart=0
Don, you do realise that the KEEP and RECYCLE pools have nothing to do with non-default block sizes don’t you ?
You do realise that non default block sized segments can not use either the KEEP or the RECYCLE pools don’t you ?
So why do you reference this with regard to non default block sizes again and again and again ?
LikeLike
Hi Don
I must say it’s pleasing to see that at least you’re now changing your views somewhat and saying that it’s “only for specialized cases” and that you’re no longer saying things along the lines of:
“Hence, one of the first things the Oracle database administrator will do is to create a 32K tablespace, a corresponding 32K data buffer, and then migrate all of the indexes in their system from their existing blocks into the 32K tablespace. Upon having done this, the Oracle database can read a significant amount of index note branches in a single disk I/O, thereby reducing stress on the system and improving overall performance.”
as you did in this article:
http://www.dba-oracle.com/art_dbazine_9i_multiblock.htm
LikeLike
Hi Richard,
>> must say it’s pleasing to see that
at least you’re now changing your views somewhat
>> I try to keep an open mind! It’s tough then the advice changes between releases (i.e. OICA is OK, in 8i and 9i, not so much in 11g).
Remember, I write to the senior DBA, and most of the ones I know enjoy the flexibility that comes from being able to manage I/O at a more granular level!
I’ve updated that article:
http://www.dba-oracle.com/art_dbazine_9i_multiblock.htm
Remember, in ten years you will have this challenge too, as your advice becomes obselete in a later release.
BTW, I heard a rumor that “fusion” is the big thing in the next release, and that it may be called Oracle 12fu . . . .
LikeLike
Hi Richard,
>> Don, you do realise that the KEEP and RECYCLE pools have nothing to do with non-default block sizes don’t you ?
Yes. I was talking about two topics at once (mutliple buffers and multiple blocksizes), and I got my arguments intermingled.
Prior to Oracle9i, the KEEP and RECYCLE pools were a sub-set of the DEFAULT pool. Starting with Oracle9i, the KEEP and RECYCLE pools are allocated in addition to the db_cache_size.
I fixed that page, thanks. Errata is most welcome!
LikeLike
No you can’t, hence my latin earlier – See http://en.wikipedia.org/wiki/Post_hoc_ergo_propter_hoc My favourite from that page, since the numbers of pirates has declined the incidence of global warming has increased, with the attendant suggestion that the Kyoto and other protocols should include measures to increase the number of pirates in the world.
In your case it’s particularly dangerous since you cannot *just* change the blocksize – you have to physically move and rebuild the index at the same time, as well has change how blocks from the index are cached.
I’d agree that one thing being succeeded by another is suggestive of causation, but it certainly isn’t proof.
Finally, I’d mention that, at least in OLTP environments, a 6% gain nearly always just isn’t worth chasing.
LikeLike
Ref Comment 13:
Poor response time translates into lost productoivity for thousands of their end-users, and pay will pay a high price for good performance. I have clients with nightly rebuild jobs on sorted hash clusters, and it’s worth it to them because of the huge response time benefits.
Frankly that sounds like they’re using the wrong technology for the job they want to do – but I will agree that there are some nasty defects in sorted hash clusters that could make rebuilds necessary.
But your point is totally irrelevant in terms of the argument about using the TPC as a justification for “doing X”.
You “do X” only if “X” is the correct thing for your system – and there are lots of dirty tricks in the TPCs where X is totally inappropriate for almost everyone almost all the time.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
LikeLike
Hi Miall,
>> In your case it’s particularly dangerous since you cannot *just* change the blocksize – you have to physically move and rebuild the index at the same time, as well has change how blocks from the index are cached.
True, that’s an issue, since they go hand-in-hand. It would be nice to isolate it, and I think that David did so in his test.
>> I’d mention that, at least in OLTP environments, a 6% gain nearly always just isn’t worth chasing.
Like all answers in Oracle, “it depends”!
If I have a database doing 100k LIO’s/sec, a six percent drop is significant, over 20m per hour. Check out this beast:
http://www.dba-oracle.com/t_statspack_typical_busy_oltp.htm
>> No you can’t, hence my latin earlier
I love latin, it sounds so profound:
– Sharpei Diem – Seize the wrinkled dog
– DBA Non Carborundum – Don’t let the DBA wear you down
– Quantum materiae materietur marmota monax si marmota monax materiam possit materiari? – How much wood would a woodchuck chuck if a woodchuck could chuck wood?
– Domino vobiscum – The pizza guy is here
– Fac ut gaudeam – Make my day
And my favorite “He knows Faith”, translated into latin, perfect for the school cafeteria. . . .
LikeLike
>> Frankly that sounds like they’re using the wrong technology for the job they want to do
How could you come to such a conslusion with such little detail?
In one case, a customer wanted to fetch all details for a customer order in a single data block. (As I recall, each customer had about 20 orders, and pre-migration there were many I/O’s per retrieval)
It worked great, an order of magnitude I/O reduction! If you size the overflow properly, and are prepared to burn some disk, it’s a great solution.
LikeLike
Hi Don
>> Remember, in ten years you will have this challenge too, as your advice becomes obselete in a later release.
The best way to avoid this of course is to offer accurate advice in the first place 😉 Suggesting one of the first things a DBA should do is move all indexes into a 32K block tablespace has always been very very bad advice. It was wrong then and it’s still bad advice now. Hopefully this blog is helping you to understand why.
>> I was talking about two topics at once (mutliple buffers and multiple blocksizes), and I got my arguments intermingled.
But you KEEP getting these topics intermingled, no pun intended 🙂
Moving on …
Do you understand now why David’s case proves nothing (as he himself admitted) ?
Do you understand now why Robin Schumacher’s demo proves nothing ?
Do you understand why flies don’t go deaf when you pull their wings off and why the lack of pirates as had no impact on global warming ?
Note so far, I’ve only just mentioned the one myth regarding multiple block sizes but hopefully we’re getting somewhere …
>> I’ve updated that article:
Yes, but you still recommend moving indexes into a 32K block tablespace if the index experiences multiblock reads despite the fact it’ll generally make no difference and without mentioning the various disadvantages and costs associated with such a move, so maybe we’re getting nowhere with you after all 😦
LikeLike
>> I have clients with nightly rebuild jobs on sorted hash clusters, >> and it’s worth it to them because of the huge response time
>> benefits.
>> In one case, a customer wanted to fetch all details for a
>> customer order in a single data block. (As I recall, each
>> customer had about 20 orders, and pre-migration there were >> many I/O’s per retrieval)
Hi Don
I really just wanted to keep the debate focused on the (so-called) benefits of multiblocks reads with a separate larger block size tablespace.
However, your comments regarding clusters has me intrigued. Do you really rebuild clusters each and every night because of the “huge response time benefits” of reading 20 order records ?
Really ?
I’m not sure what astonishes me the most, that you need to rebuild the clusters nightly or that transactions reading 20 records on average are so problematic …
Can you quantify “huge response time benefits” and why you need to rebuild the clusters each and every night ?
LikeLike
>> Yes, but you still recommend moving indexes into a 32K block tablespace if the index experiences multiblock reads despite the fact it’ll generally make no difference
So, how do we explain the credible positive experiences?
>> Do you understand now why David’s case proves nothing (as he himself admitted) ? Do you understand now why Robin Schumacher’s demo proves nothing ?
Proofs? Proof? There ARE NO proofs in Oracle tuning!! We live in a world of probabilities, not proofs!
Oracle tuning is a semi-structured task, with some well-defined decision rules, but there are no “proofs”.
Conversely, you cannot “disprove” something by showing a single negative example.
>> maybe we’re getting nowhere with you after all
This isn’t about me. Or is it?
I report on real-world empirical phenomenon, that’s it. It’s up to the reader to evaluate the credibility of the reported phenomenon and come to their own conclusions . . . . .
That’s why the credibility of the reporter (their real-world experience) is so important.
>> I’ve only just mentioned the one myth regarding multiple block sizes
Are you suggesting that these people are not telling the truth when they observed improved response time?
http://www.dba-oracle.com/t_multiple_blocksizes_summary.htm#dba
It’s not a myth. It’s real and reproduceable, and due, I believe, to external factors (the unique I/O sub-system of each database). Until you explain the real-world observations, you are premature in calling this a “myth”. In fact, some might say that you are creating a myth by claining that it’s not real, without any evidence, except theory on why it should not happen.
Richard, I’m starting to understand why you are reluctant to reproduce this phenomenon.
Are you truly interested in investigating this, or do you have other motives?
LikeLike
Hi Don
Oh boy …
Did you actually read this blog entry, it explains it all. Yes they might see better response times, no it doesn’t mean it has anything to do with bigger block tablespaces …
Did you actually follow the demo, it gives an example on it all ? Yes response times improves, no it had nothing to do with the bigger block size …
“These people” includes David’s example who himself didn’t think it had anything to do with the bigger block size !! They may observe better response times, it doesn’t mean however it was due to bigger block sizes …
That’s the whole point. Pirates and flies and no wings …
I have reproduced this phenomenon, look at the demo I provided !!
Click to access 16k-block-not-real-solution.pdf
Read it and at least try and follow it !!
I referred to “you” because I was responding to “your” comments.
Seriously, if you don’t “get it” by now, I really don’t think you ever will …
One can only lead a horse to water …
LikeLike
Hi Niall
I love your example, thank you.
Not sure everyone has got it though 😉
LikeLike
>> Proofs? Proof? There ARE NO proofs in Oracle tuning!! We live in >> a world of probabilities, not proofs!
>> Oracle tuning is a semi-structured task, with some well-defined >> decision rules, but there are no “proofs”.
Don, of course you can prove things in Oracle. In this very blog, I’ve proven for example how:
– An max outlier value causes 90-10 splits and reduces the compactness and efficiency of an index
– An outlier value causes the CBO to incorrectly cost range scans
– A non-unique index requires an extra bye of storage per index entry
– Non unique indexes require additional latches vs. unqiue indexes
– Entirely null indexed values are not actually indexed
– Null values with a not null index value are indeed indexed
– Reverse key indexes can perform range scan operations
and of course
– The multiblock read size is identical regardless of the tablespace block size
– Etc. Etc. Etc. Etc. Etc. Etc. Etc. Etc. Etc. Etc. Etc. Etc. Etc. ….
The fact you think tuning issues can’t be proven in Oracle rather makes me think of this early blog entry of mine:
🙂
LikeLike
>> Seriously, if you don’t “get it” by now, I really don’t think you ever will …
So, you think that being condescending and arrogant is going to help? I get it, I’m not so sure about you, my friend. For starts, using the word “poof” when talking tuning is puzzling . . . .
>> Read it and at least try and follow it !!
Seriously, do you realy think that treating me like a dumb child is going to accomplish anything?
Seriously, I’ve been a DBA for a long, long time, and I don’t appreciate being talked-down by someone who dows N OT appear to be keeping an open mind about this problem.
You can talk “proofs” all you want, but your approach to scientific study baffles me . . . .
Science must be different where you live, I guess.
Bye!
LikeLike
Re Comment 24: Don Burleson:
It’s easy to come to the conclusion with so little data. The point that you’ve missed (possibly deliberately) is that you said ” I have clients with nightly rebuild jobs on sorted hash clusters,and it’s worth it to them because of the huge response time benefits.”
When Oracle devises a new structural mechanism they usually do it to address specific data handling problems. If you find that a wonderful new feature only gives you a huge response time benefit if you rebuild the data set every night then either (a) you’re using the feature in a way that it’s not designed for or (b) you’ve found a bug in the feature.
By the way – you don’t need sorted hash clusters to get “all details for a customer order” into a single block – the basic hash cluster would be sufficient, so would the the old index cluster technology that’s been around since at least Oracle 5. Since hash clusters require you to pre-define the space requirements up front and index clusters don’t, you probably are using the wrong technology if you put order data into a hash cluster.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
LikeLike
[…] i thread di discussione a cui Richard Foote si riferisce e la serie di commenti che segue alla seconda puntata sull’argomento specifico) molto […]
LikeLike
I did. I notice that 66% of the response time is db file sequential read – i.e single block reads and a further 30% is CPU. It’s difficult to say the least to see how moving those single block reads from one block size to another would have any effect at all – even the 6%. It isn’t difficult to speculate though that there might be some un-optimised sql in there, either in terms of access path, or in terms of the number of times it is being executed. Obviously we can’t tell that from the sample on the web, so it’s mere speculation.
LikeLike
Richard,
Not my example orginally, but that of the Church of the Flying Spaghetti Monster (more usually known as the Pastafarians). See http://www.venganza.org/ I have a sneaking suspicion that the numbers may just have been made up and that more studies are needed as the best scientists always say.
Niall
LikeLike
sorry Richard for being OT, but yesterday OTN forum thread cited by you at the beginning of this post had three pages and concluded with a reference at the “Bug 5893614 – Full table scan very slow on ASM”. Today that part of thread seems missing! what happened? why there is only the not so helpful discussion between Burleson and others?
Thank’s again for useful informations shared by you through this blog.
Cristian
LikeLike
Billy’s story reminds of the great quote:
“When you study ants under a magnifying glass, its amazing to find that they often spontaneously burst into flames”
🙂
LikeLike
Hi Don
Hey, I just realised why you’re so against the concept of proof:
http://www.dba-oracle.com/oracle_tips_blocksizes-.htm
😉
LikeLike
Hi Jonathan
The whole thing has me scratching my head …
LikeLike
Hi Niall
The whole thing has given me the urge to go out and buy one of those fluffy “pirate” shirts from Seinfield.
“But I don’t want to be a pirate !!” 🙂
LikeLike
Hi Cristian
I’m sorry but I have absolutely no idea why the thread has been edited.
OTN unfortunately features some pretty unprofessional “discussions” every now and then, it’s likely related to something someone said ?
LikeLike
Hi Connor
Yeah, I’ve noticed that myself, but it generally only happens on hot, sunny days. Ants must obviously like to drink tiny flaming Sambucas on sunny days and they get nervous when you watch them through a magnifying glass and it all goes horribly wrong 🙂
LikeLike
Re comment 27:
Don, you write
“So, how do we explain the credible positive experiences?” and
“I report on real-world empirical phenomenon, that’s it. It’s up to the reader to evaluate the credibility of the reported phenomenon and come to their own conclusions . . . . .
That’s why the credibility of the reporter (their real-world experience) is so important.”
While the second quote seems a bit self referencing (we need to evaluate the credibility of the reporter but we should rely on the credibility of the reporter) what really bothers me is this: without information about the real world scenario that allegedly produced the 20% improvement I have no chance at all to evaluate credibility. Richard, on the other hand, provides little demos that illustrate the point and that I can take, experiment with and change them to help me understand and check.
I have not seen something that would put me in a position to do the same for the 20% improvement claim so with all I’ve read so far I can’t help but believe that it was not the changed block size that lead to the improvement. After all the 20% improvement was an improvement in IO measured by comparing weekly statistics (from [1]) which seems a fragile method to do the measuring.
As for the 6% that were mentioned I’d probably not even bother to collect that because of the downsides (management overhead etc.). After all, getting rid of FFS should be prio 1 vs. making it faster.
[1] http://dba.ipbhost.com/index.php?s=e46bdc8bc91bd9149d23b1cd946c55e7&showtopic=1239&st=75#entry5419
LikeLike
Hi Robert
The problem with this so-called 6% improvement is that Don can have absolutely no idea how or why this specific improvement was achieved.
None.
No one can, that’s the point.
And he still asks “So how do we explain the credible positive experiences” despite:
1) being told several times a number of “possible” explanations
2) being shown a demo of why the 6% claim is meaningless
3) David Aldridge himself who came up with the 6% improvement example didn’t think it had anything to do with the block size 😦
Interestingly, in his current 2008 multiple blocksizes summary:
http://www.dba-oracle.com/t_multiple_blocksizes_summary.htm
Don lists the following “Real World Experiences With Bigger Block Sizes”:
M. J. Schwenger who makes this unsubstantiated quote “I have used in the past 32KA blocksize for indexes to improve performance and had very good results” and then proceeds to ask in a forum thread whether or not using multiple blocksizes is actually a good idea or not !!
Balkrishan Mittal who in the very same forum discussion as M. J. Schwenger warns him not to use a larger block size as it caused him negative results with 100% CPU consumption and was forced within days to put the indexes back in a smaller block size !!
David Aldridge with the 6% example already discussed where he says “Unless I’ve done something wrong here, I don’t see much advantage in the large block size. I could see those percentage differences being attributable just to the physical location of the segments on the disks and background noise, no?” !!
Chris Foot with a link to a book to very non real-world experience book on OCP Certification !!
Santosh Kumar who only asks the question “Is it true” on an OTN forum on an example on a AskTom thread where an anonymous Russian makes unsubstantiated claims on the benefits of bigger index block sizes (dismissed by Tom), the very same OTN thread where Don himself admits “Yeah, I redacted that one” when I highlighted to him Santosh himself never actually made the claim !!
And finally Steve Taylor, the same Steve Taylor with the same claim as discussed in the David Aldridge thread, who is totally vague about the so-called performance improvements, mentions improving max queries (that sounds suspiciously like improvements due to rebuilding fragmented indexes, not larger blocks) and ultimately admits ” Now from reading the thread – some of this could have been a result of external factors such as the cache segregation… ” and “Sorry if this sounds a bit vague.. I think I’ve learnt a couple of valuable lessons here” !!
And that’s it !!
That’s the summation of all the “Real World Experiences” !!
You gotta admit, it’s not a particular impressive list of references, especially since most of them either disagree with bigger block sizes or are unsure and are simply asking for further advice 🙂
LikeLike
Richard said “poor Don has absolutely no idea how or why this improvement was achieved. None”. Do you really think so? After reading this I think that Don knows exactly what is happening, and he has been trying to encourage you to explore this and learn for yourself. But neither you nor Jonathan is willing to do so. Instead of writing unprofessional insults against Don I hoped that you would write some relevant tests to explore this.
Richard from what you have written it seems like you are the one who has no idea why this happens and you don’t seem to care. By your admission you have made no attempt to explore this except a self evident test that can easily be looked-up in the documentation. The OS read size is the same regardless of the blocksize, and no demo is needed.
Greater concern is that you give your fly analogy, and then proceed to do it yourself. Your alleged proof does not attempt to measure performance at all, and you use this proof to make unsupported conclusions.
Richard you wrote “Don, of course you can prove things in Oracle.” Were you trying to be sarcastic? Your humour missed the mark. Forgive me but you have not measured any performance in your tests. Performance is measured by throughput transactions or by elapsed time, and your tests contain no performance measurement. Perhaps if you set timing on your might be able to say something about performance but you did not do so.
I was hoping that noone would believe your conclusions but you have fooled people. In the pythian blog Shakir wrote: “Richard Foote’s Oracle Blog debunks the myth of increased performance when building indexes in larger block size tablespaces.” Both you and Jonathan have conceded that different block sizes have different performance, and yet you persist in spread these myths. I can only hope that you post something on that blog to have it corrected.
The Oracle blogs are full of enough of rubbish without you and Jonathan creating new myths. Are you and Jonathan competing to be the new Cherry sisters by spreading more myths?
LikeLike
Hi Sum
Look at the demo carefully. You will notice it measures both CPU and elapsed times. Believe it all not, these are often regarded as a means of measuring performance.
You will notice that elapsed times goes down when the index was rebuilt in a larger block size, although CPU had gone up. You will also notice that both CPU and elapsed times both go further down when you simply rebuild the index back in the same block size tablespace.
Note this is more informative than simply setting timing on …
Sum, no I wasn’t being sarcastic when I said of course you can prove things in Oracle because Sum guess what, you can.
Sum, by all means, please explain how a multiblock index read performs more efficiently in a larger block size tablespace.
Please sum it all up for me 😉
LikeLike
“poor Don has absolutely no idea how or why this improvement was achieved. None”.
Whay say you? Do you have a clew?
LikeLike
Hi Sum
You seem to miss the point yet again.
Not only does Don not have any idea how this improvement was achieved, but neither do you, or I or David Bowie, or David Aldridge who put the demo together or anyone. There’s simply not enough information.
That’s the point, we don’t know.
Therefore, we can’t simply say here is a real world scenario of where larger blocks have improved performance because the improved performance can be attributed to so many other things.
That’s the whole point.
And note poor David himself said all along that the performance difference can’t simply be attribued to a larger block.
So maybe, just maybe the fly wasn’t simply deaf when it didn’t take off after the scientist clapped his hands …
That’s the whole point …
So Sum, why is set timing on better than a trace that shows both elapsed and CPU times ?
So Sum, can you please explain how a mulitblock index read performs more efficiently in a larger block tablespace ?
LikeLike
Richard, thank you for the summary! Although I do appreciate you stepping in and replying to my question I would prefer if Don defends his point himself. He is the one who claims there are circumstances where this is beneficial, so I believe it is his job to supply evidence and not only quotes from credible sources.
Don, I am still waiting for your detailed explanation how this works and where the 20% IO improvement came from. Until then I refuse to believe that putting an index on a different block sized tablespace has more advantages than disadvantages. Thank you!
LikeLike
Hello Richard Foote,
You write that these “prove” something about Oracle performance:
– An max outlier value causes 90-10 splits and reduces the compactness and efficiency of an index
– An outlier value causes the CBO to incorrectly cost range scans
– A non-unique index requires an extra bye of storage per index entry
– Non unique indexes require additional latches vs. unqiue indexes
– Entirely null indexed values are not actually indexed
– Null values with a not null index value are indeed indexed
– Reverse key indexes can perform range scan operations
and of course
– The multiblock read size is identical regardless of the tablespace block size
Do you mean “proof” in the sense of a math proof, where any single contrary result disproved it?
Everyone has duly noted that none of these “proofs” says anything at all about performance. Are you being deceptive or are you simple? Can you re-state these proofs to say something about response time or throughput or was this just something you pulled forth from your bum to chase Don away?
“There’s simply not enough information.”
I do not know you Richard. Are you incapable of measuring things outside Oracle, or just unwilling? Just because something happens outside Oracle does not mean that it does not exist.
“Not only does Don not have any idea how this improvement was achieved”
Here never said that. Do you really think that Don is incapable of investigating this? In fact, he said that it was due to external factors, which I think is true.
http://www.dba-oracle.com/t_physical_io_disk_metrics.htm
He said it was due to this.
Stripe size – The stripe size for raw disk partitions will influence I/O behavior, especially for multi-block read operations (full scans).
TCP/IP – TCP IP settings and Oracle TNS settings (e.g. tcp.nodelay) effect I/O timings.
RAID level – The RAID used on the disk array can have a profound impact on end-to-end I/O timings. For example, up until 2007, RAID 5 with Oracle was sometimes inappropriate for high-update databases.
Disk Controllers – A bottleneck in the disk farm such as a lack of controller resources can precipitate high I/O latency.
Storage Array Internals – Many of today’s storage arrays have specialized optimization software and on-board RAM caching to improve throughput.
SAN/NAS – Dynamically attached storage has special issues.
“And note poor David himself said all along that the performance difference can’t simply be attribued to a larger block.”
“Poor” David knew that it was the block size change that precipitated it.
I do not blame Don or anyone else for not responding to your rude comments and undeserved disrespect. You are getting a reputation as a difficult person to converse with, and you have yet to tell us why this happens.
“He is the one who claims there are circumstances where this is beneficial, so I believe it is his job to supply evidence”
His job? It’s not Don’s job to educate you. He was very patient in pointing you in the right direction, and you treated him like an imbecile.
He would no more disclose details from his systems than you would disclose details fro the AUS government databases.
Don is right about credibility.
He says he saw it, you confirmed it happens, and I believe it.
“That’s the point, we don’t know.”
So, why not investigate it and learn why? If you were really being scientific, you would note the response time differences, redo the test with additional external monitors and explain it.
Thusfar, you have failed dismally to do so.
LikeLike
Hello Niall,
“I have a sneaking suspicion that the numbers may just have been made up”
“:42.7 percent of all statistics are made up on the spot” – Larry the Cable Guy
LikeLike
Hi Hostellerie
Yet another anonymous post, with the same “message”, posted at a similar time …
Interesting.
For whatever reason, I appreciate you’re only trying your best to defend Don and his position of moving indexes into a separate larger block tablespace.
And why let little things such as facts get in the way, eh ?
However, you may be disappointed to know that Don himself is not likely to take too kindly to such “contributions”.
You see, he has a general dislike of anonymous posters such as yourself and doesn’t hold them in high esteem:
http://forums.oracle.com/forums/thread.jspa?messageID=2303009�
He also has a general dislike of people who give advice without first showing their credentials and experience. He has a deep suspicion of anyone who makes unsubstantiated comments when they don’t first disclose their education, training, work experience and validates their expertise
http://forums.oracle.com/forums/thread.jspa?threadID=587394&start=15&tstart=0
Now I honestly couldn’t care less but Don is likely to be unimpressed by your anonymous, non-resume contributions 😉
LikeLike
Ref Post 50: Hostellerie,
Do you really think that Don is incapable of investigating this? In fact, he said that it was due to external factors, which I think is true.
So if the improvement came about because of external factors, why is he saying that rebuilding indexes into larger block sizes is a good idea.
If it’s external factors, you just have to move the datafile to the place where the external factors make a difference.
Regards
Jonathan Lewis
LikeLike
Jonathan,
If Don is wrong can you show why pls?
Why does noone explain the reason for the performance boost?
LikeLike
Hi Sum
Here’s a clue for you Sum, read this post again. I mean really read it.
Because the fact you have to ask this question clearly suggests you haven’t read it and understood it.
It provides several reasons for the “performance boost”, none of which have anything directly to do with having the index in a larger block tablespace.
It also provides an example of how rebuilding an index in a larger block tablespace appeared to improve performance, but the larger block tablespace had nothing to do with the improved performance. In fact rebuilding the index back in the smaller block tablespace actually improved performance by an even greater amount.
Oh, and you forgot to answer these questions:
Why is set timing on better than a trace that shows both elapsed and CPU times ?
Can you please explain how a mulitblock index read performs more efficiently in a larger block tablespace ?
Bzzzzzzzzzzzzzzz 😉
LikeLike
So after 55 comments and numerous alleged sitings (as in UFO) of faster performance of larger block indexes, there is still no test case that demonstrates any of Don’s claims, only ones that demonstrate there are no gains? I would have hoped that someone that is a consultant, recommending this to customers, could not only explain why, but demonstrate it. Very disappointing. I suspect those sitings of performance gains were related to solar flares and not block size at all.
And the comments that ask for Richard and Jonathan to demonstrate why Don is wrong… Perhaps Don can show why he is right with a demo that supports his claim, not just a collage of comments copied and pasted on a web page, none of which have any technical detail.
LikeLike
Hi Freddie
I really appreciate that Don has made comments and contributed to this discussion, putting the best case forward he can for his point of view.
People reading through the posts and comments can see both sides of the debate and decide for themselves whether or not rebuilding indexes in a larger block tablespace is really such a good idea after all …
LikeLike
Hi Richard,
Have you (or has anyone reading this really informative thread) ever rebuilt an index into a larger-than-default blocksize and it then having better response time as well as rebuilding an index in its current default blocksize and it being no better, or worse?
Aside: Was Tin Machine ever, ever a sane thing for Bowies to do?!
LikeLike
Hi Nigel
In all my tests and playing around with multiblock tablespaces for b-tree indexes, the results have generally been negligable, especially between 8K (which are the default of all our databases) and 16K which is max supported for most of our databases.
I’m always interested in hearing success stories with using larger blocks for indexes, just dont’ get to hear many …
LikeLike
Bowie, not Bowies!
LikeLike
On Tin-Machine, I remember when it first came out, I was one of the few around who really liked it. I was a bit disappointed by the “Tonight” album and really disappointed by the “Never Let Me Down” album, especially after Iggy Pop’s “Blah Blah Blah” which I really liked. So Tin Machine sounded so fresh and radical and totally non-commercial which I loved. Also, songs such as “I Can’t Read”, “Prisoner Of Love” and “Tin Machine” are all actually really good songs but I think the concept of Bowie fronting a band and acting as if he was just one of the boys put a lot of people off-side. It was a period that Bowie needed to wash out the commercial demons that while providing him with hit records and financial reward, were stifling his artistic instincts. If Bowie didn’t go through Tin Machine and made another “Never Let Me Down”, it might have been the end and future classics such as “Outside” and “Earthling” and “Heathen” might never have been.
LikeLike
Yes, in the context of the genre, Tin Machine is sane. You just have to be in the right mood. Is it really so different than Scary Monsters? (And has it really been 20 years???)
LikeLike
Hi Joel
No way, it can only have been about 5 or 6 years ago. No wait a minute, no maybe 12 or 13 years ago. Hey wait a miniute, oh my God, it was 20 years ago !!
Wow, now I’m depresssed !!
LikeLike
Even more bizarro, I used to watch Soupy Sales, who just died and is related to Tin Machine by… http://www3.signonsandiego.com/stories/2009/oct/23/us-obit-sales-102309/?&zIndex=187669
LikeLike
Hi Joel
Indeed. The Sales Brothers finest moment though is not Tin Machine but their work with Iggy Pop during the late Berlin, “Lust For Life” period. Love that album and I love their rhythm work.
LikeLike
Raw Power tour: http://www.rollingstone.com/rockdaily/index.php/2009/11/03/iggy-pop-says-stooges-are-rehearsing-for-2010-tour/
Love Lust For Life too, but it lost a bit of shine being used in commercials around here. Voted “Worst Ad Song Ever” 2005 for Carnival Cruise lines ad – they clipped the “Johnny Yen” part, of course. I always thought W95 Start Me Up song was worst, myself.
LikeLike
Hi Joel
Ah, the corruption that is commercialism. It’s still a great song with a great riff no matter how many jars of pasta sauce it might have sold 🙂
LikeLike