DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
        b=Pjlf5HsqRGMoZcpwAqV7vXW9wt0L02QoriiNKDjiFnUOeHzFLqCu4tp0z1yTKdkfZIYNYRCfwgSR63Rq5Qf0/KmGYWwAMy3rrW1elvKPMshyhGe1mIsP6OWBRIeKf+rVQ6SMuumYmtkmIPgXFY1OypA0VLV5Ek3XTnTFUu+2K3E=
Message-ID: <666b99c70801221613v5eab8e8eo3fbae58a6c45d6bc@mail.gmail.com>
Date: Tue, 22 Jan 2008 16:13:46 -0800
From: Prasad <p4cldba@gmail.com>
To: kennaim@gmail.com
Subject: Re: Paralellizing Pl/sql inserts
Cc: oracle-l@freelists.org
In-Reply-To: <901CE9A27473416CABACB807651DE527@KenPC>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="----=_Part_11690_31798650.1201047226900"
References: <BLU108-W9E42A6E05C8CC5384D638973E0@phx.gbl>
	 <BAY107-W15CE9A0AFF45D13DA8C1BBA13E0@phx.gbl>
	 <4ef2fbf50801221450g7a887eb7w9a0e0a6b7aac8a0f@mail.gmail.com>
	 <329b3afe0801221518x36a941e1ga2ca456f7a311152@mail.gmail.com>
	 <901CE9A27473416CABACB807651DE527@KenPC>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: p4cldba@gmail.com
------=_Part_11690_31798650.1201047226900
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Have you tried BULK insert .

On Jan 22, 2008 4:03 PM, Ken Naim <kennaim@gmail.com> wrote:

>
> I have a process that is currently running at a rate of 4 million inserts
> per hour. In selects from a cursor that returns 65 million records bulk
> collects 500 at a time and forall inserts them into another table with 1.5
> billion rows. I cannot do an insert as select as the record may already
> exists. I use the save exceptions clause to allow the code to continue
> processing but I don't care about which record turn the unique constraint
> violation. The 1.2 billion row table is partitioned but the data can go
> into
> any partition. The 65 million row table is not partitioned
>
> On smaller tables I would do a minus between the primary keys of both
> tables
> and use that as a filter on an insert as select.
>
> The 65 million record cursor takes only 15 minutes to return all 65
> million
> records, so 98.5% of the time is consumed by the inserts. I am going to
> move
> the table to non assm tablespace and increase its free lists but I believe
> these measures will only provide a marginal improvement. 33% of the
> elapsed
> time is waiting on cpu and the top wait events are db file sequential read
> (94%) and db file scattered read (6%)
>
> The business requires this process to run in under 2 hours as it will run
> weekly (if not daily) so I need an order of magnitude increase in
> performance. The infrastructure (server/storage array etc.) is high end
> and
> shows little load during this process and this process will be the only
> one
> running on the box at that time.
>
> I am open to any suggestions but my question is how can I parallelize the
> process into multiple (24-48) threads.
>
> Thanks,
> Ken
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

------=_Part_11690_31798650.1201047226900
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Have you tried BULK insert .<br><br><div class="gmail_quote">On Jan 22, 2008 4:03 PM, Ken Naim &lt;<a href="mailto:kennaim@gmail.com">kennaim@gmail.com</a>&gt; wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>I have a process that is currently running at a rate of 4 million inserts<br>per hour. In selects from a cursor that returns 65 million records bulk<br>collects 500 at a time and forall inserts them into another table with 1.5<br>
billion rows. I cannot do an insert as select as the record may already<br>exists. I use the save exceptions clause to allow the code to continue<br>processing but I don&#39;t care about which record turn the unique constraint<br>
violation. The 1.2 billion row table is partitioned but the data can go into<br>any partition. The 65 million row table is not partitioned<br><br>On smaller tables I would do a minus between the primary keys of both tables<br>
and use that as a filter on an insert as select.<br><br>The 65 million record cursor takes only 15 minutes to return all 65 million<br>records, so 98.5% of the time is consumed by the inserts. I am going to move<br>the table to non assm tablespace and increase its free lists but I believe<br>
these measures will only provide a marginal improvement. 33% of the elapsed<br>time is waiting on cpu and the top wait events are db file sequential read<br>(94%) and db file scattered read (6%)<br><br>The business requires this process to run in under 2 hours as it will run<br>
weekly (if not daily) so I need an order of magnitude increase in<br>performance. The infrastructure (server/storage array etc.) is high end and<br>shows little load during this process and this process will be the only one<br>
running on the box at that time.<br><br>I am open to any suggestions but my question is how can I parallelize the<br>process into multiple (24-48) threads.<br><br>Thanks,<br>Ken<br><font color="#888888"><br>--<br><a href="http://www.freelists.org/webpage/oracle-l" target="_blank">http://www.freelists.org/webpage/oracle-l</a><br>
<br><br></font></blockquote></div><br>

------=_Part_11690_31798650.1201047226900--
--
http://www.freelists.org/webpage/oracle-l