From oracle-l-bounce@freelists.org Mon Oct 10 07:23:57 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9ACNvSD020612 for ; Mon, 10 Oct 2005 07:23:57 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j9ACNtvX020598 for ; Mon, 10 Oct 2005 07:23:55 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0A9551F997B; Mon, 10 Oct 2005 07:23:52 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07632-08; Mon, 10 Oct 2005 07:23:51 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 797131F72B6; Mon, 10 Oct 2005 07:23:51 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=AwQk2ABTqq1Ky1SYsCYWgieX6YydoDPWZeleV6Hqakh7E8sIs5IgJaY0sr3sGcRGIBgEynZgvPIcOxjun2Ls1x2yoAHC7j7AtxKhSabbqjUyGxiSwYvr+mkgDXtmYfINsRCK3BYK3QRVfIvWHhs3UaEmmWtM9N1IzwL+nxeCyrc= Message-ID: <962cf44b0510100521r689de838q94a7cc2dc8b6c7aa@mail.gmail.com> Date: Mon, 10 Oct 2005 20:21:46 +0800 From: zhu chao To: scott.hutchinson@interact-analysis.com Subject: Re: Ridiculously high number of commits Cc: oracle-l@freelists.org In-Reply-To: <1128945498.434a575af3ac1@mopp.namemagic.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3678_17228912.1128946906108" References: <1128945498.434a575af3ac1@mopp.namemagic.com> X-archive-position: 26626 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: zhuchao@gmail.com Precedence: normal Reply-To: zhuchao@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-3.7 required=5.0 tests=AWL,BAYES_00,HTML_MESSAGE autolearn=ham version=2.63 ------=_Part_3678_17228912.1128946906108 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline The Most easy and efficitve way, is to change your load application do commit every 1000(for exmaple) rows. Check the application with developer see if that is possible. Also, can you consider using sqlldr to load. If you are using multple thread, either consider using multiple freelists for that table, or place it in assm tablespce. that should hlep. If commit rate can't be changed, reduce the log buffer maybe helps. On 10/10/05, scott.hutchinson@interact-analysis.com < scott.hutchinson@interact-analysis.com> wrote: > > All, > > I have a performance problem while loading data into SAP/BW from flat > files. > This process for loading the data is a standard SAP routine, and it issue= s > a > COMMIT after each record is inserted - we have about 20 million rows to > insert, > so this is a lot of commits! > > We've broken the load process into 10 jobs that run concurrently, however > they > spend the majority of their time sitting around waiting on "log file > sync", > which is no great surprise. I have a target of 4 hours for loading this > data > into SAP's "Info Cubes", but this is currently taking 8 hours. > > Does anyone have any smart ideas for lessening the impact in the database > from > issuing such a high number of commits? > > btw - the DB server is a 12 CPU HP running at 12% utilisation during the > load. > And yes - we are also enganging SAP to see if they can improve their load > process. > > Thanks, > Scott Hutchinson > Interact Analysis Ltd. > > ::This message sent using the free Web Mail service from > http://TheName.co.uk > -- > http://www.freelists.org/webpage/oracle-l > -- Regards Zhu Chao www.cnoug.org ------=_Part_3678_17228912.1128946906108 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
The Most easy and efficitve way, is to change your load application do= commit every 1000(for exmaple) rows. Check the application with devel= oper see if that is possible.
 
Also, can you consider using sqlldr to load.
 
If you are using multple thread, either consider using multiple freeli= sts for that table, or place it in assm tablespce. that should hlep.
 
If commit rate can't be changed, reduce the log buffer maybe helps.  
On 10/10/05, scott.hutchinson= @interact-analysis.com < scott.hutchinson@interact-analysis.com> wrote:
All,

I have a performance= problem while loading data into SAP/BW from flat files.
This process fo= r loading the data is a standard SAP routine, and it issues a
COMMIT after each record is inserted - we have about 20 million rows to= insert,
so this is a lot of commits!

We've broken the load proce= ss into 10 jobs that run concurrently, however they
spend the majority o= f their time sitting around waiting on "log file sync",
which is no great surprise.  I have a target of 4 hours for l= oading this data
into SAP's "Info Cubes", but this is currentl= y taking 8 hours.

Does anyone have any smart ideas for lessening the= impact in the database from
issuing such a high number of commits?

btw - the DB server is a = 12 CPU HP running at 12% utilisation during the load.
And yes - we are a= lso enganging SAP to see if they can improve their load
process.

Thanks,
Scott Hutchinson
Interact Analysis Ltd.

::This mes= sage sent using the free Web Mail service from http://TheName.co.uk
--
http://www.freelists.org/webpage/oracle-l


--
Regards
Zhu Chao
www.cnoug.org
------=_Part_3678_17228912.1128946906108-- -- http://www.freelists.org/webpage/oracle-l