Cursor_sharing

[Ed: problem fixed in 11g]

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.