FAQ
Dear All,

Oracle 9.2.0.5.0 RAC on Solaris 8 Nodes, with cursor_sharing = SIMILAR.
Application uses literals.

I was investigating the reason for one particular query having very high
version count and too many child cursors (v$sql_shared_cursor has no
mismatches, and plenty of rows). And found that setting
CURSOR_SHARING=FORCE was one way of avoiding these child cursors. So, I
deduce that, with cursor sharing set to SIMILAR the optimizer peeks at the
bind variables everytime, and comes to the wrong conclusion that it could
possibly arrive at a different plan.

The table has one primary key (LOGIN_ID number), and one unique key (LOGIN
varchar2), which are often queried upon. When I look at the histogram, I
see that login_id has one bucket, and login has 200 buckets. The two
queries in question are:
select * from table where login_id = :"SYS_B_0"
select * from table where login = :"SYS_B_0"

The first one does not create multiple child cursors.
The second one creates multiple child cursors.

I observed that no multiple child cursors are created, when :
1. No stats on the table
2. analyze using "analyze table .... estimate statistics sample 20 percent"
(Creates one bucket each for both the columns)
2. Analyze using dbms_stats with method_opt set to "For all columns size 1"
(Creates one bucket each for both the columns)

And Multiple child cursors are created when:
1. analyze using dbms_stats with method_opt set to "For all columns size
auto"
2. analyze using dbms_stats with method_opt set to "For all columns size
skewonly"

Both of the analyze above creates 200 buckets for the unique key column,
and just one for the primary key column

My questions (if my inferences are right) are :

The optimizer for some reason believes that the unique key values are
skewed, whereas for the primary key the values are not skewed. Why?
The presence of an unique index should tell the optimizer that only one
row should be returned when queried using the unique key predicate? So, why
does the optimizer infer that the plan could change, after peeking at the
bind variables?

Regards
Raj

