Oracle Performance and Engineering

If you're already on Oracle 10g you must have noticed the very *significant* baseline performance gain your application has (if it's PL/SQL intensive) after the upgrade. If you're closely monitoring the database, you may also have found that PLSQL_OPTIMIZE_LEVEL init parameter's default value is now 2 (was 0 pre 10g). This means PL/SQL compiler now works hard to tune your PL/SQL program for you.

They say and we've verified -

"Oracle Database 10g provides a new optimizing PL/SQL compiler which speeds up computationally intensive programs ported from older versions by two to three times on average."

An example, so far --

A = B + C + D ;

or

X or Y or Z

or

if
SQL that's slow
but executes LESS often;
else
SQL that's fast
but executes more because earlier part is evaluated false most of the time;
end if;


(or, any associative expression for that matter) was always evaluated from left to right.

No more - if you've upgraded to 10g *and* have not tweaked with PLSQL_OPTIMIZE_LEVEL (let it remain default). This is the best feature of Oracle10g according to my needs and tests.

A classic paper by Charles Wetherell discusses this in more technical detail.

Some other examples from the paper -

B = A * NULL - should NEVER be evaluated. Since it's always true.

The operations

c := a + b + e;
…
d := f + a + b;

can be 'optimized' as -

T := a + b;
c := T + e;

So, you are doing LESS calculation since (a + b) is evaluated ONCE!

or, the expressions

… A + B …
…
… A + B …

can be changed to

T := A + B;

… T …
…
… T …

or,

for i in 1 .. 10 loop
A := B + C;
…
end loop;

can be changed to

A := B + C;
for i in 1 .. 10 loop
…
end loop;

"To achieve these improvements, the compiler must be free to reorder and to eliminate computations so long as the computational intention of the original program is preserved."

- That's where the PLSQL_OPTIMIZE_LEVEL comes handy to Oracle!

I got from there that "for" loop has become much faster since the iterative incrementing of the loop indicator has been "rewritten". In my tests, simple FOR loops are indeed over 400% faster between comparable 9i and 10g versions.

However, I was trying if the "PL/SQL function used in WHERE clause issue"has been fixed. Earlier, even if the function was bringing back a constant - it was evaluated ONCE per ROW returned by the query. The most optimized form would of course be executing the function just once.

I see something interesting in the white paper in this regard. To quote -

"There is a DETERMINISTIC keyword which is used by SQL on PL/SQL functions to indicate that the function’s value depends only on the values of the actual arguments and that the function does not have any side effects which are of consequence. For the moment, the PL/SQL compiler ignores this keyword. It is possible in the future and with explicit permission from the programmer that the compiler will take advantage of the DETERMINISTIC keyword to eliminate some function calls. If this change is ever made to the behavior of PL/SQL, there will be plenty of advanced warning to programmers."

It seems to me that in some future release Oracle will try to figure out if the function is DETERMINISTIC and the parameters passed are the same between two iterations. If the parameters passed are the same and its deterministic (means it will ALWAYS produce the same result for the same parameter) - Oracle would not compute the function twice. But we're not there yet! Sigh...

I will put up the test case for it tomorrow.

Meanwhile, I really liked the definition of optimizer in the above paper. It says -

"An optimizing compiler makes programs run more quickly. Optimizers employ three
basic techniques:

1. Replacement of a slow operation with a faster one.

2. Elimination of an unnecessary operation. This might be regarded as a special case of
the first technique where the faster operation does nothing at all!

3. Movement of an operation from a place where it will be executed many times to a
place where it will be executed less often."

It seems Charles Wetherell was working on this re-designing the compiler thing way back in 1999 ;)

BTW, can anyone lend me the "Etudes for Programmer" book by him. Now I think he's just too smart a guy to leave his book out!