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.
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.
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.
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?