Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> The Outcome: Performance Question - High I/O per Insert

The Outcome: Performance Question - High I/O per Insert

From: Mark Strickland <mstrickland_at_drugstore.com>
Date: Wed, 6 Oct 2004 09:46:05 -0700
Message-ID: <63A70CA32CEF354892F78EADB13D329702F62DA3@seaems005c>


Thank you to several people who responded to this earlier request and gave me good ideas. I was able to perform some tests and wanted to report on the outcome. The situation is a daily SQL*Loader conventional path load of about 2-million records into a range-partitioned table with five partitioned indexes, one local prefixed and four local non-prefixed. Partitions contain a "week's" worth of data. The load rate starts out at around 19,000 records per minute on the first day of a new partition and slows down to around 4,000 records per minute. I ran nine tests loading 1-million records. Test #3 is with just the local prefixed index and the next four tests add each of the four non-prefixed indexes:  

Test     Description                                     Load Time
Index Create/Rebuild Time                               

Test #1  Direct Path Load, No Indexes on Table           6 min
6 min

Test #2 Conventional Path Load, No Indexes on Table 17:48 min 3 min

Test #3 Conventional Path Load, 1 Index on Table 15:14 min 3:10 min (for remaining four indexes)

Test #4 Conventional Path Load, 2 Indexes on Table 26:52 min 3 min (for remaining three indexes)

Test #5 Conventional Path Load, 3 Indexes on Table 39:52 min 2:29 min (for remaining two indexes)

Test #6 Conventional Path Load, 4 Indexes on Table 1:33:33 hours 1:24 min (for remaining index)

Test #7 Conventional Path Load, All 5 Indexes on Table 3:34:28 hours

Test #8 Conventional Path Load, All 5 Indexes on Table

           with pctfree=50% for last two indexes 3:13:21 hours NA

Test #9 Conventional Path Load, All 5 Indexes on Table

           with Indexes set to UNUSABLE                  10:13 min
3:17 min  

I ran test #8 because I believed that a big contributor to elapsed time was index block-splitting. I researched the issue and set pctfree to 50 when I created the indexes in order to minimize block-splitting. I supposed it helped a little. The reason that conventional path load was being used is that the SQL*Loader executable is 9.2.0.5 and the database is 8.1.7.4 and the developer believed that direct path load wouldn't work in that configuration. I was able to get it to work with the 9.2.0.5 client on linux and windows, but I had to set nls_lang=american_america.us7ascii. Doing a direct path load over the network took 20 minutes.  

On the server where I did my testing, I did turn on tracing level 8 for Test #7. About half of elapsed time was due to waits on the index files and log file sync waits. However, this was a test server with a suboptimal I/O configuration. On the production server, during the load there are waits on the index files. If the developer insists on continuing the load process as is, the best I think we can do is try to spread that index I/O around better. However, since I've demonstrated that we can indeed use direct path in this situation, I suspect that the developer will be making some changes.  

Again, thanks for the earlier responses.  

Mark Strickland

Seattle, WA  


From: Mark Strickland
Sent: Tuesday, September 28, 2004 12:27 PM To: 'oracle-l_at_freelists.org'
Subject: RE: Performance Question - High I/O per Insert  

This is a follow-up to a question I posed earlier, but I have more info. A SQL*Loader conventional path load inserts rows into a table partitioned by week. On the first day of a new week, the load runs as efficiently as expected, but as the week goes on, the load goes more and more slowly. There is a single local index on each partition and the blevel is 2. I'm scratching my head. I realize I'm not providing much info, but can anyone tell me why loading into a partition might get slower as the partition fills? Thx!  

Mark Strickland  


From: Mark Strickland
Sent: Wednesday, September 22, 2004 12:42 PM To: oracle-l_at_freelists.org
Subject: Performance Question - High I/O per Insert  

I'm trying to understand why I'm seeing a high number of I/Os for inserts. Inserts are through SQL*Loader conventional patch. Average row length is 129 bytes, no longs or blobs. The table has five indexes and each index has a blevel of 2. According to v$sqlarea, each insert uses 650 logical I/Os, 69 physical I/Os. I would expect fewer than 20 I/Os per insert. There are db file sequential read waits on the data files that make up the index tablespace. That file system also contains the archived logs. Not surprised at the contention. Can someone point me in the right direction to understand this? Thx.  

Mark Strickland

Drugstore.com

Seattle, WA

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 06 2004 - 11:41:46 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US