Re: LOB columns and migrated rows

What problem are you trying to solve?

I stored millions of LOB's of various sizes in a database and didn't concern myself with the issue of chained rows. The SLA's I dealt with were wrapped around how fast files from clients were loaded into the system and available for viewing.

Unless you have static sized LOB's, and depending upon the manner in which you load the LOB's, it may be extremely difficult to prevent chaining.

During testing disabling in-line storage of the lobs considerably slowed the load process and it was a better business decision to leave them in-line with chaining vs storing them out-of-line.

Response time was never an issue so 'chained rows' wasn't a problem that needed to be solved. Was more undo/redo generated - yes - but once again - non-issue for the application I was supporting.

With out-of-line lobs the minimum size of a lob will be a single block-size. So if you're storing 50M - 500 byte blobs out-of-line in a 32K block then I hope you're prepared for the 1.5T you'll need vs the 25G storing it in-line.

Also - I don't think you've been able to store the LOB index separate from the LOB since V8 of Oracle.

Multiple block sizes can help with caching of blocks in memory but I don't personally believe it's effective and worth the hassle. If you have a table that is forcing blocks to be flushed from cache for whatever reason I'd look more at the recycle and keep caches with 1 block size before even thinking about going to multiple block sizes.

Also in Linux at least if you go with a large SGA and have to revert to db_block_buffers vs db_cache_size you can only have 1 block size.

Just a few things to think about.

• Brian

"hitender chugh" <chughhk_at_hotmail.com>
Sent by: oracle-l-bounce_at_freelists.org
02/05/2006 09:41 AM
Please respond to chughhk  

        To:     tanel.poder.003_at_mail.ee, oracle-l_at_freelists.org
        cc: 
        Subject:        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




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