Stored Procedure and Network bottlenect!

raoraghunandan · Post by **raoraghunandan** » Fri Feb 25, 2005 4:13 am

Hi All,

We are calling a Oracle Stored Procedure in after-sql in our Target OCI 9i Stage. This Stored procedure basically selects records from our source database(oracle 9i) using a database link, does some complex transformations and inserts into target database(oracle 9i). The source and target databases are in the same location. Our DataStage Server is at a different location from our source and target databases and there are some network bottlenecks because of this.

My Question is would the Oracle Stored procedure processing also suffer from these network bottlenecks since the stored procedure should get processed within the database?

-rao

ArndW · Post by **ArndW** » Fri Feb 25, 2005 4:19 am

Rao,

as you correctly surmised, the Stored Procedure execution is done within the database and you will not have any data going across the network when you execute it.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Feb 25, 2005 4:19 am

Your Oracle to Oracle links works as

Oracle DB ===> Oracle DB

But via DataStage, it becomes

Oracle DB ==> DataStage Server ==> Oracle DB

Your dbLink is generally slow. Even though the 2 db are in the same machine, they may talk via SQL*Net. Thus there is an output and then an input action. This will be slow compared to having the source and target db as the same.

Your connection via DataStage depends on the network.

Even though from the initial top level look it appear to be slow than db to db link, you can have high power networks to minimise this.

raoraghunandan · Post by **raoraghunandan** » Fri Feb 25, 2005 4:31 am

Thanks for the prompt responses.
However, Sainath's response has confused me. Are you saying that in the case that i mentioned, there would be network bottlenecks even for the stored procedure?
When I execute the stored procedure in sqlplus it gets executed in around a hour.

So, If I have a dummy mapping and I am calling this procedure in after-sql, then this mapping should take the same amount of time as the stored procedure is executed within the db ?

-Rao

ArndW · Post by **ArndW** » Fri Feb 25, 2005 4:38 am

Rao,

Sainath was just explaining the basic function of DS and that if your server machine is different from the DB machine(s) you will be getting a network bottleneck. In your case you are not processing the stored procedure for each row, but only once, so the actual data processing is not done by DS over the net but in Oracle within the database.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Feb 25, 2005 4:44 am

Yep. ArndW is correct. An After-SQL gets processed only once. Even though it is triggered from DataStage server, your stored procedure will run as usual.

You may need to ensure that the user defined in the OCI stage has necessary authorisation to invoke the procedure.

raoraghunandan · Post by **raoraghunandan** » Fri Feb 25, 2005 4:47 am

Thanks Arnd and Sainath.

Topic Closed...

-Raghu