FAQ
When I query v$sql_plan, I see some SQL_IDs with different plan_hash_value.
I thought if the plan changed while the query was cached, it would generate
a child_number.
When would a query generate a new child number, vs. a new plan_hash_value?
I have one query with

2 plan_hash_value

first plan_hash_value has 3 child_numbers
second one has 2.

Search Discussions

  • GovindanK at Mar 23, 2012 at 11:39 pm
    A child number will be generated if AUTH_CHECK_MISMATCH occurs too for the
    same sql_id / query.
    HTH
    GovindanK
    On Fri, Mar 23, 2012 at 12:49 PM, Dba DBA wrote:

    When I query v$sql_plan, I see some SQL_IDs with different plan_hash_value.
    I thought if the plan changed while the query was cached, it would generate
    a child_number.
    When would a query generate a new child number, vs. a new plan_hash_value?
    I have one query with

    2 plan_hash_value

    first plan_hash_value has 3 child_numbers
    second one has 2.


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


    --
    http://www.freelists.org/webpage/oracle-l
  • Sreejith S Nair at Mar 25, 2012 at 11:32 am
    Please see few comments for a similar question posted sometime back

    There are many reasons why a child cursor is created.
    It could be that the optimizer environment differs or because the bind variablen had a different length, ... .
    But, this does not have to mean that a different sql plan will be used.

    You can check the reasons why a certain child exists for a cursor in the v$sql_shared_cursor view.

    Apart from a new 11.2 scalability option, things like adaptive cursor
    sharing and cardinality feedback, and anomalies caused by bugs, a new child
    cursor is generated when a session tries to use a statement that is in the
    library cache and finds that every child cursors for that statement already
    in the cache has a different optimizer environment from the session's
    optimizer environment. It is possible that different optimizer environments
    will, however, still produce the same execution plan.

    A common example of changing the optimizer environment is to enable
    SQL_trace; other options include local changes to workarea_size_policy,
    sort_area_size, db_file_multiblock_read_count, and so on. If you check
    v$sql.optimizer_env_hash_value for the statements you may find that they
    differ.

    Regards,
    Sreejith
    -- Sent from my iPhone
    On 24-Mar-2012, at 5:08 AM, GovindanK wrote:

    A child number will be generated if AUTH_CHECK_MISMATCH occurs too for the
    same sql_id / query.
    HTH
    GovindanK
    On Fri, Mar 23, 2012 at 12:49 PM, Dba DBA wrote:

    When I query v$sql_plan, I see some SQL_IDs with different plan_hash_value.
    I thought if the plan changed while the query was cached, it would generate
    a child_number.
    When would a query generate a new child number, vs. a new plan_hash_value?
    I have one query with

    2 plan_hash_value

    first plan_hash_value has 3 child_numbers
    second one has 2.


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


    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Dba DBA at Mar 27, 2012 at 2:27 pm
    i figured that out. it was monday morning confusion....ignore my last
    email.

    if v$sqlarea is a rollup of v$sql to the sql_id level, how does oracle
    decide which plan_hash_value to have in v$sqlarea? If you have multiple
    child cursors you can have multiple plan_hash_values, but only 1 is chosen
    for v$sqlarea?

    When I look at the definition of v$sql it doesnt make sense? This is from
    10.2.0.5
    It is querying directly from v$sql. v$sql is a synonym that points to
    v_$sql.
    I ran this from sysdba. See definition below.

    I have a 3rd question. I have 1 query with 3 child cursors and 3 different
    plan_hash_values. When I look in v$sql_shared_cursor, all of the flags are
    N.
    How can I have multiple child cursors if none of the reasons in
    v$sql_shared_cursor are Y? It is a very simple query. queries a single
    record on a unique key and joins to another table. End result is 1 record
    back.

    One interesting thing is that 2 of the 3 plans look the same when I query
    them from dbms_xplan.display_cursor. I find it interesting that I can get a
    different plan hash value, but the path looks to be identical. so it has to
    be something else in v$sql_plan that is leading to a plan_hash_value
    change. I have not compared all the fields. I just found this interesting.

    Here is the definition on v$sql


    select
    "SQL_TEXT","SQL_FULLTEXT","SQL_ID","SHARABLE_MEM","PERSISTENT_MEM","RUNTI
    ME_MEM","SORTS","LOADED_VERSIONS","OPEN_VERSIONS","USERS_OPENING","FETCHES","EXE
    CUTIONS","PX_SERVERS_EXECUTIONS","END_OF_FETCH_COUNT","USERS_EXECUTING","LOADS",
    "FIRST_LOAD_TIME","INVALIDATIONS","PARSE_CALLS","DISK_READS","DIRECT_WRITES","BU
    FFER_GETS","APPLICATION_WAIT_TIME","CONCURRENCY_WAIT_TIME","CLUSTER_WAIT_TIME","
    USER_IO_WAIT_TIME","PLSQL_EXEC_TIME","JAVA_EXEC_TIME","ROWS_PROCESSED","COMMAND_
    TYPE","OPTIMIZER_MODE","OPTIMIZER_COST","OPTIMIZER_ENV","OPTIMIZER_ENV_HASH_VALU
    E","PARSING_USER_ID","PARSING_SCHEMA_ID","PARSING_SCHEMA_NAME","KEPT_VERSIONS","
    ADDRESS","TYPE_CHK_HEAP","HASH_VALUE","OLD_HASH_VALUE","PLAN_HASH_VALUE","CHILD_
    NUMBER","SERVICE","SERVICE_HASH","MODULE","MODULE_HASH","ACTION","ACTION_HASH","
    SERIALIZABLE_ABORTS","OUTLINE_CATEGORY","CPU_TIME","ELAPSED_TIME","OUTLINE_SID",
    "CHILD_ADDRESS","SQLTYPE","REMOTE","OBJECT_STATUS","LITERAL_HASH_VALUE","LAST_LO
    AD_TIME","IS_OBSOLETE","CHILD_LATCH","SQL_PROFILE","PROGRAM_ID","PROGRAM_LINE#",
    "EXACT_MATCHING_SIGNATURE","FORCE_MATCHING_SIGNATURE","LAST_ACTIVE_TIME","BIND_D
    ATA","TYPECHECK_MEM" from v$sql

    O
    On Sun, Mar 25, 2012 at 7:31 AM, Sreejith S Nair wrote:


    Please see few comments for a similar question posted sometime back

    There are many reasons why a child cursor is created.
    It could be that the optimizer environment differs or because the bind
    variablen had a different length, ... .
    But, this does not have to mean that a different sql plan will be used.

    You can check the reasons why a certain child exists for a cursor in the
    v$sql_shared_cursor view.

    Apart from a new 11.2 scalability option, things like adaptive cursor
    sharing and cardinality feedback, and anomalies caused by bugs, a new
    child
    cursor is generated when a session tries to use a statement that is in
    the
    library cache and finds that every child cursors for that statement
    already
    in the cache has a different optimizer environment from the session's
    optimizer environment. It is possible that different optimizer
    environments
    will, however, still produce the same execution plan.

    A common example of changing the optimizer environment is to enable
    SQL_trace; other options include local changes to workarea_size_policy,
    sort_area_size, db_file_multiblock_read_count, and so on. If you check
    v$sql.optimizer_env_hash_value for the statements you may find that they
    differ.

    Regards,
    Sreejith
    -- Sent from my iPhone

    On 24-Mar-2012, at 5:08 AM, GovindanK wrote:

    A child number will be generated if AUTH_CHECK_MISMATCH occurs too for the
    same sql_id / query.
    HTH
    GovindanK

    On Fri, Mar 23, 2012 at 12:49 PM, Dba DBA <oracledbaquestions@gmail.com
    wrote:
    When I query v$sql_plan, I see some SQL_IDs with different
    plan_hash_value.

    I thought if the plan changed while the query was cached, it would
    generate

    a child_number.

    When would a query generate a new child number, vs. a new
    plan_hash_value?

    I have one query with


    2 plan_hash_value


    first plan_hash_value has 3 child_numbers

    second one has 2.



    --

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






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


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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 23, '12 at 7:50p
activeMar 27, '12 at 2:27p
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase