This content has been marked as final. Show 34 replies
Have fun in SFHi Howard
Strange. Whatever gave you the idea that I'd be in
I think he might be confusing us ?
Although we're both Australian, I'm clearly the better looking ;)
Yeah, sorry, you all look alike to me!!!
I think that he's confusing us
You are at OOW this week, right?
Howard, I know that you think that I "make things up", but I swear to God, I have a rigorous testing approach for all my clients, and my original advise stands:
On the other hand, you have been invited to adopt a testing approach.
"Don't guess - If you think that you DB may benefit from multiple blocksizes, test it yourself and prove it, using a real-world workload from your database"
BTW, Dr. Scalzo has a good book on Oracle benchmark testing with real workloads, and I'll be glad to mail you a copy, if you like:
I don't know if you are a consultant, but over 95% of my clients require a strict NDA, and I simply cannot publish ANYTHING from their databases. Is it like that in Oz?
I'm trying to show evidence, truly. I sent e-mails to some of my clients asking if I could disclose details about their experiences with multiple blocksizes, and they all refused or ignored my request. . . Some shops don't even want it disclosed that they use Oracle . . .
But I'm still trying, stand by . . .
As Oracle moves toward workload-based testing, it;s going to be even more difficult to share evidence.
BTW, I updated my notes, and I'll be glad to add any comments that you want:
The IBM Oracle Technical Brief titled "Oracle Architecture and Tuning on AIX" (November 2006) notes that careful evaluation is required before implementing multiple blocksizes:
While most customers only use the default database block size, it is possible to use up to 5 different database block sizes for different objects within the same database.
Having multiple database block sizes adds administrative complexity and (if poorly designed and implemented) can have adverse performance consequences. Therefore, using multiple block sizes should only be done after careful planning and performance evaluation.
The paper continues with specific examples of differing I/O patterns that are related to the database blocksize:
Some possible block size considerations are as follows:
- Tables with a relatively small row size that are predominantly accessed 1 row at a time may benefit from a smaller DB_BLOCK_SIZE, which requires a smaller I/O transfer size to move a block between disk and memory, takes up less memory per block and can potentially reduce block contention.
- Similarly, indexes (with small index entries) that are predominantly accessed via a matching key may benefit from a smaller DB_BLOCK_SIZE.
- Tables with a large row size may benefit from a large DB_BLOCK_SIZE. A larger DB_BLOCK_SIZE may allow the entire row to fit within a block and/or reduce the amount of wasted space within the block. Tables or indexes that are accessed sequentially may benefit from a larger DB_BLOCK_SIZE, because a larger block size results in a larger I/O transfer size and allows data to be read more efficiently.
- Tables or indexes with a high locality of reference (the probability that once a particular row/entry has been accessed, a nearby row/entry will subsequently be accessed) may benefit from a larger DB_BLOCK_SIZE, since the larger the size of the block, the more likely the nearby row/entry will be on the same block that was already read into database cache.
But what is Oracle's official position on multiple blocksizes. For Oracle metal-level customers we have the Oracle Metalink system which provides the official position of Oracle's own experts.
Metalink Note:46757.1 titled "Notes on Choosing an Optimal DB BLOCK SIZE" says that there are some benefits from having larger blocksizes, but only under specific criteria (paraphrased from Metalink):
Large blocks gives more data transfer per I/O call.
Larger blocksizes provides less fragmentation (row chaining and row migration) of large objects (LOB, BLOB, CLOB)
Indexes like big blocks because index height can be lower and more space exists within the index branch nodes.
Moving indexes to a larger blocksize saves disk space. Oracle says "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."
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 . . . "
Very interesting discussion.
So, here's my 2 cents.
In one specific instance here, I had to implement multiple block size, and it prove benefic.
Siebel is an oltp system, but we had to add some tables to produce reports. These tables were big enough (200gb minimum) and the sql statement on it were reading a lots of blocks.
Performance wise, it was better to create 32k tablespaces, and put these tables in it.
We created a 32 cache in the sga for that purpose.
Overall, we gain better thoughput and reduced IO.
so beautifiul explanation...