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”
SET SERVEROUTPUT ON
DECLARE
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;
BEGIN
— Populate collections.
FOR i IN 1 .. l_size LOOP
l_id_tab.extend;
l_code_tab.extend;
l_desc_tab.extend;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);
END LOOP;— 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);
END LOOP;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));COMMIT;
END;
/Normal Updates : 56
Bulk Updates : 7PL/SQL procedure successfully completed.
My test run was using
Solaris 9, 64-bit Oracle, version 8.1.7.4. 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.
