2007/02/17

why are LOBs dangerous?

because for the last 5 years, Oracle has heard from many users that the way they get updated is broken and needs recoding.

And they have done preciously nothing about it.
In 8i, 9i and 10g.

Heck, they don't even use them in their dictionary itself!


Have a look at just one of many problems - for more, go to Metaclick and search on LOB in the bug database.

It starts off mild and sedate:


SQL> create table zot (f1 number, f2 clob)
2 lob (f2) store as (cache disable storage in row)
3 /
Table created.

SQL> select segment_name,bytes from user_segments
2 /
SEGMENT_NAME
---------------------------------------------------
BYTES
----------
ZOT
1048576
SYS_IL0000052408C00002$$
1048576
SYS_LOB0000052408C00002$$
1048576 <--------------All is good
3 rows selected.

SQL> insert into zot
2 select object_id,object_name from all_objects
3 /
2903 rows created.
Statistics
----------------------------------------------------------
947 recursive calls
39894 db block gets
55033 consistent gets
0 physical reads
3299520 redo size
1020 bytes sent via SQL*Net to client
1074 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2903 rows processed

SQL> commit;
Commit complete.

SQL> select segment_name,bytes from user_segments
2 /
SEGMENT_NAME
-------------------------------------------------
BYTES
----------
ZOT
1048576
SYS_IL0000052408C00002$$
1048576
SYS_LOB0000052408C00002$$
24117248 <--------------no problemo
3 rows selected.


do the arithmetic for 8192 block size and 1MB uniform allocation in LMT and that is precisely what one would expect.

Note that there is one block allocated for EACH separate LOB value in each row. 8192 byte block size in this case.

Now the real fire and brimstone starts...


SQL> update zot set f2 = rpad(f2,4000,'Z');
2903 rows updated.
Statistics
----------------------------------------------------------
1084 recursive calls
23231689 db block gets <---Holy cow!
31800555 consistent gets <--Holy stampede!
1474 physical reads
21930192 redo size
1020 bytes sent via SQL*Net to client
1050 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2903 rows processed

SQL> rollback;
Rollback complete.

SQL> select segment_name,bytes
2 from user_segments
3 /
SEGMENT_NAME
---------------------------------------------
BYTES
----------
ZOT
1048576
SYS_IL0000052408C00002$$
1048576
SYS_LOB0000052408C00002$$
48234496 <----------------Yikes!
3 rows selected.


even after a rollback, my LOB segment is still TWICE what it was before!

No wonder: the code for UPDATE of a LOB uses the LOB segment itself to keep the previous version. Yes, Virginia: the UNDO image for the LOB is in the LOB segment itself!

"Not fair", I hear you whine? I increased the size of the LOB and that's why it ended up with increased allocation?


OK, then consider this:



SQL> drop table zot
2 /
Table dropped.

SQL> create table zot (f1 number, f2 clob)
2 lob (f2) store as (cache disable storage in row)
3 /
Table created.

SQL> insert into zot
2 select object_id,rpad(object_name,4000,'Z') from all_objects
3 /
2903 rows created.
Statistics
----------------------------------------------------------
961 recursive calls
39898 db block gets
55034 consistent gets
1 physical reads
14931568 redo size
1023 bytes sent via SQL*Net to client
1092 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2903 rows processed

SQL> commit;
Commit complete.

SQL> select segment_name,bytes from user_segments
2 /
SEGMENT_NAME
----------------------------------------------------
BYTES
----------
ZOT
1048576
SYS_IL0000052411C00002$$
1048576
SYS_LOB0000052411C00002$$
24117248 <-------------as expected
3 rows selected.

SQL> update zot set f2 = rtrim(f2,'Z');
2903 rows updated.
Statistics
----------------------------------------------------------
1011 recursive calls
104779 db block gets
19335358 consistent gets <----again!
162 physical reads
10321304 redo size
1023 bytes sent via SQL*Net to client
1046 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2903 rows processed

SQL> commit;
Commit complete.

SQL> select segment_name,bytes
2 from user_segments
3 /
SEGMENT_NAME
--------------------------------------------
BYTES
----------
ZOT
1048576
SYS_IL0000052411C00002$$
1048576
SYS_LOB0000052411C00002$$
48234496 <-----Errm... nice theory...
3 rows selected.


Amazing: I REDUCE the size of the LOB and STILL end up with twice the allocation of space in the LOB segment!

In other words: the UNDO blocks still take up the same space. As expected: they are fixed size blocks...

Just in case you might want to see the details:


SQL> select dbms_metadata.get_ddl('TABLE','ZOT') from dual;
DBMS_METADATA.GET_DDL('TABLE','ZOT')
------------------------------------------------------------
CREATE TABLE "TPT_NPS"."ZOT"
( "F1" NUMBER,
"F2" CLOB
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SD_ARCHIVE"
LOB ("F2") STORE AS (
TABLESPACE "SD_ARCHIVE" DISABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))


Always do the above to ensure you know exactly what was assumed by Oracle out of whatever you used to create the table with the LOB.


