Benchmarking – Oracle Implicit Cursors vs Explicit Cursors

There are two primary cursor types that can be used in Oracle PL/SQL: implicit cursors and explicit cursors. Implicit cursors take the form of a FOR loop that loops that individually process each result of a SELECT statement. The cursor is implicitly opened and closed. Explicit cursors require the declaration of a cursor object, which is then opened, processed, and finally closed. I’ve read several sources stating that implicit cursors, although easier to code in many cases, are less efficient. I thought I would try some less-than-scientific benchmarking of these two cursor types to see just what the difference is. To test, I’m using a simple two value table. ID serves as a sort column and VALUE is the arbitrary value we will retrieve for processing in the loop. The benchmark does nothing with the returned values, it simply places them in a variable with each iteration. Here is the test table I’m using:

SQL> DESC test_tab;
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                               NUMBER
 VALUE                            VARCHAR2(30)

Next, I’ll be using three simple procedures to set up and run the benchmark. BUILD_TEST_TAB simply populates TEST_TAB with 2 million random values, incrementing the ID field for each record. An index resides on this column to sort the list of values returned from the query. TEST_IMPLICIT_CURSOR creates a FOR loop that traverses the results of a query against TEST_TAB. TEST_EXPLICIT_CURSOR creates an explicit cursor for the same query.

SQL> CREATE OR REPLACE PROCEDURE build_test_tab
  2  AS
  3  BEGIN
  4
  5    FOR i IN 1..2000000
  6    LOOP
  7      INSERT INTO test_tab VALUES ( i, DBMS_RANDOM.STRING( 'X', 30 ) );
  8    END LOOP;
  9
 10  END;
 11  /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE test_implicit_cursor
  2  AS
  3    curr_val TEST_TAB.VALUE%TYPE;
  4  BEGIN
  5
  6    FOR i IN
  7      (
  8      SELECT value
  9      FROM test_tab
 10      ORDER BY id
 11      )
 12    LOOP
 13      curr_val := i.value;
 14    END LOOP;
 15
 16  END;
 17  /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE test_explicit_cursor
  2  AS
  3    CURSOR test_curs
  4    IS
  5    SELECT value
  6    FROM test_tab
  7    ORDER BY id;
  8
  9    curr_val TEST_TAB.VALUE%TYPE;
 10  BEGIN
 11
 12    OPEN test_curs;
 13
 14    LOOP
 15
 16      FETCH test_curs INTO curr_val;
 17
 18      EXIT WHEN test_curs%NOTFOUND;
 19
 20    END LOOP;
 21
 22    CLOSE test_curs;
 23
 24  END;
 25  /

Procedure created.

Below is the call to the two test procedures. I rebuild TEST_TAB twice and run benchmarks 10 times against each table, alternating between implicit and explicit calls. This is an attempt to reduce the influence of other processes running, stats being generated on the table, and other factors that might skew results.

SQL> SET TIMING OFF
SQL> DROP INDEX i_test_id;
Index dropped.
SQL> TRUNCATE TABLE test_tab;
Table truncated.
SQL> EXEC build_test_tab;
PL/SQL procedure successfully completed.
SQL> CREATE INDEX i_test_id ON test_tab( id );
Index created.
SQL> SET TIMING ON


SQL> EXEC test_implicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.65
SQL> EXEC test_explicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:16.75
SQL> EXEC test_implicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.23
SQL> EXEC test_explicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:16.54
SQL> EXEC test_implicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.53
SQL> EXEC test_explicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:16.52
SQL> EXEC test_implicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.43
SQL> EXEC test_explicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:16.68
SQL> EXEC test_implicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.29
SQL> EXEC test_explicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:16.64


SQL> SET TIMING OFF
SQL> DROP INDEX i_test_id;
Index dropped.
SQL> TRUNCATE TABLE test_tab;
Table truncated.
SQL> EXEC build_test_tab;
PL/SQL procedure successfully completed.
SQL> CREATE INDEX i_test_id ON test_tab( id );
Index created.
SQL> SET TIMING ON


SQL> EXEC test_explicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:15.54
SQL> EXEC test_implicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.89
SQL> EXEC test_explicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:14.36
SQL> EXEC test_implicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.91
SQL> EXEC test_explicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:14.47
SQL> EXEC test_implicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.52
SQL> EXEC test_explicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:14.72
SQL> EXEC test_implicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.48
SQL> EXEC test_explicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:15.64
SQL> EXEC test_implicit_cursor;
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.55

This resulted in the following timings:

Total Time Total Runs Average Time
RUN 1
Implicit 27.13 5 5.426
Explicit 66.49 5 13.298
RUN 2
Implicit 34.8 5 6.96
Explicit 74.73 5 14.946

Interestingly enough, this simple series of tests is pointing to significantly better performance of implicit over explicit cursors. This is the opposite of what I have read in several sources. Granted, this is an oversimplified benchmark test and is only taking timing into account. I have not attempted to measure or compare resource usage or any other metric.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s