We are hitting this bug:
Bug 6510615 REF CURSOR opened by PLSQL does not use session cursor cache
An application I work on does a ton of executions of packages with cursor variables returning ref cursors. Apparently the session cursor cache normally reduces the use of the library cache latch – see Oracle note 62143.1 – Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention
During a peak time we saw a bunch of library cache latch waits (and cpu usage). The top SQL was a query that is run as a REF CURSOR. I don’t think that patching for the bug will resolve the issues completely – if the system is maxed out it will still be maxed out. But, fixing the bug should reduce the use of the latch and allow the system to handle more concurrent access.
To prove this out I tested a simple ref cursor by running it 10,000 times against 10.2.0.3 and 11.2.0.1. In 10.2.0.3 the cursor wasn’t cached.
10.2.0.3:
session cursor cache hits 4
11.2.0.1
session cursor cache hits 10004
I ran the same ref cursor test with 11.2.0.1 with these parameters:
ALTER SESSION SET SESSION_CACHED_CURSORS = 1000;
session cursor cache hits 9999
Elapsed: 00:00:01.00
ALTER SESSION SET SESSION_CACHED_CURSORS = 0;
Elapsed: 00:00:01.01
session cursor cache hits 0
So, in the 11.2.0.1 test turning off cached cursors added a small amount of run time and elminated the cache hits.
So, all this proves is that there is some advantage to session cached cursors and that this advantage is missing on 10.2.0.3 when using ref cursors and cursor variables.
Code to run a bunch of times with ref cursor:
DECLARE
TYPE CurType IS REF CURSOR;
v_Cursor CurType;
TYPE RecType IS RECORD
(
DUMMY VARCHAR2(1)
);
Rec RecType;
innerloop number;
outerloop number;
BEGIN
innerloop := 0;
outerloop := 0;
LOOP
outerloop := outerloop + 1;
EXIT WHEN outerloop > 100000;
TEST_PACKAGE.TEST_PROC(v_Cursor);
LOOP
FETCH v_Cursor into Rec;
EXIT WHEN v_Cursor%NOTFOUND;
innerloop := innerloop + 1;
END LOOP;
END LOOP;
dbms_output.put_line('innerloop = '||innerloop);
dbms_output.put_line('outerloop = '||outerloop);
END;
/
Decided to do this from Java since the real app is java.
Results weren’t much different.
Here is the java:
CallableStatement call =
thisConxn.prepareCall ("{call TEST_PACKAGE.TEST_PROC(?)}");
call.registerOutParameter (1, OracleTypes.CURSOR);
for (int i=0;i <= 10000; i++) {
call.execute ();
ResultSet theseResults = (ResultSet)call.getObject (1);
while (theseResults.next()) {
}
theseResults.close();
}
call.close();
Here is the package:
CREATE OR REPLACE PACKAGE TEST_PACKAGE IS
TYPE CurType IS REF CURSOR;
PROCEDURE TEST_PROC
(
p_Cursor OUT CurType
);
END TEST_PACKAGE;
/
show errors
CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE IS
PROCEDURE TEST_PROC
(
p_Cursor OUT CurType
) IS
BEGIN
OPEN p_Cursor FOR
SELECT DUMMY FROM DUAL;
END TEST_PROC;
END TEST_PACKAGE;
/
