Oracle stored procedure job running long time from DS

[pimmit22043]

Hi

We have a job that calls the oracle stored procedure(using stored procedure stage), which is running long time from DS parallel job about 20 hours and we have ran the stored procedure from SQLPLUS, it ran about 2 to 3 hours and we also ran the SP from DataStage server using SQLPLUS, it completed with in 3 hour,

Did anybody came across this kind of problem running long time or can anybody has any ideas, please share.

[asorrell]

Not really - the first thing I would do is have your Oracle DBA check to see what threads are being started in Oracle and what differences, if any, he can see between the two separate executions.

[pimmit22043]

Thanks for your time,

basically he is saying both separate executions are same and explain plans are same.

[asorrell]

At that point I'd contact your Service Provider to see what they say. I've not seen this behaviour (and we do a LOT of Stored Procedures with Oracle) and if the explain plans are the same it sounds like there may be some sort of resource constraint being applied by PX that isn't readily viewable.

[John Smith]

How does your job look like? When you run it using the Stored Procedure stage, all it does is pass the parameters back to the database so in essence it should be the same. I done it before and performance is the same. Has this problem just happened recently? has your job been able to run well in the past?

[pimmit22043]

We have added the NLS_LANG parameter to the project as none value and called this parameter to the job,now stored procedure run with in couple hours.