Need to Improve Oracle to Netezza Performance

Hema.k · Post by **Hema.k** » Mon Apr 11, 2011 5:33 am

Hi ,

We are using 8.1 DS parallel job with 4 node configuration.
Job Design: Oracle db------->netezza db
issue: Slow performance.

Can anybody discuss steps to improve the performance.

Thanks in advance.

Regards,
Hema K

zulfi123786 · Post by **zulfi123786** » Mon Apr 11, 2011 6:15 am

Performance improvement has always been a topic of debate.
well you could use the performance measurement tools to find what is actually going beyond the limits.

Are both databases over the same server? if not your job will be limited by the network bandwidth

Are you doing an upsert? For bulky data you could separate the Insert and Update for better results and for huge data you could use bulk loading options

Look at the select query if you could redo the SQL trying to use indexed columns in join if you have any.

Try increasing the array sizes for database stages to a suitable limit for better results.

MarkB · Post by **MarkB** » Mon Apr 11, 2011 7:23 am

Considering Netezza is a database appliance and his source database is Oracle, they are obviously on a different server

.

It's possible your network is slowing you down. Are you running directly from Oracle to Netezza with no other steps (Transformer, etc)? Have you tried writing the Oracle data to a sequential file and using nzload at the shell level to load the file and compare load times?

ray.wurlod · Post by **ray.wurlod** » Mon Apr 11, 2011 4:40 pm

Slow compared to what?

Hema.k · Post by **Hema.k** » Thu Apr 14, 2011 12:11 am

Hi All,

Thanks for the suggestions.

@Ray,
Normally the jobs that uses the same db sever is fine.
If we use different servers say oracle ---->netezza ,its taking longer time for the extraction of data from the source.Is there any options to overcome this.
Please suggest.

Regards,
Hema K

jwiles · Post by **jwiles** » Thu Apr 14, 2011 1:05 am

To improve performance, you should first identify what is causing your perceived performance problem.

With reference to MarkB's post, have you isolated the extract and load steps to separately test their performance? Run each several times to get a good average, with each run several minutes apart to help negate caching effects.

Original job:
- Oracle --------------> Neteeza

Test jobs:
- Oracle ------> dataset
- dataset ------> Neteeza

Also, as MarkB asks, is there any other work being done? Transformers, sorts, etc.?

You are running with a 4-node configuration file, but are your database stages running in parallel mode? zulfi has provided some good suggestions as to where to begin looking, as well as a valid reason or for slower performance.

Are there differences in database configuration? Partitioning/Hardware/other applications

You've only told us it's running "slower". Please quantify that: How much slower? For all we know, you could now be averaging just 100 rows/sec slower than before, from 30000/sec to 29900/sec.

Regards,