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
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.
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.
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 complete software kit (6040 Kb)
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.
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.
Summary of New PL/SQL Language Features
Oracle Database 10g introduces support for these new language features:
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.
Click here to see archived information about Oracle9i and Oracle8i PL/SQL