Implicit vs. Explicit Cursors in Oracle PL/SQL
This article compares the performance of implicit and explicit cursors. The test use the DBMS_UTILITY.GET_TIME function to get the current time before and after the test, with the delta value representing the elapsed time in hundredths of a second.
SELECT INTO vs. FETCH (1)
The code below demonstrates the performance difference between using a SELECT ... INTO, an implicit cursor, and FETCH, an explicit cursors.
SET SERVEROUTPUT ON
DECLARE
l_loops NUMBER := 100000;
l_dummy dual.dummy%TYPE;
l_start NUMBER;
CURSOR c_dual IS
SELECT dummy
FROM dual;
BEGIN
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
OPEN c_dual;
FETCH c_dual
INTO l_dummy;
CLOSE c_dual;
END LOOP;
DBMS_OUTPUT.put_line('Explicit: ' ||
(DBMS_UTILITY.get_time - l_start) || ' hsecs');
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
SELECT dummy
INTO l_dummy
FROM dual;
END LOOP;
DBMS_OUTPUT.put_line('Implicit: ' ||
(DBMS_UTILITY.get_time - l_start) || ' hsecs');
END;
/
Explicit: 273 hsecs
Implicit: 234 hsecs
PL/SQL procedure successfully completed.
SQL>This clearly demonstrates the implicit cursor is measurably faster than the explicit cursor.
SELECT INTO vs. FETCH (2)
The implicit cursor is not only faster, but it is actually doing more work, since it includes a NO_DATA_FOUND and a TOO_MANY_ROWS exception check. If we manually code the FETCH to do the same amount of work, the comparison would look like that listed below.
SET SERVEROUTPUT ON
DECLARE
l_loops NUMBER := 100000;
l_dummy dual.dummy%TYPE;
l_start NUMBER;
CURSOR c_dual IS
SELECT dummy
FROM dual;
BEGIN
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
OPEN c_dual;
FETCH c_dual
INTO l_dummy;
IF (c_dual%NOTFOUND) THEN
RAISE NO_DATA_FOUND;
END IF;
FETCH c_dual
INTO l_dummy;
IF (c_dual%FOUND) THEN
RAISE TOO_MANY_ROWS;
END IF;
CLOSE c_dual;
END LOOP;
DBMS_OUTPUT.put_line('Explicit: ' ||
(DBMS_UTILITY.get_time - l_start) || ' hsecs');
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
SELECT dummy
INTO l_dummy
FROM dual;
END LOOP;
DBMS_OUTPUT.put_line('Implicit: ' ||
(DBMS_UTILITY.get_time - l_start) || ' hsecs');
END;
/
Explicit: 402 hsecs
Implicit: 234 hsecs
PL/SQL procedure successfully completed.
SQL>This makes the performance difference between the explicit and implicit cursor even more extreme.
Cursor FOR Loops
The performance impact of using FETCH is also evident in a comparison with a cursor FOR loop, as show in the example below.
SET SERVEROUTPUT ON
DECLARE
l_obj all_objects%ROWTYPE;
l_start NUMBER;
CURSOR c_obj IS
SELECT *
FROM all_objects;
BEGIN
l_start := DBMS_UTILITY.get_time;
OPEN c_obj;
LOOP
FETCH c_obj
INTO l_obj;
EXIT WHEN c_obj%NOTFOUND;
-- Do something.
NULL;
END LOOP;
CLOSE c_obj;
DBMS_OUTPUT.put_line('Explicit Loop: ' ||
(DBMS_UTILITY.get_time - l_start) || ' hsecs');
l_start := DBMS_UTILITY.get_time;
FOR cur_rec IN (SELECT * FROM all_objects) LOOP
-- Do something.
NULL;
END LOOP;
DBMS_OUTPUT.put_line('Implicit Loop 2: ' ||
(DBMS_UTILITY.get_time - l_start) || ' hsecs');
END;
/
Explicit Loop: 226 hsecs
Implicit Loop: 94 hsecs
PL/SQL procedure successfully completed.
SQL>For more information see:
Hope this helps. Regards Tim...
 |