February 27, 2021
Hot Topics:

Sample Chapter: Efficient PL/SQL

  • By Beck, Kallman, Katz, Knox, and McDonald
  • Send Email »
  • More Articles »


Here is where factors such as CPU, memory, and concurrency become a consideration. A PL/SQL program that runs at breakneck speed (or simply just satisfies the performance requirement) is not of much use if it disrupts another required system function in some way. A PL/SQL program could violate this requirement in a number of ways. For example:

  • It could lock all the rows in a table while it runs, thus halting end-users from doing their day-to-day tasks.
  • It could consume too many system resources (CPU, memory, and so on), thus causing response time problems in unrelated parts of the system, or crashing the system entirely.
  • It might fail to meet its concurrency requirements. If 100 users are expected to use this PL/SQL module concurrently, but it dies a slow death as soon as 10 users try to execute it at once, then as fast as it is, it is useless.


To use an Australian colloquialism, I am a bit of a "rev head." In a nutshell, this means I like driving my car fast, although four years of living in central London nearly broke me of the habit. The optimal time for driving fast tends to be about four o'clock in the morning, when there is no one else on the road. All I need is one long empty lane on the local freeway and I'm in heaven. Of course, does this mean that having a one-lane freeway is a guarantee of being able to drive fast? Definitely not. In fact, if I drive on the same freeway between 7am and 9am during the working week, it would be quicker if I got out of the car and walked!

It is common sense that what you can do when you are the only car on the road is a far cry from what you can do when there are a lot of cars on the road. But amazingly, we tend to put that same common sense on the shelf when it comes to developing (and more importantly, testing) applications. Many systems have failed under the assumption that if a program runs fine in isolation, it will run fine on a live system.

This is the key aspect of demonstrability. It is easy to wave off discussions about efficiency as just plain common sense: ("Of course I know that you want my code to run fast and not cause problems. I'm not stupid."). But there is more to efficiency than that. Developer testing strategies tend to fall into one of three categories.

  • You hope the program will satisfy its performance and impact requirements.
  • You know the program satisfies its performance and impact requirements but only under the conditions you've tested. (This is the most common testing strategy used by most developers.)
  • You have busted your gut trying to come up with ways to break the thing and you can't!

The last of these is a coarse way of describing the true concept of demon-strability—having a definitive set of tests that prove both the functionality and efficiency of the program under all reasonable conditions. Tests that prove that the code will scale to the required number of users, tests that prove that performance goals are met under all conditions (during the rush hour, not just at 4AM on the open highway).

Consider the following (true) example taken from a site that was experiencing problems with one of its online systems. We will see shortly that the efficiency problems were actually caused by a design flaw, but this was only revealed once we attempted to demonstrate that the code met all its efficiency requirements.

One of the requirements for the online system was that all new customers needed to be able to track their own personal details once they had registered over the Internet. The initial specification was that customers were given a unique numeric ID and a pin number (effectively a numeric password) that would allow them to authenticate with the system. However, the requirement changed on the premise that two numeric IDs would be too much to remember Efficient PL/SQL.for any customer, so the customer pin number would actually be the unique ID for that customer. Therefore, we wanted a table that would uniquely record customers by using a random numeric number as the primary key.

The resulting solution was that a random number generator would produce a random ID, and then the customers table would be probed to see if that ID already existed. If the customer did exist, another random number would be generated until a unique customer record was inserted. Let's look at a possible solution for the stated requirements. First we have the table that stores the unique ID for each customer.

SQL> create table customers (
  2    pin_number number(6))

Table created.
SQL> alter table customers
  2    add constraint customers_pk
  3    primary key (pin_number);

Table altered.

The customers table contains a primary key, pin_number, that will hold the unique customer identifier. Next, we create a procedure that obtains a random number, generated by the built-in dbms_random package, and inserts it into the customers table.

SQL> create or replace
  2  procedure gen_customer is
  3    v_new_cid customers.pin_number%type;
  4  begin
  5    loop
  6       begin
  7         v_new_cid :=round(dbms_random.value(1000000,9999999));

The variable, v_new_cid, contains the random number to be assigned for this new customer record. We then attempt to insert this into the customers table.

  8         insert into customers
  9         values (v_new_cid);
 10         exit;

Of course, if that random number is already in use, a constraint violation will occur and the dup_val_on_index exception will be raised. We simply catch this and loop around to try another random number.

 11         exception when dup_val_on_index then
 12           null;
 13         end;
 14    end loop;
 15  end;
 16 /

Procedure created.

This solution looks fairly simple. Let's put it through its paces and add a customer. We'll use the standard set timing on facility in SQL*Plus to record some executions times.

SQL> set timing on
SQL> begin
  2    gen_customer;
  3    commit;
  4  end;
  5  /

PL/SQL procedure successfully completed.


No problems there; a customer record added in virtually no time at all. Let's scale up the test and simulate the creation of 100,000 customers.

SQL> set timing on
SQL> begin
  2    for i in 1 ..100000 loop
  3      gen_customer;
  4    end loop;
  5    commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.


Page 4 of 19

This article was originally published on February 5, 2004

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date