indexes love big blocks

I was wondering if I can get people to have a civilized discusion on this article, how silly of me. But let's try anyway. Has anyone looked at the TPC white papers in detail? What do you think of the original article or the supporting links?

Quote:

---

Originally posted by TomKyte
...

Take your "indexes love big blocks" paper. http://www.dba-oracle.com/oracle_tip...rge_blocks.htm No caveats, nothing (in an article titled "RULES for setting large blocksizes"). The evidence you point to? a count(*) from a FAST FULL SCAN and you say "that shows indexes like it, for RANGE SCANS", huh. that is trivial to prove "wrong", "not accurate", "not true". (you can try it yourself Mike, in sqlplus, you can prove to yourself that for an index range scan, a block size of INFINITY won't reduce the number of table access by index rowid's, and when range scanning, what contributes the preponderance of the LIO's??)

In a singler user environment.

Conclusively.

Is that a performance proof? Nope, that is just showing how this thing called Oracle actually works

That article boils down to

o indexes love them
o temp digs it
o tpc-c does it, so it must be good

(in fact, that is all that article says). That is the kind of 1/2 truth, misinformation that I'll aggresively respond to with "careful there, be very very careful. sounds all good, but careful with that axe eugene"

...

sign me,

sincerely
a. gadfly

---

My original response to reading the article.

Quote:

---

Originally posted by gandolf989
Mike, it is true that I don't hang around CEO's and CIO's, but I was confused on one issue concerning your TPC Benchmark Example Look at page 379 for reference.

I found this link from the article where you talk about using large block sizes, yet in one of your benchmarking examples almost all of the block sizes along with the default block size are 2k and in the other the default block size is 4k. I thought 2k block sizes went out with 7.1. 4k isn't very large either.

It seems that from a management perspective it would be easier to use 8k for OLTP systems and 16k or 32k for datawarehouse systems. I realize that this approach is somewhat simplistic and certainly not all inclusive. But I'm not sure how using large block sizes in OLTP would help since most transactions would probably be small, most of the current data would be cached anyway, and if it weren't the OS is likely to grab multiple blocks on each read potentially causing Oracle to read more than it needs. It seems to me that the basic theory of your article is flawed, and would be seen as such to most DBA's.

Can you explain why an article talking about using large block sizes is referencing a benchmark test where they are using small block sizes???

---

Quote:

---

Originally posted by gandolf989
My oroginal response to reading the article.

---

WOW!!

A spelling error from the english major!!??
This is exciting.

This is an anti-climax.
I was hoping this would be a discussion on muscle cars.

Quote:

---

Originally posted by Mr.Hanky
WOW!!

A spelling error from the english major!!??
This is exciting.

---

EET ME!!!

*tear rolls down cheek*

you two are killing me here.

Quote:

---

Originally posted by OracleDoc
*tear rolls down cheek*

you two are killing me here.

---

Glad to be of service!!! :D

But I really hoped that someone would offer me a well reasoned argument for or against this article. Honest!

The best is when the three(gand,david and MH) get into a biaoutch_dead_lock. I guess we just need so real wicked news that'll kee them perplexed for the next couple of days. :p

I was hoping the Ault/El burlo introduction to the forum would divert their attention, Maybe I'll ask Burlisun to join the obfuscation as well.:)

Bring it on beotches!!

You people keep the peanut gallery in stitches.....thanks a million!
Bring Mike and Tom back. Please!

Quote:

---

I was hoping this would be a discussion on muscle cars.

---

So.....anyone got a '71 Chevelle? I doubt OracleDoc, Terry and DaPi do, but I'll bite on the muscle car chat..........um.....anyone?

OracleDoc, your sensitivity is causing me to weep uncontrolably:p

* Ahem *

Moving along, there are three main points made.

• Indexes love big blocks
• Temp loves big blocks
• TPC-C benchmarks use multiple block sizes and they're very heavily optimized by experts

But TPC-C doesn't seem to use big blocks for indexes, nor for Temp. So the whole house of cards comes tumbling down.

Getting Mike and Don to acknowledge this is not possible using currently available technology -- maybe in another ten years.

Quote:

---

Originally posted by slimdave
Getting Mike and Don to acknowledge this is not possible using currently available technology -- maybe in another ten years.

---

Anyone working on a cyber-rack?

Quote:

---

