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...

2007/02/09

design by rote....

One of the things that upsets me no end is to see good ideas given the "dumbass" cookie-cutter treatment!

This is a perfect example.


Notice option 4. "Can add only 10 sites (the value of increment by)" is a "disadvantage"?


Duh?, is anyone home? Has anyone stopped to think that changing the increment value to 1000 for example allows 1000 sites? Has anyone stopped to write a proper explanation of the principle of operation of this technique? Has anyone stopped to think that this technique is by far the most flexible out there? And that it MIGHT be worth explaining it in full so folks can use it out of full understanding rather than rote cut-and-paste?


The problem is not that the silly case of 10 was used.

The problem is that the PRINCIPLE of operation of the method was NOT explained.


With the result that we'll start seeing the usual "cut-and-paste" approach. And see folks working around the "10 site limitation" and other nonsense approaches!


And this was cut-and-pasted to another Oracle FAQ blog! It is bad enough that solutions "sanctioned" by Oracle sometimes are explained in as silly a fashion as any, without the "by the numbers" approach to blog posting starting to creep in as well!


Yes, mr Oracle: we KNOW that you can create a blog for each of your employees and cut and paste absolute crap into those to make up huge numbers of "publishing". It will only lead to more rubbish out there, I hope you realize?


I know Eddie has nothing to do with this, even if his name is mentioned in the blogs: he just set them up but he doesn't control what goes there. Unfortunately!


Anyways, enough ranting.


Sorry for the outburst, but this sort of thing really rattles my cage!

2007/02/06

just a short note

By sheer accident found this.

Of course, those of you with a little bit more of a clue than silly old me will recognize Jonathan straight away, from O'Reilly books.

That article on domains is absolutely brilliant: may I recommend a second, third and fourth reading and a little bit of poking around at the concepts explained there?

Long overdue someone starts exploring the Oracle TYPE functionality for its tremendous potential in improving vanilla SQL.


Wish I had the time to pursue it myself...









A while ago in Doug's blog I mentioned bbq sardines with pimento salad as a typical dish from the place where I come from. This is what it looks like:



Yum!

And recently I found this image among my old stuff:




Had forgotten how flexible the whiskers are on these little fellas. If you ever get a chance of scuba diving with them, do it! Some of them will come right up to your face and give you a brush with the whiskers: I think they must use them like cats do, as an extension to their tactile sense.


catchyalata, folks!

2007/02/01

hoping they find him

I blogged about Jim Gray not too long ago.
Today, I learn of this from Greg's blog.

Guys like Jim just don't crop up that often...
A dark day indeed if they don't find him.

Update:
nope, not looking good.

A dark week for IT indeed. And notice how little attention was paid to it by the so-called "mainstream" media! Had it been some sorry-arsed soap-star or hotel heiress that had a brain-dump of some sort, or yet another j2ee acronym, it'd have been all over the news.

one wonders...