below are some examples on how how you can use PL/SQL to bulk fetch rows from the database and store in pl/sql collections.
I have also given a nice article that i liked which gives a nice example of the benefits of Bulk Binds.
As a new oracle dba you will understand the performance benefit of bulk fetching data from the database.
DECLARE
TYPE t IS TABLE OF DATE NOT NULL
INDEX BY PLS_INTEGER;
r t;
BEGIN
r ( 1 ) := NULL;
END;
In Example 6–13, you bulk-fetch from a cursor into two collections.
Example 6–13 Fetching Bulk Data With a Cursor
DECLARE
TYPE IdsTab IS TABLE OF employees.employee_id%TYPE;
TYPE NameTab IS TABLE OF employees.last_name%TYPE;
ids IdsTab;
names NameTab;
CURSOR c1 IS
SELECT employee_id, last_name FROM employees WHERE job_id = ‘ST_CLERK’;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO ids, names;
CLOsE c1;
— Here is where you process the elements in the collections
FOR i IN ids.FIRST .. ids.LAST
LOOP
IF ids(i) > 140 THEN
DBMS_OUTPUT.PUT_LINE( ids(i) );
END IF;
END LOOP;
FOR i IN names.FIRST .. names.LAST
LOOP
IF names(i) LIKE ‘%Ma%’ THEN
DBMS_OUTPUT.PUT_LINE( names(i) );
END IF;
END LOOP;
Managing Cursors in PL/SQL
6-12 Oracle Database PL/SQL User’s Guide and Reference
END;
/
The collections are initialized automatically. Nested tables and associative arrays are
extended to hold as many elements as needed. If you use varrays, all the return values
must fit in the varray’s declared size. Elements are inserted starting at index 1,
overwriting any existing elements.
Because the processing of the BULK COLLECT INTO clause is similar to a FETCH loop,
it does not raise a NO_DATA_FOUND exception if no rows match the query. You must
check whether the resulting nested table or varray is null, or if the resulting associative
array has no elements, as shown in Example 11–10.
To prevent the resulting collections from expanding without limit, you can use the
LIMIT clause to or pseudocolumn ROWNUM to limit the number of rows processed. You
can also use the SAMPLE clause to retrieve a random sample of rows.
Example 11–12 Bulk-Fetching from a Cursor Into One or More Collections
DECLARE
TYPE NameList IS TABLE OF employees.last_name%TYPE;
TYPE SalList IS TABLE OF employees.salary%TYPE;
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE salary > 10000;
names NameList;
sals SalList;
TYPE RecList IS TABLE OF c1%ROWTYPE;
recs RecList;
v_limit PLS_INTEGER := 10;
PROCEDURE print_results IS
BEGIN
IF names IS NULL OR names.COUNT = 0 THEN — check if collections are empty
DBMS_OUTPUT.PUT_LINE(‘No results!’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Results: ‘);
FOR i IN names.FIRST .. names.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(‘ Employee ‘ || names(i) || ‘: $’ || sals(i));
END LOOP;
END IF;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘— Processing all results at once —‘);
OPEN c1;
FETCH c1 BULK COLLECT INTO names, sals;
CLOSE c1;
print_results();
DBMS_OUTPUT.PUT_LINE(‘— Processing ‘ || v_limit || ‘ rows at a time —‘);
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO names, sals LIMIT v_limit;
EXIT WHEN names.COUNT = 0;
print_results();
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE(‘— Fetching records rather than columns —‘);
OPEN c1;
FETCH c1 BULK COLLECT INTO recs;
FOR i IN recs.FIRST .. recs.LAST
LOOP
— Now all the columns from the result set come from a single record
DBMS_OUTPUT.PUT_LINE(‘ Employee ‘ || recs(i).last_name || ‘: $’
|| recs(i).salary);
END LOOP;
END;
/
Reducing Loop Overhead for DML Statements and Queries with Bulk SQL
11-18 Oracle Database PL/SQL User’s Guide and Reference
Example 11–13 shows how you can fetch from a cursor into a collection of records.
Example 11–13 Bulk-Fetching from a Cursor Into a Collection of Records
DECLARE
TYPE DeptRecTab IS TABLE OF departments%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT department_id, department_name, manager_id, location_id
FROM departments WHERE department_id > 70;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs;
END;
/
Limiting the Rows for a Bulk FETCH Operation with the LIMIT Clause
The optional LIMIT clause, allowed only in bulk FETCH statements, limits the number
of rows fetched from the database. In Example 11–14, with each iteration of the loop,
the FETCH statement fetches ten rows (or less) into index-by table empids. The
previous values are overwritten. Note the use of empids.COUNT to determine when
to exit the loop.
Example 11–14 Using LIMIT to Control the Number of Rows In a BULK COLLECT
DECLARE
TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
CURSOR c1 IS SELECT employee_id FROM employees WHERE department_id = 80;
empids numtab;
rows PLS_INTEGER := 10;
BEGIN
OPEN c1;
LOOP — the following statement fetches 10 rows or less in each iteration
FETCH c1 BULK COLLECT INTO empids LIMIT rows;
EXIT WHEN empids.COUNT = 0;
— EXIT WHEN c1%NOTFOUND; — incorrect, can omit some data
DBMS_OUTPUT.PUT_LINE(‘——- Results from Each Bulk Fetch ——–‘);
FOR i IN 1..empids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE( ‘Employee Id: ‘ || empids(i));
END LOOP;
END LOOP;
CLOSE c1;
END;
/
How Bulk Binds in PL/SQL Boost Performance
by Roger Schrag
Database Specialists, Inc.
About Database Specialists, Inc.
Database Specialists, Inc. provides remote DBA services and onsite database support for your mission critical Oracle systems. Since 1995, we have been providing Oracle database consulting in Solaris, HP-UX, Linux, AIX, and Windows environments. We are DBAs, speakers, educators, and authors. Our team is continually recognized by Oracle, at national conferences and by leading trade publications. Learn more about our remote DBA, database tuning, and consulting services. Or, call us at 415-344-0500 or 888-648-0500.
Introduction
A new feature called "bulk binds" was added to PL/SQL back in Oracle 8i. Bulk binds enable a PL/SQL program to fetch many rows from a cursor in one call instead of fetching one row at a time. Bulk binds also allow many similar DML statements to be executed with one call instead of requiring a separate call for each. For certain types of PL/SQL programs, using bulk binds will reduce CPU usage and make the code run faster.
A context switch occurs every time the PL/SQL engine calls the SQL engine to parse, execute, or fetch from a cursor. Since context switches use CPU time, reducing the number of context switches will reduce the amount of CPU time used. In addition, the SQL engine can often reduce the number of logical reads required when multiple rows are fetched in one call. Reducing logical reads also saves CPU time.
Deciding When to Use Bulk Binds
PL/SQL code that uses bulk binds will be slightly more complicated and somewhat more prone to programmer bugs than code without bulk binds, so you need to ask yourself if the improved runtime performance will justify the expense. No universal rule exists to dictate when bulk binds are worthwhile and when they are not. However, the cost of adding a few lines of code is so slight that I would lean toward using bulk binds when in doubt.
A PL/SQL program that reads a dozen rows from a cursor will probably see no noticeable benefit from bulk binds. The same goes for a program that issues five or six UPDATE statements. However, a program that reads 1,000 rows from a cursor or performs that many similar UPDATE statements will most likely benefit from bulk binds.
If you have the luxury of time, you can test your code both with and without bulk binds. Running both versions of the code through SQL trace and TKPROF will yield reports from which you may derive a wealth of information.
A Simple Program With and Without Bulk Binds
In this section we will look at a simple program written both with and without bulk binds. We’ll look at TKPROF reports that demonstrate the impact bulk binds can have. The discussion of the TKPROF reports will help you see how to interpret TKPROF output in order to assess the impact of bulk binds on your application.
Consider the following excerpts from a TKPROF report:
************************************************************************
DECLARE
CURSOR c_orders IS
SELECT order_id, currency_code, amount_local /* no bulk bind */
FROM open_orders;
v_amount_usd NUMBER;
BEGIN
FOR r IN c_orders LOOP
v_amount_usd := currency_convert (r.amount_local, r.currency_code);
UPDATE open_orders /* no bulk bind */
SET amount_usd = v_amount_usd
WHERE order_id = r.order_id;
END LOOP;
COMMIT;
END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- -------- -------- -------- --------
Parse 1 0.05 0.04 0 0 1 0
Execute 1 10.55 11.40 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- -------- -------- -------- --------
total 2 10.60 11.45 0 0 1 1
************************************************************************
SELECT order_id, currency_code, amount_local /* no bulk bind */
FROM open_orders
call count cpu elapsed disk query current rows
------- ------ -------- ---------- -------- -------- -------- --------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 30287 1.08 1.10 0 30393 0 30286
------- ------ -------- ---------- -------- -------- -------- --------
total 30289 1.08 1.10 0 30393 0 30286
************************************************************************
UPDATE open_orders /* no bulk bind */
SET amount_usd = :b2
WHERE order_id = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- -------- -------- -------- --------
Parse 1 0.00 0.00 0 0 0 0
Execute 30286 7.19 7.32 1 60576 31022 30286
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- -------- -------- -------- --------
total 30287 7.19 7.33 1 60576 31022 30286
As you can see, this is a very simple program that does not use bulk binds. (The code borders on being silly; please recognize it is for illustrative purposes only.) The PL/SQL engine used 10.55 CPU seconds to run this code (this figure does not include CPU time used by the SQL engine). There were 30,287 fetch calls against the cursor, requiring 30,393 logical reads and 1.08 CPU seconds. The UPDATE statement was executed 30,286 times, using 7.19 CPU seconds.
Now consider the following excerpts from another TKPROF report:
************************************************************************
DECLARE
CURSOR c_orders IS
SELECT order_id, currency_code, amount_local /* bulk bind */
FROM open_orders;
TYPE t_num_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE t_char_array IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
v_order_ids t_num_array;
v_currency_codes t_char_array;
v_amounts_local t_num_array;
v_amounts_usd t_num_array;
v_row_count NUMBER := 0;
BEGIN
OPEN c_orders;
LOOP
FETCH c_orders
BULK COLLECT INTO v_order_ids, v_currency_codes, v_amounts_local
LIMIT 100;
EXIT WHEN v_row_count = c_orders%ROWCOUNT;
v_row_count := c_orders%ROWCOUNT;
FOR i IN 1..v_order_ids.count LOOP
v_amounts_usd(i) := currency_convert (v_amounts_local(i),
v_currency_codes(i));
END LOOP;
FORALL i IN 1..v_order_ids.count
UPDATE open_orders /* bulk bind */
SET amount_usd = v_amounts_usd(i)
WHERE order_id = v_order_ids(i);
END LOOP;
CLOSE c_orders;
COMMIT;
END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- -------- -------- -------- --------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.60 0.62 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- -------- -------- -------- --------
total 2 0.63 0.66 0 0 0 1
************************************************************************
SELECT order_id, currency_code, amount_local /* bulk bind */
FROM open_orders
call count cpu elapsed disk query current rows
------- ------ -------- ---------- -------- -------- -------- --------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 303 0.48 0.59 0 4815 0 30286
------- ------ -------- ---------- -------- -------- -------- --------
total 305 0.48 0.59 0 4815 0 30286
************************************************************************
UPDATE open_orders /* bulk bind */
SET amount_usd = :b1
WHERE order_id = :b2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- -------- -------- -------- --------
Parse 1 0.00 0.00 0 0 0 0
Execute 303 3.75 8.38 0 30895 31021 30286
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- -------- -------- -------- --------
total 304 3.75 8.38 0 30895 31021 30286
This code uses bulk binds to do the same thing as the first code sample, but works with data 100 rows at a time instead of one row at a time. We can see that CPU time used by the PL/SQL engine has reduced from 10.55 seconds to 0.60 seconds. There were only 303 fetch calls against the cursor instead of 30,287, bringing logical reads down from 30,393 to 4,815 and CPU time down from 1.08 seconds to 0.48 seconds. The UPDATE statement was executed only 303 times instead of 30,286, reducing CPU time from 7.19 seconds to 3.75 seconds.
In this example it would appear that bulk binds were definitely worthwhile – CPU time was reduced by about 75%, elapsed time by 50%, and logical reads by 50%. Although bulk binds are indeed beneficial here, the benefit is not truly as rosy as it appears in these TKPROF reports. The SQL trace facility imparts an overhead that is proportional to the number of parse, execute, and fetch calls to the SQL engine. Since bulk binds reduce the number of SQL calls, SQL trace adds much less overhead to code that uses bulk binds. While these TKPROF reports suggest that in this example bulk binds shaved about 50% off of the elapsed time, the savings were about 25% when SQL trace was not enabled. This is still a significant savings. Thus using bulk binds in your PL/SQL programs can certainly be worth the effort. Just remember that SQL trace can inflate the perceived benefit.
Conclusion
Bulk binds allow PL/SQL programs to interact more efficiently with the SQL engine built into Oracle, enabling your PL/SQL programs to use less CPU time and run faster. The Oracle Call Interface has supported array processing for 15 years or more, and the increased efficiency it brings is well understood. It is nice to see this benefit available to PL/SQL programmers as well. PL/SQL bulk binds are not hard to implement, and can offer significant performance improvements for certain types of programs.
