Sunday, March 30, 2014

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>

2 comments:

  1. Saying that EXACT is the best setting is dangerous... We had one system with the setting exact but we did recommend them to use FORCE instead, it suffered heavily from parsing problem and SGA issues since they didn't use bind variables.
    but they found a note from Tom Kyte that said he recommended EXACT so that the developer could chose to not use bind variables(where appropriate). They totally missed the point that they _should_ use bind variables to actually get any performance in their system.

    Took me a while to conviince them to change to force, but now their system runs quite good :)

    ReplyDelete
  2. Excellent point, but it wasn't the setting of EXACT that was the problem. It was poor use of binds and a very bad misinterpretation of what it means. I stand by my recommendation that EXACT is the best setting, with the appropriate use of binds. The site you talk about here did the first part and not the second. The setting to FORCE is a lazy way to fix the real problem which is folks not using binds when they should. Yes it works to solve some over all issues but in the end isn't really fixing the base problem.

    ReplyDelete