Simple life

As the following figure shows, during the execution of a PL/SQL block, the PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine, which executes the SQL statements and, in some cases, returns data to the PL/SQL engine. 

So when a PL/SQL block including a loop, which causes frequently transfer between PL/SQL engine and SQL engine, the time cost is quite high and the efficiency is really low.

For example:

DECLARE
   TYPE NumList IS VARRAY(20) OF NUMBER;
   depts NumList := NumList(10, 30, 70);  -- department numbers
BEGIN
   ...
   FOR i IN depts.FIRST..depts.LAST LOOP
      DELETE FROM emp WHERE deptno = depts(i);
   END LOOP;
END;

The above DELETE statement is sent to the SQL engine with each iteration of the FOR loop. Too many context switches between the PL/SQL and SQL engines and it can harm performance. So, what should we do to improve the performance? Use Bulk Binds!