Database News You Can Use
A monthly newsletter for Oracle usersMarch 2004
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500
Ask DB Specialists: Bulk Binding in PL/SQL You’re invited to submit your Oracle-related questions to us at email@example.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). IOUG Live! Conference, April 18-22
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://dbspecialists.wpengine.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.
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: Oracle-L: The List for “All Things Oracle”
Register by Friday, March 19, for earlybird rates.
The Oracle-L list server is one of the oldest Oracle e-mail lists designed for discussing “all things Oracle.” Some of the best-known Oracle professionals in the world post to Oracle-L, so it’s a great place to learn from the gurus and get your questions answered in an open discussion format. Though it tends to be DBA-centric at times, all Oracle questions are welcome. Following are some recent sample topics: More for Your Oracle Library
If you’re looking to add to your Oracle resource library, you may want to browse through a site from a UK-based company called Think Forward. They’ve got plenty of scripts available for your review, along with resources organized by Oracle version. Here’s a quick sampling of the lists in their scripts section:
You’ll find these and more at http://www.think-forward.com/sql_scripts.htm.