RE: controlling CPU usage through Oracle

[Jeff Holt]

If your purpose is to slow down query execution instead of killing a query (or a process), then you cannot use resource profiles. Using resource profiles you can only kill a statement execution or kill a session. An OS resource manager might have a feature that just slows down a CPU consumer but even if it did, how would you tell it not to context switch a process holding a latch? If you’re asking ‘why would it have to context switch the process’, then the answer is ‘what else could it do with a cpu consumer’?   It’s true that HPUX, possibly others, have worked with Oracle to prevent such a catastrophe. To use this new feature of Oracle/HPUX you have to migrate to 9i. Since you aren’t on HPUX, then you’re out of luck on that feature anyway. At least for now.   I could jump on a soapbox to discuss server consolidation vs. resource management but I’m sure that’s a waste of time. Instead, we should discuss concurrent batch job management as it relates to these queries. If these individual queries response time is between 45 seconds and 2 minutes, then the processes that execute them should probably be considered batch jobs. And as such they should relent to being managed by a queue that prevents them from over consuming system resources per some unit of time. If they already relent to a manager, then the manager needs a refresher course on job scheduling.   Jeff Holt Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1–3 San Francisco, Oct 15–17 Dallas, Dec 9–11 Honolulu - 2003 Hotsos Symposium on Oracle® System Performance, Feb 9–12 Dallas - Next event: Miracle Database Forum, Sep 20–22 Middlefart Denmark -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Ed Lewis Sent: Wed, Aug 28, 2002 08:13 To: Multiple recipients of list ORACLE-L Subject: Re: controlling CPU usage through Oracle   Raj,     Thanks for your input. From what I understand with profiles, the session will be terminated once a threshold is reached.     In this particular case, I cannot terminate the session. I need to be able to control the CPU usage of a session, without ending it.      If this cannot be done within Oracle, then maybe it can be accomplished through the OS.           thanks.                         ed                       ----- Original Message ----- From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Tuesday, August 27, 2002 4:48 PM Subject: RE: controlling CPU usage through Oracle   You could use profiles to control CPU usage as well, more info is in manuals.   Raj ______________________________________________________ Rajendra Jamadagni              MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -----Original Message----- From: Ed Lewis [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 27, 2002 4:18 PM To: Multiple recipients of list ORACLE-L Subject: controlling CPU usage through Oracle Hi,     I'm looking for a way to control CPU usage of particular queries on a database.     After exploring, and implementing ways of optimizing the code, these queries can run anywhere from 45 seconds to 2 minutes.      This database is 1 of 5 on this box. So, although it's important to build effective queries, it's also important in this environment that these queries do not impact the other 4 databases.     What I want to do is place a limit on CPU usage without terminating the query. I've looked at profiles with the "cpu_per_call" and "cpu_per_session" settings. I have never used profiles, but from what I understand, these will kill the session once they reach a threshold.     I'm also looking at database resource management. This may be more appropriate for this situation.     Has anyone used this, and if so what are you experiences ? Would you recommend it ?     Would the only way to control CPU usage on this box, be through the operating system ?   I welcome any suggestions. I appreciate your time.   The environment is Oracle 8.1.7.2, AIX 4.3.3.