Cursor_sharing

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.