• Ask DB Specialists: Bulk Binding in PL/SQL
• IOUG Live! Conference, April 18-22
• Oracle-L: The List for "All Things Oracle"
• More for Your Oracle Library

You're invited to submit your Oracle-related questions to us at askdbspecialists03@dbspecialists.com. Include your name, telephone number and problem description. If your question is answered in The Specialist next month, you will receive a free copy of our Oracle Wait Events reference poster. (US shipping addresses only).

This month’s question comes from Robert D. in San Jose, California: Is bulk binding in PL/SQL worth the bother? How can I measure the benefit?

Roger Schrag of the Database Specialists team responds: The bulk bind feature 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.

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.

Please see my brief paper at http://www.dbspecialists.com/presentations.html#bulkbind for an example of a program written both with and without bulk binds. In the paper I provide TKPROF reports to 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.

IOUG Live! Conference, April 18-22

It’s time again for the annual conference of the International Oracle Users Group: IOUG Live! 2004. This year’s conference is taking place April 18-22 in Toronto, Canada. The conference is organized by Oracle users just like you, and many speakers are experienced Oracle DBAs and developers who are sharing their real-life experiences. The conference will offer attendees an in-depth look at Oracle's 10g products, with numerous 10g technical sessions, a keynote by Ken “Dr. DBA” Jacobs, migration debates, and more. Focus areas of this year’s conference are divided among the following tracks:

• DBA
• Architecture & Infrastructure
• Data Warehousing & Business Intelligence
• eBusiness & Application Development
• ERP, CRM, SFA and SCM
• Career Development