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.

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
2
  • Great answer, but using the scheduler probably requires quite some shifting here as all the "jobs" are controlled by the application tier.
    – mabi
    Commented Sep 19, 2014 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
    Commented Sep 26, 2014 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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