Home » SQL & PL/SQL » SQL & PL/SQL » Tuning PL/SQL with Collections
Tuning PL/SQL with Collections [message #218323] Wed, 07 February 2007 14:31 Go to next message
trowsdale
Messages: 17
Registered: April 2005
Junior Member
Hi,

I've been doing some reading lately, trying to find tips on how to speed up slow batch processes in PL/SQL.

This article, like many others, discusses the advantages of using collections:

http://orafaq.com/node/1399

So I wrote 2 "skeleton" procedures, and ran them. They both fetch the data that I will be manipulating, but one uses the "Explicit Cursor FOR Loop" method, and the other uses the "Implicit Cursor BULK COLLECT" method.

(see attached)

However, I'm not finding any difference in execution times.

Any ideas? Am I missing something? Would the differences show when I actually start manipulating the data?

Thanks


Re: Tuning PL/SQL with Collections [message #218337 is a reply to message #218323] Wed, 07 February 2007 15:49 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
After a very quick, not detailed, look at your file, my initial speculation is that most of your time may be being taken up executing the query itself, not processing the results.

How long does your query run by itself of those roughly 8 minutes?

Also, it isn't the implicit vs explicit that causes the speedup with bulk operations. It is the usage of bulk processing vs the usage of row by row processing.

[Updated on: Wed, 07 February 2007 15:52]

Report message to a moderator

Re: Tuning PL/SQL with Collections [message #218366 is a reply to message #218337] Wed, 07 February 2007 20:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hey, it's good to see someone's reading my stuff. Feel free to post feedback straight to the article.

For reasons not readily obvious, the improvements of BULK COLLECT over cursor loops is significantly less (based on my testing) than the advantages of FORALL over DML in a cursor loop.

It seems that the context switches for a FETCH are somewhat less expensive. I don't know why. If you have an existing PL/SQL job that uses a cursor loop but has NO NESTED SQL, then I would not expect significant improvements from BULK COLLECT. The real advantage of BULK COLLECT is that it puts the data in a nested table where you can use it in FORALL statements.

It is not easy to separate the time taked to resolve the SQL from the time to fetch it. To get a true comparison of Cursor Loop vs. BULK COLLECT, you would do better testing a single table SELECT over about 1M rows.

Ross Leishman

Re: Tuning PL/SQL with Collections [message #218420 is a reply to message #218366] Thu, 08 February 2007 02:45 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!,

after reading that article especially on the history part of forms & plsql....now i know YOU REALLY ARE AN EXPERT! he9x.

wheeww...when I got to learn about oracle stuff 3 years ago, my first exposure was already forms 6i, I never had an idea that those previous versions is that too much difficult to program with.

regards,
rhani
Re: Tuning PL/SQL with Collections [message #218528 is a reply to message #218323] Thu, 08 February 2007 09:54 Go to previous messageGo to next message
trowsdale
Messages: 17
Registered: April 2005
Junior Member
Thanks for the feedback, everyone!

Yes, I'll continue to experiment with this and I'll let you know how I fare....

To "smartin" - yes, the query I was using in the test case is very ugly - it's slow at the best of times. I imagine that it could be improved as well.

Question for all of you (especially to Ross).....

Most of the articles that discuss BULK COLLECT recommend using the LIMIT clause with it.

However, doesn't this clause negate some of the basic reasons for automating our systems? For example, say a programmer is asked to run a batch update for "any employee making over $40,000."

This is a pretty simple algorithm in most languages:

declare
cursor c1 is select * from employee where salary > 40000;
begin
for l_rec in c1
loop
update employee set give_raise_ind = 'N' where id = l_rec.id;
end loop;
end;

One of the nicest things about this method is that I don't have to find out how many people need the update. It could be 5, 50, or 50,000.

I could do a "pre-processing" query to find out how many people would be impacted, but this raises 2 other questions:

- Isn't this extra query another "hit" on the database? I thought one of the goals of "bulk collection methods" was to reduce DB traffic.

- What if I find that 80,000 people need to be updated? Isn't this well above the advised ceiling for the LIMIT clause?

I think I might know the answers to these questions: I'd need to investigate the other types of collections (as Ross discusses)

But I'd still love to hear your comments!

Bill

Re: Tuning PL/SQL with Collections [message #218539 is a reply to message #218323] Thu, 08 February 2007 10:22 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
But, don't forget about the benefits of just simply doing work inside a single sql statement. In this case, just update the table directly for employees meeting the 40000 criteria. No need to select data out and loop through it in the first place.

And remember, never select more than you need. So if you just need an id, select the id, but don't select *.

And yes, Ross is quite good Smile

Oh, and the limit clause is to make sure you don't blow out all of your memory. When you put things into an array, that array grows and takes up more space. If you know that you will only put a relatively few things in the array, then you don't need the limit. But if you don't know for sure, the limit is useful. Besides, say you do 3 "batch limits" of 30,000 each. That is still better than 90,000 "single rows".
Re: Tuning PL/SQL with Collections [message #218593 is a reply to message #218539] Thu, 08 February 2007 21:15 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You use the FETCH with BULK COLLECT / LIMIT inside a loop, just not a cursor loop.

declare
  cursor c1 is 
  select id 
  from employee 
  where salary > 40000;
  
  id_tbl_typ is table of employee.id%type;
  id_tbl id_tbl_typ;
begin
  open c1;
  loop
    fetch c1
      bulk collect into id_tbl
      limit 1000;
    exit when c1%notfound;
    
    forall i in id_tbl.first .. id_tbl.last
      update employee 
      set give_raise_ind = 'N' 
      where id = id_tbl(i);

  end loop;
end;


Ross Leishman
Re: Tuning PL/SQL with Collections [message #219487 is a reply to message #218323] Wed, 14 February 2007 11:02 Go to previous message
trowsdale
Messages: 17
Registered: April 2005
Junior Member
Thanks for all the help, everyone - I'm starting to get the hang of it.

And yes, I think I fully understand how to use the LIMIT clause now. Before, I had the impression that you had to know your fetch size to use LIMIT; now I can see how it is used in looping. Basically, you are grabbing "more than one, but probably not all" of the records in your cursor, and repeating this until all have been fetched.

Thanks again.
Previous Topic: Query help (selecting diff field from same row)
Next Topic: HELP! Insert Script
Goto Forum:
  


Current Time: Mon Dec 05 15:05:52 CST 2016

Total time taken to generate the page: 0.05890 seconds