Skip to Main Content
  • Questions

  • High CPU. Is there a way go GRACEFULLY limit CPU per user?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Bob.

Asked: January 26, 2003 - 12:58 pm UTC

Last updated: May 19, 2004 - 9:31 am UTC

Version: 9.0.1

Viewed 1000+ times

You Asked

Tom,
I've set up a few profiles to test CPU limits for users on a box that is currently CPU starved. However, what seems to happen is that the query that the user issues gets abruptly terminated if they go over the values set in the profile. They get ORA- messages instead of what I thought would be a slower completion. Is there a way to set up a graceful limit of CPU, where the query will finish, just slower and not overtake the box? The profiles for users over Database Links also create excessive trace files when they are terminated by the system CPU limit. Is there a way to limit the query over a DB-Link without generating the trace? These are mainly Portal Application accessing data in another instance.
Thanks
Bob Maggio

and Tom said...

The profiles are a resource governor -- kill it when it hits a threshold.

To restrain them from running away with resources -- you want the resource manager:

</code> http://docs.oracle.com/cd/A91202_01/901_doc/server.901/a90117/dbrm.htm#5080 <code>

that'll let you setup a graceful CPU, Logical IO limit -- limit the max number of active sessions in a group, control the amount of undo they can generate and so on.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

renice

Robert Xuequn Xu, January 26, 2003 - 8:00 pm UTC

Just a thought: suppose the database is running on a Unix system, and the clients connect to it in dedicated server mode. Would it be a simple solution (although unconventional) to just renice the specific server process?

Tom Kyte
January 26, 2003 - 8:10 pm UTC

Nope -- not only not suggested -- proactively "de-suggested"...

first, maintenance nightmare of the 1st degree. the practicality of this is really questionable. it would be a full time job just finding and re-niceing things.

second, totally unsupported. there can be un-anticipated side effects when you play with the OS scheduler and the database...

tom is simply great

A reader, May 19, 2004 - 9:31 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.