We have a dbms_parallel_execute task that calls a stored procedure using department ids as chunk's start/end ids. If we run this task, we see chunks at specified parallel level get started with status ASSIGNED.
However, in session window, we see all those chunks are frozen at one select statement, making them run for hours. This is strange, because if we run the stored procedure alone in test window, it takes minutes to run.
Furthermore, if we create dbms_scheduler jobs for each department with job action calling that procedure, instead of using parallel task, all those jobs are running stable and getting finished quickly.
What could be the reason that the same procedure runs differently when executed by dbms_parallel_execute task and dbms_scheduler job?
