I have a table of 500 millions of rows (and growing)

I did the following to improve performance of inserts:

On database side:

  • dropped all indexes and constraints
  • disabled logging

On application side:

  • switched from JPA managed entities to native insert queries, added APPEND Oracle hint to the query
  • tried to commit in batches per 1k/2k/3k of rows
  • tried to write in parallel (multiple threads, thread count = to core count on server) to one table

This gave me about 300 rows per second

Additionally tried:

  • write in parallel in batches to multiple tables (to group then back results using UNION)

This gave me about 1k rows per second, but on empty tables. But when I filled tables with dummy data (200 of millions each), speed of inserts dropped to 250 - 300 per second.

Could anyone suggest what else can I do to speed-up inserts? Basically I want to understand what is (what could be) the bottleneck first.

UPD: Table is partitioned by insert date, table has about 60 columns - most of columns are VARCHAR2(2000 BYTE)

  • You know that with logging disabled, a media failure between the load and the completion of the first subsequent backup will leave the entire table, or sections of it in the case of a direct path insert, unrecoverable, right? Mar 26, 2013 at 10:12
  • 1
    (1) Only one session can APPEND at any one time on a table. (2) the /*+APPEND*/ hint is ignored on single-row inserts (if you don't have INSERT INTO ... SELECT don't bother with append). (3) You should setup a SQL*Loader example with direct=true to establish a baseline as suggested by @parsifal. Mar 26, 2013 at 10:32
  • Are you running on real hardware or a virtual machine? If a VM, are the disk files sparse (ie: not fully pre-allocated)? Also, please edit your question with the output from a statspack or awr report (top waits section).
    – Philᵀᴹ
    Mar 26, 2013 at 14:24
  • What problem/need does partitioning by insert date resolve/satisfy?
    – Brian
    Mar 26, 2013 at 17:23
  • What is the source of your data for this table? Is this a batch load from an ASCII file or is it user generated or something else. Please be specific. Mar 26, 2013 at 18:11

2 Answers 2


Just saw the update, 60-col table with mostly VARCHAR(2k) fields -- that is (potentially) a monster table.

First things first...

You have to understand your bottleneck FIRST. On the app side, go all the way back to your single-threaded batch-insert solution (1/2/3k at a time) and begin running it and login to the DB machine and run a 'top' -- see how much time the DB process is taking AND how much (if any) wa% time the machine is showing.

If top is showing you ANY wa% time, that means your DB is I/O bound and you likely need to consider multiple DB machines (shards) or consider throwing SSDs on the host machine.

That's it; your research stops here. It doesn't matter how much CPU the DB was taking or how saturated your app client was; if you are hitting I/O latency issues on the host DB, that is as fast as it will EVER go for you.

TIP If hardware changes are out of the question, depending on the filesystem you are running (Linux) you can try disabling logging or metadata writing for the DB to slightly improve performance at the filesystem level. You can do something similar on NTFS, but this will only give you a little boost. This won't be 2x.

Now, second things second...

Let's say you had next to no wa% time but your CPU is pegged fully by the DB process. Your only option now is to introduce more DB machines (shards) and split the work.

Again, you're done with your research if this is the case. Nothing you can do to tweak the CPU to go faster.

Lastly, third things... third...

Let's say the DB isn't doing much of anything. Then, go to the client machine running the batch insert and check the CPU load -- is it pegged? If so, fire up some more machines doing the exact same batch inserts and see if you can get a linear ramp.

If the CPU isn't pegged, fire up some more threads on the same machine until it is pegged and see how the DB scales.

I think you may have already tried that, so my guess is that either your client host was already pegged (and more threads isn't going to make a difference) or the DB was already hitting its limit and can't scale any farther.


Doing raw inserts on an unindexed table that has no garbage in it is essentially an APPEND operation which should be going as fast as the disk can handle the writes.

Creating more tables on the same host machine isn't going to help, if anything it will increase your disk seeks (to get to the other tables on disk to append to) and will slow things down.

It is critical to figure out that bottleneck first, then we can optimize the hell out of it.

Hope that helps! Keep us posted.

  • 2
    Why haven't you mentioned awr or statspack?
    – Philᵀᴹ
    Mar 26, 2013 at 2:26
  • With an append hint, all but one of those threads are going to be idle due to exclusive locking. I don't think this code is at a stage of efficiency where system-level tuning is required -- it's the methodology itself that is flawed. Mar 26, 2013 at 10:10
  • Thinking further, I believe your approach has a fundamental flaw. If Viktors tried the single-threaded batch-insert method and had i/o wait times, that could be caused by an inefficient insert method and over-commiting (log file sync waits). The most important step ought to be to understand the Oracle mechanisms and choose the most appropriate one first, surely? Mar 26, 2013 at 10:39
  • @DavidAldridge Viktors clarified that he had disabled logging (and the indices) given that, I assumed there wasn't much else the DB was doing besides streaming the inserted data right to the table file which is why I had him jump right to looking at I/O wait. Maybe there is more Oracle is doing that should/could be disabled -- that's a good point of investigation, I don't know the depths of Oracle well enough to help there unfortunately. Mar 26, 2013 at 19:58

Invoking direct path insert with the append hint causes an exclusive lock to be taken against the entire table, so having multiple threads performing the insert will not help. You would need to explicitly address a different partition with each insert ...

insert /*+ append */ into my_table partition (partition_name_1) ...

... to get partition level exclusive locks. You won't be able to do that with a table partitioned on insert date, most likely, but you could use composite partitioning (not subpartitioning) to get multiple partitions per unique range of insert dates.

Do not commit in the middle of the inserts, just at the end.

  • Do I need to mention partition name in the query explicitly? I have column, sort of event type. I am going to try to partition by group of events and make so that each thread is inserting batch of rows only of particular type
    – adrift
    Mar 26, 2013 at 11:21
  • To avoid a table level exclusive lock, yes you do. Mar 26, 2013 at 13:39
  • The APPEND hint should be ignored by Oracle for single-row inserts. The description of the process by the OP seems to imply batch single-row inserts. I'm not sure how those are treated though. I would guess no APPEND but it needs some testing. Mar 26, 2013 at 14:04
  • Hmmm, didn't consider that -- it's even worse, if so. Mar 26, 2013 at 14:27
  • Is it worth to try multi-row inserts with APPEND hint? Then how many entries per multi-row insert should I send?
    – adrift
    Mar 26, 2013 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.