Re: LOB columns and migrated rows

  • From: "hitender chugh" <chughhk@xxxxxxxxxxx>
  • To: tanel.poder.003@xxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Sun, 05 Feb 2006 09:41:29 -0500


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@xxxxxxx>
Reply-To: tanel.poder.003@xxxxxxx
To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
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.

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




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


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 02-2006