| 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 -> Re: Speeding up the redo logs
Note that you have 0.91 seconds of wait in 2 minutes 38 seconds. The log file switch is not the issue. Forget Oracle stats for the moment, how much CPU is your system using for that time period - I would guess that it would be running at 99%+ for the duration.
Your model is also not realistic - your wait events seem to show no waits for disk writes, so you are not yet seeing contention between the redo writes and the inevitable block writes that will take place once you have a large volume of data.
If you get users starting to query this data, you will also get massive contention for read-consistency work, and delayed block cleanout.
Using PL/SQL is not a good strategy. Go for Steve's advice, think SQL*Load, or the direct path APIs in OCI. Think parallel server, think partitions.
-- Jonathan Lewis Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk Practical Oracle 8i: Building Efficient Databases Publishers: Addison-Wesley See a first review at: http://www.ixora.com.au/resources/index.htm#practical_8i More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html argosy22_at_my-deja.com wrote in message <94akes$e02$1_at_nnrp1.deja.com>...Received on Sat Jan 20 2001 - 04:02:51 CST
>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
>processes
>
>
>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 blocks written
>489553 244776.5 44504.82 2014.62
>
>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
>
>
>
>- uploading the results to www.oraperf.com for analysis shows the
>wait time to be distributed:
>
>
>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/
 |
 |