Performance Brainstorming

From: David Osborne <davidosborne_at_comcast.net>
Date: 25 Nov 2002 07:29:09 -0800
Message-ID: <193f1e93.0211250729.a16590a@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 Received on Mon Nov 25 2002 - 09:29:09 CST