Re: design question, use of partitioned tables?

> > Can't see anything majorly wrong with your create table.
> > I'd explicitly include NOCACHE and NOLOGGING on the LOB
> > storage control. As well as PCTVERSION 0 if the data never
> > gets updated, just loaded and retrieved.

(Thinking out loud)

It could be worth testing setting CACHE and LOGGING instead: 1) IF you set your DB in noarchivelog as you stated 2) and your logs reside on separate disk from data (btw. logs get written quite much anyway, because of table and LOB index)

The idea is, that when loading items to CACHE LOB, then for each item no direct write is requiresd, instead DBWR can write data to files in batches (and the batches could be optimized quite well because contiguous space is allocated when inserting). But yes, that way, there'll be more logging (but quite much logging is done with NOLOGGING anayway as written above).

I don't know whether this way is any faster, but if above 2 conditions are true, it's definitely worth of trying.

And I'll add a 3rd condition:

3) if it's one time load and you can backup database right after it, you can set hidden parameters disabling logging and wait for sync. Then the load should fly (as much as NFS allows of course).

> My current TOP BANANA performance killer is the huge 720GB nfs mounted
> Reiser FS partition with millions of small files!! I performed a load
> with lob data residing on nfs mounted partition (262000 records).
> tail of sqlldr log file:
> Ouch!

Yep, Ouch.
Can you concatenate the input files for better performance? Or could you transfer them to local server before using tar -> rsh -> pipe -> rsh -> tar to avoid NFS overhead for each file transfer?

> > Another thing, triggered by what Tanel said and ixora: the control
> > files seem to get some considerable I/O during a load of a lob.
> > Certainly V$SYSTEM_EVENT showed that.
> I can affirm this. On the development box where I performed only the
> load (no other DB activity) V$SYSTEM_EVENT shows:
> total_waits for following parameters:
> control file sequential read 2429
> control file parallel write 2893
> Instance was freshly started this morning.

This is probably because CKPT visits controlfiles every 3 seconds to record incremental checkpoint there.

> > Moral? I'd drop the multiplexing of controlfile for the load and
> > use just one, in a separate fs/disk.
> > Given that the whole thing is I/O bound, I'd also concentrate on
> > getting the fastest possible disk access to the control file and
> > the lobsegment tablespace(s). That means Raid 0 (striping) or some
> > other mechanism to speed up I/O on these files.

If you do a full backup after load with disabled CF updates, then there should be no problem.
Otherwise, it could be possible to put controlfile to memory disk temporarily ;)
(we'll get over of potential loss of controlfile in case of server failure).

> > For those with Win2K Server: if you format your disk partition
> > with a sector size matching the db block size, direct I/O is much,
> > much faster!

Does the sector size mean "Allocation Unit Size" in format tool?

Tanel. Received on Fri Aug 08 2003 - 08:37:48 CDT