Re: Deleting large table and the correct log buffer size

Hi Kevin,

     What version is your Oracle server? The tablespace in which the table uses 'dictionary managed' or 'locally managed' ?

  Ours is loaclly managed TS - Oracle 8.1.7

  Thanks

Vasu

Kevin Lange wrote:

> WE had a similar bad response when deleting from a 16 million row table.
> Due to certain circumastances we were unable to truncate ... only delete.
>
> We found 2 things that caused the big slowdown.
> 1) Archiving. Logging all the delete added a small amount of slowdown.
> 2) Extents. It turned out that table had 8540 extents (I know ... it was
> one reason were were rebuilding it). That many extents causes a very big
> strain on things.
>
> We ended up rebuilding it twice. The first time , the delete litterally
> took hours. The second time (After the extent problem was taken care of),
> the delete took minutes.
>
> I am curious as to how many extents that table has.
>
> -----Original Message-----
> Sent: Wednesday, June 26, 2002 3:35 PM
> To: Multiple recipients of list ORACLE-L
>
> 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
>
> Jared.Still_at_radisys.com wrote:
>
> > Personally, I'd be pretty happy with a 400K row delete
> > only taking 10 minutes.
> >
> > Jared
> >
> > "Vasu Ramasamy" <vasu.ramasamy_at_sri.com>
> > Sent by: root_at_fatcity.com
> > 06/26/2002 09:49 AM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> > 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
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Kevin Lange
> INET: kgel_at_ppoone.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).

-- 
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).



text/x-vcard attachment: Card for Vasu Ramasamy