How Bulk Binds in PL/SQL Boost Performance

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.

Source : http://www.dbspecialists.com/files/presentations/bulk_binds.html

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s