Re: design question, use of partitioned tables?

"Tanel Poder" <tanel@@peldik.com> wrote in message news:<3f326db1$1_1_at_news.estpak.ee>...

>
> Inserting that much of out of line *empty* CLOBs actually means:
> 1) normal row inserts to table (20 bytes per lob locator)
> 2) lob inode inserts into lob index (kdlinode structure) which actually
> don't point anywhere
> 3) no inserts whatsoever into lob segment

Yes, that is my understanding as well.

> > Then I tried to load the CLOB with the same value all over, for
> > 65000 rows. About 1K of character data, the chunk size is 8K. I know,
> > it is redundant but what the heck, I don't have 9i loaded in that
> > system to try a different block size. Timing this got me 20 minutes
> > to load the 65K rows! No logging. CPU and disk flat out, which
> > I found surprising. Then again, it's Windows...
>
> The big difference is, that now a direct IO has to be done for each 1kB LOB
> (there are some possibilities to optimize, though)
> LOB segments are NOCACHE by default, that means the contents are written to
> disk immediately by server process, not DBWR, thus you got 65000 waits as
> well (btw, NOLOGGING is only available in NOCACHE mode anyway).
> And this is not all, since nologging is used, control file has to be updated
> on every unrecoverable operation on a datafile (UNRECOVERABLE_CHANGE#
> column). This will cause a lot of read & write waits on controlfile as well,
> especially when you have several controlfiles.

Hmmmm, not really enough to explain the wait. I'm getting heaps of CPU use, with I/O also going ape. I just re-did the test with the SAME data and statements, this time the only difference is "enable storage in row". My clob data fits nicely into the 4K max boundary. The 65K row creation took now less than 2 minutes... As opposed to 20 minutes for the same amount of data for out of line storage!

Seems the killer is "disable storage in row". I'll do some more testing, this might be just a side effect. Wanna check what happens when load data doesn't fit in block (>4000) and it must write to lob segmnt. Will it still blow out in time?

> For getting over that, you could set event 10359. That way unrcoverable
> options won't update controlfiles, but you might have to take it into
> account with your backup & recovery strategy.
> It's documented in 9.0 AppDev guide on LOBs, but disappeared in 9.2 for some
> reason. There's some more information about the event in Ixora, where from I
> first discovered it.

Aye! Setting an event to do a load? Don't like it...

Interesting stuff, BTW. Thanks for the feedback. Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Thu Aug 07 2003 - 19:46:57 CDT