dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted July 30, 2018

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

By David Fitzjarrell

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM