Re: Deleting large table and the correct log buffer size

Hi Denis,

     I tried the same delete in the Sybase version. To delete the same number of rows, Sybase took 3 minutes and 10 seconds. Other than the deletes, response time in Oracle for other statements looks better, when compared to Sybase. Having said that, I am afraid that I may be comparing apples and oranges. The (cpu) power of Oracle machine is twice that of the Sybase's. Our developers are by and large very happy with Oracle performance.

    Thanks

Vasu

DENNIS WILLIAMS wrote:

> Good point, Jared. A thought occurs to me. Is it possible the Sybase
> architecture lends itself to deletes more than the Oracle architecture. As
> you study the Oracle architecture you note several places where Oracle is
> tuned for high insert/update performance at the expense of the delete
> performance. Perhaps someone on the list with experience in both Oracle and
> Sybase can comment on whether Sybase is faster at deletes. If that is true,
> I could see a DBA coming from a Sybase background might expect Oracle
> deletes to be much faster, while those of us who have only Oracle experience
> might have a different expectation.
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
> -----Original Message-----
> Sent: Wednesday, June 26, 2002 3:06 PM
> To: Multiple recipients of list ORACLE-L
>
> 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: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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