Loading

Friday, February 10, 2012

Performance tuning using BULK COLLECT with example


In this post, lets see the effect of BULK COLLECT over performance.Simple example are used.
The below code is written using with and with out Bulk feature to retrieve data from a table and populate the collection objects.

Without Bulk Collect

DECLARE
   TYPE namelist IS TABLE OF emp_info.ename%TYPE
      INDEX BY BINARY_INTEGER;
   TYPE sallist IS TABLE OF emp_info.sal%TYPE
      INDEX BY BINARY_INTEGER;
   CURSOR c1
   IS
      SELECT ename, sal
        FROM emp_info;

   names   namelist;
   sals    sallist;
   i       NUMBER   := 0;
BEGIN
   FOR rec_c1 IN c1
   LOOP
      names (i) := rec_c1.ename;
      sals (i) := rec_c1.sal;
      i := i + 1;
   END LOOP;
END;

The above is a very simple program that does not use bulk binds. There were
  146939 fetch calls against the cursor, requiring 146943 logical
reads and 1.57 CPU seconds. 

With Bulk Collect

DECLARE
   TYPE namelist IS TABLE OF emp.ename%TYPE;
   TYPE sallist IS TABLE OF emp.sal%TYPE;
   CURSOR c1
   IS
      SELECT ename, sal
        FROM emp_info;

   names   namelist;
   sals    sallist;
BEGIN
   OPEN c1;
   FETCH c1
   BULK COLLECT INTO names, sals;
END;

The above is a very simple program which uses bulk binds.
There were only 1 against the cursor instead of 146939, bringing logical reads down from 146943 to 475 and CPU time from 1.57 to 0.51 seconds.
This shows the effect of BULK BINDS on Performance Improvement.



1 Responses to “Performance tuning using BULK COLLECT with example”

Jennifer Wiss said...
February 21, 2012 at 2:13 AM

Gloriously expert position. I merely bumbled upon your post and desired to articulate that I have really relished understanding your blog articles. Plagiarism detection


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.