Originally posted by Alchemy
OracleDoc, your sensitivity is causing me to weep uncontrolably:p

---

Senitivity??? Pfffftttt..... that tear was from laughter. My side is hurting!

Let me put forward a situation:
let's say there is an IOT with millions of rows.This table is also highly insert/delete intensive(sort of staging table).
now there are queries which causes to read almost all the rows.
In such a situation wont a big block size reduce PIOs ??

Quote:

---

Originally posted by simply_dba
Let me put forward a situation:
let's say there is an IOT with millions of rows.This table is also highly insert/delete intensive(sort of staging table).
now there are queries which causes to read almost all the rows.
In such a situation wont a big block size reduce PIOs ??

---

the only answer to the question "won't a big block size reduce PIOs" is:

a) it will reduce PIOs
b) it will increase PIOs
c) PIOs will not budge one way or the
other

If the cache is devoid of blocks and you need to read all 10,000,000 rows that are in there, you will read X megabytes of data from disk.

It won't matter if the blocks are 2k or 32k, you will read X megabytes of data from disk.

Now, if you are doing a fast full scan of the IOT structure, Oracle will read db_file_multi_block_read_count * default_block_size bytes of data at a time.

If you have dbfmbrc = 16 and default block size is 8 K, Oracle will do reads of 128k. Regardless of block sizes. 128k of 8k blocks, 128k of 4k blocks, 128k of 32k blocks. So, for a fast full scan from disk using multiblock IO -- the math is easy.


So, that leaves single block IO. At the end you will still have read at least X megabytes of data from disk. Using an 8k block you will have made 4 times as many read calls as for 32k in a perfect world. In an imperfect world however, you may well have to re-read a block more than once (you have not said how you are using this IOT to read every row. If you were reading all rows regardless of order, I would assume a fast full scan, if you are reading all rows in Order, an index full scan, if you are reading this as a look up table, then we have on the order of some 10,000,000 range scans perhaps)

So, we hit the fast full scan above.

The index full scan (read root, branch/branch... get to left most leaf and read the leaves one by one), that one might have -- might have some advantage as it is doing the equivalent of a mini-multi-block-read on the IOT structure. Each single block IO against the 32k block is like 4 block reads of 8k blocks in one IO call.


The index range scan is where the 8k blocks would probably best the 32k. (guess). Why, because of the smaller blocks -- when I read in a leaf block that contains the key I was looking for, I read into the cache 1/4 the data. I did not read in the equivalent of 3 more blocks of data that I don't need right now -- so I did not push out some other data that I might need right now (roots and branches). I might get better cache utilization and do less overall PIO.

then again, I might not -- it could go either way and would be very very data dependent.


So, this thing that reads all of the rows, how does it read all of the rows.

And don't forget, the bigger blocksize will

o increase contention on this index structure, N times as many rows fit there, N times as many people will be hitting it. current mode get contention goes up.

o did you set your initrans appropriately for that.

o and of course the latch contention that would be increasing as well. especially in a mixed read/write environment

o and don't forget read consistency, going from 8 to 32k increased the probability of us having to do a rollback of the block(s) in order to read them by alot


but on the bright side, the blocks would split less often and block splitting is expensive.


This is why I voted directly in the middle. There is some truths out there.


One other thing about Mikes "Rules" for large blocksizes paper. He says

Quote:

---

This is very true. All temp segment access is sequential and 32k blocksizes greatly reduce logical I/O and disk sort times. I'm working an a benchmark right now that shows a one-third speed improvement of disk sorts in a 32k vs. an 8k blocksize.

---

True statement: temp IO is sequential.

Also true is that temp is read using an IO size, not a block at a time. So while true, it is not really relevant. There is an equivalent of dbfmbrc (_DB_FILE_DIRECT_IO_COUNT) for temp and if you have 32k or 8k blocks -- a read of 128k is a read of 128k. I'd rather play with that setting to see if larger IO's against temp would help or hurt me before rebuilding my entire database to get temp into a bigger block.

False Statement: 32k blocksizes greatly reduce logical I/O

I don't know what LIO has to do with temp, has anyone ever seen LIO to temp? It is all direct reads, we don't need LIO because it isn't in the buffer cache it is in temp.


Unfortunately, like most of dba-oracle.com that paper is not dated so we don't know how far away a benchmark is from completion. (but if you already know what you are going to show -- a 1/3 improvement -- it means you've already run the benchmark? doesn't it?)

