SQL compilation implies execution plans generation
PL/SQL compilation implies P-code generation
-------------------------------------------------------------------------------------------------
The Front-end:
--------------
If compiler front-end founds any error, it outputs an error-report and compilation of the unit is aborted. The output of front-end is an internal representation that exactly captures the source code’s semantics. PL/SQL compiler uses DIANA (Descriptive Intermediate Attributed Notation for Ada) for its internal representation. The front-end guarantees that when it does not report an error, the DIANA it generates is correct and needs no further correctness checking.
DIANA:
-------
It is an abstract data type such that each object of the type is a representation of an intermediate form of an Ada program
The Back-end (Code-Generator):
-------------------------------
Back-end takes DIANA as a input and generates an executable representation of the program in the machine code of the target machine. The compiler back-end plays a critical role in generating fast run-time code. The output is the code for PL/SQL Virtual machine.
PL/SQL Virtual Machine:
-----------------------
The instruction set of the PVM is exactly analogous to that of a computer chip. The difference, of course, is that the PVM is implemented in software (it is written in C and linked into the ORACLE executable) while the instruction set of a chip is implemented directly in hardware. A computer implemented in software is commonly called a virtual machine and any interpreted language has such a machine.
Native Compilation of PL/SQL:
-----------------------------
In the native mode, it translates the Machine Code into C source code with the same semantics.
For more details
http://www.blackhat.com/presentations/bh-usa-06/BH-US-06-Finnigan.pdf
