Re: design question, use of partitioned tables?

Manuela Mueller <mueller_m_at_fiz-chemie.de> wrote in message news:<bgt878$r2t9n$1_at_uni-berlin.de>...
> many thanks for your input and your time.
> Answers embedded

OK, here is some more I found. Hope anyone else doing a "direct mail" may also want to share the knowledge. It's not widely available anywhere...

> a) create table:

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. These made minor changes in performance in my system.

As for partitioning:
I had a look at the LOB guide and followed their example with the presidents and their photos. Apart from the unfamiliar syntax, it all worked as described. Rather than me repeating the manual, have a look at it in your own time. You'll know a lot better how to partition than any here.

Now, performance. I've tried a number of things. By far in a sparse LOB (one where there isn't a row in lobsegment for every row in main table and where the lob size varies widely), the best way to handle it is with ENABLE STORAGE IN ROW at create table time. It saves some considerable overhead. Any lob data that fits in the main table (<4000) will store at much, much faster speed.

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 don't like the idea of disabling updates to controlfiles via an event although that may not phase you? But I've tried the following: I had 3 control files, one of them in the same disk as where the lobsegment tablespace was. I've reduced the controlfiles to 1, in a different disk. That immediately halfed the load time in my system for all situations!

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 have access to a SAN, use of their partitioned caches would be an advantage. I'd also look at using the split buffer cache in Oracle and put the table in RECYCLE while it is being loaded, then back in DEFAULT after that.

Based on the above, I did manage to reduce the load times in my system from a start point of 6 minutes for 20K very large rows to just under 3 minutes. Without any h/w reconfiguration or disk optimization. I'd expect that to be much better in your system.

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!

> b) control file:

can't help much with this one.
seems OK to me, but I haven't tried for lack of suitable test data.

>
> After optimization of te Oracle Text indexes old duplicate records (not
> contained in the freshly created index) are deleted (approx 1-1.5
> million rows). Hence the table fragmentation.

This "old" qualifiation is what you need to use for the partitioning key. Use it so that rows are grouped by partition on range of "old" data. Then when it comes time to delete, all youhave to do is drop the partition. Local indexes,of course...

HTH.
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Fri Aug 08 2003 - 00:58:53 CDT