Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

A specific Insert Statement taking too long, despite all tricks.

612618Apr 14 2008 — edited Apr 14 2008
Oracle 11g. A specific Insert Statement taking too long, despite all performance enhancement techniques!! Particulars:
(1)It is a complex insert statement, with inline views and subqueries.
(2)Inserts about 1 million rows.
(3)However, while similar insert-statements, even more complex, and with more rows (2 million) and more number of columns run ok and complete within 5 minutes, this one goes on and on and takes over 45 minutes.
(4)To speed up, I have used NOLOGGING option on the table. I have also tried PARALLEL. There is much more than enough tablespace, yet!!!
(5)Strangely, sometimes it does complete within 3 minutes - but only some times, under apparently similar conditions. In other words, it is NOT reliable - it can get stuck, or it may go through : 80% of the time it gets stuck.
(6)I have tried it in single-user mode to eliminate all other possibilities.
(7)I have dropped and re-created the table - even assigned it a tablespace exclusively, yet the problem persists.
(8)If you run only the select part of the insert, the response is very fast - you get the rows within a few seconds. It is apparently only the insert-part that is taking time.
(9)I ran TKPROF. I find that the main difference between the operations when it completes (a)within 3 minutes and when it takes (b)45 minutes is the figure under "DISK= number of physical reads of buffers from disk". For (a)the figure is 38953, and for (b) it is a huge 486944 !! Reason - I do not understand. I have repeated it after re-booting even, but fugures are around this.
(10)I have to have some way to make it reliably complete the operation in 3 to 5 minutes.
Looking forward to your help.
Thanks in advance.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 12 2008
Added on Apr 14 2008
2 comments
426 views