What’s New in PL/SQL in Oracle Database 10g?

As is the case with every new release, Oracle Database 10g introduces some new PL/SQL language features and some new supplied PL/SQL packages. These are listed briefly at the end of this page.

However, the big news for PL/SQL in this release is the dramatic increase in runtime performance from transparent changes. Oracle Database 10g brings a new PL/SQL compiler and a newly tuned PL/SQL execution environment. Additionally, the system for the native compilation of PL/SQL has been substantially improved. As a result, users can expect that

• Computationally intensive PL/SQL programs compiled under Oracle Database 10g will run, on average, twice as fast as they did under Oracle9i Database Release 2.
   
• They will run three times as fast as they did under Oracle8 Database.

These factors are so large that they might seem — literally — incredible. Lest you doubt them, we provide extensive corroborative collateral:

PL/SQL Just Got Faster explains the workings of the PL/SQL compiler and runtime system and shows how major improvements on this scale are indeed possible.

PL/SQL Performance Measurement Harness describes a performance experiment whose conclusion is the large factors quoted above. We’ve provided a downloadable kit to enable you to repeat the experiment yourself.

Freedom, Order, and PL/SQL Optimization , intended for professional PL/SQL programmers, explores the use and behavior of the new compiler.

PL/SQL Performance — Debunking the Myths , again intended for readers who work at the PL/SQL codeface, re-examines some old notions about PL/SQL performance.

PL/SQL Just Got Faster

This whitepaper is derived from the OracleWorld 2003 presentation with the same title by Oracle Corporation’s PL/SQL Team. It describes the radical changes that were made to the PL/SQL compilation and runtime systems moving from Oracle9i Database Release 2 to Oracle Database 10g. It also explains how some of the improvements to the runtime system from this long term project have been “leaked” into earlier Oracle versions starting with Oracle8i Database.

Download the whitepaper

PL/SQL Performance Measurement Harness

This paper describes an experiment, using a large set of representative test programs, which measures the effect of the changes described in the PL/SQL Just Got Faster companion paper and presents the experimental results and their analysis.

Everything required to enable the reader to repeat the experiment and to verify the conclusions has been packaged for download from this site. The paper serves also as the instruction manual for the downloaded kit. It shows how you can easily include your own test programs in the experiment.

Download the whitepaper

Download the complete software kit (6040 Kb)

Freedom, Order, and PL/SQL Optimization

To achieve its speedups, the Oracle Database 10g PL/SQL compiler reorganizes the original source code somewhat (as all optimizing compilers do). The optimizations apply both to interpreted and to natively compiled PL/SQL programs. These rearrangements may cause “inessential” changes in the detailed behavior of a PL/SQL program.

This paper describes exactly what changes may be made and why; it is a careful explanation of parts of the PL/SQL definition which have been only lightly described previously. Those who design or program PL/SQL applications and who generally are familiar with the PL/SQL reference materials will benefit from reading this paper as will those who are responsible for porting existing applications from earlier Oracle Database releases. The material is technical, but every effort is made to keep the presentation clear enough so that all those interested in PL/SQL may learn from it.

Download the whitepaper

PL/SQL Performance — Debunking the Myths

The PL/SQL improvements in Oracle Database 10g change the way programmers should think about traditional hand optimizations. The paper examines some old notions about PL/SQL performance and shows that some no longer hold while others do continue to be sound. Further notions come under scrutiny which have never been sound but which mysteriously have been popularly held.

The paper describes an experimental investigation whose results speak for themselves. However, the interpretation does take advantage of the special understanding of the internals of PL/SQL brought by the team at Oracle Headquarters that implements the language. The results and their expert interpretation show that, in Oracle Database 10g, the PL/SQL programmer can now concentrate on clarity and correctness and — more than ever before — delegate the job of generating efficient runtime code to the compiler and the execution environment.

Download the whitepaper

Download the presentation slides

Summary of New PL/SQL Language Features

Oracle Database 10g introduces support for these new language features:

• the binary_float and binary_double datatypes (the IEEE datatypes).
• the regexp_like, regexp_instr, regexp_substr and regexp_replace builtins to support regular expression manipulation with standard POSIX syntax.
• multiset operations on nested table instances supporting operations like equals, union, intersect, except, member, and so on.
• the user-defined quote character.
• indices of and values of syntax for forall.
• the distinction between binary_integer and pls_integer vanishes.

All these features except the user-defined quote character — a convenience feature for the programmer — are performance features and, of course, they all have efficient implementations. For example, the IEEE datatypes enjoy the benefit of machine arithmetic for mathematical real numbers. An appropriate algorithmic task that is expressed in PL/SQL using any of these new features will run very much faster than one that avoided them. The PL/SQL Just Got Faster whitepaper reports improvement factors ranging between four and thirteen for test programs which depend heavily on number arithmetic and which were reimplemented using the new binary_double datatype.

In the list of new language features, all but the forall enhancement and the change concerning PL/SQL integers are, formally speaking, new features in SQL. PL/SQL has an obligation to support all such SQL features in a PL/SQL context (for example, in PL/SQL assignment statements). This is a unique strength of PL/SQL and is closely related to the fact that it shares the same datatype system as SQL. The PL/SQL Development Team works to enable users to be able to use SQL and PL/SQL seamlessly together.

The new PL/SQL compiler also introduces support for compiler warnings. There are various categories of warning ( severe, performance, and informational). Each category and each individual warning can be independently enabled, disabled, or treated as a compilation error.

Summary of New PL/SQL Supplied Packages

PL/SQL packages are used to extend the functionality of the Oracle Database when SQL cannot be extended for the purpose. As such, they implement a vast range of functionality. As is normal when discussing new PL/SQL features, we restrict ourselves in this section to just those packages which can be considered to augment the PL/SQL language itself.

• Utl_Mail. This new package makes it possible for a PL/SQL programmer to send programmatically composed emails. It requires only the normal mental model of a user of a GUI email client rather than an understanding of the underlying protocol (SMTP) features. This distinguishes it from Utl_Smtp which was introduced in Oracle8i Database. Utl_Smtp requires that the programmer understands the details of the SMTP protocol. Utl_Mail is much simpler to use because it supports just a limited, but very common, subset of the functionality that Utl_Smtp provides.
   
• Utl_Compress. This new package delivers the familiar functionality of the zip and unzip utilities in a PL/SQL environment. It lets you compress and uncompress a raw or blob bytestream and guarantees return of original bytestream after a round trip.
   
• Dbms_Warning. This allows the PL/SQL programmer fine grained control over which categories of warning and which individual warnings to disable, to enable, or to treat as errors. Its expected use is at the start and end of installation scripts so that each script may run in its intended regime without affecting the regime of subsequent scripts.