loss of performance for Loading Oracle

Flyerman_2 · Post by **Flyerman_2** » Tue Jan 15, 2013 7:27 am

Hi,

we use DataStage 8.0.1 on AIX 5.9 TL12.
The Oracle client is 10.1.0
We migrated the Oracle databases used by our projects to another site.

We noticed a high performance loss when jobs use the upsert mode in the Oracle Entreprise stage.
We test particulary a job with length of time of 7 minutes before and 145 minutes now.

The job extracts 800 000 rows in 4 seconds from a daatset then use the upsert mode the rest of the time at about 100 rows/s.

We got also an issue for sqlloader. Actually we use APT_ORACLE_LOAD_OPTIONS : OPTIONS (DIRECT=TRUE,PARALLEL=TRUE,SKIP_INDEX_MAINTENANCE=TRUE) UNRECOVERABLE

We note an increase of the network latency from 1 to 26ms. But it is not possible to reduce it.
That is why we try to find some way to optimize (increase the number of nodes, update the Oracle settings ...).

For the read part, we find the old results after updating the "array size" option. For the loading, this array size has no impact.

Thanks for your help

chulett · Post by **chulett** » Tue Jan 15, 2013 8:40 am

Any "performance loss" with upserts is typically due to full table scans. Are there indexes in place for the where clause columns? Stats up to date?

Flyerman_2 · Post by **Flyerman_2** » Tue Jan 15, 2013 9:42 am

Yes, the indexes are in place for the where clause columns
The stats are up to date

chulett · Post by **chulett** » Tue Jan 15, 2013 10:37 am

Confirm they are being used - have a DBA monitor the load while it runs and check the explain plan.