dcsimg
 

Oracle's 'cursor_sharing = force' May Not Behave as Expected

Monday Jul 30th 2018 by David Fitzjarrell

The cursor_sharing=force setting is designed to substitute system-generated bind variables for literals, but there are cases where not all literals are replaced.  Read on to see when this can occur.

Oracle, in a desire to let the administrator affect performance, has provided various settings in the spfile to enable or disable features that could possibly make things go faster. Long on the list of bloggers has been cursor_sharing, the parameter used to tell Oracle how to treat query text. By default, it's set to EXACT, which means Oracle takes the query as-is and executes it. This can, however, clutter the shared SQL area with what appear to be multiple copies of the same query, differing only in the literal values used. One setting for cursor_sharing, FORCE, causes Oracle to treat every query as though it's using bind variables, or so DBAs were led to believe. As George Gershwin so eloquently put it -- "It ain't necessarily so." Let's look at conditions that can throw a curve ball in this bind-variable circus.

If a query contains nothing but SQL text and literal values then cursor_sharing = FORCE does what DBAs have expected for years -- the literals are replaced with bind variables and, using bind variable peeking, the query is parsed and executed. This allows for almost unlimited reuse of a cursor, since the cursor is generated with bind variables and Oracle then uses the shared cursor to execute the query, using the system-generated bind variables to hold the literal values the original query contained. The problem arises when a query that once used only literal values is slightly rewritten to include a user-defined bind variable. This 'derails' the system-generated bind variable train and that good plan is no longer the default plan since the optimizer, having a new bind variable to contend with, reparses and generates a new, not necessarily better, plan.

Thanks to Jonathan Lewis we have an example to illustrate this. Let's use the data set he's so generously provided:


rem
rem     Script:         cursor_sharing_limit.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jun 2018
rem     Purpose:
rem
rem     Last tested
rem             18.1.0.0
rem             12.2.0.1
rem             12.1.0.2
rem

create table t1
as
select
        rownum            n1,
        rownum            n2,
        lpad(rownum,10)   small_vc,
        rpad('x',100,'x') padding
from dual
connect by
        level <= 1e4
;

The next step is to execute queries that supposedly should undergo bind variable substitution at the hands of cursor_sharing=FORCE, but don't. Jonathan has also generously provided that code:


alter system flush shared_pool;
alter session set cursor_sharing=force;

declare
        m_ct number;
        m_n1 number := 20;
begin
        execute immediate
                'select /*+ trace this */ count(*) from t1 where n2 = 15 and n1 = :b1'
                into m_ct using m_n1;
        dbms_output.put_line(m_ct);

        execute immediate
                'select /*+ trace this too */ count(*) from t1 where n1 = 15 and n2 = 15'
                into m_ct;
        dbms_output.put_line(m_ct);
end;
/

alter session set cursor_sharing=exact;

The 'hints' (which are really nothing more than comments) make it fairly easy to find and return the query text Oracle generates when those queries are executed. Putting this all together into a full example produces:


SQL> set linesize 300 trimspool on pagesize 50
SQL>
SQL> rem
SQL> rem        Script:      cursor_sharing_limit.sql
SQL> rem        Author:      Jonathan Lewis
SQL> rem        Dated:       Jun 2018
SQL> rem        Purpose:
SQL> rem
SQL> rem        Last tested
SQL> rem             18.1.0.0
SQL> rem             12.2.0.1
SQL> rem             12.1.0.2
SQL> rem
SQL>
SQL> create table t1
  2  as
  3  select
  4          rownum            n1,
  5          rownum            n2,
  6          lpad(rownum,10)   small_vc,
  7          rpad('x',100,'x') padding
  8  from dual
  9  connect by
 10          level <= 1e4 -- > comment to avoid WordPress format issue
 11  ;

Table created.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing=force;

Session altered.

SQL>
SQL> declare
  2          m_ct number;
  3          m_n1 number := 20;
  4  begin
  5          execute immediate
  6                  'select /*+ trace this */ count(*) from t1 where n2 = 15 and n1 = :b1'
  7                  into m_ct using m_n1;
  8          dbms_output.put_line(m_ct);
  9
 10          execute immediate
 11                  'select /*+ trace this too */ count(*) from t1 where n1 = 15 and n2 = 15'
 12                  into m_ct;
 13          dbms_output.put_line(m_ct);
 14  end;
 15  /
0
1

PL/SQL procedure successfully completed.

SQL>
SQL> alter session set cursor_sharing=exact;

Session altered.

SQL>
SQL> select     sql_id, parse_calls, executions, rows_processed, sql_text
  2  from       v$sql
  3  where      sql_text like 'select%trace this%'
  4  and        sql_text not like '%v$sql%'
  5  ;

SQL_ID        PARSE_CALLS EXECUTIONS ROWS_PROCESSED
------------- ----------- ---------- --------------
SQL_TEXT
-----------------------------------------------------------------------------------------
cbu4s78h5pfj5           1          1              1
select /*+ trace this too */ count(*) from t1 where n1 = :"SYS_B_0" and n2 = :"SYS_B_1"

cru67sufptx8x           1          1              1
select /*+ trace this */ count(*) from t1 where n2 = 15 and n1 = :b1


SQL>

Notice that the first query in the script, with a combination of a literal value and a user-defined bind variable, underwent no substitution with respect to the literal value. If straight SQL is used, through SQL*plus, this is what happens:


SQL> variable b1 number
SQL>
SQL> begin
  2          :b1 := 15;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ SQL*Plus session */ count(*) from t1 where n2 = 15 and n1 = :b1;

  COUNT(*)
----------
         1

SQL>
SQL> select     sql_id, parse_calls, executions, rows_processed, sql_text
  2  from       v$sql
  3  where      sql_text like 'select%Plus session%'
  4  and        sql_text not like '%v$sql%'
  5  ;

SQL_ID        PARSE_CALLS EXECUTIONS ROWS_PROCESSED
------------- ----------- ---------- --------------
SQL_TEXT
--------------------------------------------------------------------------------------------------
gq2qy2a9yuta7           1          1              1
select /*+ SQL*Plus session */ count(*) from t1 where n2 = :"SYS_B_0" and n1 = :b1


SQL>

Executing this through SQL*Plus directly, without the context switch of 'execute immediate' from a PL/SQL block, changes the behavior of the optimizer so that Oracle provides the expected results. It's interesting, to say the least.

The expected doesn't always happen with SQL statements, and that may be due to how those statements reach the optimizer. Knowing when, and when not, to expect behavior may make explaining things to users and developers a bit less stressful. As Norton Juster put it in "The Phantom Tollbooth": “Expect everything, I always say, and the unexpected never happens.” Which is good advice for a DBA.

See all articles by David Fitzjarrell

Home
Mobile Site | Full Site
Copyright 2018 © QuinStreet Inc. All Rights Reserved