This discussion is archived
4 Replies Latest reply: Mar 27, 2012 10:43 AM by CharlesHooper RSS

v$sqlarea and plan_hash_value

Guess2 Newbie
Currently Being Moderated
If I have a query with multiple child cursors, I can have more than 1 plan_hash_value for the same sql_id.

how does oracle decide which plan_hash_value to put into v$sqlarea which is a rollup to the sql_id level?
  • 1. Re: v$sqlarea and plan_hash_value
    912595 Expert
    Currently Being Moderated
    Guess2 wrote:
    If I have a query with multiple child cursors, I can have more than 1 plan_hash_value for the same sql_id.
    Yes multiple child cursor can have different plan_hash_values i.e can have different plans.
    how does oracle decide which plan_hash_value to put into v$sqlarea which is a rollup to the sql_id level?
    It depends, oracle decides during oracle optiization phase which plan to pickup and which not to use depending upon the conditions apply to sql query condition maybe like using different bind variable value etc.

    http://hoopercharles.wordpress.com/2009/12/01/sql-plan_hash_value-changes-for-the-same-sql-statement/

    Also see-
    https://blogs.oracle.com/optimizer/entry/whydo_i_have_hundreds_of_child_cursors_when_cursor_sharing_is_set_to_similar_in_10g

    Edited by: 909592 on Mar 27, 2012 1:31 AM - Added Charles link
  • 2. Re: v$sqlarea and plan_hash_value
    Guess2 Newbie
    Currently Being Moderated
    you answered an imaginary question that was not asked.

    v$sqlarea is a rollup of all the child cursors to the SQL_ID. Child Cursors can have multiple plan_hash_values. v$sqlarea has 1 plan_hash_value. So oracle has to decide which plan_hash_value to use in the rollup view. How does it decide?

    When I run

    select text from dba_views where view_name = 'V_$SQL' I get this
    How can it query from v$sql? v$sql is a synonym that points to v_$sql.

    same thing happens with v$sqlarea
    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
    Edited by: Guess2 on Mar 27, 2012 7:17 AM
  • 3. Re: v$sqlarea and plan_hash_value
    912595 Expert
    Currently Being Moderated
    Guess2 wrote:
    you answered an imaginary question that was not asked.

    v$sqlarea is a rollup of all the child cursors to the SQL_ID. Child Cursors can have multiple plan_hash_values. v$sqlarea has 1 plan_hash_value. So oracle has to decide which plan_hash_value to use in the rollup view. How does it decide?
    Ok, I understand your question. Correct me if i'm wrong in interpreting your question.

    Oracle use one SQL plan (plan_hash_value) when running sql statement and which plan is used recently is visible in v$sqlarea. Is that right?

    Now oracle can have multiple child cursor(which further can have different plan built in it), For getting the list of child cursor and its plans, v$sqlarea is not a view for you.
    You would rather check from V$SQLAREA_PLAN_HASH,V$SQL_PLAN etc.

    During sql optimization phase optimizer will choose which child to execute along with its sql plan, once its does that oracle will update v$sqlarea with its latest plan used.It doesn't record historic values for plan.

    I hope i made myself clear.

    In 11g we have concept of sql plan baselines,
    please have a read http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf
  • 4. Re: v$sqlarea and plan_hash_value
    CharlesHooper Expert
    Currently Being Moderated
    Guess2 wrote:
    If I have a query with multiple child cursors, I can have more than 1 plan_hash_value for the same sql_id.

    how does oracle decide which plan_hash_value to put into v$sqlarea which is a rollup to the sql_id level?
    Let's try a bit of an experiment (in 11.2.0.2) to determine which child cursor's PLAN_HASH_VALUE appears in V$SQLAREA. First, we will create a test table with skewed data:
    CREATE TABLE T1 AS
    SELECT
      ROWNUM C1,
      DECODE(ROWNUM,1,1,0) C2,
      LPAD('A',255,'A') C3
    FROM
      DUAL
    CONNECT BY
      LEVEL<=10000;
     
    CREATE UNIQUE INDEX IND_T1_C1 ON T1(C1);
    CREATE INDEX IND_T1_C2 ON T1(C2);
     
    ALTER TABLE T1 MODIFY (C1 NOT NULL, C2 NOT NULL);
     
    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254')
    The initial test:
    SET LINESIZE 120
    SET PAGESIZE 1000
     
    VARIABLE V1 NUMBER
    EXEC :V1:=1
     
    SELECT /*+ BIND_AWARE */
      C1,
      C2,
      C3
    FROM
      T1
    WHERE
      C2=:V1;
     
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
     
    SQL_ID  7p4yxrzwwuybt, child number 0
    -------------------------------------
    SELECT /*+ BIND_AWARE */   C1,   C2,   C3 FROM   T1 WHERE   C2=:V1
     
    Plan hash value: 236868917
     
    -----------------------------------------------------------------------------------------
    | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |   136 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | IND_T1_C2 |     1 |       |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("C2"=:V1)
     
    SELECT
      PLAN_HASH_VALUE
    FROM
      V$SQLAREA
    WHERE
      SQL_ID='7p4yxrzwwuybt';
     
    PLAN_HASH_VALUE
    ---------------
          236868917
    As shown above, the PLAN_HASH_VALUE was set to 236868917, which is the most recently executed child number.

    Trying again with a different bind variable value:
    EXEC :V1:=0
     
    SELECT /*+ BIND_AWARE */
      C1,
      C2,
      C3
    FROM
      T1
    WHERE
      C2=:V1;
     
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
     
    SQL_ID  7p4yxrzwwuybt, child number 1
    -------------------------------------
    SELECT /*+ BIND_AWARE */   C1,   C2,   C3 FROM   T1 WHERE   C2=:V1
     
    Plan hash value: 3617692013
     
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |       |       |    33 (100)|          |
    |*  1 |  TABLE ACCESS FULL| T1   |  9999 |  1327K|    33   (0)| 00:00:01 |
    --------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter("C2"=:V1)
     
    SELECT
      PLAN_HASH_VALUE
    FROM
      V$SQLAREA
    WHERE
      SQL_ID='7p4yxrzwwuybt';
     
    PLAN_HASH_VALUE
    ---------------
         3617692013
    As shown above, the PLAN_HASH_VALUE changed to the value 3617692013, which is the most recently executed child number.

    TRying again without changing the bind variable value:
    SELECT /*+ BIND_AWARE */
      C1,
      C2,
      C3
    FROM
      T1
    WHERE
      C2=:V1;
      
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
     
    SQL_ID  7p4yxrzwwuybt, child number 1
    -------------------------------------
    SELECT /*+ BIND_AWARE */   C1,   C2,   C3 FROM   T1 WHERE   C2=:V1
     
    Plan hash value: 3617692013
     
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |       |       |    33 (100)|          |
    |*  1 |  TABLE ACCESS FULL| T1   |  9999 |  1327K|    33   (0)| 00:00:01 |
    --------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter("C2"=:V1)
     
    SELECT
      PLAN_HASH_VALUE
    FROM
      V$SQLAREA
    WHERE
      SQL_ID='7p4yxrzwwuybt';
     
    PLAN_HASH_VALUE
    ---------------
         3617692013
    As shown above, the PLAN_HASH_VALUE remained at the value 3617692013, which is the most recently executed child number.

    Switching back to the original bind variable value:
    EXEC :V1:=1
     
    SELECT /*+ BIND_AWARE */
      C1,
      C2,
      C3
    FROM
      T1
    WHERE
      C2=:V1;
     
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
     
    SQL_ID  7p4yxrzwwuybt, child number 0
    -------------------------------------
    SELECT /*+ BIND_AWARE */   C1,   C2,   C3 FROM   T1 WHERE   C2=:V1
     
    Plan hash value: 236868917
     
    -----------------------------------------------------------------------------------------
    | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |   136 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | IND_T1_C2 |     1 |       |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("C2"=:V1)
     
    SELECT
      PLAN_HASH_VALUE
    FROM
      V$SQLAREA
    WHERE
      SQL_ID='7p4yxrzwwuybt';
     
    PLAN_HASH_VALUE
    ---------------
          236868917
    As shown above, the PLAN_HASH_VALUE switched back to the original value.

    In 11.2.0.2, V$SQLAREA is based on GV$SQLAREA, which is based on the X$KGLCURSOR_CHILD_SQLID structure. You can execute the following SQL statement to show the columns that are retrieved and the corresponding names of those columns in GV$SQLAREA:
    SELECT
      VIEW_DEFINITION
    FROM
      V$FIXED_VIEW_DEFINITION
    WHERE
      VIEW_NAME='GV$SQLAREA';
     
    DESC GV$SQLAREA
    We are able to confirm that the most recently executed child cursor number will appear in V$SQLAREA with the following SQL statement (I suggest not running this on a very busy database instance):
    SET LINESIZE 120
    SET PAGESIZE 1000
     
    SELECT
      X.KGLOBT03 SQL_ID,
      X.KGLOBT30 PLAN_HASH_VALUE,
      TO_CHAR(X.KGLOBCLA,'HH24:MI:SS') LAST_ACTIVE_TIME,
      S.CHILD_NUMBER,
      S.PLAN_HASH_VALUE S_PLAN_HASH_VALUE,
      TO_CHAR(S.LAST_ACTIVE_TIME,'HH24:MI:SS') S_LAST_ACTIVE_TIME,
      DECODE(X.KGLOBCLA,S.LAST_ACTIVE_TIME,NULL,'DIFFERENT') TIMESTAMP
    FROM
      X$KGLCURSOR_CHILD_SQLID X,
      V$SQL S
    WHERE
      X.KGLOBT02 != 0
      AND X.KGLOBT03=S.SQL_ID
    ORDER BY
      S.SQL_ID,
      S.CHILD_NUMBER;
     
    SQL_ID        PLAN_HASH_VALUE LAST_ACT CHILD_NUMBER S_PLAN_HASH_VALUE S_LAST_A TIMESTAMP
    ------------- --------------- -------- ------------ ----------------- -------- ---------
    fz2htd7p723p5      3775029212 13:01:24            0        3775029212 13:01:24
    fzrshwabvtwc0      3637245398 13:21:28            0        3637245398 13:21:28
    fzt8s1f6kmk5k               0 12:58:30            0                 0 12:58:30
    ...
    g00cj285jmgsw       315182377 13:06:24            0         315182377 12:58:30 DIFFERENT
    g00cj285jmgsw       315182377 13:06:24            1         315182377 13:06:24
    ...
    g3wrkmxkxzhf2       749386351 13:21:24            0         749386351 13:21:24
    g3wrkmxkxzhf2       749386351 13:21:24            1         749386351 12:51:27 DIFFERENT
    ...
    ga9j9xk5cy9s0      1697022209 13:01:24            0        1697022209 13:01:24
    ga9j9xk5cy9s0      1697022209 13:01:24            1        1697022209 12:51:27 DIFFERENT
    ...
    grwydz59pu6mc      3684871272 13:21:24            0        3684871272 13:21:24
    grwydz59pu6mc      3684871272 13:21:24            1        3684871272 12:51:27 DIFFERENT
    ...
    gx4mv66pvj3xz      2570921597 13:21:24            0        1932954096 12:51:27 DIFFERENT
    gx4mv66pvj3xz      2570921597 13:21:24            1        2570921597 13:21:24
    ...
    gzyt498gtbgt5      2826905927 12:51:23            0        2826905927 12:51:23
    As shown above, the child with the most recent LAST_ACTIVE_TIME timestamp appears in V$SQLAREA.

    Charles Hooper
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points