| 5M record insert takes two hours [message #611470] |
Wed, 02 April 2014 16:29  |
 |
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 #611631 is a reply to message #611498] |
Fri, 04 April 2014 22:32  |
 |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
