Implicit vs. Explicit Cursors Oracle Tips by Burleson Consulting

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

For a long time there have been debates over the relative merits of implicit and explicit cursors.  The short answer is that implicit cursors are faster and result in much neater code so there are very few cases where you need to resort to explicit cursors.

The cursor_comparison.sql script creates a procedure that compares the performance difference between the two approaches by performing multiple queries against the dual table.

cursor_comparison.sql
CREATE OR REPLACE PROCEDURE cursor_comparison AS
  l_loops  NUMBER := 10000;
  l_dummy  dual.dummy%TYPE;
  l_start  NUMBER;

  CURSOR c_dual IS
    SELECT dummy
    FROM dual;
BEGIN
  -- Time explicit cursor.
  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));

  -- Time implicit cursor.
  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));
END cursor_comparison;
/
SHOW ERRORS

The output from the procedure clearly demonstrates that implicit cursors are faster than explicit cursors.

SQL> SET SERVEROUTPUT ON
SQL> EXEC cursor_comparison;
Explicit: 203
Implicit: 162

PL/SQL procedure successfully completed.

The interesting thing is that 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.  To make them equivalent we should actually code the explicit cursor like that shown in the true_cursor_comparison.sql script.

true_cursor_comparison.sql

CREATE OR REPLACE PROCEDURE true_cursor_comparison AS
  l_loops  NUMBER := 10000;
  l_dummy  dual.dummy%TYPE;
  l_start  NUMBER;

  CURSOR c_dual IS
    SELECT dummy
    FROM dual;
BEGIN
  -- Time explicit cursor.
  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));

  -- Time implicit cursor.
  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));
END true_cursor_comparison;
/
SHOW ERRORS

The output from this procedure shows an even greater speed discrepancy.

SQL> SET SERVEROUTPUT ON
SQL> EXEC true_cursor_comparison;
Explicit: 264
Implicit: 162

PL/SQL procedure successfully completed.

Since both the cursors are now doing the same amount of work why is there a speed difference?  The answer is simply the volume of code being used.  PL/SQL is an interpreted language so every extra line of code adds to the total processing time.  As a rule of thumb, make the code as compact as possible without making it unsupportable.

One may then ask if native compilation would remove this discrepancy.  That question can be answered very easily.

SQL> ALTER SESSION SET plsql_compiler_flags = 'NATIVE';

Session altered.

SQL> ALTER PROCEDURE true_cursor_comparison COMPILE;

Procedure altered.

SQL> ALTER SESSION SET plsql_compiler_flags = 'INTERPRETED';

Session altered.

SQL> SET SERVEROUTPUT ON
SQL> EXEC true_cursor_comparison;
Explicit: 263
Implicit: 160

PL/SQL procedure successfully completed.

Native compilation will be dealt with in more depth later in this chapter.

This shows that there is still a speed difference between the two cursor types, so even when natively compiled the rule of "less code is faster" still holds true.  In the next section will explore the impact of placing blocks of code within loop structures.

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006. You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts: