Sep 28, 2012

Oracle Histogram

                       Histograms are used to predict cardinality and the number of rows returned to a query .The Oracle Query Optimizer uses histograms to predict better query plans. The ANALYZE command or DBMS_STATS package can be used to compute these histograms.A histogram is a frequency distribution meta-data that describes the distribution of data values within a column of a table.A histogram is a collection of information about the distribution of values within a column.

In some cases, the distribution of values within a column of a table will affect the optimizers decision to use an index vs. perform a full-table scan. This scenario occurs when the value with a where clause has a disproportional amount of values, making a full-table scan cheaper than index access.

Histograms are also important for determine the optimal table join order.

Sep 21, 2012

PLSQL Compiler

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.

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