Re: Performance Brainstorming

Chuck had some good points.

if we assume all defaults then you are using SQL*loader in conventional mode.

Please post your SQL*loader ctl file and log file from the load.

and the definition of the tables (full including tablespaces and ALL indexes and constraints )
then post a statspack delta before and after.

I've just loaded 10,000 rows in 3.2 seconds from a remote ethernet pc (to a slow PC running 92 ) so there is probably some scope for improvement for you !

"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
Received on Mon Nov 25 2002 - 10:55:26 CST