Quote:

---

So, this thing that reads all of the rows, how does it read all of the rows.

And don't forget, the bigger blocksize will

o increase contention on this index structure, N times as many rows fit there, N times as many people will be hitting it. current mode get contention goes up.

o did you set your initrans appropriately for that.

o and of course the latch contention that would be increasing as well. especially in a mixed read/write environment

o and don't forget read consistency, going from 8 to 32k increased the probability of us having to do a rollback of the block(s) in order to read them by alot.

---

true , all this is perfectly true .I face them a lot
But

Quote:

---

if you are reading all rows in Order, an index full scan,

---

Consider a 320M IOT , using 32k, 10 IO calls(asynchronous) , using 8k 40 IO calls asychronous).Isn't this a gain worth speaking about?

Quote:

---

Originally posted by TomKyte
Now, if you are doing a fast full scan of the IOT structure, Oracle will read db_file_multi_block_read_count * default_block_size bytes of data at a time.
...

Also true is that temp is read using an IO size, not a block at a time. So while true, it is not really relevant. There is an equivalent of dbfmbrc (_DB_FILE_DIRECT_IO_COUNT) for temp and if you have 32k or 8k blocks -- a read of 128k is a read of 128k. I'd rather play with that setting to see if larger IO's against temp would help or hurt me before rebuilding my entire database to get temp into a bigger block.

False Statement: 32k blocksizes greatly reduce logical I/O

I don't know what LIO has to do with temp, has anyone ever seen LIO to temp? It is all direct reads, we don't need LIO because it isn't in the buffer cache it is in temp.

---

And just to add to that, should the IOT be large enough to warrant a parallel fast full index scan then we're into direct i/o and _DB_FILE_DIRECT_IO_COUNT, and the block size is no more relevant than it is for TEMP -- I could prove that with a script if you believe in that kind of thing ;)

So whenever anyone talks about table or FFI scans of even modestly large objects the blocksize again becomes irrelevant.

Do I recall correctly that prior to v8 or v8i the TEMP i/o was not direct? And if the temporary tablespace was created (incorrectly) in 8i/9i/10g as a permanent tablespace then i/o would not be direct? (just hypothetically that is)

Quote:

---

Originally posted by simply_dba
true , all this is perfectly true .I face them a lot
But

Consider a 320M IOT , using 32k, 10 IO calls(asynchronous) , using 8k 40 IO calls asychronous).Isn't this a gain worth speaking about?

---

but that is exactly why I wrote:

Quote:

---

The index full scan (read root, branch/branch... get to left most leaf and read the leaves one by one), that one might have -- might have some advantage as it is doing the equivalent of a mini-multi-block-read on the IOT structure. Each single block IO against the 32k block is like 4 block reads of 8k blocks in one IO call

---

And exactly why I voted right in the middle, there are some truths.


I am not against (or really for) multiple block size tablespaces.

I am against people saying "they are so cool, use them" without any sort of information to go with it.

And just how many posts would I need to do to get rid of that "junior" tag :)

Quote:

---

Originally posted by TomKyte
And just how many posts would I need to do to get rid of that "junior" tag :)

---

Don't go bringin' no ego to "Obfuscation Unlimited", Kyte. Many have tried that before ... it's not pretty, let me tell you.

Quote:

---

Originally posted by slimdave
Don't go bringin' no ego to "Obfuscation Unlimited", Kyte. Many have tried that before ... it's not pretty, let me tell you.

---

Harrumph to you then I say with my arms firmly crossed over my chest.

(see, this will get me one more post and I'll graduate to "not as junior" soon)

Amazingly enough, Harrumph is actually a word.

Quote:

---

Originally posted by TomKyte
And just how many posts would I need to do to get rid of that "junior" tag :)

---

Anyone who can't hack it, deserves to keep it :D

DBAsupport ought to give me some super hi-fi status for bringing in Tom kyte and also others who would keep Hanky busy for a longer period.

So what'd I win?? :D

Quote:

---

Originally posted by TomKyte
but that is exactly why I wrote:




And exactly why I voted right in the middle, there are some truths.


I am not against (or really for) multiple block size tablespaces.

I am against people saying "they are so cool, use them" without any sort of information to go with it.

---

