Home » SQL & PL/SQL » SQL & PL/SQL » Bulk collect limit on what basis we will keep
Bulk collect limit on what basis we will keep [message #639964] Mon, 20 July 2015 21:51 Go to next message
sasank54
Messages: 4
Registered: July 2013
Location: Banglore
Junior Member
I am having a table 'account_id' that table is having 10 million records, without knowing blindly i kept limit of
10000,is there any reason on what basis the limit should be kept, then my session became very slow.
Re: Bulk collect limit on what basis we will keep [message #639965 is a reply to message #639964] Mon, 20 July 2015 22:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
sasank54 wrote on Mon, 20 July 2015 19:51
I am having a table 'account_id' that table is having 10 million records, without knowing blindly i kept limit of
10000,is there any reason on what basis the limit should be kept, then my session became very slow.



NEVER do in PL/SQL, that which can be done in plain SQL.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use [code] tags and make your code easier to read

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Bulk collect limit on what basis we will keep [message #639980 is a reply to message #639964] Tue, 21 July 2015 02:06 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The difference in performances between 1000 and 10000 is negligible but the difference in usage of memory is huge: 10 times.
If you really need BULK COLLECT (that is if you can't do the same thing in plain SQL and can't avoid PL/SQL) then I bet the slowness comes from memory management (assuming your PL/SQL code is correct, that is optimized, and you do no commit inside loop).

Previous Topic: linking gv$sql and gv$session
Next Topic: Issue with Number Conversion !!
Goto Forum:
  


Current Time: Thu Mar 28 09:20:35 CDT 2024