ORACLE ENTERPRISE UPDATE-POOR PERFORMANCE

santhooosh.c · Post by **santhooosh.c** » Tue Aug 12, 2008 1:29 am

Could Someone assist me on this issue.

My system Details:

DataStage server 7.5.3
OS:Solaris
Oracle 10g

My Issue:

I have four primary keys, datatype of one is varchar2 others are numeric in my table, Table has been indexed

When i go for insert using the Oracle enterprise stage. i am able to get performance of 4570rows/sec

In Same table, When i go for Update using the Oracle enterprise stage. i am able to get performance of only 200rows/sec or even low

If i change my varchar2 key data type to numeric and then if i go for update. i am able to get the performance of 4125/sec

Note: To the same table using ODBC stage from server jobs, i am able to get better performance without changing the keys data type, for update action

My jobs are supposed to be in parallel. So help me out to resolve this issue.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 12, 2008 1:43 am

What indexes exist on the table? In particular, is the "Key" column indexed? If not UPDATE will need to perform a table scan for the key of each value being updated.

You would find this difference no matter what tool you used. It's an artifact of how databases work. In particular it's nothing whatsoever to do with DataStage.

Rows/sec is not a meaningful metric of throughput in DataStage. I have just run one server job in which there are 19 different rows/sec values on different links. Which one is correct?

santhooosh.c · Post by **santhooosh.c** » Tue Aug 12, 2008 1:57 am

Thanks for the Prompt Reply Ray

Yes, Key column has been indexed.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 12, 2008 2:40 am

UPDATE is always slower anyway. Each insert merely has to write a row into (at the end of some physical structure in) the table. An update has to find that row, read that row, change the indicated column values, and then write the row back. If it fits in its original position it can be written back there but, if it has grown too large for that, the space it formerly occupied must be marked as free and the enlarged record written somewhere where it does fit physically. As you can see, rather a lot more work than insert. The best you can do is to limit the change columns to those that actually need to be changed.