Context Switches and Performance issue In PL/SQL With BULK COLLECT Statement.

Context Switches:- In a PL/SQL program contain SQL and PL/SQL statements. PL/SQL statements are run by the PL/SQL statement executor; SQL statements are run by the SQL statement executor. When Pl/SQL engine find SQL Statement it stop own works and pass SQL statement to SQL engine. The SQL engine execute SQL Statement and return back to PL/SQL engine. This types of transferring are called Context Switching.

Example :-
SQL> CONN RND/rnd
Connected.
SQL>
SQL> SPOOL D:\BULK_COLLECT.TXT
SQL>
SQL> SET SERVEROUTPUT ON;
SQL>
SQL> CREATE TABLE EMP
  2  (
  3    EMPLOYEE_ID     NUMBER(6),
  4    FIRST_NAME      VARCHAR2(20 BYTE),
  5    LAST_NAME       VARCHAR2(25 BYTE)             NOT NULL,
  6    EMAIL           VARCHAR2(25 BYTE)             NOT NULL,
  7    PHONE_NUMBER    VARCHAR2(20 BYTE),
  8    HIRE_DATE       DATE                          NOT NULL,
  9    JOB_ID          VARCHAR2(10 BYTE)             NOT NULL,
 10    SALARY          NUMBER(8,2),
 11    COMMISSION_PCT  NUMBER(2,2),
 12    MANAGER_ID      NUMBER(6),
 13    DEPARTMENT_ID   NUMBER(4)
 14  );

Table created.

SQL> CREATE TABLE E AS SELECT * FROM EMP;

Table created.

SQL>
SQL> Insert into E
  2     (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
  3   Values
  4     (197, 'Kevin', 'Feeney', 'KFEENEY', '650.507.9822',
  5      TO_DATE('05/23/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'SH_CLERK', 3000, 124, 50);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO E
  2  SELECT * FROM E;

1 row created.

SQL> /

2 rows created.

SQL> /

4 rows created.

SQL> /

8 rows created.

SQL> /

16 rows created.

SQL> /

32 rows created.

SQL> /

64 rows created.

SQL> /

128 rows created.

SQL> /

256 rows created.

SQL> /

512 rows created.

SQL> /

1024 rows created.

SQL> /

2048 rows created.

SQL> /

4096 rows created.

SQL> /

8192 rows created.

SQL> /

16384 rows created.

SQL> /

32768 rows created.

SQL> /

65536 rows created.

SQL> /

131072 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE OR REPLACE PROCEDURE RND.DPR_BULK_DATA_TEST (P_DEPT_ID NUMBER)
  2  IS
  3     V_SQL          VARCHAR2 (3000);
  4
  5     TYPE TYP_EMP_ALL IS TABLE OF EMP%ROWTYPE;
  6
  7     REC_EMP_ALL    TYP_EMP_ALL;
  8     V_START_TIME   NUMBER;
  9     V_END_TIME     NUMBER;
 10  BEGIN
 11     V_SQL :=
 12        'SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
 13                FROM E
 14                WHERE DEPARTMENT_ID=:2';
 15
 16     EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO REC_EMP_ALL USING P_DEPT_ID;
 17
 18     V_START_TIME := DBMS_UTILITY.GET_CPU_TIME;
 19
 20     FOR I IN REC_EMP_ALL.FIRST .. REC_EMP_ALL.LAST
 21     LOOP
 22        INSERT INTO EMP (EMPLOYEE_ID,
 23                         FIRST_NAME,
 24                         LAST_NAME,
 25                         EMAIL,
 26                         PHONE_NUMBER,
 27                         HIRE_DATE,
 28                         JOB_ID,
 29                         SALARY,
 30                         COMMISSION_PCT,
 31                         MANAGER_ID,
 32                         DEPARTMENT_ID)
 33             VALUES (REC_EMP_ALL (I).EMPLOYEE_ID,
 34                     REC_EMP_ALL (I).FIRST_NAME,
 35                     REC_EMP_ALL (I).LAST_NAME,
 36                     REC_EMP_ALL (I).EMAIL,
 37                     REC_EMP_ALL (I).PHONE_NUMBER,
 38                     REC_EMP_ALL (I).HIRE_DATE,
 39                     REC_EMP_ALL (I).JOB_ID,
 40                     REC_EMP_ALL (I).SALARY,
 41                     REC_EMP_ALL (I).COMMISSION_PCT,
 42                     REC_EMP_ALL (I).MANAGER_ID,
 43                     REC_EMP_ALL (I).DEPARTMENT_ID);
 44     END LOOP;
 45
 46     V_END_TIME := DBMS_UTILITY.GET_CPU_TIME;
 47
 48     DBMS_OUTPUT.
 49      PUT_LINE ('Executing Time Using Loop ' ||TO_CHAR( V_END_TIME-V_START_TIME));
 50     V_START_TIME := DBMS_UTILITY.GET_CPU_TIME;
 51
 52     FORALL J IN REC_EMP_ALL.FIRST .. REC_EMP_ALL.LAST
 53        INSERT INTO EMP (EMPLOYEE_ID,
 54                         FIRST_NAME,
 55                         LAST_NAME,
 56                         EMAIL,
 57                         PHONE_NUMBER,
 58                         HIRE_DATE,
 59                         JOB_ID,
 60                         SALARY,
 61                         COMMISSION_PCT,
 62                         MANAGER_ID,
 63                         DEPARTMENT_ID)
 64             VALUES (REC_EMP_ALL (J).EMPLOYEE_ID,
 65                     REC_EMP_ALL (J).FIRST_NAME,
 66                     REC_EMP_ALL (J).LAST_NAME,
 67                     REC_EMP_ALL (J).EMAIL,
 68                     REC_EMP_ALL (J).PHONE_NUMBER,
 69                     REC_EMP_ALL (J).HIRE_DATE,
 70                     REC_EMP_ALL (J).JOB_ID,
 71                     REC_EMP_ALL (J).SALARY,
 72                     REC_EMP_ALL (J).COMMISSION_PCT,
 73                     REC_EMP_ALL (J).MANAGER_ID,
 74                     REC_EMP_ALL (J).DEPARTMENT_ID);
 75
 76     V_END_TIME := DBMS_UTILITY.GET_CPU_TIME;
 77
 78     DBMS_OUTPUT.
 79      PUT_LINE (
 80        'Executing Time Using BULK COLLECT ' ||TO_CHAR(V_END_TIME-V_START_TIME));
 81     COMMIT;
 82  END;
 83  /

Procedure created.

SQL> EXEC DPR_BULK_DATA_TEST(50);
Executing Time Using Loop 5327
Executing Time Using BULK COLLECT 80

PL/SQL procedure successfully completed.

SQL>