Re: Use of Multiple BLOCK Sizes ?
- From: Nuno Souto <dbvision@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 20 Dec 2006 03:13:52 +1100
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@xxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts: