Hi Jared, Our developers are expecting it to be equivalent Sybase response time which is less than 2 minutes. We are moving from Sybase to Oracle. But Oracle beats Sybase response time in all other SQL queries. I was trying to see whether any incorrect init params are set.
Thanks Vasu [EMAIL PROTECTED] wrote: > Personally, I'd be pretty happy with a 400K row delete > only taking 10 minutes. > > Jared > > "Vasu Ramasamy" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 06/26/2002 09:49 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > Subject: Deleting large table and the correct log buffer size > > 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
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