db_block_size

[Greg Hall]

Hi,

Does HPUX v11.11 support an Oracle database block size of 32K? Any comments on the implications of using a larger block size? This will be a Data Warehouse application.

thanks, Greg

[A. Clay Stephenson · Accepted solution]

Yes it does and don't let anyone tell you to match database block size to filesystem block size (as long as you are using vxfs filesystems -- and you should be). Vxfs filesystems are extent based meaning they try to write in large chunks and the block size has nothing to do with this. Whether or not your 32K blocksize makes sense depends upon the data. I've found that for most applications, a good all-around blocksize is 8k.

[Indira Aramandla]

Hi Greg,

Yes Oracle DB block size of 32K is supported.

Oracle block size has high incidence in the throughput of the database. Depending on the type of transactions involved the size of the parameter DB_BLOCK_SIZE might help to improve the performance of the I/O operation.

Usually for OLTP (Online Transaction Processing) systems is advisable to have smaller block size assuming that data requirement for information tend to be small and granular. Contrary, for DSS (Decision Support Systems) were reads tend to be large, a bigger database block size will help in caching more information in memory with less I/O accesses.

Oracle recommends to have a Oracle block size as a multiple of the Operating system block size to reduce the overload in I/O operation.

With large DB block size the advantage is there is relatively less overhead, thus more room to store useful data. Good for sequential access, or very large rows. The disadvantage is large DB block size is not good for index blocks used in an OLTP type environment, because they increase block contention on the index leaf blocks. s more room to store useful data.

Attached is a white paper from metalink "Choosing an Optimal DB BLOCK SIZE and Block Size Advantages and Disadvantages".

Indira A

[Greg Hall]

excellent, thankyou people. I'll feed all that to the DBA, should keep them quiet for a while. :)

cheers, Greg

[Yogeeraj_1]

hi greg!

for data warehouse application you can use 32K block sizes since HP-UX supports it.

Here are a couple of pros and cons (some of them will be both pros and cons --
depends on your perspective)....

Below a list of pros and cons for going from 2k blocksize to xk blocksizes - x > 2 (depending on your perspectives of course)

1. The total amount of block overhead in your database will decrease. The amount of overhead per block is normally fixed. If your block is 4 times LARGER then you will have less then 1/4 the block overhead after the change over.

2. Row chaining will decrease and perhaps less row migrations. As you must akready know, row chaining happens when a row is too large to fit on one contigous block (which is much less likely in an Xk block then a 2k block). When does row migration happens? it is when we attempt to update a row but discover there is insufficient space on the block to do so. Therefore, we leave the "head" of the row on the original block (cannot change the rowid) and put the row itself on another block entirely. When you go from 2k with a 10% pctfree (default) to an Xk block with 10% free means we have perhaps more free space on each block to accomidate this (but we also have more rows per block so it might not matter all of the time).

3. Contention for the same block may increase perhaps. As you do lots of stuff at the block level -- you may notice more contention in this area.

4. You will have the same amount of data cached (volume) but the granularity of the data will be N times larger. For every row you want, you might get N times as many rows pulled along. This can be good -- this can be bad. It really depends on what you are doing. In a heavy duty OLTP system with lots and lots of scattered reads all over the place -- this might be bad as no one is going after the same blocks (eg: I read a row on a block and no one else ever needs that block -- i cached lots of stuff no one is going to use and I may have bumped out some other data that we could have used). In a query intensive system (DSS, Data Warehouse), it may be very good as I was going to read the blocks right around my data anyway. And - people are going to be needing that data again and again.

5. The number of rows per block in indexes will increase hence indexes will require less maintenance as they tend to need to reorganize themselves less frequently.

6. Larger block sizes will allow for larger index keys (although whether or not THIS is a good thing is in question as well, there is something to be said for reasonably sized index entries). The size of the index keys is a little less then about 40% of the block size. In an 8k database for example, you'll get:
create index myidx on t1(a,b,c)
*
ERROR at line 1:
ORA-01450: maximum key length (3218) exceeded

for a 2k block, it would be lesser.

hope this helps too!
regards
yogeeraj

[Fred Ruffet]

Be aware that increasing DB_BLOCK_SIZE parameter will change optimizer behaviour. The larger is DB_BLOCK_SIZE, the cheaper is a full table scan. So CBO will be more likely to choose this kind of access and this may not be a good thing. It's better to make test before increasing this parameter on your production system.

This may increase administration tasks, but you can set a different block size for different tablespace, depending on their usage.

Regards,

Fred

[Yogeeraj_1]

hi greg,

see also:
http://www.ixora.com.au/tips/buffered_block_size.htm

hope this helps too!
regards
yogeeraj

[Eric Antunes]

Hi Greg,

I just want to add a consideration for you to choose your block size.

For good performance your Oracle Block Size should be equal to or a multiple of the O/S blocksize.

See metalink Note 34020.1 for more info about db_block_size...

Best Regards,

Eric Antunes