Home » RDBMS Server » Performance Tuning » 5M record insert takes two hours (11gR2)
5M record insert takes two hours [message #611470] Wed, 02 April 2014 16:29 Go to next message
That Don Guy
Messages: 1
Registered: January 2011
Location: Fairfield, CA
Junior Member
I have a table with about 900 million rows, and am trying to insert 5 million rows in a single insert statement.

However, it takes almost two hours to complete.

The table has NOLOGGING set, and the insert uses the APPEND hint.
The table has two indexes; a PK on two numbers and a date, and a normal index on a date.
Also, the table has OLTP compression turned on.

Can somebody give me some ideas as to what to look for in order to get this to run faster? (I have five similar tables that are taking almost as long; something that I would expect to take no more than 10 minutes is taking 10 hours.)
Re: 5M record insert takes two hours [message #611471 is a reply to message #611470] Wed, 02 April 2014 16:43 Go to previous messageGo to next message
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

We need a few more details.
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
3) output from SQL_TRACE & tkprof

is the slowdown on the INSERT or SELECT?

[Updated on: Wed, 02 April 2014 16:48]

Report message to a moderator

Re: 5M record insert takes two hours [message #611498 is a reply to message #611471] Thu, 03 April 2014 02:30 Go to previous messageGo to next message
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are there any triggers on the tables?
Re: 5M record insert takes two hours [message #611631 is a reply to message #611498] Fri, 04 April 2014 22:32 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, Cookiemonster has the right idea. The most likely suspect is a piece of code that you are not aware of that is going real slow. The things I can think of:

1. (Cookiemonster) trigger on the table.
2. you are inserting into a view and the view has an instead of trigger.
3. there is a function in your insert that has bad SQL in it (too bad you did not post your insert statement) (look for un-optimized query, row-at-a-time dblink hop).

other more obscure possibilities that come to mind:

4. VPD (Virtual Private Database) might be adding some code that is slowing you down.
5. you are running with parallel_degree_policy=auto and doing parallel DML and not getting enough PQ slaves so your query is being queued until DOP slaves become available.
6. disk subsystem failures (channel/disk/redolog) causing multiple tries before writes and/or reads succeed.
7. rows you need are pending changes so you are doing massive undo block rebuilds to get consistent reads from dirty blocks.
8. disk queues are way backed up because of other activity.

and of course there is the always popular and generic:

9. you are waiting on something, figure out what it is.

and lastly there is the truly simple:

10. if you are doing insert/select then maybe your query plan is just bad and you have not figured it out yet.

I am glad to see you have some idea of how long it should take. That means you have been paying attention to things you do and over time have built and understanding of your system. You are correct of course; the typical Oracle database can insert 1 million rows per minute without breaking a sweat, even on low end hardware.

Good luck. Kevin

[Updated on: Fri, 04 April 2014 22:37]

Report message to a moderator

Re: 5M record insert takes two hours [message #611712 is a reply to message #611631] Mon, 07 April 2014 10:54 Go to previous message
Messages: 78
Registered: November 2011
Location: UK
Also check for any FK constraints, dictionary managed tablespace resulting in recursive SQL also comes to mind
Previous Topic: Materialized View not being used on production (works elsewhere)
Next Topic: COUNT STOPKEY issue -- performance issue
Goto Forum:

Current Time: Mon Mar 27 06:39:13 CDT 2023