Re: design question, use of partitioned tables?

From: Manuela Mueller <mueller_m_at_fiz-chemie.de>
Date: Fri, 08 Aug 2003 12:35:20 +0200
Message-ID: <bgvu9e$s2t27$1@uni-berlin.de>

Dear Nuno and Tanel,
answer embedded
Have a nice weekend
Manuela Mueller

Nuno Souto wrote:
<snip>
> 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.
I'll try the CREATE TABLE statement with initrans 4, freelists 4, pctfree 5, pctused 80 and pctversion 0. LOB TS was created with NOLOGGING clause.
>
> 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.

Thanks for the hint.
>
> 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.
>

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:

Total logical records read:        262557
Total logical records rejected:       123
Total logical records discarded:        0

Run began on Wed Aug 06 10:23:50 2003
Run ended on Wed Aug 06 21:22:46 2003

Elapsed time was:     10:58:55.77
CPU time was:         00:04:27.47


Same data, only difference: lobs on local file system:
Total logical records read:        262557
Total logical records rejected:       123
Total logical records discarded:        0

Run began on Fri Aug 08 09:56:15 2003
Run ended on Fri Aug 08 10:42:15 2003

Elapsed time was:     00:45:59.81
CPU time was:         00:01:42.08

Ouch!

Regarding ENABLE STORAGE IN ROW:
In our application the lob size varies widely, from the 262000 loaded lobs 76356 are <= 4000 bytes. Hence we'll stick to enable storage in row.

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

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

<snip>

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

I'll review table and application design to implement it. Current design is not adequate for this.
>
> HTH.
> Cheers
> Nuno Souto
> wizofoz2k_at_yahoo.com.au.nospam
Received on Fri Aug 08 2003 - 05:35:20 CDT