Oracle Scratchpad

March 9, 2008

Cursor_sharing

Filed under: CBO,Execution plans,Hints,Troubleshooting — Jonathan Lewis @ 9:13 pm BST Mar 9,2008

From time to time I’ve commented on the fact that setting cursor_sharing to force or similar may be a temporary workaround to bad coding practises, but that it can introduce problems, has a few associated bugs, and shouldn’t  be viewed as a guaranteed, or long-term, solution.


I don’t think I’ve ever published any specific examples, though, of the oddities that can appear when you enable the feature – so here’s a simple one to watch out for:

 
select 
	{list of columns} 
from	{table} 
where	rownum = 1 
; 

What difference will setting cursor_sharing to force make for this query?

Here are the “before” and “after” execution plans in 10.2.0.3 (pulled out of memory using the dbms_xplan.display_cursor() procedure) for an example of this type of query. :

 
SQL_ID  16x8y4zkwqhtt, child number 0 
------------------------------------- 
select  /*+ not sharing */  small_vc from t1 where rownum = 1        

Plan hash value: 3836375644 
----------------------------------------------------------- 
| Id  | Operation          | Name | Rows  | Bytes | Cost  | 
----------------------------------------------------------- 
|   0 | SELECT STATEMENT   |      |       |       |     2 | 
|*  1 |  COUNT STOPKEY     |      |       |       |       | 
|   2 |   TABLE ACCESS FULL| T1   |     1 |    11 |     2 | 
----------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   1 - filter(ROWNUM=1) 
. 
. 
. 
SQL_ID  56rbgjy8h1yaa, child number 0 
------------------------------------- 
select  /*+ sharing */  small_vc from t1 where rownum = :"SYS_B_0"        

Plan hash value: 624922415 
------------------------------------------------------------ 
| Id  | Operation           | Name | Rows  | Bytes | Cost  | 
------------------------------------------------------------ 
|   0 | SELECT STATEMENT    |      |       |       |    16 | 
|   1 |  COUNT              |      |       |       |       | 
|*  2 |   FILTER            |      |       |       |       | 
|   3 |    TABLE ACCESS FULL| T1   |  3000 | 33000 |    16 | 
------------------------------------------------------------ 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   2 - filter(ROWNUM=:SYS_B_0)        

The question, of course, is how much difference that extra filter operation at line 2 makes to the performance. If you can’t guess from the plan, you can always re-run the queries with autotrace enabled to capture the execution statistics:

 
Statistics (cursor_sharing = exact) 
---------------------------------------------------------- 
          1  recursive calls 
          0  db block gets 
          3  consistent gets 
          0  physical reads 
          0  redo size 
        419  bytes sent via SQL*Net to client 
        396  bytes received via SQL*Net from client 
          2  SQL*Net roundtrips to/from client 
          0  sorts (memory) 
          0  sorts (disk) 
          1  rows processed 
. 
. 
. 
Statistics (cursor_sharing = force) 
---------------------------------------------------------- 
          1  recursive calls 
          0  db block gets 
         97  consistent gets 
          0  physical reads 
          0  redo size 
        419  bytes sent via SQL*Net to client 
        396  bytes received via SQL*Net from client 
          2  SQL*Net roundtrips to/from client 
          0  sorts (memory) 
          0  sorts (disk) 
          1  rows processed       

In the first run the optimizer could see that it had to stop after one row of the tablescan. But when the rownum target was forced into a bind variable, the optimizer produced a plan that made the entire tablescan happen with a check on every single row returned to see if its row number matched the input bind variable!

This anomaly is fixed in 11g – the filter operation disappears – but if you’ve got code like this in any earlier versions of Oracle, then it needs to be fixed. Funnily enough, changing the predicate from “rownum = 1″ to “rownum <= 1″ is sufficient to allow Oracle to get the right answer with the minimum of work.

There are a couple of other common anomalies that appear when you enable the feature, and I may find time to write about them in the future. In the interim you might want to investigate adding the hint /*+ cursor_sharing_exact */ (which appeared in 9i) to any queries that behave sufficiently badly when you fiddle with the cursor_sharing parameter – this gives you a localised way of turning sharing off.

