Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Deleting large table and the correct log buffer size

Re: Deleting large table and the correct log buffer size

From: Vasu Ramasamy <vasu.ramasamy_at_sri.com>
Date: Wed, 26 Jun 2002 11:17:31 -0800
Message-ID: <F001.00489200.20020626111731@fatcity.com>


Hi Alexandre,

    The checkpoint and log file switch are happening every 4 to 5 minutes. I will try to increase the soze of the redo log file.

   When I reduced the log buffer from 20MG to 4 MG, the log buffer space wait event stats has just doubled. I am really confused with this params value.

    Thanks

Vasu

Alexandre Gorbatchev wrote:

> Vasu,
>
> I think you should increase the size of your redo log files to make log
> switches at average once every 10-30 mins.
>
> I wouldn't make redo log buffer more than several Megs.
> Alexandre
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, June 26, 2002 6:49 PM
>
> > Hello Gurus,
> > Ours is basically a Sybase shop. But moving towards Oracle. We are in
> > the process of conversion. I have a problem in deleting a large number
> > of rows. To delete 400,000 rows approx., it takes nearly 10 minutes to
> > complete and sometimes hogs system. It is a simple delete statement,
> > delete all rows. We do not want to use truncate statement. I try to find
> > why it is taking so much time by checking the wait events. The session
> > event wait statistics is provided below.
> >
> >
> > SIDEVENT
> > TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
> > --------------------------------------------------------------------------
> ------------------------------------
> >
> > AVERAGE_WAIT MAX_WAIT
> > ----------------------
> > 27latch
> > free
> > 10 10 20
> > 2 2
> >
> > 27log buffer
> > space
> > 298 271 29267
> > 98.2114094 104
> >
> > 27log file switch
> > completion 11
> > 10 1069
> > 97.1818182 103
> >
> > 27log file
> > sync
> > 3 2 221
> > 73.6666667 103
> >
> > 27db file sequential
> > read 3222
> > 0 7695
> > 2.38826816 589
> >
> > 27db file scattered
> > read 271
> > 0 493
> > 1.81918819 236
> >
> > 27file
> > open
> > 4 0 0
> > 0 0
> >
> >
> > The log_buffer_space wait event looks like is the culprit with
> > total_wait =298, total_timeout = 271, and time_waited = 29267 ( 1/100th
> > of a second). Are these normal values for this wait event? The log
> > buffer init param is set to 20MB. To set up the init params, we took the
> > advice of the consultant DBA who worked for a short time (6 months) to
> > help us to set up servers. While I was going through the DBA hand books,
> > ( 101 Oracle performance tuning by gaja krishna vaidyanatha and other)
> > it clearly says that setting higher value will cause trouble. He
> > recommends to set it to 1MB. I tried with 4MB log buffer, the wait stats
> > for log_buffer_space just doubles.
> >
> > Can you please advice me what should be the correct size for log
> > buffer?
> > Do you see any problem with wait stats provided above?
> >
> > If you need further details, let me know please.
> >
> > Thanks
> >
> > Vasu
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Alexandre Gorbatchev
> INET: alexandre.gorbatchev_at_avermann.de
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Vasu Ramasamy
  INET: vasu.ramasamy_at_sri.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jun 26 2002 - 14:17:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US