I have a oracle procedure which needs to be run on hourly basis. My Application server is Linux based and my database is oracle. Now I have 2 options . Either I can create a job in Oracle and schedule it or I can write a shell script and schedule it with the help of cron tab in my application server. Can any one please let me know which approach is the better in terms of database performance and why ? Or is it like both approaches will lead to the same performance results ? If yes, then what should be the standard approach.
We don't look at the difference in terms of performance. Either way, a session will be spawned and the performance of that session would be evaluated.
So it depends on who has access to the server. If it is a DBA-only job then we find it easier to manage on the server. If they are shared jobs with people who write for example long-running reports on the warehouse, then they can more easily be managed together.
The downside of cron jobs is that when oracle is upgraded, all jobs have to be stopped, moved, started, tested and this can be a pain.
The downside of scheduler job processes is that it's sometimes difficult to track who ran what job, so we have scripts that match job numbers to workers in the job name.