FAQ
�Hi,

First of all thanks a lot Cheng for the clue.

I just turned-off dynamic sampling and it behaved as expected. Means, 1
version_count in case of similar and exact cursor_sharing setting.

But, even in case of dynamic sampling, table data is not skewed and
histogram is of no use in this case. Thi can be checked even from
plan_hash_value of child cursors. Execution plan is same. Then why Oracle is
creating multiple child.

Disabling the bind value peeking, resulting in three parent cursors. I
didn't know any reason for that.

Neeraj

Search Discussions

  • Randolf Geist at Jul 1, 2009 at 8:41 am
    Some comments:

    Without any statistics and enabled dynamic sampling with CURSOR_SHARING=SIMILAR, one could argument that every distinct literal value passed potentially leads to a different execution plan due to the dynamic sampling performed, therefore this cursor is marked as unsafe to share and you'll get a different child cursor per different literal value passed.

    So CURSOR_SHARING=SIMILAR and dynamic sampling will cause a lot of child cursors, since it's by definition unsafe to share. Note that CURSOR_SHARING similar doesn't mean that you get only a new child cursor if the plans are actually different. You get different child cursors since you *potentially* get different execution plans. It's perfectly valid with CURSOR_SHARING=similar to have dozens of child cursors that share the same plan.

    What you're looking for is (partly) implemented by the Adaptive Cursor Sharing (ACS) introduced in 11g, that attempts to minimize the number of child cursors generated. In fact, with Adaptive Cursor Sharing it's recommended to use CURSOR_SHARING=FORCE instead of SIMILAR to minimize the number of child cursor generated.

    Note however that the ACS doesn't work very well if you have dramatic differences in execution runtime and high aging rate of the SQLs as Kerry Osborne reports here:

    http://kerryosborne.oracle-guy.com/2009/06/oracle-11g-adaptive-cursor-sharing-acs/

    Its current implementation might however work very well if the SQLs stay in the shared pool and you don't suffer from "killer queries" that are using the "wrong" plans.

    2. The case that you've had when changing the CURSOR_SHARING to FORCE and a version of the statement without replaced literals popped up might be the same as observed here:

    http://forums.oracle.com/forums/thread.jspa?messageID=3573497&#3573497

    Try to flush the shared pool twice after changing the CURSOR_SHARING setting.

    3. What I can't answer is why you get no literal replacement when switching bind variable peeking off. I couldn't reproduce in 10.2.0.4 Win32, depending on the order of the steps executed it might be a issue and described above (2.)

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
    Hi,

    First of all thanks a lot Cheng for the clue.

    I just turned-off dynamic sampling and it behaved as expected. Means, 1
    version_count in case of similar and exact cursor_sharing setting.

    1) But, even in case of dynamic sampling, table data is not skewed and
    histogram is of no use in this case. Thi can be checked even from
    plan_hash_value of child cursors. Execution plan is same. Then why Oracle is
    creating multiple child.

    2) Disabling the bind value peeking, resulting in three parent cursors. I
    didn't know any reason for that.
    Neu: WEB.DE Doppel-FLAT mit Internet-Flatrate + Telefon-Flatrate
    für nur 19,99 Euro/mtl.!* http://produkte.web.de/go/02/
  • Neeraj Bhatia at Jul 1, 2009 at 1:05 pm
    �Many thanks Randolf. Your reply really justified the results.

    I just checked that after parsing first version of the query, sql trace of
    second version (i.e. where deptno=20) is resulting in library cache miss.
    Need to re-run the case, and if result is consistent, will definitely share.

    Oracle creating multiple child cursors, in hope that literal replacement may
    result in sub-optimal plan, but at last choose same execution plan, is a
    bizarre thing. It is related to safe and unsafe binding, that i will cover
    next by reading 10046 trace file. It is important to understnad in which
    cases, Oracle treats unsafe binding.

    BTW, while search in metalink, there are numbers of bug hits related to the
    issue.

    Regards,
    Neeraj Bhatia
  • Rich Jesse at Jul 1, 2009 at 1:39 pm

    BTW, while search in metalink, there are numbers of bug hits related to the
    issue.

    Regards,
    Neeraj Bhatia
    FWIW, I've run into a situation without using CS=S on 10.1 and 10.2. The
    interesting part is that v$sql_shared_cursor showed "N" for ALL reasons for
    a few cursors. I had opened an SR with Oracle Support, but didn't get any
    resolution. Here's a query for 10gR1 that can show SQLs with children with
    no reason:

    select sc.address, sc.total, vsa.sql_text
    from
    (
    SELECT address,

    unbound_cursor|| sql_type_mismatch||

    optimizer_mismatch|| outline_mismatch|| stats_row_mismatch||
    literal_mismatch|| sec_depth_mismatch|| explain_plan_cursor||
    buffered_dml_mismatch|| pdml_env_mismatch|| inst_drtld_mismatch||

    slave_qc_mismatch|| typecheck_mismatch|| auth_check_mismatch||
    bind_mismatch|| describe_mismatch|| language_mismatch||
    translation_mismatch|| row_level_sec_mismatch|| insuff_privs||

    insuff_privs_rem|| remote_trans_mismatch|| logminer_session_mismatch||
    incomp_ltrl_mismatch|| overlap_time_mismatch|| sql_redirect_mismatch||
    mv_query_gen_mismatch|| user_bind_peek_mismatch|| typchk_dep_mismatch||

    no_trigger_mismatch|| flashback_cursor|| anydata_transformation||
    incomplete_cursor|| top_level_rpi_cursor|| different_long_length||
    logical_standby_apply|| diff_call_durn|| bind_uacs_diff||
    plsql_cmp_switchs_diff|| cursor_parts_mismatch|| stb_object_mismatch||
    row_ship_mismatch|| pq_slave_mismatch|| top_level_ddl_mismatch||
    multi_px_mismatch|| bind_peeked_pq_mismatch|| litrep_comp_mismatch "FLAGS",
    count(*) "TOTAL"
    FROM v$sql_shared_cursor
    group by
    address,
    unbound_cursor|| sql_type_mismatch||

    optimizer_mismatch|| outline_mismatch|| stats_row_mismatch||
    literal_mismatch|| sec_depth_mismatch|| explain_plan_cursor||
    buffered_dml_mismatch|| pdml_env_mismatch|| inst_drtld_mismatch||

    slave_qc_mismatch|| typecheck_mismatch|| auth_check_mismatch||
    bind_mismatch|| describe_mismatch|| language_mismatch||
    translation_mismatch|| row_level_sec_mismatch|| insuff_privs||

    insuff_privs_rem|| remote_trans_mismatch|| logminer_session_mismatch||
    incomp_ltrl_mismatch|| overlap_time_mismatch|| sql_redirect_mismatch||
    mv_query_gen_mismatch|| user_bind_peek_mismatch|| typchk_dep_mismatch||

    no_trigger_mismatch|| flashback_cursor|| anydata_transformation||
    incomplete_cursor|| top_level_rpi_cursor|| different_long_length||
    logical_standby_apply|| diff_call_durn|| bind_uacs_diff||
    plsql_cmp_switchs_diff|| cursor_parts_mismatch|| stb_object_mismatch||
    row_ship_mismatch|| pq_slave_mismatch|| top_level_ddl_mismatch||
    multi_px_mismatch|| bind_peeked_pq_mismatch|| litrep_comp_mismatch
    having count(*) > 1
    ) "SC", v$sqlarea vsa

    where flags = 'NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN'

    and sc.address = vsa.address
    order by total desc;

    This may work in 10gR2 or 11, but I'm almost positive there are more columns
    for v$sql_shared_cursor in the newer versions of Oracle.

    GL!

    Rich
  • Tanel Poder at Jul 1, 2009 at 9:51 pm
    In 11.1.0.7 there's a new field HASH_MATCH_FAILED in v$sql_shared_cursor
    which covers the "unsafe binds due histograms with cursor_sharing=similar"
    problem case.

    --
    Tanel Poder
    http://blog.tanelpoder.com

    On Wed, Jul 1, 2009 at 4:39 PM, Rich Jesse
    wrote:
    BTW, while search in metalink, there are numbers of bug hits related to the
    issue.

    Regards,
    Neeraj Bhatia
    --
    http://www.freelists.org/webpage/oracle-l
  • Neeraj Bhatia at Jul 2, 2009 at 7:52 am
    Hi Tanel,

    Thanks for the update. I am on 11.1.0.6, will upgrade and check the
    functionality.

    One causual question, how you track version releases and check the
    functionality so quickly. It's really amazing.

    Warm Regards,
    Neeraj.
    On Thu, Jul 2, 2009 at 3:21 AM, Tanel Poder wrote:

    In 11.1.0.7 there's a new field HASH_MATCH_FAILED in v$sql_shared_cursor
    which covers the "unsafe binds due histograms with cursor_sharing=similar"
    problem case.

    --
    Tanel Poder
    http://blog.tanelpoder.com



    On Wed, Jul 1, 2009 at 4:39 PM, Rich Jesse <
    rjoralist_at_society.servebeer.com> wrote:
    BTW, while search in metalink, there are numbers of bug hits related to the
    issue.

    Regards,
    Neeraj Bhatia
    --
    http://www.freelists.org/webpage/oracle-l
  • Tanel Poder at Jul 2, 2009 at 8:30 am
    Two main reasons, either:

    I have hit this problem myself (or a client has hit it), had to
    troublesheoot it and found this solution or piece of information

    I have read about it as someone else has hit such issue and has posted
    about it in a forum like Oracle-L or Oracle Forums. That's why the lists &
    forums are great, you spend 5 minutes skimming through the posts every day
    and when hitting some weird problem, there's a chance you've already heard
    about it, so will troubleshoot faster.
    On Thu, Jul 2, 2009 at 10:52 AM, Neeraj Bhatia wrote:

    Hi Tanel,

    Thanks for the update. I am on 11.1.0.6, will upgrade and check the
    functionality.

    One causual question, how you track version releases and check the
    functionality so quickly. It's really amazing.

    Warm Regards,
    Neeraj.
    On Thu, Jul 2, 2009 at 3:21 AM, Tanel Poder wrote:

    In 11.1.0.7 there's a new field HASH_MATCH_FAILED in v$sql_shared_cursor
    which covers the "unsafe binds due histograms with cursor_sharing=similar"
    problem case.

    --
    Tanel Poder
    http://blog.tanelpoder.com



    On Wed, Jul 1, 2009 at 4:39 PM, Rich Jesse <
    rjoralist_at_society.servebeer.com> wrote:
    BTW, while search in metalink, there are numbers of bug hits related to the
    issue.

    Regards,
    Neeraj Bhatia
    --
    Tanel Poder
    http://blog.tanelpoder.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Randolf Geist at Jul 1, 2009 at 1:42 pm

    I just checked that after parsing first version of the query, sql
    trace of second version (i.e. where deptno=20) is resulting in
    library cache miss. Need to re-run the case, and if result is
    consistent, will definitely share.
    Neeraj,

    just beware of the effect of enabling SQL trace: By enabling SQL Trace you force a library cache miss if the version of the statement *with tracing enabled* is not yet in the library cache, because this setting is part of environment that is checked when looking for existing child cursors. You might want to double-check V$SQL_SHARED_CURSOR to make sure that the miss wasn't caused by simply enabling the trace.

    Depending on the order of execution you need to take that into account for your analysis.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/

    Neu: WEB.DE Doppel-FLAT mit Internet-Flatrate + Telefon-Flatrate
    für nur 19,99 Euro/mtl.!* http://produkte.web.de/go/02/

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 1, '09 at 4:56a
activeJul 2, '09 at 8:30a
posts8
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase