Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 14, 2007

Product Review: Oracle PL/SQL Tuning

By Steve Callan

I like to recommend good products or finds from time to time, and the subject of this recommendation is Tim Hall’s Oracle PL/SQL Tuning Expert Secrets for High Performance Programming (published by Rampant TechPress). Books on PL/SQL, like books on other topics related to Oracle, range from the just so-so to what can be called bible status. A few books rise to the top and become the de facto definitive reference, but in some topic areas, the definitive reference goes into such in-depth detail that the number of people who can fully appreciate what the author is writing about tends to drop off fairly quick.

Books in Rampant’s Oracle In-Focus Series are narrow in scope, relatively inexpensive, have code (and some of the books too) available online, and the customer service/shipping is very good. On the other hand, the proofreading can be better. The preface of this particular book deals with job scheduling, not PL/SQL tuning. That’s kind of sloppy, but let’s looks at what the book offers overall.

Why We Need Targeted Books

Take HTML as an example. The marketplace has a slew of books covering “how” to do HTML. Many of them do a decent job of covering the mechanics, but not very many get into standards and where those standards originate from, let alone what the competing standards are to begin with. Most “I just need enough to get by on the job” coders will use the basic table-row-cell presentation. More sophisticated users, on the other hand, are going to use validation tools, use cascading style sheets, understand the difference between content and presentation, and know what W3C stands for. For what it’s worth, W3C is the World Wide Web Consortium and its stated mission is to “lead the World Wide Web to its full potential by developing protocols and guidelines that ensure long-term growth for the Web.”

Coming back to PL/SQL, what semi-official organization exists to develop similar protocols and guidelines that ensure best practices for this language? Obviously, the user base for PL/SQL is going to be much smaller than the base for Web page development (i.e., billions of Web pages versus something I’m sure is quite a bit less than that for coded program units). Web development standards has its evangelist (safe to say that is Tim Berners-Lee) and Steven Feuerstein fills that role for PL/SQL. With respect to PL/SQL, a high end user is going to get more out of Feuerstein’s books than what a relative beginner will absorb. Conversely, a high-end user is going to be bored (but that doesn’t mean he won’t learn anything) by a dummies type of book.

Cursors are a good model to illustrate a stratification of a user’s level of sophistication. Most books start off with the basic open-fetch-close model, and then progress into a cursor for loop. After the for loop (most coders understand that and start using it in short order), the more advanced features involving collections, REF, bulk collect and forall constructs cause eyes to glaze over. Most authors, I’m guessing, are aware of this. There will be plenty of examples covering rudimentary usage (open-fetch-close), a “let’s see how much easier and better for loops are,” and then a quick mention/literary hand waving covering bulk collect and forall. This is not a criticism of that approach, but a realization that not many Oracle developers are going to need (or even know that they could benefit greatly from this) more advanced constructs.

Chapter 3 of Hall’s book is reason enough on its own to justify adding it to your collection, regardless of your experience level. Ever wanted to see clear examples of using arrays and bulk binds? The chapter includes examples of creating and populating bulk collections, using the limit clause, bulk collection of DML results, FORALL in PL/SQL, bulk insert, bulk update, bulk delete, and many other topics. The examples are instrumented (recording start and end times and reporting on them) and are easy to follow along as they are complete (not just the code snippet for a loop).

What I particularly like about the examples, and there are lots of them, is that they span versions. The old/more cumbersome way in 8i gets about the same amount of attention as the new/more streamlined way in 10g. Examples using more efficient constructs will help you get up to speed in this regard. As an analogy, string manipulation can still be done the old way (substr, instr, etc.), but you’re missing out on the benefits of using REGEXP (regular expression) functions.

Validating an Example

The code depot contains all of the code shown in the book. After extracting the file, take care to edit it so that high bit characters or odd spaces are removed before copying and pasting into SQL*Plus. In TextPad, turn on visible spaces, and you’ll see how the spacing isn’t the same as what you may have already typed into the document. Instead of evenly spaced dots, you may see two dots very close together, followed by a small space, pattern repeating. Secondly, at line 2005, edit the single quote mark before Deleted. The mark is like a “pretty” single quotation mark, not the same as the one shown on your keyboard.

Here is the bulk update for 8i example (page 120).

-- update_forall_8i.sql XE "update_forall_8i.sql"
 TYPE t_id_tab IS TABLE OF forall_test.id%TYPE;
 TYPE t_code_tab IS TABLE OF forall_test.code%TYPE;
 TYPE t_desc_tab IS TABLE OF forall_test.description%TYPE;

 l_id_tab t_id_tab := t_id_tab();
 l_code_tab t_code_tab := t_code_tab();
 l_desc_tab t_desc_tab := t_desc_tab();
 l_start NUMBER;
 l_size NUMBER := 10000;
 -- Populate collections.
 FOR i IN 1 .. l_size LOOP

   l_id_tab(l_id_tab.last) := i;
   l_code_tab(l_code_tab.last) := TO_CHAR(i);
   l_desc_tab(l_desc_tab.last) := 'Description: ' || TO_CHAR(i);

 -- Time regular updates.
 l_start := DBMS_UTILITY.get_time;

 FOR i IN l_id_tab.first .. l_id_tab.last LOOP
   UPDATE forall_test
   SET id = l_id_tab(i),
   code = l_code_tab(i),
   description = l_desc_tab(i)
   WHERE id = l_id_tab(i);

 DBMS_OUTPUT.put_line('Normal Updates : ' ||
 (DBMS_UTILITY.get_time - l_start));

 l_start := DBMS_UTILITY.get_time;

 -- Time bulk updates.
 FORALL i IN l_id_tab.first .. l_id_tab.last
   UPDATE forall_test
   SET id = l_id_tab(i),
   code = l_code_tab(i),
   description = l_desc_tab(i)
   WHERE id = l_id_tab(i);

 DBMS_OUTPUT.put_line('Bulk Updates : ' ||
 (DBMS_UTILITY.get_time - l_start));


Normal Updates : 56
Bulk Updates : 7

PL/SQL procedure successfully completed.

My test run was using Solaris 9, 64-bit Oracle, version It’s a small data set, but seeing this type of result (the order of magnitude difference, your time may vary) certainly has to get you excited about restructuring the plain old "declare a cursor, loop through each record one at a time” way of doing business. The other examples in this chapter are by far the best collection (no pun intended) in terms of completeness and just enough explanation to help push you over the edge of resistance to this programming approach and make your PL/SQL and data processing scream. The other chapters are also quite worthwhile, even if you are starting out in PL/SQL as a complete beginner.

In Closing

Being able to process data using efficient PL/SQL (and SQL) can be a discriminator during a job interview. If you state that you can program PL/SQL related objects (packages, procedures, function, and triggers), and can provide details about what you did and how much of a performance gain you were able to achieve, and everything else being equal, you will stand head and shoulders above your competition. If not asked directly about your skills (and there are interviews where nary a technical question is asked), find a way to bring it up. On the other hand, if you don’t have sufficient skills, it is in your best interest to avoid being pinned down on technical questions.

I recently interviewed someone for a senior database administrator position. A bullet item on his resume stated that he was “fully trained” in Oracle wait event analysis. Being “fully trained” sounds like he was fair game to ask about a couple of very common wait events (db file scattered and sequential reads). After gacking on defining those, I asked him what he meant by stating “fully qualified.” He went into confession mode and admitted having that bullet on his resume was a bit of a stretch. I told him I wholeheartedly agreed with him. The other bullets about him being able to program packages and procedures also included some stretching of the truth.

The point I’m making is this: if you know you need improvement in certain areas, and you are willing to help yourself get better in those areas, then find the resources (on your own or recommendations from others) to help make you smarter/better. With respect to PL/SQL, tuning included or not, Tim Hall’s book is one of those high return on investment purchases that will go a long way towards helping you achieve this goal. I believe that from now on, if an applicant states that he or she can program in PL/SQL, I’m going to ask about collections and bulk binding, and if not using them, then why not. The information is out there. It is time to start pushing yourself and others to take advantage of the power within PL/SQL, and this book will help you do just that.

» See All Articles by Columnist Steve Callan

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM