Re: How to improve performance on dataload process

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