Re: design question, use of partitioned tables?

Hi!

Comments emb'd.

> Well, I've done a little testing myself with 8ir3 db in a P4 NT4Server.
> Single data disk, redo logs in another disk, no archivelog. Block size
> 8Kb. LMT 8Mb fixed alloc like yours. Create table in one tablespace,
> put a CLOB column in another. disable storage in row.
>
> Timed inserts with EMPTY_CLOB() as the value for the column, got about
> 11 million in one hour, single process. The lobindex was defined as
> initial/next 8Mb and showed just a little volume past the initial extent.
> Not really significant.

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

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

Tanel. Received on Thu Aug 07 2003 - 10:18:08 CDT