OPP/News for December 2005

Dear Oracle technologists,

As I get older, the years seem to get shorter. I guess that makes sense, since a year makes up an increasingly smaller precentage of my life. Ah well....this will be my last PL/SQL newsletter for 2005. We've already some bitterly cold days here in Chicago. I plan to be spending lots of time indoors over the next several months, playing around with Oracle Database 10g Release 2 new features, writing articles, and working on new editions of my books.

Beyond all that, I will in the next month or so announce availability of Qut, a new tool to help you design, generate and run unit test packages for your PL/SQL program units. I am very excited about its potential to make comprehensive, flexible unit testing a practical reality. More on Qut to come...

Upcoming training opportunities

The next major opportunity for PL/SQL developers to receive training, from myself and others, is through the Desktop Conference 2006 virtual conference organized by ODTUG. I encourage you to sign up and take advantage of attending this conference from the comfort of your own cubicle.

My presentation at Desktop Conference 2006 is titled "SQL Games We Can Play in PL/SQL" and comes with this abstract:

"No doubt about it: PL/SQL is the best place to write SQL when you need to access the Oracle database. No need for JDBC, ODBC or any of that other nonsense! Just write your SQL and off you go...but wait, it gets even better! Over the past several versions, Oracle has added SQL-centric functionality to PL/SQL to make it easier to write and faster to run SQL from within PL/SQL programs. This presentation will make sure you know about all the things you can do to take maximum advantage of SQL inside PL/SQL, from BULK COLLECT and FORALL to table functions, with a visit into the world of dynamic SQL."

Tip of the Month: Insights into PL/SQL Integers

When it comes to declaring and manipulating integers, Oracle offers lots of options, including:

INTEGER - defined in the STANDARD package as a subtype of NUMBER, this datatype is implemented in a completely platform-independent fashion, which means that anything you do with NUMBER or INTEGER variables should work the same regardless of the hardware on which the database is installed.

BINARY_INTEGER - defined in the STANDARD package as a subtype of INTEGER, variables declared as BINARY_INTEGER can be assigned values between -2**31 .. 2**31, aka -2,147,483,647 to 2,147,483,647. Prior to Oracle9i Database Release 2, BINARY_INTEGER was the only indexing datatype allowed for associative arrays (aka, index-by tables), as in: 

TYPE my_array_t IS TABLE OF VARCHAR2(100) 
 INDEX BY BINARY_INTEGER

PLS_INTEGER - defined in the STANDARD package as a subtype of BINARY_INTEGER, variables declared as PLS_INTEGER can be assigned values between -2**31 .. 2**31, aka -2,147,483,647 to 2,147,483,647. PLS_INTEGER operations use machine arithmetic, so they are generally faster than NUMBER and INTEGER operations. Also, prior to Oracle Database 10g, they are faster than BINARY_INTEGER. In Oracle Database 10g, however, BINARY_INTEGER and PLS_INTEGER are now identical and can be used interchangeably.

POSITIVE - defined in the STANDARD package as a subtype of BINARY_INTEGER, variables declared as POSITIVE can be assigned values between 1 and 2,147,483,647. Whenever you assign a value to a variable declared as POSITIVE, Oracle will check to see if the value falls within this range. If not, it will raise the VALUE_ERROR exception.

NATURAL - defined in the STANDARD package as a subtype of BINARY_INTEGER, variables declared as POSITIVE can be assigned values between 0 and 2,147,483,647. Whenever you assign a value to a variable declared as NATURAL, Oracle will check to see if the value falls within this range. If not, it will raise the VALUE_ERROR exception.

SIGNTYPE - defined in the STANDARD package as a subtype of BINARY_INTEGER, variables declared as SIGNTYPE can be assigned values between -1 and 1. Whenever you assign a value to a variable declared as SIGNTYPE, Oracle will check to see if the value falls within this range. If not, it will raise the VALUE_ERROR exception.

The question that immediately comes to my mind is: how much of a difference in performance does the choice of datatype make in my program? I put together a script to analyze precisely that: the integer_compare script set. It comes in two flavors: integer_compare.sql, which can used in Oracle Database 10g (relies on DBMS_UTILITY.GET_CPU_TIME to compute elapsed time) and integer_compare_pre_10g.sql, which can used in versions earlier than Oracle Database 10g (relies on DBMS_UTILITY.GET_TIME to compute elapsed time).

Here is a subset of the integer_compare.sql script that I will use to explain the approach I have taken for each of the datatypes I tested:

 1 CREATE OR REPLACE PROCEDURE test_integer_performance
 2 IS
 3    c_iterations PLS_INTEGER := 10000000;
 4    --
 5    l_pls_integer PLS_INTEGER := 0;
 6    c_pls_integer_15 CONSTANT PLS_INTEGER := 15;
 7    --
 8    l_start_time PLS_INTEGER;
 9
