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)
SELECT INTO vs. FETCH (2)
Cursor FOR Loops

SELECT INTO vs. FETCH (1)

The point of this example is to compare the performance of a single SELECT ... INTO, an implicit cursor, and FETCH, an explicit cursor. Since both these actions are really quick of an individual statement, we will repeat them in a loop to magnify the impact of the difference. Remember that a real system will be running many individual queries, so although each may appear to be quick on it's own, any unnecessary performance overhead, like FETCH, will affect whole system performance.

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 logically 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 shown in the example below.

Notice how using the explicit and implicit cursor FOR loops give comparable performance. This is because both are rewritten to BULK COLLECT ... LIMIT 100 syntax by the compiler.

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 Fetch Loop: ' || 
 (DBMS_UTILITY.get_time - l_start) || ' hsecs');
 l_start := DBMS_UTILITY.get_time;
 FOR cur_rec IN c_obj LOOP
 -- Do something.
 NULL;
 END LOOP;
 DBMS_OUTPUT.put_line('Explicit For 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 For Loop : ' || 
 (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END;
/
Explicit Fetch Loop: 226 hsecs
Explicit For Loop : 94 hsecs
Implicit For Loop : 93 hsecs
PL/SQL procedure successfully completed.
SQL>

As expected, manually performing a fetch has a negative impact on performance.

For more information see:

Oracle Database PL/SQL Language Reference 11g Release 2 (11.2)

Hope this helps. Regards Tim...

Back to the Top.

(追記) (追記ここまで)