25.5 Overview of PL/SQL8 Enhancements

Oracle Corporation has improved the peformance of PL/SQL programs with the release of Oracle8 and PL/SQL8. Without delving into detailed descriptions of the internal changes made to the engine, it is worth noting which elements of the technology have improved. This knowledge may well affect your choice of implementation for algorithms and your willingness to employ some features which in the past have come with a noticeable penalty.

PL/SQL8 offers improved performance in the following areas:[2]

[2] This information has been provided by Oracle Corporation and has not been independently confirmed.

• The memory required for PL/SQL code in the SGA has been reduced by about 20-25%. This will be especially important for large packages. In addition, Oracle8 uses a paging algorithm for code segments and data segments, leading to less fragmentation in the SGA.

• Large VARCHAR2 and RAW variables are dynamically allocated and resized as needed. For example, you might declare a package variable to be length 32,000 to handle all possible scenarios, but in most cases that variable only holds 255 bytes of data. You will no longer pay the penalty of all that extra memory.

• Anonymous blocks with bind variables execute much more rapidly. This is of special importance when you are working with DBMS_SQL or executing PL/SQL functions from within SQL. Oracle claims that the overhead of calling PL/SQL functions in SQL now is "negligible." If true, this will have significant implications for the deployment of PL/SQL code throughout SQL implementations.

• The use of temporary data structures has been minimized, resulting in performance improvements for such operations as concatenations and implicit conversions.

• PL/SQL records are now supported more efficiently for runtime operations. Prior to Oracle8, the compiler exploded records into individual scalar fields. Now, as you would expect with support for objects, PL/SQL provides native support for the RECORD composite type.

• The performance of index-by tables (formerly known as PL/SQL tables) has improved significantly due to a change in implementation from B*trees to a paged-array representation.