Geeeezzz Tom how you and me think alike, I also voted right in the middle.:D

Quote:

---

And just how many posts would I need to do to get rid of that "junior" tag

---

Take the hint from other posters.See that funky tag of Mr Hanky and DaPi??
:D :D

Quote:

---

Originally posted by TomKyte
... see, this will get me one more post and I'll graduate to "not as junior" soon ...

---

Ah, then you understand how the system works.

Mr Hanky, for example, has over 2,600 posts but most of them are the most misguided nonsense you've ever read. Probably 1,500 of them are about shooting developers, so you guys ought to get along just fine and dandy.

Quote:

---

Originally posted by slimdave
Ah, then you understand how the system works.

Mr Hanky, for example, has over 2,600 posts but most of them are the most misguided nonsense you've ever read. Probably 1,500 of them are about shooting developers, so you guys ought to get along just fine and dandy.

---

Wow, honerable mention in a dave/tom thread, I am flattered.

I have to agree with dave on this, although the only difference between my posts and his is that I realize mine are (for the most) nonsense. Dave has yet to get past the denial phase, I believe it is step three of his looooong 12 step program.

Also to note that reply from mike ault's on the oracle app dba forum, it's a copy/paste from metalink.
Is that allowed?

Quote:

---

Originally posted by TomKyte
And just how many posts would I need to do to get rid of that "junior" tag :)

---

Hi Tom, just click here and look for "Custom User Text:". You can change it to anything you want. Like for example, "Smarter than DB". Maybe one of the moderators will give you an honorary 100,000 posts. Oh but I guess we shouldn't do that as it is wrong to inflate stats. ;)

Quote:

---

Originally posted by Mr.Hanky
Wow, honerable mention in a dave/tom thread, I am flattered.

I have to agree with dave on this, although the only difference between my posts and his is that I realize mine are (for the most) nonsense. Dave has yet to get past the denial phase, I believe it is step three of his looooong 12 step program.

---

The mention was in passing, try not to get too excited! ;)

Quote:

---

Originally posted by gandolf989
Hi Tom, just click here and look for "Custom User Text:". You can change it to anything you want.

---

no such option for me :(

I only see email, homepage, icq, IM, yahoo meseenger, signature, birthdate, biography, location, interests and occupation...

Must be something for not-junior members ;) Either that or it is a conspiracy, I've seen those before.

Quote:

---

Originally posted by TomKyte
no such option for me :(

I only see email, homepage, icq, IM, yahoo meseenger, signature, birthdate, biography, location, interests and occupation...

Must be something for not-junior members ;) Either that or it is a conspiracy, I've seen those before.

---

Are you sure it isn't between birthdate and biography? The magic number for losing the jr. is 100. Maybe you can create a topic with 100 bogus posts. Mr. Hanky will still have you beat on bogus posts though.

HEY !!

Let's give him a shot on bogus posts, you never know what someone is capable of.

I am going with the conspiracy theory, I think Mike Ault hacked his profile and will soon be editing his posts to reflect incorrect information.

Well, now that Tom is here glad I changed my tag. Even though it was with the utmost respect :p
*wipes forehead*

Quote:

---

Originally posted by OracleDoc
Well, now that Tom is here glad I changed my tag. Even though it was with the utmost respect :p
*wipes forehead*

---

Bring back the old one wimp!!

Quote:

---

Originally posted by OracleDoc
Well, now that Tom is here glad I changed my tag. Even though it was with the utmost respect :p
*wipes forehead*

---

Yeah, it's a good thing he didn't see you tag when it was "Tom Kyte's love child!"
We should really keep that a secret. ;)

The guy is pouring his heart out there and you're selling him out.

Quote:

---

Originally posted by Tarry
The guy is pouring his heart out there and you're selling him out.

---

You are right, I should not have done that. : (
However, I would think that Tom would be flattered. : )

Quote:

---

Originally posted by gandolf989
Hi Tom, just click here and look for "Custom User Text:".

---

Spoil sport!

Quote:

---

Originally posted by TomKyte
... Either that or it is a conspiracy, I've seen those before.

---

OK that's it buddy, you've crossed the line. Someone de-register this bozo and block his domain (make sure you get his home one as well). (And Starbucks).

Quote:

---

Originally posted by Mr.Hanky
Bring back the old one wimp!!

---

I hate to agree, but ....