Re: Ever popular LOB question
- From: Brian Wisniewski <brian_wisniewski@xxxxxxxxx>
- To: tanel.poder.003@xxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 18 Aug 2005 17:36:26 -0700 (PDT)
Thanks for the excellent explanation Tanel - I knew you'd have the answer!.
Thanks - Brian
Tanel Põder <tanel.poder.003@xxxxxxx> wrote:
Hi,
Oracle handles out of line "enable storage in row" LOBs and out of line
"disable storage in row" LOBs differently. With an enable storage row out of
line lob, Oracle can keep up to 12 LOB chunks address in line with row itself,
thus there is no need to access LOB index for reading the LOB as long as it
fits into 12 LOB chunks and is not modified after creation (12 chunks is max
384kB, depending on chunk size, which max is 32kB).
When modifying an existing LOB, then Oracle uses LOB index for maintaining read
consistency within LOB segment (unlike with LONGs, you can update one chunk of
it only if you like). But as long as you insert only up to 12 chunk LOBs and
you don't modify them, the LOB index remains empty.
But with "disable storage in row" LOBs, you'll always need to access LOB index
after getting the locator from row, thus needing extra buffer gets/IO even with
tiny LOBs. On the other hand, inline LOBs might take lots of space in your
datablock (as LONGs do), thus making full table scans slow (especially when you
don't need LOB info), but on the other hand, if you're doing indexed access to
this table only, you won't have FTS speed issue, but again you'd probably still
be doing more IOs for index non-unique and range scans (as rows are likely more
scattered across blocks).
One way to keep your tables small, but avoiding LOB index access would be then
to define LOB as "enable storage in row" and to pad LOB items <= 3964 bytes in
size to a bit larger value, that way LOB items would always be in external LOB
segment and the LOB chunk addresses would be kept inline, thus eliminating need
for LOB index access. But as I mentioned, it works only for non-updated LOBs
with size under 12 chunks - and you would need to keep the real LOB length
information somewhere (probably in the row as an extra column) as well.
Also there are other issues, like if some of your LOBs are really small, then
you'd be wasting space and IO bandwidth, since out of line LOB item space
allocation granularity is 1 chunk and redo for out of line LOBs is logged in
full datablocks, not with granular redo vectors as with normal segments.
In case of huge number of LOBs, this could perhaps be fixed by having one
VARCHAR2/RAW datatype (not LONG RAW) column for small LOBs and BLOB/CLOB column
for big ones. I like RAW better for tiny binary information, as it is
internally a regular column and it doesn't have LOB locator overhead.
Some more info on LOB internals & performance:
http://integrid.info/Poder_LOB_Internals.pdf
Tanel.
----- Original Message -----
From: Brian Wisniewski
To: JApplewhite@xxxxxxxxxxxxx ; oracle-l@xxxxxxxxxxxxx
Sent: Friday, August 19, 2005 7:20 AM
Subject: Re: Ever popular LOB question
Thanks Jack. However, Oracle's docs state the opposite - even for lobs > 4000
bytes. I was trying to find this - I knew I had read it before but couldn't
find it until 5 minutes ago.
"The default, ENABLE STORAGE IN ROW, is usually the best choice for the
following reasons:
Small LOBs: If the LOB is small (< 4000 bytes), then the whole LOB can be
read while reading the row without extra disk I/O.
Large LOBs: If the LOB is big (> 4000 bytes), then the control information
is still stored in the row if ENABLE STORAGE IN ROW is set, even after moving
the LOB data out of the row. This control information could enable us to read
the out-of-line LOB data faster."
What control information is Oracle storing with enable storage in row that
isn't present when the row is explicitely stored out of line? There's still
the pointer but is there something more? Seems strange that the preferred way
would be "inline" even if it's going to be moved out of line due to size.
Thanks - Brian
JApplewhite@xxxxxxxxxxxxx wrote:
Explicitly store 'em out-of-line from the get-go.
1. You can manage the LOB segments more directly and sensibly.
2. Queries that just need "regular", non-LOB, data don't have to read as
many blocks.
Worked for me with 13 million CLOB documents. Also, almost essential if
you ever use interMedia / Oracle Text to index the docs (which we did).
Jack C. Applewhite - Database Administrator
Austin (Texas) Independent School District
512.414.9715 (wk) / 512.935.5929 (pager)
The devil made me do it the first time,
The second time I done it on my own.
- Billy Joe Shaver, "Black Rose"
Brian Wisniewski
yahoo.com> cc:
Sent by: Subject: Ever popular LOB question
oracle-l-bounce@fr
eelists.org
08/18/2005 12:27
PM
Please respond to
brian_wisniewski
RHAS 3.0/Oracle 9.2.0.6
Is there any benefit to explicitely stating 'out of line' on lobs outside
of rowsize/possible caching issues. I'm going to be storing ~100K lobs -
chunksize defaulted to the blocksize - 8k so obviously these will be out of
line because of their size in this particular table. Once the lobs are
inserted into the database they will not be updated.
I'm not sure of how Oracle will recognize the entire size of the lob when
it gets it from the application. Will Oracle start storing it in line w/
associated redo until it reaches ~4k and then move it out of line for every
one of these lobs? That would seem like an expensive operation but I'm
not really sure of the behind the sceens actions of what Oracle is doing.
I'm trying to set up some tests with our apps folks but was wondering if
anyone had any insight regarding explicit out-of-line vs in-line migrated
to out-of-l ine because of size.
When we were dealing with smaller lobs we experienced decrease performance
when we had explicitely used out-of-line. But those lobs at the time would
have been a mix of in-line and out-of-line so I don't consider that valid
given the difference in lob sizes.
Test, test, test - I know - just having 'challenges' with developers. I
also want to test different chunksizes but time is critical right now.
Any thoughts/experiences you'd like to share regarding lobs?
Thanks - Brian
--
//www.freelists.org/webpage/oracle-l
---------------------------------
Start your day with Yahoo! - make it your home page
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Other related posts:
