Re: Use of Multiple BLOCK Sizes ?

VIVEK_SHARMA wrote,on my timestamp of 19/12/2006 9:40 PM:
>
>
> Does use of Multiple BLOCK sizes (for different Tablespaces) help in
> Performance of a Hybrid Application?

Performance by itself, I don't think so. At least, not that I know of. There are some papers on it being of use in indexes, but nothing has been conclusively proved in that area despite heaps of discussion about the subject around two years ago.

Having said that: db2 uses different tablespace page sizes quite effectively to optimize IO for "hot" tables. So perhaps that is an area for further research with Oracle?

As mentioned already, it may definitely help with LOBs. Not necessarily in performance but more in terms of management of disk space. Keep in mind that a LOB instance stored in-line with the table uses nothing in the LOB tablespace. But when it grows beyond 4K, it gets "moved" to the LOB tablespace.

And this is where it gets interesting: when this happens, there will be only one LOB instance per block in the LOB tablespace. Or one "LOB row" per block, if that makes more sense.

So if your LOB is just over 4K but your LOB tablespace block size is much larger, you'll basically be wasting disk space. Of course as the LOB grows larger, it'll use more and more of its block size. Until it "spills over" to additional blocks. Whereby the cycle of disk wastage repeats again.

As such I'd be careful with increasing LOB tablespace block size indiscriminately without some careful analysis of the pattern and history of LOB size growth in the given application: I might well end up with a lot of unused space.

Add to that the fact that UNDO space for a LOB is taken off the LOB tablespace and not off the system UNDO tablespace and things get interesting very quickly in a high update activity LOB tablespace.

Another thing to be aware of: except for 9.2.0.8 and the latest patch levels of 10gr2, do NOT use ASSM in a LOB tablespace if the LOB gets updated frequently: there is a bug with handling the UNDO of the LOB with ASSM that will virtually ensure corrupt LOB data at some future stage. The symptom is that you start getting 1555 errors even when no one else is using that table and no matter what you do. The recovery is ugly...

And that's as much as I want to know at the moment about LOB tablespaces, their block sizes and the relationship between the two.

Add to this the already mentioned warning about memory usage and impact on it of different block sized tablespaces.

-- 
Cheers
Nuno Souto
dbvision_at_iinet.net.au
--
http://www.freelists.org/webpage/oracle-l