And the inserts are being performed as?

- insert as select
- bulk insert
- implicit cursor loop
- explicit cursor loop
- individual insert statements

These are in descending order of performance, so I would guess it is the last one.

Assuming that you're using JDBC API to access Oracle,
you might need to cross check both of your Java app and Oracle.

1. Extended sql trace on Oracle session. (or AWR report ?)
2. Garbage collection dump and/or thread dump on JDBC session.
(Or do you use profiling tool for java app?)

There can be too many reasons..Don't you think so?
You need to post what you've actually seen using above tools..

========================================
Dion Cho
Blog: http://ukjax.blogspot.com
Wiki: http://wiki.ex-em.com
========================================

Good guidance, but too general.

All we need is "what is actually happening"? Don't you agree?
If the undo space is the problem, the AWR report would show HW contention on undo segment or some other undo-related statistics.
If the commit frequency is the problem, the AWR report would show long log file sync wait or log file parallel write wait.
Wouldn't the AWR snapshots of "good status" and "bad status" tell something meaningful?

If the java garbage collection is the problem, the garbage collection dump would show a quite frequent and annoying garbage collection.
The java app has so many various performance pitfalls just like Oracle does.

As i told, there can be "too" many reasons to list here.
All we need is the fact that what is really happening here.

Just to make this one clear - I commit every 10K records, and I have enough of UNDO, which never gets full.
Everything runs fine, very fast, until it hits these last 10% of data, and starts reading the index tablespace like crazy.
It could be the garbage collection too, I force it right after commit, but you never know if anything is leaking. I'll have to look into this as well.
And it doesn't explain peaking I/O either.

Glad to hear it...but did you ever by chance use v$session_wait.event or tkprof to find the exact wait event that was causing your issue? Inquiring minds want to know!

> v$session_wait was showing async reads of index tablespace (never showed writes)

Can you tell me the exact wait event name and the parameters(p1,p2,p3) of that event?
Event name and its parameters are very important.

Oh, yes. The the reason of performance degradation is very simple.

Insertion on table with indexes need index management.
At early stage, the buffer cache space owned by indexes are not so big that many of the indexe blocks are located in the buffer cache and your insertion operation might end up with memory operation.
But as index grows fast, your buffer cache has no room for index blocks.
DBWR flushs out dirty buffers and you should reload target index block from disk.
This involves large amount of disk I/O, hence performace degrades.

You said you have a couple of indexes, so these indexes compete to occupy the buffer cache. This makes things worse.

This is a natural pheonemon caused by online index management.
For large insertion, direct path load and/or offline index management might be the only solution.

PS) Conventional write operation is always done by DBWR process.
It's the reason why you can't see the write wait event from v$session_wait view.

Typo...
Message was edited by:
Dion_Cho