How does PL/SQL program executed internally in Oracle Database server

In previous post we learned that  PL/SQL program consist of two different types of code: procedural and SQL and each is processed by a different engine -  SQL statements by SQL statement processor of DBMS server and PL/SQL construct is processed by PL/SQL processing engine.

Diagram -1 : Interaction of PL/SQL engine with SQL statement processor
The PL/SQL engine can be installed in the database or in an application development tool, such as Oracle Forms.In either case, PL/SQL engine accept any valid PL/SQL unit as input and the engine runs procedural statements, however sends SQL statements to the SQL engine/SQL processing engine in the database. Diagram-1 show a PL/SQL program unit approaches PL/SQL engine which processes it and sends procedural statement to its executors and SQL statement execution request is forwarded to SQL statement executor.
Note : PL/SQL is used both in the database (for stored procedures and database triggers) and in the application code (to implement logic within a oracle form and execute local PL/SQL programs).For example, following diagram shows, how both oracle Forms client and a non-oracle client running PL/SQL program against an Oracle Server database in client server architecture.
Diagram-2 : PL/SQL within the Oracle client-server architecture (Image courtesy: O'Reilly's oracle PL/SQL programming)
In diagram-2, PL/SQL engine is installed in Oracle Forms to execute local PL/SQL program and uses database PL/SQL engine for executing stored program unit (compile in database). At the same time, a non-oracle client accessing database server PL/SQL engine for executing stored program. PL/SQL can be used as client and server side programming language.  
Context Switches and Performance hit:- The tight integration of PL/SQL with SQL provides developers with the best of both worlds -- declarative and procedural logic. However, performance is  hampered when we are dealing with large PL/SQL looping and their are SQL statement execution in between because PL/SQL statements are run by the PL/SQL statement executor; SQL statements are run by the SQL statement executor. When the PL/SQL run-time engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine. The SQL engine executes the SQL statement and returns information back to the PL/SQL engine. This transfer of control is called a context switch, and each one of these switches incurs overhead that slows down the overall performance of our PL/SQL programs or application.
In order to deal with such performance hit scenario, Oracle provides FORALL and BULK COLLECT for BULK processing and they dramatically improves performance.Please refer this Oracle article and this for more detail.

Read also: Why do we need "set serveroutput on" to see output on console in SQL*PLUS

5 Comments

Previous Post Next Post