use of db_file_multiblock_read_count parameter in oracle

db_file_multiblock_read_count parameter in oracle
================================================


"Multiple block sizes were implemented to transport tablespaces."


That is what they primarily were implemented to facilitate it .

--------------------------------------------------

Questions:-
===================

1) We have LOB column tables and I see lot of chainig/migration.
If we move the LOB column tables to a bigger blocksize tablespace.
Will that not reduce the migration?
2) I think the The max block size possible is 32k on 9i.
How do reduce the migration for such big column tables as average
row length is more than 100k?

Ans:-

answer from Tom katys :
===================

1) lob columns are stored inline ONLY if they are 4000 bytes or less.
If they are bigger than a varchar2 - the are automagically moved out
of line into the lob segment.

It could be that a simple alter table t move with a larger pctfree
to set aside sufficient space for updates is what is needed
(and that moving to a larger blocksize without changing pctfree
would not *fix a darn thing*).

If you are using lobs - I feel pretty confident in stating
"the average row length is NOT 100K!!!!",
the lobs are stored OUT OF LINE, in the lob segment,
when they exceed 4000 bytes.

(and if they were 100k, just give up, I don't care what your
block size is, it won't matter)


to move indexes to a tablespace of 16k, helps me to keep memory
only for indexes, and control better the index memory usage.
For me (a small database) makes sense.

Consider (if ANYTHING) the keep and recycle pools, but
using multiple block sizes for "tuning" isn't really something recommended.


I've not advised to use multiple blocksizes, use the keep and recycle pools
if you want to segregate things out.
use keep and recycle pools if you want multiple buffer caches