Speed up your PL/SQL just by upgrading (Oracle 10g)

If you’ve inherited a lot of slow PL/SQL code, you may be at a loss to figure out how you’re going to speed it all up. One thing that can help is Oracle Database 10g’s optimizing compiler. In 10g, Oracle’s PL/SQL compiler automatically performs a degree of optimization.

So if you’re seeking another reason to upgrade to Oracle Database 10g, the need to deal with that slow PL/SQL may be the answer.

The compiler has three optimization levels, as shown in here:

Level Meaning

0     No optimization

1     Medium optimization and compile times

2     Best optimization; slowest compile times (default)

Level 2, which gives the most optimization, is the default. If you need to set the optimization down for any reason (such as to compile faster), you can use code similar to the following:

alter session

set PLSQL_OPTIMIZE_LEVEL = 1;