A specific Insert Statement taking too long, despite all tricks.
612618Apr 14 2008 — edited Apr 14 2008Oracle 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.