7.3. Configuring PL/SQL for Better Performance
There are several great features that PL/SQL gives a developer to fine-tune their code and prepare it for promotion to production. But like many development-oriented features, they may not be appropriate for production or may need to be disabled to support legacy code. The database initialization parameters that affect PL/SQL performance include:
PLSQL_WARNING
PLSQL_DEBUG
PLSQL_OPTIMIZE_MODE
PLSQL_CODE_TYPE
NOTE
For advanced tuning of PL/SQL programs, see the white paper "PL/SQL Just Got Faster" by Bryn Llewellyn and Charles Wetherell. It is located on the Oracle Technet website. At the time of publication, the URL was www.oracle.com/technology/tech/pl_sql/htdocs/New_In_10gR1.htm.
PL/SQL compiler warnings are new to Oracle10g. These optional compile-time warnings can help developers create better programs by identifying potential problems that might result in runtime errors or poor performance. In general, disable compile-time warnings in production. To ensure these warnings are disabled, execute the following:
ALTER SYSTEM SET plsql_warnings='DISABLE:ALL' SCOPE=BOTH;
The PLSQL_DEBUG parameter forces all subsequent PL/SQL compilations to be interpreted and include additional debugging information. Again, although this feature can be beneficial in development, disable it in production. To ensure that it is disabled, execute the following:
ALTER SYSTEM SET plsql_debug=FALSE SCOPE=BOTH;
Oracle10g introduced an optimizing compiler that significantly improves PL/SQL performance of computing-intensive programs. Unless testing has shown this optimization to be detrimental to your programs, enable it fully in both production and development. To fully enable this feature, execute this:
ALTER SYSTEM SET plsql_optimize_mode=2 SCOPE=BOTH;
The parameter PLSQL_CODE_TYPE specifies whether to compile the PL/SQL code into the default-interpreted byte code or native machine code. The native machine code provides faster runtime performance at a cost of longer compilation times and slightly greater administrative overhead. To enable native compilation, you need the C compiler supplied by your platform vendor. Set PLSQL_NATIVE_LIBRARY_DIR and make sure the directory exists. Finally, set your PLSQL_CODE_TYPE to 'NATIVE' and CREATE OR REPLACE your programs.