"Sybrand Bakker" <gooiditweg_at_sybrandb.nospam.demon.nl> wrote in message
news:vti1uvcf9oasb2i8if2p6jkamrdmv3ulgl_at_4ax.com...
> 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
- I've yet to see a database that uses more resource for a given set of
data, but feel free to share your own experience.
- Not in my experience. Symptoms are as likely to be exceptions within
Oracle or server hangs.
- Agreed, but a possibility nonetheless.
- Your original response was to find the client who's issuing the
inefficient query and tell him to fix his query. While this is a sensible
thing to do, it does not address the more significant issue which is that
highly-available, robust, server software should not allow any client
request, however malformed or inefficient, to bring it to its knees. Ever.
Received on Wed Dec 17 2003 - 16:30:09 CST
