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.
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “Performance tuning using BULK COLLECT with example”
Post a Comment