10    PROCEDURE show_elapsed_time ( what IN VARCHAR2, t IN NUMBER )
11    IS
12    BEGIN
13      DBMS_OUTPUT.put_line ( ' '
14        || what
15        || ': '
16        || LTRIM ( TO_CHAR ( t / 100, '999.99' ))
17        || ' seconds'
18        );
19    END;
20 BEGIN
21    l_start_time := DBMS_UTILITY.get_cpu_time;
22
23    FOR indx IN 1 .. c_iterations
24    LOOP
25       l_pls_integer := l_pls_integer + c_pls_integer_15;
26    END LOOP;
27
28    show_elapsed_time ( 'PLS_INTEGER'
29       , DBMS_UTILITY.get_cpu_time - l_start_time );
30* END test_integer_performance;

Here is an explanation of this code:

Lines 5-6: Declare a local variable of the type I want to test, along with a constant of the same type to be used in the computation section.

Lines 10-19: Define a local subprogram to consolidate display logic.

Line 21: Obtain the starting time of the test with the DBMS_UTILITY built-in. If you are running a version prior to 10g, you will need to use DBMS_UTILITY.get_time instead of get_cpu_time.

Lines 23-26: Execute a loop that both reads and writes the variable of the type to be tested. Notice that I use only variables of the same datatype to avoid implicit conversions.

Lines 28-29: Call DBMS_UTILITY.get_cpu_time again and subtrace the start time to obtain the elapsed time for this code to run. Pass it to the display program.

I then do the same thing for each of my various datatypes and scenarios, which are:

NUMBER - I used the base NUMBER datatype.

INTEGER - I used the platform-independent INTEGER datatype, based on NUMBER.

BINARY_INTEGER, PLS_INTEGER and POSITIVE - Variations of the restricted range integer datatype. Note that when I declare a variable of type POSITIVE, Oracle will make sure that any value assigned to that variable is not less than 1. 

MY POSITIVE - Rather than have Oracle enforce the positive constraint, I do it myself with this code inside the loop:

   FOR indx IN 1 .. c_iterations
   LOOP
      IF l_my_positive < 1
      THEN
         RAISE VALUE_ERROR;
      END IF;

      l_my_positive := l_my_positive + c_my_positive_15;
   END LOOP;

MY SUBTYPE OVER PLS_INTEGER - I declared a SUBTYPE on top of PLS_INTEGER to see if that "indirect" datatype added any overhead. This is the code used to define that subtype:

   SUBTYPE my_pls_integer IS PLS_INTEGER;
   l_my_pls_integer my_pls_integer := 1;
   c_my_pls_integer_15 CONSTANT my_pls_integer := 15;

MY PKG SUBTYPE OVER PLS_INTEGER - I declared a SUBTYPE on top of PLS_INTEGER inside a package to see if that "indirect" datatype added any overhead. This is the code used to define that subtype:

CREATE OR REPLACE PACKAGE my_package
IS
   SUBTYPE my_pkg_pls_integer IS PLS_INTEGER;
END my_package;
/

Here are typical results, when I run the script on Oracle Database 10g Releases 1 or 2 database on my IBM Thinkpad T42 (1.8 Ghz, 1.5 gig RAM, Windows XP SP 2):

NUMBER: 1.34 seconds
INTEGER: 2.25 seconds
BINARY_INTEGER: .55 seconds
PLS_INTEGER: .55 seconds
POSITIVE: 1.06 seconds
MY POSITIVE: 1.03 seconds
MY SUBTYPE OVER PLS_INTEGER: .56 seconds
MY PKG SUBTYPE OVER PLS_INTEGER: .55 seconds

From these results, I conclude: BINARY_INTEGER and PLS_INTEGER are indeed interchangeable; we don't pay any price in overhead to use subtypes, whether declared locally or in an "external" package; there is a noticeable impact of enforcing the positive constraint; it definitely pays off to use PLS_INTEGER rather than plain, old INTEGER. 

The integer_compare.sql file contains a comment block with the results from running this script in 8i and 9i as well as 10g. The results when running this script on earlier versions of Oracle showed very clearly the following:

1. Regarding POSITIVE: I found that in 8i and 9i the code ran at least twice as fast when I declared my datatype to be PLS_INTEGER and enforced the positive constraint myself, rather than rely on Oracle to do it via a declaration of a POSITIVE variable.

2. On 8i and 9i, PLS_INTEGER was noticeably faster than BINARY_INTEGER.

I also ran some tests to see if it made any difference as to whether I used INDEX BY BINARY_INTEGER or INDEX BY PLS_INTEGER in my associative array type declarations (integer_compare_index_by.sql). I found that there was no difference. In other words, even when you use the INDEX BY BINARY_INTEGER declaration, it looks like Oracle actually implements the associative array using PLS_INTEGER arithmetic.

Bottom lines:

1. Upgrade to Oracle Database 10g Release 2 as soon as possible! As you will see by comparing the performance results in the integer_compare.sql script, the example code runs much faster on 10g than on any other version. And beyond the performance gains, you won't have to worry so much about which variant of BINARY_INTEGER you use. 

2. In general, though, use PLS_INTEGER as much as possible to achieve the best performance and highest code consistency.

Copyright © 2003-2005 PL/Solutions. All Rights Reserved.