The Outcome: Performance Question - High I/O per Insert
- From: "Mark Strickland" <mstrickland@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 6 Oct 2004 09:46:05 -0700
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@xxxxxxxxxxxxx'
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@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » The Outcome: Performance Question - High I/O per Insert
