Store Indexes In A Larger Block Tablespace: The Multiblock Read Myth Part II (The Fly) March 20, 2008

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.

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.

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.

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:

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. . . . . )

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?

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.

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

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

>> 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?

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.

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

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.

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:

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.

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.”

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!)

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.

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.

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

>> 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 :(

>> 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 ?

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?

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 …

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.

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.

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.

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.

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.

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 :)

“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.

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 :)

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?

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.

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 ?

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!

– 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.

“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.

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

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 ?

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.

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 …

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?

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 …

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.

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.