Track 2: Oracle PL/SQL—the View from Redwood Shores
Presenter: Bryn Llewellyn
As PL/SQL Product Manager at Oracle's Headquarters, I strive to keep my understanding of PL/SQL untainted by knowledge of the details of its implementation. Occasionally, though, knowing what's going on under the covers can help. Sometimes, superstitions arise about the performance of PL/SQL idioms, and the most persuasive way to dispel these is to interpret the results of a timing experiment using knowledge of the internals. In preparing my first talk, I relied very heavily on such expert interpretation by my colleagues in the PL/SQL Development Team at Redwood Shores.
Session 1. PL/SQL Performance - Debunking the Myths
Everyone who writes PL/SQL programs cares about their performance. But life is too short to experiment with performance. So you write what you believe is fastest. However, some of your beliefs may be myths. Moreover, Oracle Database 10g introduces a brand new optimizing PL/SQL compiler, and so other beliefs—once true—are no longer true in this environment.
If a select statement returns exactly one row, which idiom is quicker? This one:
cursor c is select last_name from employees
where employee_id = 150;
...
open c; fetch c into v; close c;
Or this one:
select last_name into v from employees
where employee_id = 150;
Maybe you have a mental model to support your belief. Is it true? Was it once true? Or is it a myth?
How about this loop:
for j in 1..employees.Count() loop
employees(j).net_pay := employees(j).gross_pay*(1+federal_tax+state_tax);
end loop;
Is this version faster:
f := 1+federal_tax+state_tax;
for j in 1..employees.Count() loop
employees(j).net_pay := employees(j).gross_pay*f;
end loop;
How did you make your choice—are the reasons still good for Oracle Database 10g?
This session takes the Oracle Database 10g viewpoint and examines historical notions about PL/SQL performance. Some are myths—and are debunked. Others were once true but aren't true now. You will learn what the PL/SQL optimizing compiler can—and cannot—do for you. And you'll get a sense of our vision for what it will do for you in future versions.
With a better understanding of how to write fast PL/SQL, what you should care about, and what you needn't worry about, you'll find not only that your programs are faster but also that they are clearer and therefore more likely to be correct.
Session 2. PL/SQL Conditional Compilation: Use Cases and Best Practices
Oracle Database 10g Release 2 introduces support for a powerful new PL/SQL language feature: conditional compilation. The feature is elegant and easy to understand and has many interesting uses. Some of these may not have occurred to you. This session will illustrate a set of use cases with code samples and by doing so will demonstrate every conditional compilation construct. It will recommend best practices by discussing alternative implementation approaches to the use cases.
The session will also briefly review the other new PL/SQL features brought by Oracle Database 10g Release 2: relaxation of line length and total output volume in the Dbms_Output package; the Dbms_Ddl.Create_Wrapped() procedure to allow PL/SQL programs created by a PL/SQL program to be wrapped; the Utl_Nla package exposing an implementation of the popular BLAS and LAPACK libraries for matrix math.