| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Speeding up the redo logs
Hi all,
I'm currently working on a project where we will eventually want to insert up to a billion rows a day into the database. This is an average of over 11,000 rows per second.
To this point, I've just made a table called JUNK:
Name Null? Type ----------------------------------------- -------- ---------------------------- JUNK_ID NUMBER(6) JUNK_DESC VARCHAR2(20)
I truncate this, and populate it with 1 million rows with a PLSQL routine. It takes 2 minutes 38 seconds to populate it. About 6,300 inserts per second. Naturally, if I add indexes, it slows the inserts down a lot.
The main part that slows everything down is the redo logs. There are five 50 meg redo logs on their own drive.
I have been tweeking the INIT.ora parameters, running utlbstat, and utlestat and seeing what the results are. No matter what parameters I tweek, the inserts take pretty much the same time. They are:
log_buffer log_checkpoint_interval log_checkpoint_timeout
I have also varied the commit frequency. If I commit every row, it actually gets much slower. It was faster to commit either at the end altogether, or after every 1000 rows (no difference in speed). There are big rollback segments.
The results from utlestat show:
Statistic
Total Per Transaction Per Logon Per Second
redo buffer allocation retries
5 2.5 .45 .02
redo entries
1009653 504826.5 91786.64 4154.95
redo log space requests
6 3 .55 .02
redo log space wait time
91 45.5 8.27 .37
redo ordering marks
2219 1109.5 201.73 9.13
redo size
241785892 120892946 21980535.64 995003.67
redo synch time
4 2 .36 .02
redo synch writes
3 1.5 .27 .01
redo wastage
1022212 511106 92928.36 4206.63
redo write time
5652 2826 513.82 23.26
redo writes
2862 1431 260.18 11.78
Event Name
Count Total Time Avg Time
PX Idle Wait
952 194208 204
SQL*Net message from client
180 36975 205.42
dispatcher timer
4 24576 6144
virtual circuit status
8 24576 3072
rdbms ipc message
3 18432 6144
log file switch completion
6 91 15.17
rdbms ipc reply
78 11 .14
latch free
7 6 .86
log file sync
3 4 1.33
local write wait
1 1 1
SQL*Net break/reset to client
1 0 0
file open
13 0 0
SQL*Net message to client
180 0 0
refresh controlfile command
4 0 0
control file sequential read
12 0 0
db file sequential read
38 0 0
log file switch completion 91 81.25 rdbms ipc reply 11 9.82 latch free 6 5.36 log file sync 3 3.57
So, the log file switch completion always takes the bulk of the wait time.
I will watch the logs by repeating the query:
Select *
from v$log,
v$logfile
where v$log.group# = v$logfile.group#
You can see how many seconds it takes. A log's status will be Current. Then, it becomes Active, and another log becomes Current. The Active log will stay active for a number of seconds while it is written to. Yet, a standard unix copy command of an inactive log took less than a second.
Unfortunately, the drives are configured as RAID 5, and I'm sure that this has much to do with the slowless. We will eventually be moving to RAID 1. But in the meantime, I wondering if there is anything that I have missed that I might try. If I can get this to work on RAID 5, it should work even better on RAID 1.
Any other ideas?
What is your experience inserting so many rows at once? How fast can you get it to work in PLSQL?
Thanks a lot
Sent via Deja.com
http://www.deja.com/
Received on Fri Jan 19 2001 - 18:00:32 CST
 |
 |