Are the disks housing redo logs contain other 'hot' files? Are these disks busy with other tasks (than writing redo logs). Are you committing too often? How many log switches are taking place? What is the size of the redo log file? May be increasing it may help.
What is the redo size per commit? Use this query to find out RSPC: select r.value / decode (c.value, 0, 1, c.value) RSPC from v$sysstat r, v$sysstat c where r.name = 'redo size' and c.name = 'user commits'; You can use RSPC to size log buffer by multiplying it by number of user commits per second. HTH, - Kirti -----Original Message----- Sent: Wednesday, June 26, 2002 11:49 AM To: Multiple recipients of list ORACLE-L 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: Deshpande, Kirti 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).