Context Switch in PL/SQL

PL/SQL [procedural language extension to structure query language],the name itself says that it is a procedural language. The PL/SQL code complied into pcode( e.g byte code) and then it processes to perform the pogram stuffs where as SQL is a non-procedural language. So both PL/SQL and SQL are different from each other. But if we are using both in a block of code,then there must be a call among these. In other word we can say that PL/SQL subpogram will have procedural statement as well as the SQL statements.

To execute both PL/SQL and SQL, we normally refer to the exchange of processing control between the SQL and PL/SQL.
So it uses two engine for the execution of PL/SQL programs.
a) PL/SQL Engine
b) SQL Engine

These two engines are separate and distinct but we can use interchangeably that means  when we call SQL from PL/SQL and viceversa, the calling context needs to store its process  state and hand over both control and data to its other part engine. This switching cycle is computationally intensive and can typically be repeated so many times that its effects on response times can become quite noticeable.

In PL/SQL, when context is switched to sql, it switched immediate back to PL/SQL after the sql is completed.
For example, if we have two insert statements in row then the context switch is like this plsql-sql-plsql-sql-plsql not like plsql-sql-plsql.


So while executing a PL/SQL block, when the PL/SQl engine finds a  SQL statement, it stops and passes the control to the SQL engine. The SQL engine executes the SQL
statement and returns data back to the PL/SQL engine. This transfer of control is called as  Context Switch. It is a faster process but if it occurs again and again in the same PL/SQL program then it increases the elapsed time of our programs and introduced unnecessary CPU overhead causing the performance issue. So we have to reduce the number of context switches by eliminating or reducing the switching between these two environments to increase the PL/SQl program performance. This can be only solved by using Bulk Collect and Bulk Bind.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s