PL/SQL Engine – How it Process SQL Statements

You are so crazy to know the real mechanism how PL/SQL statements process inside the Oracle serever.

Statements means- the code present inside the PL/SQL blocks you can say statements.

  • How PL SQL engine process statements

The flow diagram is a self explanatory help you understand the PL/SQL engine mechanism

Oracle PL/SQL Engine Architecture

PLSQL

How PL/SQL Works..

  • When a PL/SQL program unit is passed to Oracle, it gets processed through a compiler.
  • The compiler creates a syntax tree, passes it through an optimizer, and generates machine code that’s stored in the database for execution at a later time.
  • Oracle 9i introduced native compilation. Using native compilation, the code is converted to shared C libraries on the database host.
  • Computation-intensive PL/SQL programs will notice an improvement in performance since little interpretation is required by Oracle in order to process the instructions.
  • Execution is performed in the PL/SQL engine with the assistance of the PL/SQL virtual machine and the SQL engine.
  • When a program unit is called, code that is compiled as interpreted has its machine code (MCode) heap loaded into the SGA. The instructions are processed by the PL/SQL virtual machine which communicates with the RDBMS kernel. The PL/SQL engine carries out the instructions with the assistance of the SQL engine to help with the SQL statements.
  • If a program unit is compiled using native compilation, instead of the machine code heap being loaded to the SGA, the shared libraries are loaded to the PGA. The PL/SQL virtual machine is still required, though it does not need to do any interpretation of the code. The PL/SQL and SQL engines perform the same functions as with interpreted.

Early Binding Vs Late Binding..

Late binding means that code is compiled at execution. Languages that use late binding are generally very flexible since modifications can be made up to and during compilation and execution.

Early binding means that code is compiled prior to execution. Namespaces are verified, permissions are checked, and all syntax is validated. This saves considerable time during execution since much of the work has already been completed. Oracle PL/SQL employs early binding.

Recent Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.