20 Comments »

  1. I almost got bitten by
    select *
    from parttab
    where col like :a1 escape :a2
    and partkey=:a3

    while having a locally partitioned (list) index on col. Oracle 9i did a range scan, 10g would do a full scan unless either a2 or a3 was a literal, or the index was global. Since the app has a middle layer (vendor independence, ugh) that does its own cursor_sharing=force, I had to un-partition my indexes before migrating to 10g. I haven’t got around to investigating the issue yet, but it seems to be something to watch out for.

    Comment by Flado — March 9, 2008 @ 10:06 pm BST Mar 9,2008 | Reply

  2. How come the Outline information are the same for both queries? Since the execution plans are different, shouldn’t their Outline information be different too?

    I used the dbms_xplan.display_cursor with the ‘advanced’ format as per your March 6, 2008’s blog entry on Dbms_Xplan(3)

    SQL> Select *
    From Table(Dbms_Xplan.Display_Cursor(‘g2u25sckmws3b’,Null,’Advanced’));
    2 SQL_ID g2u25sckmws3b, child number 0
    ————————————-
    select /*+ tst */ pmc_cd, account_num from account where rownum = 1

    Plan hash value: 3971388088

    ——————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ——————————————————————————
    | 0 | SELECT STATEMENT | | | | 2 (100)| |
    |* 1 | COUNT STOPKEY | | | | | |
    | 2 | TABLE ACCESS FULL| ACCOUNT | 1 | 20 | 2 (0)| 00:00:01 |
    ——————————————————————————

    Query Block Name / Object Alias (identified by operation id):
    ————————————————————-

    1 – SEL$1
    2 – SEL$1 / ACCOUNT@SEL$1

    Outline Data
    ————-

    /*+
    BEGIN_OUTLINE_DATA
    IGNORE_OPTIM_EMBEDDED_HINTS
    OPTIMIZER_FEATURES_ENABLE(‘10.2.0.3′)
    OPT_PARAM(‘optimizer_index_cost_adj’ 10)
    OPT_PARAM(‘optimizer_index_caching’ 90)
    OUTLINE_LEAF(@”SEL$1″)
    FULL(@”SEL$1″ “ACCOUNT”@”SEL$1″)
    END_OUTLINE_DATA
    */

    Predicate Information (identified by operation id):
    —————————————————

    1 – filter(ROWNUM=1)

    Column Projection Information (identified by operation id):
    ———————————————————–

    1 – “PMC_CD”[VARCHAR2,64], “ACCOUNT_NUM”[VARCHAR2,17]
    2 – “PMC_CD”[VARCHAR2,64], “ACCOUNT_NUM”[VARCHAR2,17]

    ——————————————–

    SQL> Select *
    From Table(Dbms_Xplan.Display_Cursor(‘a88w7tnav8dvc’,Null,’Advanced’));
    2 SQL_ID a88w7tnav8dvc, child number 0
    ————————————-
    select /*+ tst_bind */ pmc_cd, account_num from account where rownum
    = :bind_val

    Plan hash value: 4109293921

    ——————————————————————————-
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ——————————————————————————-
    | 0 | SELECT STATEMENT | | | | 8396 (100)| |
    | 1 | COUNT | | | | | |
    |* 2 | FILTER | | | | | |
    | 3 | TABLE ACCESS FULL| ACCOUNT | 4027K| 76M| 8396 (2)| 00:01:41 |
    ——————————————————————————-

    Query Block Name / Object Alias (identified by operation id):
    ————————————————————-

    1 – SEL$1
    3 – SEL$1 / ACCOUNT@SEL$1

    Outline Data
    ————-

    /*+
    BEGIN_OUTLINE_DATA
    IGNORE_OPTIM_EMBEDDED_HINTS
    OPTIMIZER_FEATURES_ENABLE(‘10.2.0.3′)
    OPT_PARAM(‘optimizer_index_cost_adj’ 10)
    OPT_PARAM(‘optimizer_index_caching’ 90)
    OUTLINE_LEAF(@”SEL$1″)
    FULL(@”SEL$1″ “ACCOUNT”@”SEL$1″)
    END_OUTLINE_DATA
    */

    Predicate Information (identified by operation id):
    —————————————————

    2 – filter(ROWNUM=:BIND_VAL)

    Column Projection Information (identified by operation id):
    ———————————————————–

    1 – “PMC_CD”[VARCHAR2,64], “ACCOUNT_NUM”[VARCHAR2,17]
    2 – “PMC_CD”[VARCHAR2,64], “ACCOUNT_NUM”[VARCHAR2,17]
    3 – “PMC_CD”[VARCHAR2,64], “ACCOUNT_NUM”[VARCHAR2,17]

    Comment by Milo — March 10, 2008 @ 11:38 pm BST Mar 10,2008 | Reply

  3. Milo,

    Interesting point – I guess the answer is that (a) there is no hint that is the equivalent of “you must filter at this point” and (b) the table order, access methods, join methods, and cardinality arithmetic don’t need to be manipulated for the filter to be relevent – so there is no need to have a directive to filter, it just happens anyway.

    Comment by Jonathan Lewis — March 11, 2008 @ 6:02 am BST Mar 11,2008 | Reply

  4. Flado,

    Thanks for passing on that warning.

    Comment by Jonathan Lewis — March 11, 2008 @ 6:11 am BST Mar 11,2008 | Reply

  5. Dear Jonathan Lewis

    I have a DB where in there are two to three queries with very high version counts .
    viz :- VERSION_COUNT EXECUTIONS SQL_ID
    ————- ———- ————-
    1793 1615 64xajvnup93t7
    1674 1608 gfgq0c6zs5u9b
    790 238 0rjsrk8g87dm5

    cursor_sharing parameter is set to similar .There are no library cache latches or any lock related to library cache observed. Also there are no historgrams on the tables in the queries .Also that i had checked for bind mismatch or differnt data types of bind values passed ,but nothing of such sort is there .

    I suggested to change the value of cursor_sharing=force .Should this help in reducing the version count !! Or is there any other was of solving the issue !!

    All the queries are run through two uses in the database viz :-

    USERNAME USER_ID
    —————————— ———-
    LOSSMS 67
    LOS 62

    Regards
    Amit Bhube

    Comment by Amit Bhube — August 20, 2009 @ 6:05 am BST Aug 20,2009 | Reply

    • When cursor_sharing is set to similar, Oracle will do bind-variable substitution on the incoming code, and then re-optimise the statement anyway if any of several conditions is met:

      a) A predicate references a column for which a histogram exists.

      b) A predicate uses a range operator (such as “between”, “>=”

      c) conditions exist that make the optimizer try dynamic sampling

      d) A predicate references a partitioning column

      I can’t think of any others at present.

      Of course, some of the duplication occurs for other reasons – your different optimising schema, different optimizer environments , differences in length of character inputs, but the range and histogram causes are the commonest.

      If that [range and histogram effects] is the case, then switching to force will address this issue – but may result in some extreme swings in performance because of bind variable peeking.

      Note – having hundreds of versions of the same query means you may end up suffering severe library cache latch contention on the latch protecting the library cache hash bucket holding all those different versions.

      Comment by Jonathan Lewis — August 20, 2009 @ 9:23 pm BST Aug 20,2009 | Reply

      • “If that is the case, then switching to -exact- will address this issue”
        I think you meant ‘force’ not exact.
        I find that users don’t appreciate the unpredictable plans that can happen with cursor_sharing=force and bind variable peeking.

        Comment by hpdba — August 23, 2009 @ 4:20 am BST Aug 23,2009 | Reply

        • hpdba,

          Thanks for that – “force” was what I had intended. Now corrected in the original comment.

          Of course, technically speaking, switching to “exact” will probably make the problem he was asking about go away because all those statements (that look identical because of cursor-sharing) will go back to being unique statements with literals – but I don’t think that would necessarily be a helpful solution.

          Cursor_sharing – in either of its guises – should be viewed as a temporary hack to workaround a design error: wild variation in execution plans is just one of the side effects you have to consider when trying to decide whether or not to use the feature. This is probably why the “counter-hack” of the /*+ cursor_sharing_exact */ hint also appeared in 9i. You might be able to add that to statements which cause problems with cursor_sharing=force.

          Comment by Jonathan Lewis — August 23, 2009 @ 7:20 am BST Aug 23,2009

  6. Dear Jonathan

    On further investegation i came to know

    SQL_ID with very high version count
    ————————————
    64xajvnup93t7 –> Query had 500 Children cursors
    gfgq0c6zs5u9b –> Query had 400 Children cursors
    gfgq0c6zs5u9b –> Query had 450 Children cursors

    For v$sql_shared_cursor for all the three sql_id’s there was
    AUTH_CHECK_MISMATCH,LANGUAGE_MISMATCH …

    Can you tell what can be done in this situation ?
    Also i checked for historic plan_hash_value WRH$_SQL_PLAN and current plan_hash_vlaue
    There was no changes there too ..

    Regards
    Amit

    Comment by Amit Bhube — August 25, 2009 @ 10:21 am BST Aug 25,2009 | Reply

    • Amit,

      A possible cause of the AUTH_CHECK_MISMATCH is that you have multiple schemas with the same objects, so that a piece of SQL that looks the same for two different users is accessing two different sets of objects. If this is the case, there’s nothing (simple) you can do about it.

      The LANGUAGE_MISTMATCH probably means that different users have different setting for NLS_LANGUAGE (or one of the other NLS parameters) which means the SQL statements may have to be optimized in different ways. For an example of how NLS parameters can have a big impact, see this note about the effects of NLS settings.

      Comment by Jonathan Lewis — August 26, 2009 @ 7:28 pm BST Aug 26,2009 | Reply

  7. [...] earlier posting on my blog about one of the little oddities that can appear when you use cursor_sharing. Leave a [...]

    Pingback by Cursor_sharing (2) « Oracle Scratchpad — October 13, 2009 @ 6:24 pm BST Oct 13,2009 | Reply

  8. Jonathan,

    In Which version of 11G filter disappears. It is still there for my 11.1.0.6 on windows

    SQL&gt; select * from v$version
      2  ;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
    PL/SQL Release 11.1.0.6.0 - Production
    CORE    11.1.0.6.0      Production
    TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
    NLSRTL Version 11.1.0.6.0 - Production
    
    SQL&gt; show parameter optimizer_features_enable 
    
    NAME                                 TYPE                             VALUE
    ------------------------------------ -------------------------------- ----------------------------------
    optimizer_features_enable            string                           11.1.0.6
    
    SQL&gt; show parameter cursor_sharing
    
    NAME                                 TYPE                             VALUE
    ------------------------------------ -------------------------------- ----------------------------------
    cursor_sharing                       string                           SIMILAR
    
    SQL&gt; select id from t1 where rownum=1;
    
            ID
    ----------
        128734
    
    SQL&gt; @e2
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------
    -------------------------------------------------
    SQL_ID  cpbxvfq99ntfb, child number 0
    -------------------------------------
    select id from t1 where rownum=:"SYS_B_0"
    
    Plan hash value: 3836375644
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
    |*  1 |  COUNT STOPKEY     |      |       |       |            |          |
    |   2 |   TABLE ACCESS FULL| T1   |     1 |     5 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(ROWNUM=:SYS_B_0)
    
    
    19 rows selected.
    
    SQL&gt; select id from t1 where rownum=2;
    
    no rows selected
    
    SQL&gt; @e2
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------
    -------------------------------------------------
    SQL_ID  cpbxvfq99ntfb, child number 1
    -------------------------------------
    select id from t1 where rownum=:"SYS_B_0"
    
    Plan hash value: 624922415
    
    ----------------------------------------------------------------------------
    | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |       |       |   765 (100)|          |
    |   1 |  COUNT              |      |       |       |            |          |
    |*  2 |   FILTER            |      |       |       |            |          |
    |   3 |    TABLE ACCESS FULL| T1   |  1000K|  4882K|   765   (2)| 00:00:10 |
    ----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter(ROWNUM=:SYS_B_0)
    
    
    20 rows selected.
    

    Comment by coskan — October 21, 2009 @ 9:16 am BST Oct 21,2009 | Reply

    • Coskan,

      I’ll look into that as soon as I can – and see if I can work out why I thought that my statement was true. I’m fairly sure it would have been 11.1.0.6.

      Comment by Jonathan Lewis — October 21, 2009 @ 10:05 am BST Oct 21,2009 | Reply

  9. is there actually a hint cursor_sharing_force available in oracle 11.1.0.x because I have tried that in house and it seems as though such a hint does not exist?

    Comment by Anthony Odukoya — December 16, 2009 @ 9:15 am BST Dec 16,2009 | Reply

  10. [...] another anomaly with cursor_sharing (fixed in 11g) that might be a source of performance [...]

    Pingback by Cursor Sharing 3 « Oracle Scratchpad — May 3, 2010 @ 8:58 pm BST May 3,2010 | Reply

  11. Just another sharing oops:

    SQL> alter session set cursor_sharing = force;
    
    Session altered.
    
    SQL> select /* cursor_sharing */ 1 from dual;
    
             1
    ----------
             1
    
    SQL> select /* cursor_sharing */ null from dual;
    
    N
    -
    
    
    SQL> select /* cursor_sharing */ -1 from dual;
    
            -1
    ----------
            -1
    
    SQL> insert /* cursor_sharing */ into y values (1);
    
    1 row created.
    
    SQL> insert /* cursor_sharing */ into y values (-1);
    
    1 row created.
    
    SQL> insert /* cursor_sharing */ into y values (null);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select sql_text from v$sql where sql_text like '%/* cursor_sharing */%' order by 1;
    
    SQL_TEXT
    ------------------------------------------------------------
    insert /* cursor_sharing */ into y values (-:"SYS_B_0")
    insert /* cursor_sharing */ into y values (:"SYS_B_0")
    insert /* cursor_sharing */ into y values (null)
    select /* cursor_sharing */ -:"SYS_B_0" from dual
    select /* cursor_sharing */ :"SYS_B_0" from dual
    select /* cursor_sharing */ null from dual
    
    6 rows selected.
    

    Comment by Valentin Nikotin — March 12, 2013 @ 12:15 pm BST Mar 12,2013 | Reply

    • Valentin,

      You’re very good at finding entertaining anomalies.

      I wonder if this manages to have some side effects relating to the problems that people can see with bind variable mismatch based on varchar() lengths ?

      Comment by Jonathan Lewis — March 12, 2013 @ 12:31 pm BST Mar 12,2013 | Reply

      • In my case I did some spool-based export script, that creates file with inserts. To reduce parsing I added “cursor_sharing = force” before inserts, but still found a lot of cursors after data had been loaded. I noticed that sql_id was different, so it wasn’t just mismatch issue. When I checked SQL, I got it.

        Comment by Valentin Nikotin — March 12, 2013 @ 2:47 pm BST Mar 12,2013 | Reply

  12. And another funny thing with force sharing. This result is from 11g, but try to check it with 10g – there is no any reason in v$sql_shared_cursor why we have 2 cursors :-)

    SQL> alter session set cursor_sharing = force;
    
    Session altered.
    
    SQL> select /* cursor_sharing2 */ level, -level from dual connect by level < 3 order by 1;
    
         LEVEL     -LEVEL
    ---------- ----------
             1         -1
             2         -2
    
    SQL> select /* cursor_sharing2 */ level, -level from dual connect by level < 3 order by 2;
    
         LEVEL     -LEVEL
    ---------- ----------
             2         -2
             1         -1
    
    SQL> select sql_text,sql_id,plan_hash_value,literal_hash_value from v$sql s where sql_id = '75qbnnd93d3sw';
    
    SQL_TEXT                                                                                                       SQL_ID        PLAN_HASH_VALUE LITERAL_HASH_VALUE
    -------------------------------------------------------------------------------------------------------------- ------------- --------------- ------------------
    select /* cursor_sharing2 */ level, -level from dual connect by level < :"SYS_B_0" order by :"SYS_B_1"         75qbnnd93d3sw      3764016154         2342552567
    select /* cursor_sharing2 */ level, -level from dual connect by level < :"SYS_B_0" order by :"SYS_B_1"         75qbnnd93d3sw      3764016154         2064090006
    
    SQL> select hash_match_failed from v$sql_shared_cursor where sql_id = '75qbnnd93d3sw';
    
    H
    -
    N
    Y
    

    Comment by Valentin Nikotin — March 12, 2013 @ 2:57 pm BST Mar 12,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,909 other followers