Re: Performance Brainstorming

davidosborne_at_comcast.net (David Osborne) wrote in message news:<193f1e93.0211250729.a16590a_at_posting.google.com>...
>
> The table involved has 75 million rows, and is non-partitioned. Each
> night we add about a million rows and it takes several hours (4+).

Hmmmmm.... Do you ever delete rows? There might be something wrong with the setup of the table: 4 hours for a million rows is waaaaaay too much, unless you have a very slow Sun box. Are you using the array interface for row insertion? How many indexes?

My desktop PC can insert a million rows in less than 10 minutes. How often do you re-build the indexes? Why haven't you considered partitioning? Sounds like you'd have a perfect fit for it. You didn't say, but I'm assuming you have 8.0 or later?

> Our goal is under two hours (is this reasonable?) The table and
> indexes are on different tablespaces; which are on different disks and
> controllers (although I'm hearing that this is no longer an issue).

It might or might not be. Depends on too many things. In general, if you have a SAN or RAID-type disk architecture, that would tend to be true. Even with native disks and controllers it can also be true if you're using a LVM. In your case, I'd say putting the redo logs in separate disks and/or controllers would gain you more.

>
> I have divided the load up into 16 seaprate jobs (using DBMS_JOB) and
> set the freelists on the table and indexes to 16.

Probably too much. I'd say you shouldn't set freelists to more than you have set the number of db-writer processes/threads. Pure rule of thumb, but it seems to work fine for me.

>
> When the job is running (all 16), TOP shows that the CPU is 0-3% idle
> and the IO wait varies from about 5% to 20% so it appears that we are
> fully utilizing the server.

Sounds OK, although I'd expect I/O saturation for that many concurrent insert jobs . What size are the redo logs? Are you positive you're using array processing?

>
> One idea is to load the new rows into a new table and then insert them
> with one "insert into big_table select * from new_small_table" with
> the append hint. The big table has pctfree at 10%.

Dunno why that would be so much better. What are you using now, SQL*Loader? If you're using a programmatic loop with an INSERT for every row without even array processing thrown in, there lies your problem.
Make sure you're using arrays for the INSERT. That will gain you at least an order of magnitude in processing time.

If you're going to the trouble of the above, I'd investigate partitioning instead. A much better way to handle this type of load.

HTH
Cheers
Nuno Souto
nsouto_at_optusnet.com.au.nospam Received on Wed Nov 27 2002 - 16:33:42 CST