To BIND or not to BIND.... CURSOR_SHARING

CURSOR_SHARING is a parameter that controls what does the Oracle Optimizer do with literal values in a SQL statement. The default setting is EXACT which I personal think is the best.  This doesn't change literals and its up to the coder to put in either literals or binds as needed in the code.  A common setting is FORCE which forces the replacement of binds with literals.

In this simple example you can see that when CURSOR_SHARING is EXACT we have 3 distinct statements for the 3 runs.  However when set to FORCE the optimizer substitutes in a BIND variable for the literal value and we have just one statement.  This is good right?  Well sort of, but I'll talk about how this can be problematic in another blog entry soon.

Here is the simple example (FYI - I removed the actual output from the 3 queries for readability):

SQL> set lines 200
SQL> column SQL_TEXT format a85
SQL> column EXE format 999
SQL> column PAR format 999
SQL> column LDS format 999
SQL> column OWNER format a40
SQL>
SQL> set feedback on
SQL> set echo on
SQL> set termout on
SQL>
SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing = exact;

Session altered.

SQL>
SQL> show parameter cursor_sharing;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
SQL>
SQL> select /* BINDS_TEST */ distinct owner from big_tab where object_type='TABLE';


SQL> select /* BINDS_TEST */ distinct owner from big_tab where object_type='SYNONYM';


SQL> select /* BINDS_TEST */ distinct owner from big_tab where object_type='INDEX';

SQL>
SQL> select SQL_ID,EXECUTIONS EXE,LOADS LDS,PARSE_CALLS PAR,SQL_TEXT from v$sqlarea where sql_text like 'select /* BINDS_TEST */ distinct %';

SQL_ID         EXE  LDS  PAR SQL_TEXT
------------- ---- ---- ---- -------------------------------------------------------------------------------------
ajarrkkxts4hk    1    1    1 select /* BINDS_TEST */ distinct owner from big_tab where object_type='SYNONYM'
75zrs6yswsdka    1    1    1 select /* BINDS_TEST */ distinct owner from big_tab where object_type='INDEX'
23g9hv9w9tfmk    1    1    1 select /* BINDS_TEST */ distinct owner from big_tab where object_type='TABLE'

3 rows selected.

SQL>
SQL> alter system flush shared_pool;

System altered.

*********************************************************************
*****             Changing CURSOR_SHARING to FORCE              *****
*********************************************************************
SQL> alter session set cursor_sharing = force;

Session altered.

SQL>
SQL> show parameter cursor_sharing;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           FORCE
SQL>
SQL> select /* BINDS_TEST */ distinct owner from big_tab where object_type='TABLE';

SQL> select /* BINDS_TEST */ distinct owner from big_tab where object_type='SYNONYM';

SQL> select /* BINDS_TEST */ distinct owner from big_tab where object_type='INDEX';

SQL>
SQL> select SQL_ID,EXECUTIONS EXE,LOADS LDS,PARSE_CALLS PAR,SQL_TEXT from v$sqlarea where sql_text like 'select /* BINDS_TEST */ distinct %';

SQL_ID         EXE  LDS  PAR SQL_TEXT
------------- ---- ---- ---- -------------------------------------------------------------------------------------
bj0a2w1puthua    3    2    3 select /* BINDS_TEST */ distinct owner from big_tab where object_type=:"SYS_B_0"

1 row selected.

SQL>
SQL>
SQL>

Ace it is!

I've been recognized as an Oracle Ace!  

I'm one of the first 10 folks approved for the new Ace Associate level.  I'm pleased to be recognized for my many years working with Oracle and helping other use it more effectively thru presentations, email lists, user group meetings, casual conversation, twitter and this blog of course.

A big thanks to Paul Jackson and Mark Bobak for submitting my application.