Skip to content

PL/SQL using Bulk Binds – some examples – for the new oracle dba




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.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.