Tuesday, June 8, 2010

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

4 comments:

  1. Kyle,

    I don't think that this is a "bug" per se, but intended behaviour of CURSOR_SHARING=SIMILAR.

    There are more reasons why the child cursor won't be shared than histograms. Basically anything that could lead to a different execution plan might lead to a bind variable being marked as "unsafe" which can be seen from some flag in the 10046 trace file if I remember correctly.

    As far as I know this is the list of reasons that lead to "unsafe" binds:

    - predicates on columns with histograms
    - range based predicates
    - predicates on partition keys
    - Dynamic Sampling

    For more details, see e.g.

    Oracle Forums thread 1

    Oracle Forums thread 2

    So your example above seems to fall into the category "range-based predicates".

    Randolf

    ReplyDelete
  2. Interesting to note that the same problem shows up for equality
    SELECT count(*) FROM t1 where c1=val1 and c2=val2
    As Dion Cho pointed out in one of your links, maybe it has something as well to do with table stats missing, as I didn't create stats on the table.
    Which ever the case, it was definitely considered a bug back when I was at Oracle in 2005. Surprised to see similar is still an issue in 11gR2
    At one point if I recall correctly, a cursor was only allowed to have around 1000 children and after that it got errors, of the genre ORA-600. On 11gR2 I saw up to 2500 child cursors in the above example
    Thanks for the note and links

    ReplyDelete
  3. Kyle,

    I think the main issue with the CURSOR_SHARING is that it creates the child cursors unconditionally if the bind is marked as unsafe - so for each unique literal value you end up with a separate child cursor, but this is independent from the execution plan generated - so you might end up with literally thousand(s) of child cursors all having the same execution plan.

    This is were Adaptive Cursor Sharing is different, since it tries to "group" the input values to minimize the number of child cursors.

    However the potential drawback is that it will need a few "bad" executions to have Adaptive Cursor Sharing kick in which might not be acceptable in all cases since these "bad" executions might bring your system already to a halt.

    Randolf

    ReplyDelete
  4. see also
    http://blogs.oracle.com/optimizer/

    ReplyDelete