This content has been marked as final. Show 34 replies
Hi,There's No advantage to have multiple block size in database in either of these types of system. Having multiple block sizes in one database will only increase complexity and decrease efficiency of your database.
What is the advantage of having multiple block size
for OLTP and DSS systems.
For general guideline of choose block size check here
Choosing Data Block Size
it is recommended to have small sized blocks for OLTP, and big sized for DSS. The reasons:
On OLTP, a smaller block size reduces contention:
1. Reduces the possibility of concurrent transaction and locks on the same block.
2. Provides a better locking mechanism
3. Less transacction locks possibilities at the block header.
DSS benefit from big blocks mainly because of IO:
1. Retrieves in less IO operations more data, which is critical for FTS and index scans.
2. A higher row density means less IO
3. Indexes retrieve information on a block per block basis, with big blocks indexes are retrieved with less IO
4. Reduces the chances of chained rows.
it is recommended to have small sized blocks for OLTP, and big sized for DSS
It is not recommended to be quite so simplistic about it, however!
You state that "smaller block sizes reduce contention". If I have a 16K block which is 1/8th full and a 2K block that is 100% full, which one will suffer more contention?
Contention, in other words, is not a function of block size per se, but a function of the amount of data which is stored within a block. A higher PCTFREE, for example, allows you to store no more data in a big block than you would in a small block, thus incurring no more contention than you would with a small block... and yet you would get all the advantages that you list for DSS systems using big blocks -lower I/O, better index performance, low row chaining for tables that don't have a contention problem.
Trivialised rules of thumb such as these are really not worth repeating, in short. The problem (contention vs. I/O efficiency) is a subtle one, for which many different cures exist (PCTFREE, INITRANS, MAXTRANS), and block size is one of the last you should think of because it is so inflexible.
The locking mechanism in Oracle doesn't depend on the block size, either, so I have no idea what you're getting at with your point 2 for OLTP systems!
Different block sizes.
Requires to process specific rows, if on a big sized block, then there will be buffer wastage. A 128 bytes row retrieved by an OLTP system from an 8K block tablespace will waste 8K - 128bytes, meanwhile on a 2k block size, the wasteage will be 2K-128bytes.
Improvements in data buffer utilization.
By segregating high activity tables into a separate, smaller data buffer, Oracle has far less RAM frames to scan for dirty block, improving the throughout and also reducing CPU consumption. This is especially important for super-high update tables with more than 100 row changes per second.
on DSS the most frequent operations found is FTS and index scans. The effort to retrieve indexes will be bigger for a small sized tablespace than for a big sized tablespace, as for small blocks oracle will have to perform a greater number of operations.
Oracle b-tree indexes are built in flatter structures in 32k blocksizes. There is a meaningful reduction in logical I/O during index range scans and sorting within the TEMP tablespace because adjacent rows are located inside the same data block.
By moving Oracle indexes into a fully-cached 32k buffer will ensure that Oracle favours index access, reducing unnecessary full-table scans and greatly reducing logical I/O because adjacent index nodes will reside within the larger, 32k block.
Datablock header contention
Data block header contention is reduced, as the number of rows to be affected by a transaction is less in a small sized block than in a big sized block, reducing the related CPU cost.
Ref. Different Block Sizes
Ah, I see you've been drinking from the fountain that is Don Burleson.
Your points have about as much validity as his stuff usually has, however.
OLTP: you rather convolutedly state that small blocks have a bigger Oracle overhead than big ones. True. So what? When I measure my RAM in multi-gigabytes and my disk space in multi-Terabytes, who gives a fig about the sort of wastage amounts you're talking about?
Improvements in data buffer utilization This is just pure Burleson. Including phrases such as "super-high update tables" and completely spurious claims that there's some magic cut-off limit of 100 row changes per second that somehow mandate the use of different block sizes. You don't have any facts or figures to back this silly claim up. Meanwhile, it's just hand-waving hocus-pocus.
Oh, and if you're going to quote Burleson, at least do him the courtesy of a citation. In this case, you've lifted verbatim large chunks of http://www.dba-oracle.com/oracle_tips_multiple_blocksizes.htm
Plagiarism is ugly at the best of times, but plagiarising Don is worse.
DSS Systems: Yes, you've just agreed with me. Thanks: Indexes do indeed work better with bigger blocksizes than with smaller. OLTP systems use indexes, too, of course.
Indexes Yet more pure Burleson. Yes, let's just move all indexes into 32K blocks and have done with it. Let's NOT mention the increase in contention this will cause. Let's not mention that you can't even use 32K blocks on Windows or Linux. Let's keep quite about the fact that this is the silliest sort of advice imaginable.
Datablock header contention: Again, the point has been completely missed that contention is the result of the amount of data stored in a block, not the size of the block itself. If you can waste space in a block, thus reducing the amount of data it stores, then you can reduce contention. Therefore, use a big block and use PCTFREE and/or INITRANS to reduce the number of rows the block can store for those few segments that actually need this sort of treatment.
The alternative is to scare people into using smaller blocks in an attempt to head off contention that might never happen; in doing so, they will have crippled their index performance and their full table scan performance; every segment will be crippled in this way; and they won't be able to alter their decision except by re-creating their entire database. Oh, silly me: of course, they could create bigger blocksized tablespaces and move their tables there. Except they won't then be able to use the keep or recycle caches for such tables. Which means they'll be sharing a large, undifferentiated default cache, which you (or Don, it's difficult to say) earlier claimed would be bad news because "separate smaller data buffers" are supposed to be so much more efficient.
Jeesh! Make your mind up!!
Your post is, I'm afraid, a mere regurgitation of some very, very bad advice and has accordingly no technical merit whatsoever.
On the contrary.
I'm merely pointing out that (a) your contribution is actually Don Burleson's, without the attribution; (b) Don Burleson's views on multiple block sizes are technically wrong and misleading and (c) that your contribution on the subject is therefore equally technically wrong and misleading.
That's not 'wanting to fight' you. That's wanting to point out the technical deficiency of your post. There's a difference.
Interesting you appear concerned about "buffer wastage" with regard to tables but not indexes ?
You might be surprised to find that due to the order of magnitude required for an index to increase it's height, you'll find that a significant proportion of indexes between (say) an 8K and a 32K block size actually have the same height. Remember that a 32K block can only hold 4 times what can be held in an 8K block and yet a height increase with just 2 branch blocks can hold double the previous index structure. Even an 8K block can hold many hundreds of branch pointers.
However, assuming that you have a 32K block index that has 2 levels and say you had an equivalent 8K block index that was 3 levels, consider the cost of a scan for a moment.
With the 32K index, you MUST read the root block (32K) plus a leaf block (32K) for a total of 64K of index block/buffer as a minimum, say for a unique scan or a moderate range scan that reads less than 8K or so of index row entries.
For the 8K index, you ONLY have to read 8K for the root block, 8K for the additional branch block and 8K for the leaf block. Yes, that's 3 LIOs and yes there are some overheads associated with the additional LIO but they only require 24K of buffer or 24K off disk in a worst case scenario vs. 64K of buffer or 64K off disk in a worse case scenario.
And yet reading and (potentially) pinning 64K each time is always better, remembering that in many cases, both the 8K and 32K index will have the same height ?
Yes, for very large range indexes scans, the number of LIOs can be less with a 32K block size, however, in OLTP what's more common, very large index scans or unique / small index scans ?
The advantages of 32K blocks for indexes sometimes gets rather overstated, especially when the 32K is not the default database block size and especially in OLTP system ...
No, I respectfully disagree. . . .
Don Burleson's views on multiple block sizes are technically wrong and misleading
Tell me, Howard, if multiple blocksizes are "wrong" why are they used in TPC benchmarks? Are the major hardware vendors who use multiple blocksizes wrong too?
No, I respectfully disagree. . . .
Well, as Christine Keeler would have said, "You would say that, wouldn't you".
You can harp on all you like about TPC benchmarks, but as you well know they are simply carefully-crafted synthetic benchmarks to gain marketing and bragging rights and their relationship to the real world is tenuous at best. Do you warn your readers that unless they're spending $6m or $1.4m on their hardware, the TPC results may not apply to them? Or that the parameter configurations taken to achieve them may be highly detrimental on less expensive (=somewhat more real-world realistic) kit? No??
No. In fact, you do the opposite. You encourage the use of TPC-inspired techniques without qualification. A bit like encouraging desktop PC users to configure as if they were running on an IBM BlueGene supercomputer... and about as useful.
I have gone thru the following link:
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
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')
(This command is executed several times - the execution time was approximately the same ~
And now the test with the same table, but created together with the index in 16k tablespace:
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')
(Again, the command is executed several times, the new execution time is approximately the same ~
Does it really happens? why?
Yes it really happens.
If you have to work on (say) 1MB of data, and that 1MB is stored in 128 8K chunks, you have to do 128 bits of work (logical reads, essentially). Store it in 64 16K chunks and you've just reduced your workload by half. You're still working on the same amount of data, of course, but you've reduced the number of 'units of work' required to process that data.
Just to be clear: it's certainly not a myth that big blocks are more efficient for Oracle to work with than small ones. There is an excellent case to be made for selecting 16K or 8K blocks instead of 4K or 2K (though as ever, there are exceptions). The problems start when people want a mix of block sizes in the one database, because then they are asking Oracle to do things Oracle wasn't designed to do, and which it doesn't do very efficiently in the long run (which is what you'll want a production database to be good at!)
I respectfully disagree.
it doesn't do very efficiently in the long run
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:
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:
Hope this helps. . .
Oracle Press author
Author of “Oracle Tuning: The Definitive Reference”