Oracle-L: RE: Deleting large table and the correct log buffer size

From: Weaver, Walt <wweaver_at_rightnow.com>
Date: Wed, 26 Jun 2002 12:49:43 -0800
Message-ID: <F001.00489555.20020626124943@fatcity.com>

Being a non-certified MySQL DBA, I can assure you that delete performance in Oracle is VERY disappointing if you've transitioned over from MySQL.

--Walt Weaver

Bozeman, Montana

-----Original Message-----

Sent: Wednesday, June 26, 2002 2:35 PM
To: Multiple recipients of list ORACLE-L

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: Weaver, Walt
INET: wweaver_at_rightnow.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