Last week I was busy with my training on PL/SQL for PSI Data Systems. I was discussing the “PL/SQL Performance tuning with BULK COLLECT” with participants and I demonstrated the performance boost with the example. I would like to share the discussion with this post.
Scenario: Having a table with 10 million records needs to store into the PL/SQL Table by Index collection using the Cursors. The code snippet is as below.
DECLARE
time_before BINARY_INTEGER;
time_after BINARY_INTEGER;
CURSOR empcur IS SELECT * FROM testemp;
TYPE emp_table_type IS TABLE OF varchar2(20) INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
idx number := 1;
BEGIN
time_before := DBMS_UTILITY.GET_TIME;
OPEN empcur;
LOOP
FETCH empcur INTO emp_table(idx);
idx := idx + 1;
EXIT WHEN empcur%NOTFOUND;
END LOOP;
CLOSE empcur;
time_after := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE (round((time_after – time_before)/60));
END;/
The above code without any performance trick, it took nearly 5 minutes (in my laptop with normal configuration) to complete.
The reason behind the bad performance is, all we know that in PL/SQL there are two statement executors to execute the program. One is SQL statement executor which executes only SQL statements in the program and the other one is PL/SQL statement executor who will execute procedural instructions.
Whenever a PL/SQL program is executing, a context switching will take place between the two statement executors. Especially if the program is doing some iterative task which contains the combination of SQL and PL/SQL code, it needs frequent context switching which Leeds into performance drawback.
This is what happening in the above code while fetching the records one by one in the loop. For each iteration there is a context switching and this will happens over 10 million times.
To avoid the context switching repeatedly, we need to collect the data at once using the below code.
The same code has been modified with the BULK COLLECT as follows.
DECLARE
time_before BINARY_INTEGER;
time_after BINARY_INTEGER;
CURSOR empcur IS SELECT * FROM testemp;
TYPE emp_table_type IS TABLE OF varchar2(20) INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
BEGIN
time_before := DBMS_UTILITY.GET_TIME;
OPEN empcur;
FETCH empcur BULK COLLECT INTO emp_table;
CLOSE empcur;
time_after := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE (round((time_after – time_before)/60));
END;
/
The above code with BULK COLLECT took just 1 minute to complete. So we nearly increased 80% of the performance.
Similarly in PL/SQL there are many such tuning tips are there. So keep visit my blog for more tips.
Happy programming.
