CURSOR_SHARING vs dynamic SQL


You need to take extreme care when using cursor sharing and dynamic SQL. You might think that opening a dynamic ref cursor would be treated the same as that same SQL using 'execute immediate'...but check the example below

SQL> drop table t;

Table dropped.

SQL> create table T ( x number primary key, y date, z varchar2(10));

Table created.

SQL> insert into t
  2  select rownum, sysdate+rownum,rownum
  3  from all_objects
  4  where rownum <= 5000;

5000 rows created.

SQL> commit;

Commit complete.

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing = exact;

Session altered.

SQL>
SQL> DECLARE
  2     rc SYS_REFCURSOR;
  3  BEGIN
  4    for i in 1 .. 5000 loop
  5      open rc for 'SELECT /*+XXX*/ * from T where x = '||i;
  6    end loop;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> select count(sql_text) from v$sql
  2  where sql_text like '%XXX%';

COUNT(SQL_TEXT)
---------------
           5002  <== as expected, no sharing yet

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing = force;

Session altered.

SQL> select count(sql_text) from v$sql
  2  where sql_text like '%XXX%';

COUNT(SQL_TEXT)
---------------
              1

SQL> DECLARE
  2     rc SYS_REFCURSOR;
  3  BEGIN
  4    for i in 1 .. 5000 loop
  5      open rc for 'SELECT /*+XXX*/ * from T where x = '||i;
  6    end loop;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> select count(sql_text) from v$sql
  2  where sql_text like '%XXX%';

COUNT(SQL_TEXT)
---------------
           5002  <== HEY! NO SHARING!

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing = force;

Session altered.

SQL> select count(sql_text) from v$sql
  2  where sql_text like '%XXX%';

COUNT(SQL_TEXT)
---------------
              1

SQL> DECLARE
  2  r number;
  3  BEGIN
  4    for i in 1 .. 5000 loop
  5      execute immediate 'SELECT /*+XXX*/ 1 from T where x = '||i into r;
  6    end loop;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> select count(sql_text) from v$sql
  2  where sql_text like '%XXX%';

COUNT(SQL_TEXT)
---------------
              3    <== This time we got sharing!

SQL>