Search Discussions

  • Jonathan Lewis at Mar 31, 2005 at 11:16 am
    There are many data distributions that could be
    "skewed" without displaying the typical "very
    popular value" syndrome that is so loved of the
    manuals and sound-bite guides. For example,
    if you have 1,000 rows in the range 1 - 2000,
    then a gap, then 1,000 rows in the range 1,000,000
    to 2,000,000 that data is skewed and Oracle may
    choose to create a histogram on it.

    If there is a histogram on any column in the
    WHERE clause, and you have cursor_sharing
    set to similar, then Oracle re-optimizes. That's
    just the way it is (at present). Your observation
    that the this is a query on a column with a unique
    index in place (is there a unique constraint as
    well - might this make a difference) does suggest
    that there had been an oversight in the code path
    that might be fixed on a future release.

    Regards

    Jonathan Lewis

    http://www.jlcomp.demon.co.uk/faq/ind_faq.html
    The Co-operative Oracle Users' FAQ

    http://www.jlcomp.demon.co.uk/seminar.html
    Public Appearances - schedule updated March 9th 2005

    Original Message -----
    From:
    To:
    Sent: Thursday, March 31, 2005 4:59 PM
    Subject: Cursor_sharing - Optimizer - Histograms

    My questions (if my inferences are right) are :

    The optimizer for some reason believes that the unique key values are
    skewed, whereas for the primary key the values are not skewed. Why?
    The presence of an unique index should tell the optimizer that only one
    row should be returned when queried using the unique key predicate? So, why
    does the optimizer infer that the plan could change, after peeking at the
    bind variables?

    Regards
    Raj
  • Wolfgang Breitling at Mar 31, 2005 at 11:24 am

    Rajesh.Rao_at_jpmchase.com wrote:

    Dear All,

    Oracle 9.2.0.5.0 RAC on Solaris 8 Nodes, with cursor_sharing = SIMILAR.
    Application uses literals.


    My questions (if my inferences are right) are :

    1. The optimizer for some reason believes that the unique key values are
    skewed, whereas for the primary key the values are not skewed. Why?
    A bug. I believe it is fixed in 9.2.0.6 Unless my memory fails me, I
    tried that in preparation for my Hotsos presentation on histograms
    2. The presence of an unique index should tell the optimizer that only one
    row should be returned when queried using the unique key predicate? So, why
    does the optimizer infer that the plan could change, after peeking at the
    bind variables?
    a possible bug? Remember that the optimizer only gets involved AFTER the
    cursos_sharing=similar codepath decided that the plan could change and
    it probably only looks at the presence of the histogram as the deciding
    factor.

    As you found out, "for all columns size {auto|skewonly}" is bad. Don't
    do it (btw, "for all indexed columns size {auto|skewonly}" is no
    better). Create histogram specifically and ONLY for those columns where
    you demonstrated a benefit.

    --
    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    www.centrexcc.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Dirschel, Steve at Mar 31, 2005 at 1:20 pm
    We ran into the exact same issue noticing a lot of sql with high version
    counts. After talking to oracle developers who worked on parts of the
    code the bottom line is if CURSOR_SHARING=3DSIMILAR the following will
    happen (with 9i- this is expected behavior):

    If there is a non-equal clause (<, >, BETWEEN) Oracle will reparse
    and you will get a new child
    If there is a histogram on a column in the where clause Oracle will
    reparse and you will get a new child.

    Basically CURSOR_SHARING=3DSIMILAR + HISTOGRAMS can be as bad as
    CURSOR_SHARING=3DEXACT on a system with no bind variables.

    This should be interesting when people upgrade to 10g who are using
    SIMILAR and the default METHOD_OPT- it changes from FOR ALL COLUMNS SIZE
    1 to FOR ALL COLUMNS SIZE AUTO in 10g. Assuming SIMILAR acts the same
    in 10g imagine the possible problems for people not aware of this
    "feature".

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    Rajesh.Rao_at_jpmchase.com
    Sent: Thursday, March 31, 2005 10:00 AM
    To: Oracle-L_at_freelists.org
    Subject: Cursor_sharing - Optimizer - Histograms

    Dear All,

    Oracle 9.2.0.5.0 RAC on Solaris 8 Nodes, with cursor_sharing =3D =
    SIMILAR.

    Application uses literals.

    I was investigating the reason for one particular query having very high
    version count and too many child cursors (v$sql_shared_cursor has no
    mismatches, and plenty of rows). And found that setting
    CURSOR_SHARING=3DFORCE was one way of avoiding these child cursors. So, =
    I
    deduce that, with cursor sharing set to SIMILAR the optimizer peeks at
    the
    bind variables everytime, and comes to the wrong conclusion that it
    could
    possibly arrive at a different plan.

    The table has one primary key (LOGIN_ID number), and one unique key

    (LOGIN

    varchar2), which are often queried upon. When I look at the histogram, I
    see that login_id has one bucket, and login has 200 buckets. The two
    queries in question are:
    select * from table where login_id =3D :"SYS_B_0"
    select * from table where login =3D :"SYS_B_0"

    The first one does not create multiple child cursors.
    The second one creates multiple child cursors.

    I observed that no multiple child cursors are created, when :
    1. No stats on the table
    2. analyze using "analyze table .... estimate statistics sample 20
    percent"
    (Creates one bucket each for both the columns)
    2. Analyze using dbms_stats with method_opt set to "For all columns size
    1"
    (Creates one bucket each for both the columns)

    And Multiple child cursors are created when:
    1. analyze using dbms_stats with method_opt set to "For all columns size
    auto"
    2. analyze using dbms_stats with method_opt set to "For all columns size
    skewonly"

    Both of the analyze above creates 200 buckets for the unique key column,
    and just one for the primary key column

    My questions (if my inferences are right) are :

    The optimizer for some reason believes that the unique key values are
    skewed, whereas for the primary key the values are not skewed. Why?
    The presence of an unique index should tell the optimizer that only
    one
    row should be returned when queried using the unique key predicate? So,
    why
    does the optimizer infer that the plan could change, after peeking at
    the
    bind variables?

    Regards
    Raj

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Rajesh.Rao_at_jpmchase.com at Mar 31, 2005 at 2:12 pm
    Thank to Wolfgang, Jonathan and Steve for the replies.

    Meanwhile, I had opened a TAR with oracle support on this, and I am now
    working on making a test case. If reproducible, it will be filed as a new
    bug under the latest patchset, I am told.

    Regards
    Raj

    "Jonathan Lewis",
    emon.co.uk> cc:
    Sent by: Subject: Re: Cursor_sharing - Optimizer - Histograms
    oracle-l-bounce_at_fr
    eelists.org

    03/31/2005 11:13
    AM
    Please respond to
    jonathan

    There are many data distributions that could be
    "skewed" without displaying the typical "very
    popular value" syndrome that is so loved of the
    manuals and sound-bite guides. For example,
    if you have 1,000 rows in the range 1 - 2000,
    then a gap, then 1,000 rows in the range 1,000,000
    to 2,000,000 that data is skewed and Oracle may
    choose to create a histogram on it.

    If there is a histogram on any column in the
    WHERE clause, and you have cursor_sharing
    set to similar, then Oracle re-optimizes. That's
    just the way it is (at present). Your observation
    that the this is a query on a column with a unique
    index in place (is there a unique constraint as
    well - might this make a difference) does suggest
    that there had been an oversight in the code path
    that might be fixed on a future release.

    Regards

    Jonathan Lewis

    http://www.jlcomp.demon.co.uk/faq/ind_faq.html
    The Co-operative Oracle Users' FAQ

    http://www.jlcomp.demon.co.uk/seminar.html
    Public Appearances - schedule updated March 9th 2005

    Original Message -----
    From:
    To:
    Sent: Thursday, March 31, 2005 4:59 PM
    Subject: Cursor_sharing - Optimizer - Histograms

    My questions (if my inferences are right) are :

    The optimizer for some reason believes that the unique key values are
    skewed, whereas for the primary key the values are not skewed. Why?
    The presence of an unique index should tell the optimizer that only one
    row should be returned when queried using the unique key predicate? So, why
    does the optimizer infer that the plan could change, after peeking at the
    bind variables?

    Regards
    Raj

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 31, '05 at 11:03a
activeMar 31, '05 at 2:12p
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase