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


  1. Top Courses to learn
    Excellent blog with lots of information. I have to thank for this. Do share more.

  2. The development of artificial intelligence (AI) has propelled more programming architects, information scientists, and different experts to investigate the plausibility of a vocation in machine learning. Notwithstanding, a few newcomers will in general spotlight a lot on hypothesis and insufficient on commonsense application. machine learning projects for final year In case you will succeed, you have to begin building machine learning projects in the near future.

    Projects assist you with improving your applied ML skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include projects into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Final Year Project Centers in Chennai even arrange a more significant compensation.

    Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.

Previous Post Next Post