Re: Performance Brainstorming

If you use partitions you can simply create a load table on a second box or instance then use transportable tablespace to move the partition into the production system and use the partition add/swap/merge commands to add or merge the partition. This allows you to build the table at your convienience. The transportable tablespace and partition add/swap commands are fast.

Mike

davidosborne_at_comcast.net (David Osborne) 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
Received on Tue Nov 26 2002 - 08:08:17 CST