Diagram -1 : Interaction of PL/SQL engine with SQL statement processor |
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) |
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