Hi Tom,

We are running MOD_PLSQL on our 10.2.0.1 db server and are constantly having performance problems. The AWR report shows latch waits as the highest wait taking as much as 35% of DB time. The ADDM report says that the database is soft parsing too much. It recommends increasing session cached cursors to reduce the number of soft parses.

In response to this, I have set the DAD to perform a session reinitialize which should keep the cached cursors open at the end of each call (problem persists). I have increased session cached cursors from 0 to 100 (problem persists). I am now looking at the program logic to try and determine why it is not using the session cached cursors.

In my quest to understand cached cursors better, I put together a small code comparison and came out with a result I just simply don't understand. Perhaps you could help.

In my example below, I have two procedures both opening cursors to all_objects. One procedure uses a string (with bind variables) to open the sql, and the other uses embeded SQL to open the identical cursor. I would have assumed the embeded SQL procedure to perform better than the string sql procedure (in terms of parsing and latching). The strange thing is though... it doesn't.

My test seems to tell me that the string SQL will make use of session cached cursors while the embeded SQL will not. Why is this happening? Why is the string SQL "reusable" and the embeded not?

dayneo@SANDBOX> create or replace procedure test_string_cursor as
  2           l_rc    sys_refcursor;
  3           l_sql   varchar2(32767);
  4           l_value number := 1;
  5  begin
  6           -- using "where 1=1" to differenciate query from hardcoded sql
  7           l_sql := 'select object_name from user_objects where 1=:bv';
  8           open l_rc for l_sql using l_value;
  9           close l_rc;
 10  end test_string_cursor;
 11  /

Procedure created.

dayneo@SANDBOX> SHOW ERRORS
No errors.
dayneo@SANDBOX> create or replace procedure test_hardcoded_cursor as
  2           l_rc    sys_refcursor;
  3           l_value number := 2;
  4  begin
  5           -- using "where 2=2" to differenciate query from string sql
  6           open l_rc for select object_name from user_objects where 2 = l_value;
  7           close l_rc;
  8  end test_hardcoded_cursor;
  9  /

Procedure created.

dayneo@SANDBOX> SHOW ERRORS
No errors.
dayneo@SANDBOX> alter session set session_cached_cursors=10;

Session altered.

dayneo@SANDBOX> begin
  2           -- warmup
  3           test_string_cursor();
  4           test_hardcoded_cursor();
  5           runstats_pkg.rs_start();
  6           for l_count in 1..10000 loop
  7               test_string_cursor();
  8           end loop;
  9           runstats_pkg.rs_middle();
 10           for l_count in 1..10000 loop
 11               test_hardcoded_cursor();
 12           end loop;
 13           runstats_pkg.rs_stop(500);
 14  end;
 15  /
Run1 ran in 200 hsecs
Run2 ran in 201 hsecs
Run1 ran in 99.5% of the time
    
Name                                Run1      Run2      Diff
STAT...session cursor cache hi    10,000         0   -10,000
LATCH.library cache               40,004    50,011    10,007
LATCH.library cache pin           40,004    20,010   -19,994
LATCH.library cache lock               0    40,002    40,002
    
Run1 latches total versus runs -- difference and pct
      Run1      Run2      Diff     Pct
    80,179   110,493    30,314  72.56%

PL/SQL procedure successfully completed.

I found the same behavior on the 10.1.0.4 db.

Thanks Tom