Re: Responsiveness of Server at high CPU load

On Wed, 17 Dec 2003 10:37:17 -0500, "Jarmo" <jarmo_at_jarmo.com> wrote:

>"Rick Denoire" <100.17706_at_germanynet.de> wrote in message
>news:bptutv4qmvps97fbo234irlh7dhd8pqg4m_at_4ax.com...
>> (Excerpt from a TAR - still open)
>>
>> From time to time, our Oracle test server (9.2.0.4 on Intel/Linux, 2
>> CPUs) got unusuable at CPU load of 99% as shown by top; in this state,
>> nothing else could be done with Oracle, even trying to connect via
>> sqlplus took about 1 hour (assuming one would wait that long).
>> Processes running were Oracle processes and kswap (meaning that
>> swapping was heavily taking place).
>>
>> After analyzing lots of things we found out that nothing seems to be
>> wrong with the database - it is just that a very inefficient query is
>> running which blocks the Oracle server and avoids any other activity.
>
>My limited exposure to Oracle 9 on RH Linux would suggest one, and quite
>possibly all, of the following, in order:
>
>1. your system is not 'big' enough for Oracle; even the tiniest database
>requires inordinate amounts of RAM and diskspace
>2. you haven't set sem, shmmax, shmmni and so on to big enough numbers
>3. there is a bug in Oracle on your version of Linux
>4. there is a significant design flaw in Oracle that allows it to be
>saturated by a single, errant query
>

1 is simply not true
2 is not true. Oracle would simply fail with an error 3 is just guessing
4 is just nonsense.
You can set up resource limits. If the query is hitting the resource limits it will simply be aborted.

--
Sybrand Bakker, Senior Oracle DBA