shared container - increasing the speed

[priyaram]

i created one shared container which contains one dynamic sql st , that calls the pl/sql procedure. and i called that shared container using a test job (i.e) parallel job.
while running the job in datastage it's taking twice the time as that of compared with pl/sql procedure on unix.
anybody having any idea about incresing the speed of datastage job

[ray.wurlod]

What stage type inside the shared container contains the "dynamic SQL" statement?

What you seem to be trying to accomplish here is to have the following architecture run at no cost; that is, to run at the same speed as if it weren't there. The layers are:

• UNIX
  osh (several processes)
  DataStage job process(es)
  Shared container process(es)
  Stage type (ODBC?) executing "dynamic SQL"
  ODBC driver?
  Oracle client software

Every time you add a layer to your architecture, there's a cost. Indeed, if the shared container uses Server stages, there are even more layers.
You want to compare the above with:

• UNIX
  Oracle client software (includes sqlplus)

Can't be done.
The obvious question is whether it's really necessary to invoke a stored procedure for every row. Could you do the same thing with a buildop, which would be much faster? Or re-think your design, so that the data involved resides in datasets while your job is running - therefore all data modifications are effected in memory.

[priyaram]

inside the transfor mer stage , i am giving the dynamic sql statement

[ray.wurlod]

So, in DataStage you're generating the "dynamic SQL" for every row processed, while in PL/SQL (presumably) you're not. Is this the case? And you expect it to run at the same speed! Read my earlier post again.
I don't think you have much scope for improving performance without moving away from a "dynamic SQL" approach; this is not really how DataStage is designed to execute.