Re: Performance Brainstorming

You didn't mention how are you loading the rows? SQL*Plus? Inserts? Convertional path SQL*Loader? Direct path SQL*Loader?

Whoever told you that separating disks and controllers is no longer an issue is not 100% correct. It depends on your disk subsystem. If it's a SAN with lots of cache memory and striping it's *much* less of an issue than with local disks and controllers. The idea no matter what disk subsystem you use is to keep physical head movement to a minimum and balance the i/o accross controllers.

Loading into a staging table is a good idea if you need to update existing rows in the main table. If you are just inserting new rows though, why not just append directly into the main table?

--
Chuck

"David Osborne" <davidosborne_at_comcast.net> wrote in message
news:193f1e93.0211250729.a16590a_at_posting.google.com...


> I have been struggling with a performance issue for a few months now

> and was hoping to get some direction from the best!

>

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

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

>

> I have divided the load up into 16 seaprate jobs (using DBMS_JOB) and

> set the freelists on the table and indexes to 16.

>

> 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.  The server is running SunOS 5.6 and has 4

> GB of memory.  The DBA has alloted about 1.1GB to this Oracle

> instance.

>

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

>

> Thanks,

> David