We observed a different scenario with the sessions going to delay state .
There is a informatica job when submitted logged into 10 sessions .when the system is very busy 8 sessions go to active state and others go to delay .
The 8 sessions stay active waiting for the delay sessions to be released .As soon as we release from delay state the job completes .
Does this mean all the 10 sessions are not moved from delay state but each session based on the slot available ?
How to make sure all 10 go active at a time?
Help is appreciated .
Yes, normal SQL sessions are delayed / released independent of each other (even if they come from the same client "job"). Sounds like there may be a throttle imposed, based on system state, that is less than the number of concurrent connections this particular job requires. You may need to redesign the job to require fewer concurrent sessions. (Sometimes this can be done by "pushing down" transformations into the database instead of having them processed on the Informatica server.) Or raise the throttle limit / move the job to a different workload (with a higher throttle limit).