Re: LOB columns and migrated rows

hi,

>During brief check I didn't get where and which overheads were mentioned?
It was by Ric from hotsos.com
>Using multi-block sizes within a database is not necessarily a bad
>thing, it is however a maintenance issue at multiple levels. Managing
>the data files becomes a bit more cumbersome, and managing the buffer
>pool is not quite as straight forward.

So what issues we might have to deal with managing the datafiles?
>A LOB item stored within row is never larger than 4000 bytes (36 bytes for
>metainfo and rest for LOB data). When you exceed this limit, Oracle stores
>LOB data in separate LOB segment.

So if I disable the storage of LOB data in the row the LOB data is stored in seperate segments then
I do not have to worry about chaining and migration. What is the best way for storing/managing LOB segements? As per my understanding storing LOB segments they should have their own tablespaces and storing of LOB indexes in seperate tablespaces is bing deprecated.

Thanks in Advance.

>From: Tanel Põder <tanel.poder.003_at_mail.ee>
>Reply-To: tanel.poder.003_at_mail.ee
>To: "ORACLE-L" <oracle-l_at_freelists.org>
>Subject: Re: LOB columns and migrated rows
>Date: Sun, 5 Feb 2006 02:36:17 -0500
>
>Hi,
>
>>I want to know whether it is being used in real world to get I/O
>>performance ,for storing indexes in 32 k tablespaces and/or storing LOB or
>>big column tables.
>
>Playing with blocksizes can be a two-edged sword. With bigger block size
>you might reduce index height and number of leaf blocks, making some
>queries faster etc, but you could introduce buffer busy waits due
>concurrent access to too many rows sharing the same block.
>
>Test it out for your learning and amusement, but I think multiple
>blocksizes for normal tables/indexes for performance reasons are justified
>only in extreme systems, where you are willing to put 90% more effort to
>gain 10% in performance. For example with multiple blocksizes you
>effectively have to split your cache and mange their sizes differently (10g
>sga_target might relieve this issue though).
>
>>>But for chained rows - what is the block size for original poster?
>>The db blocksize is 8k.
>>In one of the posts it was mentioned that it has maintenance overheads.
>>What kind of maintenance issues we have to deal with?
>
>During brief check I didn't get where and which overheads were mentioned?
>
>A LOB item stored within row is never larger than 4000 bytes (36 bytes for
>metainfo and rest for LOB data). When you exceed this limit, Oracle stores
>LOB data in separate LOB segment.
>
>So if you'r table has only one LOB column, it's unlikely that your row
>sizes exceed about 8000 bytes in size (unless you have large number of
>other long columns in the table which might show non-optimal (physical)
>design IMHO). Anyway, when all your rows are smaller than 8000 B then you
>shouldn't have chained rows due too large row size. You might have migrated
>rows (which are just a special case of chained rows internally), but these
>can be "fixed" with appropriate PCTFREE, not block size. If you went to 32k
>blocksize and have a not appropriate PCTFREE value, you will still get
>migrated rows.
>
>
>Tanel.
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l