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.
