Performance comparision between ref cursor and regular cursor .

Ref cursor is not a good option if performance is a concern. but ofcourse, there are place you can not avoid ref cursor. Ref cursor is not an option when you want to process the records inside the PLSQL procedure. Ref cursor is always slower then explicit cursor and implicit cursor. Let me demonstrate how process time between ref cursor, explicit cursor and implicit cursor...

As per the below example, the ref cursor consumes more time then the explicit cursor and implicit cursor.... So in netshell, use ref cursor only if it is required. otherwise, try to use regular cursor..


SQL> CREATE OR REPLACE PROCEDURE stp_refcursor_comparison AS
    l_loops NUMBER := 10000;
    l_dummy dual.dummy%TYPE;
    l_start NUMBER;
CURSOR c_dual IS
    SELECT dummy
    FROM dual;
    l_cursor SYS_REFCURSOR;
    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 ref cursor.
    l_start := DBMS_UTILITY.get_time;
    FOR i IN 1 .. l_loops LOOP
    OPEN l_cursor FOR SELECT dummy FROM dual;
    FETCH l_cursor

    INTO l_dummy;
    CLOSE l_cursor;
    END LOOP;
    DBMS_OUTPUT.put_line('REF CURSOR: ' || (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 stp_refcursor_comparison;
/

Procedure created.

SQL> set serveroutput on
SQL> execute stp_refcursor_comparison;
Explicit: 53
REF CURSOR: 67
Implicit: 35

PL/SQL procedure successfully completed.