Session cursor cache bug results in high latch waits?

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;
/

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

3 Responses to Session cursor cache bug results in high latch waits?

  1. Pingback: Blog second anniversary | Bobby Durrett's DBA Blog

  2. bronx says:

    Hi Bob,

    Do you think we need to increase the session_cached_cursors when the usage is 500%. See below. Thanks

    PARAMETER                 VALUE USAGE
    ----------------------    ----- -----
    session_cached_cursors       20  500% 
    open_cursors               9900    1%
    
    • Bobby says:

      Thank you for your comment. I’m not sure what 500% usage means. Maybe you could let me know how you calculated that number. But, I wouldn’t change this parameter unless you had some performance issue that mattered that you could tie back to parse time. If your important process is spending a lot of time on latch waits related to parsing or just using a lot of CPU doing soft parses (running the same query over and over again) then you may want to try increasing session_cached_cursors to see if it improves performance.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.