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.

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