Niall's Oracle Pages

Couple of things came up on Oracle-L recently that prompted some playing on the laptop.

1, Block sizes. It remains possible in 10g to choose completely mad block sizes - for example the 5k block size we have here.

SYS 27-APR-04@block>show parameter block
NAME TYPE VALUE
------------------------------------ ----------- =
----------------------------
db_block_buffers integer 0
db_block_checking boolean FALSE
db_block_checksum boolean TRUE
db_block_size integer 5120
db_file_multiblock_read_count integer 16
SYS 27-APR-04@block>show release
release 1001000200
SYS 27-APR-04@block>

This would not be a sensible thing to do in production. Why mention it? Well you might be thinking that Oracle can only use the traditional 2,4,8,16 or 32k block sizes. This is just not true. More to the point this page on Steve Adam's excellent web site gives suggestions as to how sensibly to set the block size for a database.

2, The old separating indexes and data for performance reasons is a myth chestnut came up again. Enough already. the two have no automagic correlation, if you have io contention find out where it is and separate those segments. Mean time some things to wonder about.

where does an IOT go then?
what about a bitmap join index?
what about parent/child tables?

10046 will likely show you what blocks you repeatedly wait for and there are other hot block scripts around as well.