Monday, February 12, 2007

I learn something new every day...

Ok, time for a geek (eg: having a smidge of technical content) post.  What I learned new today about Oracle that I don't think I knew about before.  Notice that I said "that I don't think I knew", there is a chance I did - but forgot.

In a nutshell - LOBS are always out of line in an index organized table unless you have an overflow segment defined.  And that fact may have a massive impact on your performance during inserts, retrievals and the total amount of storage used.

ops$tkyte%ORA10GR2> create table t
2 ( x int primary key,
3 y clob
4 )
5 organization index
6 lob (y) store as
7 ( enable storage in row )
8 /
create table t
*
ERROR at line 1:
ORA-22853: invalid LOB storage option specification

So, we cannot special enable storage in row here at all - not without an OVERFLOW clause:  

ops$tkyte%ORA10GR2> create table t
2 ( x int primary key,
3 y clob
4 )
5 organization index
6 lob (y) store as
7 ( enable storage in row )
8 overflow
9 /
Table created.

ops$tkyte%ORA10GR2> insert into t
2 select object_id,
3 object_name
4 from all_objects;
50047 rows created.
Elapsed: 00:00:03.68

ops$tkyte%ORA10GR2> select segment_name,
2 bytes
3 from user_segments;

SEGMENT_NAME BYTES
------------------------------ ----------
SYS_LOB0000065089C00002$$ 65536
SYS_IOT_TOP_65089 4194304
SYS_IL0000065089C00002$$ 65536
SYS_IOT_OVER_65089 65536

About 3 and 1/2 seconds, about 4 MB of data - however, if we just "default" - don't specify the overflow: 

ops$tkyte%ORA10GR2> drop table t purge;
Table dropped.

ops$tkyte%ORA10GR2> create table t
2 ( x int primary key,
3 y clob
4 )
5 organization index
6 lob (y) store as
7 ( disable storage in row )
8 /
Table created.

ops$tkyte%ORA10GR2> insert into t
2 select object_id,
3 object_name
4 from all_objects;
50046 rows created.
Elapsed: 00:02:13.66

ops$tkyte%ORA10GR2> select segment_name, bytes
2 from user_segments;

SEGMENT_NAME BYTES
------------------------------ ----------
SYS_IL0000065094C00002$$ 3145728
SYS_IOT_TOP_65094 2097152
SYS_LOB0000065094C00002$$ 419430400


As you can see - when we skip the overflow clause - the LOB will be stored out of line - regardless of size.  That means each row will touch the lob index and maintain it and each row will allocate at least a chunk of storage (even though we only need 30 characters at most in this example).  So, we go from 3 and 1/2 seconds roughly using inline LOBS to 2 and 1/4 minutes when the LOB is out of line.  Additionally - we go from 4 MB total storage (all of it in the IOT) to over 400 MB (due in part to my chunk size, 8k in this case - as small as possible) spread across the IOT, the lob segment and the lob index.


All of this came about due to a customer saying "sqlldr is really slow against tables with LOBS".  Slowly peel away the onion to find it was IOT's with LOBS (not just tables), peeling further back to recognize the default (and only) storage for a LOB in and IOT without an overflow is "disable storage in row" - and there you go.  "The reason".


Lots of layers on some onions.

POST A COMMENT

11 Comments:

Blogger Bill S. said....

So then, are you saying IOTs with lobs are like ogres?

:-D

Bill S.

word verification: qterpled

Mon Feb 12, 04:00:00 PM EST  

Blogger Noons said....

yeah sure, agreed: layers.

but the problem still is that at some stage in future, it's likely that inline LOBs will move to out of line.

THEN, you pay the overhead price.

Example: the db block size = 8K and the LOB exceeds the 4K inline limit by 1 byte: bang, you now have an overhead of one 8k block to store a 4k + 1 LOB.

All we're doing with inline is delaying the impact of out of line storage.

And then we have other problems, like the UNDO stored in the out-of-line area.

Worse: unless one knows for sure what the pattern of updates to the LOB is going to be, it's very hard to predict how space will be used.

