OraFAQ Forum: Performance Tuning » Performance issue in Oracle 10g using Bulk Collect

Home » RDBMS Server » Performance Tuning » Performance issue in Oracle 10g using Bulk Collect (Oracle 10.2.0.3.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance issue in Oracle 10g using Bulk Collect [message #442144]

Fri, 05 February 2010 00:32

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 #442159 is a reply to message #442144]

Fri, 05 February 2010 01:15

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Use

INSERT /*+ APPEND PARALLEL(intermediate_table, x) */ INTO intermediate_table
SELECT /*+ PARALLEL(tab1 x) ... */ *
FROM (query of ODB_VIEW);

to speed up the process. Evaluate the parallel level you can use depending on your OS, hardware and available resources.
tab1... are tables used in the view, use the view definition instead of the view itself.
If you do this quite oftenly you should view to use a materialize view instead of a view.

Regards
Michel

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

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

Report message to a moderator

Re: Performance issue in Oracle 10g using Bulk Collect [message #442176 is a reply to message #442170]

Fri, 05 February 2010 02:58

Michel Cadot
Messages: 68650
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

Report message to a moderator

Previous Topic:	Function Based Indexes
Next Topic:	HASH cluster building very Slow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 12 03:08:27 CDT 2024