Avoid Unnecessary Context Switches

Oracle use two engine for execution of PL/SQL programs:

1. PL/SQL Engine
2. SQL Engine

During execution of PL/SQL program to execute all procedural statements (like if, for, assignments...) Oracle use PL/SQL engine and sends all SQL statements present in the code to the SQL engine. The SQL engine will parse and execute the query or DML statement and return the expected output back to the PL/SQL engine. This switch between the two engines is called context switching.

Which impose additional overhead like maintain the state of variables and stack on every switch..... means highly expensive. Specially the cursor loops are very expensive so instead of that one should use Bulk Bind and Bulk collect features which reduce context switching.

Instead of fetching into simple variables or records fetch into a collection of scalars or a collection of records using BULK COLLECT INTO.

FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine.

Usually, using of the BULK COLLECT and FORALL statements can drastically improve the performance.