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
Stored Procedure and Network bottlenect!
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 19
- Joined: Sun Jul 20, 2003 4:29 am
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
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.
-
- Charter Member
- Posts: 19
- Joined: Sun Jul 20, 2003 4:29 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
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
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 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.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Charter Member
- Posts: 19
- Joined: Sun Jul 20, 2003 4:29 am