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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
