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
vasu.ramasamy.vcf
Description: Binary data
