Performance issue in Oracle 10g using Bulk Collect [message #442144] |
Fri, 05 February 2010 00:32 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
venkat_bollu
Messages: 20 Registered: June 2009
|
Junior Member |
|
|
Hi,
I am facing a strange issue in Oracle 10.2.0.3.0. I have a procedure which pulls the data of appr.28 millions from other database using a DB link. This process is taking 5 to 6 hours in my development environment. This is done using a single INSERT statement in the procedure as follows:
INSERT INTO intermediate_table
SELECT *
FROM ODB_VIEW;
ODB_VIEW is created to access the tables of the other database.
If I do count on ODB_VIEW query takes 8 minutes to give the count.
I replaced above insert statement using the bulk collect & with a limit of 2000 on each fetch and then forall insert as follows:
OPEN c1 FOR (SELECT * FROM odb_view);
LOOP
FETCH c1 INTO tab LIMIT 2000;
EXIT WHEN tab.count = 0;
FORALL idx IN 1..tab.count
INSERT INTO intermediate_table
VALUES tab(idx);
END LOOP;
CLOSE c1;
For few times I was able to complete the process in one hour time. but i am unable to repeat this further.
Could some let me if there is any environmental settings require to use the BULK COLLECT? I guess no settings are required.
I have done this exercise many a times with my previous organizations and tasted the success but I am failing here to do the same exercise.
Thanks in Advance
Venkat.
CM - fixed code tags
[Updated on: Fri, 05 February 2010 03:38] by Moderator Report message to a moderator
|
|
|
|
Re: Performance issue in Oracle 10g using Bulk Collect [message #442170 is a reply to message #442159] |
Fri, 05 February 2010 01:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
venkat_bollu
Messages: 20 Registered: June 2009
|
Junior Member |
|
|
Hi Michel,
Thanks for your valueable suggestion. I forgot to mention, I have /*+ APPEND */ hint while inserting.
INSERT /*+ APPEND */ INTO intermediate_table
....
I can't opt for MView as I don't have permission to create that on the other database. The view is already existing in the production and client doesn't prefer to modify that script.
But any idea the reason for the slowness when we use Bulk collect? It shoud be much better than any other method.
Thanks
Venkat
|
|
|
Re: Performance issue in Oracle 10g using Bulk Collect [message #442176 is a reply to message #442170] |
Fri, 05 February 2010 02:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:It shoud be much better than any other method.
No insert select direct mode (append hint) is the fastest way.
Quote:I can't opt for MView as I don't have permission to create that on the other database. The view is already existing in the production and client doesn't prefer to modify that script.
Tell them you have to if they want better performances.
Writing is a long process.
You can first check how long it takes to select all data (not just count them) but anyway writing is a long process with extends/blocks allocation, rollback allocation and so on.
Regards
Michel
|
|
|