I have gone thru the following link:

http://asktom.oracle.com/pls/asktom/f?p=100:11:3384703436001222::NO:::

where a user has given example of the response time of the same query from two databases, one was having standard block size of 8k and other one was having 16k:

set timing on

SQL> r
1 select count(MYFIELD) from table_8K where ttime >to_date('27/09/2006','dd/mm/y
2* and ttime <to_date('06/10/2006','dd/mm/yyyy')

COUNT(MYFIELD)

---

164864

Elapsed: 00:00:01.40
...
(This command is executed several times - the execution time was approximately the same ~
00:00:01.40)

And now the test with the same table, but created together with the index in 16k tablespace:

SQL> r
1 select count(MYFIELD) from table_16K where ttime >to_date('27/09/2006','dd/mm/
2* and ttime <to_date('06/10/2006','dd/mm/yyyy')

COUNT(MYFIELD)

---

164864

Elapsed: 00:00:00.36
(Again, the command is executed several times, the new execution time is approximately the same ~
00:00:00.36 )

Does it really happens? why?

I respectfully disagree.

I've been using multiple blocksizes on mainframes since the 1980's, and Oracle since 9i, and it's a well proven tool, used by many of the hardware vendors themselves.

The TPC benchmarks THROUGHLY tested multiple blocksizes vs. one-size fits all, and they chose multiple blocksizes because it provided the fastest performance. HP spent a small fortune doing their testing because they wanted their benchmark to have truly stunning speed, and multiple blocksizes (and the segregated pools) worked for them:

http://www.google.com/search?q=site:www.tpc.org+db_16k_cache_size&hl=en&safe=off&rls=GGLJ,GGLJ:2006-27,GGLJ:en&filter=0

This has also been my experience when working with many Fortune 500 companies that utilize multiple blocksizes, benefits to both performance and manageability:

- Far better management of buffering with multiple buffers
- Less RAM wastage in the SGA

For my clients, the best part is segregating objects that get multi-block scans (e.g. index FFS, range scans) into 32k blocksizes.

I have more complete notes here:

http://www.google.com/search?hl=en&safe=off&rls=GGLJ%2CGGLJ%3A2006-27%2CGGLJ%3Aen&q=site%3Awww.dba-oracle.com+multiple+blocksizes

Hope this helps. . .

Don Burleson
Oracle Press author
Author of “Oracle Tuning: The Definitive Reference”
http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm

Hi Santosh,


I couldn't have said it better myself. That's a GREAT question.

Can you please paste-in the whole script that you used, please?

It would be interesting to explore why you noted this huge improvement in response time with larger blocksizes . . . .

TIA!

Don Burleson
Oracle Press author

Yeah, I redacted that one. Do you find the other evidence credible?

http://www.dba-oracle.com/t_multiple_blocksizes_summary.htm


Sorry, Richard, I'm not biting. At best, your demo is a rule-of-thumb, but not a proof, by any definition.

Your test case shows only one of zillions of possible conditions.

Don't you agree that it's reckless to generalize from a single test case, especially when exceptions are known to exist?

Message was edited by:
burleson

I habve found one real world application of TPC-C benchmarks, It is a usefull tool when evaluating hardware when you do not know exactly what your workload is going to be so cannot produce more realistic benchmarks.

Las time I did this it was to compare the performance of a number of iSCSI SAN solutions, using an open source tool to build the TPC-C database and run a load test. Along with some bulk load tests and bulk deletes it gave us the ability to say SAN 1 is faster/slower than SAN 2 but nothing else.

As for using multiple block sizes in a single database as a performance tool, I suspect that in the mundane world of the databases I deal with that range from 50GB to a couple of terabytes where I don't have the time or equipment to run complete tests or for the kind of micromangment required to keep on top of this.

I think if there is any performance advantage to this it will be similar to the advantages of moving tables and indexes onto different disks ( i.e. tables seperated from tables, indexes from indexes and tables from indexes) back before we all used massivly stripped disk arrays reduce IO contention, the actual analysis required is so high we end up with a generic rule of 'stick all indexes into a large blocksize' rather than a rule of 'possibly, maybe, in some cases when you have performed a couple of months of analysis you may get a performance boost from using non default blocksizes for some objects/segments but are going to increase the amount of database managment overhead and may have got a better result by analysing the application code instead '

Hi Don

You totally and fundamentally miss the point (again).

It's not a single test case or demo, it's an approach that can generate as many test cases as you like, in whatever environments or like, be it "real world" or otherwise (whatever that means), that can be modified in any manner you like.

You have proven to be "reckless" in relying on Robin's simplistic and erroneous test case for years to justify multiblock databases for indexes. Seriously, how many times has this subject come up and how many times have you used Robin's "test" case as some kinda proof, when really it tells you nothing, absolutely nothing except the obvious, the consistent gets get halved.

Here's a method or approach to prove (or otherwise) a piece of Oracle behavior and yet you simply refuse to even try.

Sorry Don, I'm afraid you're the one who relies on single, simplistic test cases, without even understanding what these simple results really mean ..

This is why we fundamentally differ you and I.

You can only lead a horse to water ...

Cheers

Richard

Hi Richard,


OK, I'll buy that, in theory, but what about the TCP standard? The TPC benchmarks are 100% reproduceable, and you are free to re-run their TCP-C and TPC-H benchmarks and prove them wrong.


No, it cannot "prove" anything, IMHO. There are too many interviening factors (init.ora parms, I/O sub-system, etc). In my world, we conduct a full test using a real-life workload, the only truly representative measure, IMHO.


No, I respectfully disagree. Do you really think that shops just rock-and-roll in production without testing? Now, that would be reckless! I ALWAYS insist on complete load testing whenever a client considers implementing multiple blocksizes.

After all, WHY GUESS?


That's a bit presumptious. How would you know what I do at a client site?

I spoke with a client just today who use using multiple blocksizes ( a multiu-terabyte OLTP), and they reminded me of another reason that some shops use multiple blocksizes to save RAM resources. They use a separate 2k data buffer for tablespaces that randomly fetch small rows, thereby maximizing RAM by not reading-in more data than required.

Why fetch 16k to get 80 bytes?

Oh, and Chris Foot (Oracle ACE and senior database architect) notes that employing multiple block sizes will help maximize I/O performance:

"Multiple blocksize specifications allow administrators to tailor physical storage specifications to a data object’s size and usage to maximize I/O performance."

http://www.dbazine.com/oracle/or-articles/foot5

***********************************************

Also, see this, the official word on Metalink:

Metalink Note:46757.1 titled "Notes on Choosing an Optimal DB BLOCK SIZE":

- Using bigger blocks means more data transfer per I/O call; this is an advantage since the cost of I/O setup dominates the cost of an I/O. . .

- Using bigger blocks means more space for key storage in the branch nodes of B*-tree indexes, which reduces index height, which improves the performance of indexed queries.

- Using a block size that is k times bigger than your current one will save you (k-1)f/(kb-f) bytes of space for large segments, where f is the size of a block's fixed block header (61 bytes for tables, 57+4n for n-table clusters, 113 for indexes). For example, you will conserve about 4% of data storage (4GB on every 100GB) for every large index in your database by moving from a 2KB database block size to an 8KB database block size.

- When using large block there are less probability of chained and migrated rows, which in turn reduced the number of reads required to get the information.
Metalink goes on to say that multiple blocksizes may benefit shops that have "mixed" block size requirements:

What can you do if you have mixed requirements of the above block sizes? Oracle9i "Multiple Block Sizes" new feature comes into the rescue here, it allows the same database to have multiple block sizes at the same time . . .
You can specify up to additional four block sizes , you can configure subcaches within the buffer cache for each of these block sizes in init.ora file or dynamically . You can create tablespaces having any of these block sizes.


And that's OK! We come from very different worlds, and we each have a fresh (and largely valid) perspective on this issue . . . .

Hi Don

I guess that's a valid point. Sometimes one simply needs to agree to disagree.

Cheers

Richard