Hi kirti, My responses are embedded in your message.
Thanks Vasu "Deshpande, Kirti" wrote: > Are the disks housing redo logs contain other 'hot' files? Are these disks > busy with other tasks (than writing redo logs). No. Though there are db files in its disks, they are not that active. More over, the server where I perform this test is totally quiet. > Are you committing too > often? No. There is only one commit after the delete statement. > How many log switches are taking place? 10 or 11 switches > What is the size of the redo > log file? 100MB > 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. > 1104361.8003 > > 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).
begin:vcard n:Ramasamy;Vasu tel;fax:650 859 3668 tel;work:650 859 4818 x-mozilla-html:FALSE adr:;;;;;; version:2.1 email;internet:[EMAIL PROTECTED] title:Principal Database Administrator fn:Vasu Ramasamy end:vcard