Re: Big update makes Oracle stop responding

Brian Peasland <dba_at_remove_spam.peasland.com> wrote in message news:<3FDE0C19.5DB6C845_at_remove_spam.peasland.com>...
> Can you sign on to the Linux box while this is occurring? Or is that
> "dead" too? If you can sign on to the box, you'll probably want to use
> OS utilities (top, sar, etc.) to see what is going on.
>
> What architecture do you have for your disk units? Are they IDE or SCSI
> or something else?
>
> Thanks,
> Brian
>
> Easy wrote:
> >
> > Hello there,
> > we have a table named TBL_TREE having 11 columns (10*number, 1*date)
> > with 4 millions rows (about 400MB). Table has 5 indexes (together
> > 800MB). Sometimes we need to delete about 30000 rows from the table or
> > insert such amount of rows into it. When this operation starts
> > database stopps responding to other queries. It's even impossible to
> > connect to instance or (when already connected) perform query which
> > works with real tables. V$ views works so so.
> >
> > Database is 9i SE running on Linux (SuSE) server with 1GB memory,
> > multiprocessor Intel architecture, archiving is on, redologs have 40MB
> > each (5 groups), logswitch each 10-12 minutes when load is high, 40-60
> > minutes when load is normal. Redo buffer 512KB.
> >
> > I understand the database has to do a lot of IOs when modifying large
> > table and its indexes but I expect other sessions still can execute
> > their queries (not as fast as usually, of course). Unfortuntely,
> > database looks like dead for 10 minutes.
> >
> > Can you give me a clue what's wrong? What should I check?
>
> --
> ===================================================================
>
> Brian Peasland
> dba_at_remove_spam.peasland.com
>
> Remove the "remove_spam." from the email address to email me.
>
>
> "I can give it to you cheap, quick, and good. Now pick two out of
> the three"

I think Brian is correct and you should start first by making sure the problem is not at the OS level. Depending on your IO setup you could be overloading a channel (IO path) or have some other problem.

If the OS statistics are good the query: "select * from v$resource_limit" and see if you are maxing any of the listed parameters out.

Are there any alert log entries during the time periods when the problem occurs? Trace flies?

Have you veried that your online redo logs are large enough to not switch excessively (and checkpoint) during this processing.

HTH -- Mark D Powell -- Received on Mon Dec 15 2003 - 19:51:37 CST