Chapter 8. Compiling and Tuning to Improve Performance

The code compilation philosophy is one of the transparent activities in a programming language which latently influences a program's execution performance. Oracle 11g has introduced the real native compilation to cope with the bitter experiences of native and interpreted compilation techniques. Besides the compiler enhancement, Oracle 11g has introduced a new optimization level which brings terrific improvements in database performance by fueling up the optimizer's intelligence. Furthermore, the intra unit inlining feature applies an optimization level to assure logical gains in PL/SQL code performance by inlining local subprogram invocations in program calls. In this chapter, we will learn the best practices and recommendations to improvise upon the PL/SQL code performance. The topics to be covered in this chapter are as follows:

• Compiler enhancements

  • Native and interpreted compilation—background

  • Real native compilation in PL/SQL

• Tuning...

Native and interpreted compilation techniques

In basic terms, a language compiler converts the program code (in high level language) to a machine code (also known as M code or byte code), which can be understood by the machine runtime engine. Once the database is installed and ready for use, code compilation turns out to be a transparent activity to the users.

Until the release of Oracle9i, Oracle relied on an interpretable method of compilation of its database program units. A compiler in interpreted mode converts a PL/SQL program into machine code, stores in the database, and interprets upon its invocation. Oracle9i brought the revolutionary change in the compilation philosophy by introducing native compilation. But a question popped up amongst the DBAs and developers, "Is native compilation really more effective than an interpreted compilation?" An interpreted mode of compilation was not supported by RAC and backups.

It was the time when Oracle identified code compilation technique as a...

Tuning PL/SQL code

Once the DBA configures the database for optimal performance, the code development plays an essential role in PL/SQL performance. Now, we will discuss certain areas of improvements. These improvements can be made during the development stage so as to avoid the nightmares later.

The tunable areas identified and covered in this section are as follows:

• Avoiding implicit typecasting: Identification of appropriate data type

• Modularizing the programs: Modular programming and effective code sampling shares the work load

• Usage of bulk bind collections and the FORALL function : Usage of the FORALL function and bulk bind collections optimize the bulk operations

• Optimized conditional statements: Conditional statements can be optimized by logically placing the conditions

Besides the above areas, there are several other areas where tuning can bring comprehensive changes in performance. There is an immense scope for tuning in the code which uses dynamic SQL, SQL queries in PL/SQL blocks...

Enabling intra unit inlining

In conventional programming terminology, the program body of an inline program is stored along with the program unit which references it. In context of Oracle subprograms, the term inlining a subprogram refers to the replacing of a subprogram call with the copy of an actual subprogram body itself. At major occasions, this activity cohesively demonstrates better performance and thus, reaps out better benefits along with modularity and call optimization.

Usually, when a program is executed, the PL/SQL engine searches for the program definition in the available objects' lists. It then validates the program, executes the body, and maintains the result in the stack frame. Later it substitutes the results in the calling program unit and proceeds for further execution. When an inline program is called from a program unit, the PL/SQL engine replaces the call statement with the copy of the program body. The copied program body works faster than the program call execution...

PRAGMA INLINE

We learned the effect of the optimization level and subprogram inlining on the program performance. Once PLSQL_OPTIMIZER_LEVEL is set by the DBA, the optimization strategy is transparent to the end user. For this reason, Oracle introduced a pragma (PRAGMA INLINE) to authorize the user to explicitly inline a subprogram of its own choice. In the last section, we saw that PLSQL_OPTIMIZE_LEVEL 2 will prioritize the subprograms for inlining as per its own intelligence, while PLSQL_OPTIMIZE_LEVEL 3 will forcibly inline all the subprogram calls. While the latter setting might skip our expected subprogram, the latter setting might inline the expected subprogram along with the unnecessary inlining(inlinings).

Note

PRAGMA INLINE is the fifth pragma in Oracle after AUTONOMOUS_TRANSACTION, EXCEPTION_INIT, RESTRICT_REFERENCES, and SERIALLY_REUSABLE.

PRAGMA INLINE can be used with PLSQL_OPTIMIZER_LEVEL 2 and PLSQL_OPTIMIZER_LEVEL 3. When PLSQL_OPTIMIZE_LEVEL is set to 2, the pragma can be...

Summary

In this chapter, we learned the effect of compilation settings on the application performance. We understood the difference between interpreted/native compilation modes and learned the real native compilation feature introduced in Oracle 11g. We covered the PL/SQL tuning recommendations supported with demonstrations. Towards the end, we saw the effect of the optimization level on code execution through illustrations, intra unit inlining feature, and usage of PRAGMA INLINE in programs to explicitly inline subprogram calls.

In the next chapter, we will cover one of the most talked about features of Oracle 11g. The feature is known as result caching which promises tremendous performance gains in database applications.

Practice exercise

1. Identify the nature of the program which is best suited for the interpreted mode of compilation.

   1. The program unit contains multiple SQL statements.

   2. The program unit has been just developed and is in debug stage.

   3. The program unit uses collections and bulk bind statements.

   4. The program unit is in production phase.

2. Choose the correct statements about the real native compilation mode in Oracle 11g;

   1. The compilation method uses C compiler to convert the program into the equivalent C code.

   2. The compilation method mounts the shared libraries through the PLSQL_NATIVE_LIBRARY_DIR and PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT parameters.

   3. The compilation does not use C compiler but converts the program unit directly to the M code.

   4. The real native compilation is supported for RAC environments and participates in the backup recovery processes.

3. Determine the behavior of the PLSQL_OPTIMIZE_LEVEL optimizer when it has been set to 3.

   1. The optimizer would inline the programs which are necessary.

   2. The optimizer would...