I have a plsql block scheduled under a scheduler job. Recently it has some performance problem. When I run that block directly under sqlplus exactly at the time it is supposed to run under the scheduler job (which I already disable it when testing ), it runs just fine. This test has been done like 40,50 times and all show that it runs fine under sqlplus which connects to the same server that handles the above scheduler job.
This scheduler job belong to the APP_JOB_CLASS with no resource_consumer_group. If I understand correctly, this means this scheduler job runs under the default group OTHER GROUPS of the Resource Manager.
The server uses the plan DEFAULT_PLAN which covers OTHER_GROUPS. The PLSQL block does not manually change its resource group so it runs under OTHER_GROUPS as well (if I understand correctly). So, from the system resource standpoint, this PLSQL block should run with the same performance under either a scheduler job or sqlplus.
What do I miss in the above analysis ? I am sure I do because it conflicts with the actual performance under those cases.
Any advice is greatly appreciated.
Vu