[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.
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 GMT Mar 9,2008 |
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 GMT Mar 10,2008 |
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 GMT Mar 11,2008 |
Flado,
Thanks for passing on that warning.
Comment by Jonathan Lewis — March 11, 2008 @ 6:11 am GMT Mar 11,2008 |
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 |
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:
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 |
“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 |
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
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 |
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 |
[…] 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 |
Jonathan,
In Which version of 11G filter disappears. It is still there for my 11.1.0.6 on windows
Comment by coskan — October 21, 2009 @ 9:16 am BST Oct 21,2009 |
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 |
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 GMT Dec 16,2009 |
Anthony,
No, there isn’t – even in 11.2
For a list of valid hints you can query v$sql_hint in 11g.
Comment by Jonathan Lewis — December 16, 2009 @ 1:54 pm GMT Dec 16,2009 |
[…] 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 |
Just another sharing oops:
Comment by Valentin Nikotin — March 12, 2013 @ 12:15 pm GMT Mar 12,2013 |
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 GMT Mar 12,2013 |
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 GMT Mar 12,2013 |
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 :-)
Comment by Valentin Nikotin — March 12, 2013 @ 2:57 pm GMT Mar 12,2013 |