Would you like to see what happens if you follow Oracle's recommendation of using INLINE storage of the LOBs and then letting them expand to the LOB segment as they grow?

It's not pretty. But here goes anyway. First we list the table we now use, then we do the update. I've ommitted the sordid details of initial load, it's the same as above.
Try it yourself:


SQL> select dbms_metadata.get_ddl('TABLE','ZOT') from dual;
DBMS_METADATA.GET_DDL('TABLE','ZOT')
------------------------------------------------------
CREATE TABLE "TPT_NPS"."ZOT"
( "F1" NUMBER,
"F2" CLOB
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SD_ARCHIVE"
LOB ("F2") STORE AS (
TABLESPACE "SD_ARCHIVE" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

SQL> update zot set f2 = rpad(f2,4500,'Z');
2903 rows updated.

Elapsed: 00:16:56.64 <---Holy hippo stampede!

Statistics
----------------------------------------------------------
946 recursive calls
26104287 db block gets <--OMG!
13032665 consistent gets <--Ditto...
10 physical reads
15938512 redo size
1025 bytes sent via SQL*Net to client
1050 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2903 rows processed

SQL> commit;
Commit complete.

SQL> select segment_name,bytes
2 from user_segments
3 /
SEGMENT_NAME
----------------------------------------------
BYTES
----------
ZOT
1048576
SYS_IL0000052414C00002$$
1048576
SYS_LOB0000052414C00002$$
24117248 <-------- why? read on...
3 rows selected.


Why is the allocation half of what it was before with the update?

Simple. This time around we started with inline storage.

That means we only got 1M initial uniform LOB segment size because all the segment_names from ALL_OBJECTS could be stored inline with the table.

Then we padded the LOBs to 4500 bytes. The LOB values migrated, one row at a time, to individual blocks in the LOB segment.

It's almost as if they had been initially inserted when we used out of line storage. Inserts don't use UNDO.

So, we ended up with the same allocation as after the initial insert in the case of out of line storage.

Had we then proceeded to update yet again, the space usage would have doubled as before. Try it.



But what is incredible is the execution time above!


It takes only a few seconds to do a full insert with out of line storage.

But it takes 16 minutes to MOVE 2900 LOB values to out of line storage.

That's 3 per second! In a top of the line HP server
with a huge SAN on the back...


Someone is having a joke at our expense!





Does this mean one should not use LOBs with Oracle?

No, of course not! Inserts and Deletes are reasonably OK, both inline and out of line.

However when it comes to updates, the only thing that comes to my mind is this:




yes, "there be dragons"...

From all the timings I've taken so far, you're much better off deleting the original row and re-inserting it than updating
an existing LOB value!

So, proceed with caution.

And ignore complete morons who claim there is no problem whatsoever anywhere and it's all working fine and has "always worked fine for them": worst kind of advice, really.


And NO, I am NOT talking about Tom Kyte: he warns of these same problems. In fact, I was first alerted to them through asktom!








Anyways: the splendid animal above is Martha. The Grey Nurse female shark - Sand Tiger for the Murricans - in Manly Marineland Aquarium.

And yes, we were diving INSIDE the aquarium, taking the photos: anyone can do it.


She's really a gentle animal.

Easier to get along than LOB updates, for sure...

4 Comments:

Blogger Alex Gorbachev said...

Huh... LOB update is an evil indeed. Thanks for sharing this stuff.

Btw, is the photo from the Sydney Aquarium? I've been there (and it's quite impressive) but never swum.

I didn't embed video here in case you don't like it so here are links.

Sunday, February 18, 2007 3:44:00 am  
Blogger Noons said...

Marineland is across the harbour, in the suburb of Manly. They started the whole underwater-trip craze back in the 80s with their glass-walled aquarium.

Sydney Aquarium came much later, with its "tube" ride.

I think one can also dive in Sydney Aquarium but the tubes are impressive enough.

Marineland allowed private divers to go in at night, off-hours. That's why we had to carry flashlights and the shots have a black background.

It was a nice and relaxed way of taking photos of these excellent animals: hard enough to find them in the wild, let alone having a captive audience used to flashlights in their faces!

We still had to sign a non-responsibility waiver before the dive... ;-)

Sunday, February 18, 2007 11:32:00 am  
Anonymous kurt said...

Aren't lobs fun ! Indeed, the chunks don't use undo/rollback.
Instead, a lob update or delete will copy the lobchunk - the new lobchunk will have a higher chunk
version (VERSION storage parameter). So if you rollback,
the lobindex will point to the old
lobchunk with lower version - and
if you commit it will point to
the new chunk with higher version.
it's so not Oracle.
I'm hoping they have changed this in 11g with secureFile... but I don't think so ;-)

Thursday, July 19, 2007 8:31:00 pm  
Blogger Noons said...

Kurt:

yeah, me too. As is, they are to put it simply, unusable for any amount of update. Insert and delete is fine, but updates? No way!

Thursday, July 19, 2007 10:39:00 pm  

Post a Comment

Links to this post:

Create a Link

<< Home