Might as well bite the bullet upfront, store the LOB out-of-line and incur the overhead once.

The real solution to this is for Oracle to start using spanned blocking for LOB storage.

VSAM had spanned storage 30 years ago and it seems to have worked well for them: it's still used inside db2...

Mon Feb 12, 05:02:00 PM EST  

Anonymous Anonymous said....

Right - of course the implicit (likely obvious) assumption here that your LOBS are typically of a size that allows for storage within in a row.

That said, the statement:

but the problem still is that at some stage in future, it's likely that inline LOBs will move to out of line

is a sneaky and unjustified premise. The rest of noons's argument is thus unsound. And some nonsensical: "UNDO stored in the out-of-line area"?

Please please please stop the spread of misinformation!

Mon Feb 12, 06:30:00 PM EST  

Blogger Noons said....

a cowardly anonymous poster claims an assumption that LOBs stay in the same row.

Yet: what does the "L" stand for in LOB?

"Large", isn't it?

So the assumption that it will stay small is correct and the assumption it will increase in size is "sneaky and unjustified".

Obviously.

I can see where the "science" is, here...


As for the other comments, please RTFM: it's strongly recommended, in your sorry incompetent case.

Nothing like anonymous "reliable and informed spread of information", eh?

Mon Feb 12, 07:47:00 PM EST  

Anonymous anysql said....

The CLOB must use fixed-width character set, so if you database is fixed-width character set, CLOB will use that character set, else it will be converted to unicode (UCS 2). We do find that LOB will consume a lot of storage than varchar2. For a 4096 bytes data, it may need to chunks (by default 1 block per chunk).

Mon Feb 12, 07:51:00 PM EST  

Anonymous Mr. Ed said....

Funny that LOBs don't count against the row size limit on IOTs (VARCHAR2s do).

Mon Feb 12, 11:46:00 PM EST  

Blogger Alberto Dell'Era said....

"ORA-22853: invalid LOB storage option specification" is a bit misleading, wouldn't "ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces" be much more appropriate ?

Since the stored-in-row piece is about 3900 bytes in size max, the problem looks very similar to:

SQL> create table t
2 ( x int primary key,
3 y varchar2(3900)
4 )
5 organization index;
create table t
*
ERROR at line 1:
ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces

If I had gotten ORA-01429, I would have had a clear hint about the underlying problem and solution.

Tue Feb 13, 05:21:00 PM EST  

Anonymous Anonymous said....

Tom,

Any plans with Oracle to fix this performance and resource issue once we get over the 4KB magic number ? I ask this since you actually work for Oracle. How might one get around this limitation, and still preserved the performance and resource advantage of in-line (i.e. is there a workaround)? It seems this is a major deficiency given the fact that Oracle has been discouraging users from using any other stream type (i.e. long, long raw ..) and that Oracle XML database uses CLOBs/Blobs. If this is not fixed, I would think the user will receive a nasty surprise once their data is over 4KB with in-line LOB.

Thanks.

Thu Aug 30, 04:54:00 PM EDT  

Anonymous Tim said....

good Job! :)

Mon Sep 08, 10:01:00 AM EDT  

Blogger sahil said....

When I ruining the following script

ALTER TABLE T_Transaction_Image
MOVE LOB(RCSCOMPRESSED_IMAGE,CAMERA_PHOTO_1,CAMERA_PHOTO_2,NUMBER_PLATE)
STORE AS TABLESPACE IMAGE

I found error

ORA-22853: invalid LOB storage option specification

Here we have more than one column having BLOB data type .

Question

I want to know can i move multiple column in single alter table ?

and how can i move blob column one tablespace to another tablespace

Thu Feb 03, 06:41:00 AM EST  

Blogger Thomas Kyte said....

ops$tkyte%ORA11GR2> alter table t move
2 lob(y) store as (tablespace example)
3 lob(z) store as (tablespace users)
4 /

Table altered.

Thu Feb 03, 06:45:00 AM EST  

POST A COMMENT

<< Home