2

I have this requirement to run "Maintainance Jobs" for a web application. These jobs will just call a stored procedure from a set of specific packages.

Running these jobs can consume quite a bit of CPU time due to the sheer amount of work they need to do (most of it is read, to determine work to do). Due to that, they compete with actual consumer traffic.

Now, can I somehow tag or otherwise indicate that these jobs have a lower priority than queries executed by the customer facing frontend application?

I've seen Oracle Resource Manager, which would allow me to specify a "Resource Plan" based on a module/action name. But it's not quite clear from the documentation if I have to set this explicitly in the SP (which I'm trying to avoid) or if Oracle would do this for me. I'm also open for suggestions not using resource plans.

2

I can't personally think of anything better for on-the-fly resource throttling based on to-the-second stats than Resource Manager. It can be a bit of a hassle to set up, but I don't imagine how writing custom code wouldn't just be reinventing the wheel that is Resource Manager.

I'm not aware of any reliable 3rd party vendors at this time.

I do know that you don't have to do anything to your stored procedures. IMO the best way to use Resource Manager and stored procedures is to use DBMS_SCHEDULER to tie the resource plan in with a scheduled job.

Documentation on dbms_scheduler is here: http://docs.oracle.com/cd/E11882_01/appdev.112/e10577/d_sched.htm#ARPLS72235

Also, IIRC, remember when implementing that Resource Manager handles CPU and I/O, but not Memory utilization.

  • Great answer, but using the scheduler probably requires quite some shifting here as all the "jobs" are controlled by the application tier. – mabi Sep 19 '14 at 7:31
  • If you're using services to make app connections, I think you can use Resource Manager to throttle traffic based on service: docs.oracle.com/cd/E18283_01/rac.112/e17264/configwlm.htm – captjackvane Sep 26 '14 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.