From oracle-l-bounce@freelists.org Wed Sep 28 08:16:44 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j8SDGi8A019014 for ; Wed, 28 Sep 2005 08:16:44 -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 j8SDGe6H018995 for ; Wed, 28 Sep 2005 08:16:40 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 16D991E0B24; Wed, 28 Sep 2005 08:16:35 -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 26053-01; Wed, 28 Sep 2005 08:16:35 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7171F1E0B9B; Wed, 28 Sep 2005 08:16:34 -0500 (EST) Message-Id: <200509281314.j8SDEJni087668@vegeta.p6m7g8.net> Subject: Re: How to improve performance on dataload process To: roon987@yahoo.co.uk Date: Wed, 28 Sep 2005 09:14:19 -0400 (EDT) Cc: oracle-l@freelists.org In-Reply-To: <20050928095426.50023.qmail@web25408.mail.ukl.yahoo.com> from "Zabair Ahmed" at Sep 28, 2005 10:54:26 AM From: tboss@bossconsulting.com MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-archive-position: 26011 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: tboss@bossconsulting.com Precedence: normal Reply-To: tboss@bossconsulting.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-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-3.1 required=5.0 tests=AWL,BAYES_00,NO_REAL_NAME autolearn=no version=2.63 We're an Informatica DW shop here, solaris 8 over v440s and v880s. Here's some of the lessons learned we've run into with Informatica: - make full use of the caching feature in the Informatica server. Informatica can cache all the lookup and reference tables it uses to transform data (the "T" of ETL) and it makes the operations run very fast. I had all these tables bound to a keep pool in the database ... but it was a waste of memory, since informatica uses its own caching process. Consider making informatica's caching location a ram-disk or use tempfs ... might make it go more quickly, since it defaults to reading from disk. - Avoid doing major sql operations from maplets ... we had essentially pl/sql developers working with the tool, and instead of using informatica programming they simply called cursors from teh maplets ... and we were plagued with Ora-1555s. - You can use hints with informatica-generated sql ... take a look at the sql it passes in (try to grab it out of v$sqlarea or just work w/ the informatica develoeprs. - Make sure the sql*net client informatica is using to connect to the database is also 9.2.0.6 ... if you just installed the client from Oracle's download sites or from the original oracle install disks, its 9.2.0.1 and buggy. We ran into big-time memory leaks in the 9.2.0.1 client. All your database-related fixes are good. Do your indexing in parallel. We've got far too much data to be able to drop indexes, load data, then re-index and re-gather stats in a maintenance window (overnight or a weekend). Thus we depend on staging all the data and pre-creating indexes before using a transportable tablespace and an alter table exchange partition process to get new data into our database. You may get to that point as well. Todd > > 9.2.0.6 on Solaris 9 > > Am loading data into a staging database using a ETL tool (Informatica) > and have done the following to improve the performance of the dataload > process > > 1) Database in no-archive log mode > 2) Indexes being built after the dataload > 3) Load being done in parallel > 4) Nologging being used > 5) Tables not being analysed during the dataload process > > What else can I do to improve the performance of this load process? > > TIA -- http://www.freelists.org/webpage/oracle-l