CURSOR_SHARING : a picture is worth a 1000 words
Anyone who has been around Oracle performance over the years knows the grief that hard parsing SQL queries can cause on highly concurrent applications. The number one reason for hard parsing has been applications that don’t use bind variables. Without bind variables queries that would otherwise be shared get recompiled because their text is different and Oracle treats them as different queries. Oracle addressed this issue with a parameter called cursor_sharing. The parameter cursor_sharing has three values
- exact – the default
- similar – replace literals with bind variables, if a histogram keep literal in place
- force – replace literals with bind variables and use existing plan if it exists
Here is what the load looks like going from the default, exact, to the value force on a load of the same query but a query that doesn’t use bind variables:
looks like a significant load savings – impressive!
Now many people tell me that they think there are bugs with “force” and that you should use “similar”. The value similar does a similar thing but if there are histograms on the column, then Oracle will attempt, in certain cases, to have different plans based on different values. Sounds cool huh? Well their are bugs. Here is the same load with similar:
If we look at the different child cursors for this statement we find that Oracle, instead of sharing the children creates a different one for each execution:
This bug still seems to exist on 11gR2 :
Here is the code for the examples I (run by 8 users on 10g and 12 users on 11g)
--alter session set cursor_sharing=exact;
--alter session set cursor_sharing=force;
--alter session set cursor_sharing=similar;
declare
l_cursor integer default 0;
stmt varchar2(400);
ret number;
BEGIN
select hparse.nextval into ret from dual;
dbms_random.seed(ret);
FOR i IN 1..1000 LOOP
l_cursor:=dbms_sql.open_cursor;
stmt:='SELECT count(*) FROM t1 where c1 < '||
dbms_random.value()||' and c2 < '||dbms_random.value();
execute immediate stmt into ret;
dbms_sql.close_cursor(l_cursor);
END LOOP;
END;
/
Table t1 has no histograms. In the case above it had one row, but results were similar with no rows:
create table t1 (c1 number, c2 number);
insert into t1 values (0,0);
commit;
The issue should be addressed in 11g with a combination of cursor_sharing and adaptive cursor sharing
Also see Charles Hooper’s blog post on this topic at
‘similar’ is so bad its been ditched in 12.
Hi Kyle,
Nice article which clearly demonstrate that a picture is worth 1000 words. We all know that when using statics SQL (PL/SQL stored procedures with input/output parameters) there is a kind of auto-binding which, behind the scene, makes sure we are using bind variables. The problem, however, resides in the manner developers are calling these stored procedures using literal input parameters instead of bind variables input parameters. And in such situations even if we force the cursor sharing parameter value to ‘FORCE’ there is big chance that this change will be of no effect on sharing code as I have explained it in this article
http://hourim.wordpress.com/2011/06/16/bind-variable-shared-pool-and-cursor-sharing-parameter/
cursor sharing when set to force fails to work with pl/sql like BEGIN my_proc(a,b) END;
Best regards
Are’nt relational operators as “<" treated as unsafe predicates and will therefore not be shared with cursor_sharing = SIMILAR intentionally ? So this would rather be an unfortunate example for demonstrating SIMILAR's misbehaviors.