CODEWITHSUNDEEP
oracle-databaseperformancecursors
Sami
Sami

Reputation: 99

Oracle Open Cursor Execution time

I'm trying to figure out how long this cursor takes to execute. However, I'm getting the same start and end time.

Please note I didn't include the actual sql statement. But I'm sure it is takes quite long time to execute.

PROCEDURE GetData (p1 IN VARCHAR2,myREFCURSOR OUT SYS_REFCURSOR)
IS 
    DynamicStatement       CLOB;
BEGIN
    INSERT INTO TimeTable VALUES ('StartTime',SYSDATE);
    COMMIT;

    OPEN myREFCURSOR FOR DynamicStatement;

    INSERT INTO TimeTable VALUES ('EndTime',SYSDATE);
    COMMIT;
END;

Upvotes: 1

Views: 1187

Answers (1)

APC
APC

Reputation: 146239

A Ref Cursor is a pointer to a chunk of memory where a query is stashed. Opening one is just an assignment operation, it doesn't execute the query. That's why it appears to take no time at all.

If you want to see how long it takes to run the actual query you need to FETCH records into some record variable.

PROCEDURE GetData (p1 IN VARCHAR2,myREFCURSOR OUT SYS_REFCURSOR)
IS 
    DynamicStatement       CLOB;
    type rec is record (id number, blah varchar2(128));
    lrec rec;
BEGIN
    INSERT INTO TimeTable VALUES ('StartTime',SYSDATE);
    COMMIT;

    OPEN myREFCURSOR FOR DynamicStatement;

    loop
        fetch myREFCURSOR into rec;
        exit when myREFCURSOR%notfound;
    end loop;
    close myREFCURSOR; 

    INSERT INTO TimeTable VALUES ('EndTime',SYSDATE);
    COMMIT;
END;

Obviously the record variable must match the projection of your query. Given that you're executing DynamicStatement that may be difficult.

Upvotes: 4

Related Questions