4 Replies Latest reply: Aug 17, 2009 10:45 AM by Charles Hooper RSS

    How to identify sqls of with execution plan but different in plan hash val

    551752
      Dear Gurus,

      Is there any way to identify the sqls with same execution plan,
      for us the problem is,
      sql is submitted with parameter hardcoded in where clause, which changes the sqlid and plan hash value, as the plan hash value changes but the infact plan remains same,
      i want to identify such sqlid?
        • 1. Re: How to identify sqls of with execution plan but different in plan hash val
          Charles Hooper
          user548749 wrote:
          Dear Gurus,

          Is there any way to identify the sqls with same execution plan,
          for us the problem is,
          sql is submitted with parameter hardcoded in where clause, which changes the sqlid and plan hash value, as the plan hash value changes but the infact plan remains same,
          i want to identify such sqlid?
          I am not sure that I understand. The PLAN_HASH_VALUE should remain the same if the plan is the same, even if hardcoded values are specified in the WHERE clause. For example, a table T1 with an ID column and a DESCRIPTION column with an index on the ID column:

          First, four SQL statements are executed with different values specified for ID:
          SELECT /*+ TEST FIND ME */ 1 FROM T1 WHERE ID=1;
          SELECT /*+ TEST FIND ME */ 2 FROM T1 WHERE ID=2;
          SELECT /*+ TEST FIND ME */ 3 FROM T1 WHERE ID=3;
          SELECT /*+ TEST FIND ME */ 4 FROM T1 WHERE ID=4;
          Now we search the V$SQL view for the SQL_IDs and PLAN_HASH_VALUEs:
          SELECT
            SQL_ID,
            CHILD_NUMBER,
            PLAN_HASH_VALUE
          FROM
            V$SQL
          WHERE
            SQL_TEXT LIKE '% TEST FIND ME %';
          
          SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE
          ------------- ------------ ---------------
          fbvgxdbddnush            0      1882569892
          5f0415sr6d7qb            0      1882569892
          0fpndy65tykxv            0      1882569892
          5jvj4jyzyb9jt            0      1882569892
          0kjp2dk2vbpra            0       903671040
          
          SELECT
            SQL_TEXT
          FROM
            V$SQL
          WHERE
            SQL_ID='0kjp2dk2vbpra';
          
          SQL_TEXT
          --------------------------------------------------------------------------------
          SELECT   SQL_ID,   CHILD_NUMBER,   PLAN_HASH_VALUE FROM   V$SQL WHERE   SQL_TEXT
           LIKE '% TEST FIND ME %'
          The only SQL statement with a different plan hash value is the SQL statement which performed the query on V$SQL.

          Next, we retrieve the plans for the SQL statements:
          SET LINESIZE 150
          SET PAGESIZE 2000
          SPOOL TEST_MY_PLANS.TXT
          
          SELECT /*+ LEADING(S) */
            T.PLAN_TABLE_OUTPUT
          FROM
            (SELECT
              SQL_ID,
              CHILD_NUMBER
            FROM
              V$SQL
            WHERE
              SQL_TEXT LIKE '% TEST FIND ME %') S,
            TABLE(DBMS_XPLAN.DISPLAY_CURSOR(S.SQL_ID,S.CHILD_NUMBER,'TYPICAL')) T;
          
          SPOOL OFF
          
          
          
          SQL_ID  fbvgxdbddnush, child number 0
          -------------------------------------
          SELECT /*+ TEST FIND ME */ 3 FROM T1 WHERE ID=3
          
          Plan hash value: 1882569892
          
          ---------------------------------------------------------------------------
          | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
          ---------------------------------------------------------------------------
          |   0 | SELECT STATEMENT |        |       |       |    24 (100)|          |
          |*  1 |  INDEX RANGE SCAN| IND_T1 | 10000 | 40000 |    24   (0)| 00:00:01 |
          ---------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
             1 - access("ID"=3)
          
          
          SQL_ID  5f0415sr6d7qb, child number 0
          -------------------------------------
          SELECT /*+ TEST FIND ME */ 2 FROM T1 WHERE ID=2
          
          Plan hash value: 1882569892
          
          ---------------------------------------------------------------------------
          | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
          ---------------------------------------------------------------------------
          |   0 | SELECT STATEMENT |        |       |       |    24 (100)|          |
          |*  1 |  INDEX RANGE SCAN| IND_T1 | 10000 | 40000 |    24   (0)| 00:00:01 |
          ---------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
             1 - access("ID"=2)
          
          
          SQL_ID  0fpndy65tykxv, child number 0
          -------------------------------------
          SELECT /*+ TEST FIND ME */ 4 FROM T1 WHERE ID=4
          
          Plan hash value: 1882569892
          
          ---------------------------------------------------------------------------
          | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
          ---------------------------------------------------------------------------
          |   0 | SELECT STATEMENT |        |       |       |    24 (100)|          |
          |*  1 |  INDEX RANGE SCAN| IND_T1 | 10000 | 40000 |    24   (0)| 00:00:01 |
          ---------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
             1 - access("ID"=4)
          
          
          SQL_ID  5jvj4jyzyb9jt, child number 0
          -------------------------------------
          SELECT /*+ TEST FIND ME */ 1 FROM T1 WHERE ID=1
          
          Plan hash value: 1882569892
          
          ---------------------------------------------------------------------------
          | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
          ---------------------------------------------------------------------------
          |   0 | SELECT STATEMENT |        |       |       |    24 (100)|          |
          |*  1 |  INDEX RANGE SCAN| IND_T1 | 10000 | 40000 |    24   (0)| 00:00:01 |
          ---------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
             1 - access("ID"=1)
          
          
          SQL_ID  0kjp2dk2vbpra, child number 0
          -------------------------------------
          SELECT   SQL_ID,   CHILD_NUMBER,   PLAN_HASH_VALUE FROM   V$SQL WHERE
          SQL_TEXT LIKE '% TEST FIND ME %'
          
          Plan hash value: 903671040
          
          ---------------------------------------------------------------------------
          | Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)|
          ---------------------------------------------------------------------------
          |   0 | SELECT STATEMENT |                   |       |       |     1 (100)|
          |*  1 |  FIXED TABLE FULL| X$KGLCURSOR_CHILD |     1 |   549 |     0   (0)|
          ---------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
             1 - filter(("KGLNAOBJ" LIKE '% TEST FIND ME %' AND
                        "INST_ID"=USERENV('INSTANCE')))
          As I stated, I am not sure I understand what you are seeing.

          Charles Hooper
          IT Manager/Oracle DBA
          K&M Machine-Fabricating, Inc.
          • 2. Re: How to identify sqls of with execution plan but different in plan hash val
            551752
            thanks for the clarification:
            but how would you explain the following

            select snap_id,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='cmrh5mk9854vs'

            SNAP_ID     SQL_ID     PLAN_HASH_VALUE
            13487     cmrh5mk9854vs     3314988613
            14471     cmrh5mk9854vs     3452699090


            Two different hash values for the same same sqls
            thanks
            • 3. Re: How to identify sqls of with execution plan but different in plan hash val
              Timur Akhmadeev
              Two different plan hash values for the same same sqls
              That's because your SQL had two structurally different plans.
              • 4. Re: How to identify sqls of with execution plan but different in plan hash val
                Charles Hooper
                user548749 wrote:
                thanks for the clarification:
                but how would you explain the following

                select snap_id,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='cmrh5mk9854vs'

                SNAP_ID     SQL_ID     PLAN_HASH_VALUE
                13487     cmrh5mk9854vs     3314988613
                14471     cmrh5mk9854vs     3452699090


                Two different hash values for the same same sqls
                thanks
                Timur is correct that because the PLAN_HASH_VALUE is different, the plans are different for the same SQL statement. I believe that your real question is why does that happen?

                There are several reasons for the change in plan, here is a demonstration on Oracle 10.2.0.2:
                CREATE TABLE T1 (
                  C1 VARCHAR2(20),
                  C2 VARCHAR2(255),
                  PRIMARY KEY (C1));
                
                INSERT INTO T1
                SELECT
                  TO_CHAR(ROWNUM),
                  LPAD(CHR(65 + ROUND(ROWNUM/1000)),255,CHR(65 + ROUND(ROWNUM/1000)))
                FROM
                  DUAL
                CONNECT BY
                  LEVEL<=1000000;
                
                COMMIT;
                
                EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)
                The table created by the above contains 1,000,000 rows with an index on column C1. Column C1 is defined as a VARCHAR2, but I placed numbers into the column. Now for the first part of the test, where I request that Oracle display the execution plan for a SQL statement without actually executing the SQL statement:
                SET AUTOTRACE TRACEONLY EXPLAIN
                
                VARIABLE N1 NUMBER
                VARIABLE N2 NUMBER
                
                SELECT
                  C1,
                  C2
                FROM
                  T1
                WHERE
                  C1 BETWEEN :N1 AND :N2;
                
                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 3390613643
                ---------------------------------------------------------------------------------------------
                | Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
                ---------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT             |              |  2500 |   639K|  1001   (1)| 00:00:05 |
                |*  1 |  FILTER                      |              |       |       | |          |
                |   2 |   TABLE ACCESS BY INDEX ROWID| T1           |  2500 |   639K|  1001   (1)| 00:00:05 |
                |*  3 |    INDEX RANGE SCAN          | SYS_C0020503 |  4500 |       |    20   (0)| 00:00:01 |
                ---------------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                   1 - filter(:N1<=:N2)
                   3 - access("C1">=:N1 AND "C1"<=:N2)
                
                
                
                VARIABLE N1 VARCHAR2(20)
                VARIABLE N2 VARCHAR2(20)
                
                EXEC :N1 := '1'
                EXEC :N2 := '9999'
                
                SELECT
                  C1,
                  C2
                FROM
                  T1
                WHERE
                  C1 BETWEEN :N1 AND :N2;
                
                
                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 3390613643
                
                ---------------------------------------------------------------------------------------------
                | Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
                ---------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT             |              |  2500 |   639K|  1001   (1)| 00:00:05 |
                |*  1 |  FILTER                      |              |       |       | |          |
                |   2 |   TABLE ACCESS BY INDEX ROWID| T1           |  2500 |   639K|  1001   (1)| 00:00:05 |
                |*  3 |    INDEX RANGE SCAN          | SYS_C0020503 |  4500 |       |    20   (0)| 00:00:01 |
                ---------------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                   1 - filter(:N1<=:N2)
                   3 - access("C1">=:N1 AND "C1"<=:N2)
                The first execution plan displayed is incorrect. I have a utility which retrieves all plans for a SQL_ID using DBMS_XPLAN and also checks V$SQL_SHARED_CURSOR and V$SQL_BIND_METADATA. This is the output:
                SQL_ID  cguuvrry2hx1z, child number 0
                -------------------------------------
                SELECT   C1,   C2 FROM   T1 WHERE   C1 BETWEEN :N1 AND :N2
                 
                Plan hash value: 3332582666
                 
                ------------------------------------------------------------------------------------
                | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
                ------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT   |      |       |       |  1970 (100)|          |        |
                |*  1 |  FILTER            |      |       |       |            |          |        |
                |*  2 |   TABLE ACCESS FULL| T1   |  2500 |   639K|  1970   (5)| 00:00:10 |    CHK |
                ------------------------------------------------------------------------------------
                 
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                   1 - filter(:N1<=:N2)
                   2 - filter((TO_NUMBER("C1")>=:N1 AND TO_NUMBER("C1")<=:N2))
                 
                
                Bind Descriptions:
                  Pos  MLen  Data Type   Bind Name
                    1    22  NUMBER      N1
                    2    22  NUMBER      N2
                
                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                BIND_MISMATCH - The bind metadata does not match the existing child cursor.
                
                SQL_ID  cguuvrry2hx1z, child number 1
                -------------------------------------
                SELECT   C1,   C2 FROM   T1 WHERE   C1 BETWEEN :N1 AND :N2
                 
                Plan hash value: 3390613643
                 
                ------------------------------------------------------------------------------------------------------
                | Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
                ------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT             |              |       |       |  1001 (100)|          |        |
                |*  1 |  FILTER                      |              |       |       |            |          |        |
                |   2 |   TABLE ACCESS BY INDEX ROWID| T1           |  2500 |   639K|  1001   (1)| 00:00:05 |    CHK |
                |*  3 |    INDEX RANGE SCAN          | SYS_C0020503 |  4500 |       |    20   (0)| 00:00:01 |    CHK |
                ------------------------------------------------------------------------------------------------------
                 
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                   1 - filter(:N1<=:N2)
                   3 - access("C1">=:N1 AND "C1"<=:N2)
                 
                
                Bind Descriptions:
                  Pos  MLen  Data Type   Bind Name
                    1    32  VARCHAR2    N1
                    2    32  VARCHAR2    N2
                Notice the Predicate Information and Bind Descriptions for the first execution plan. Child #1 was created due to "The bind metadata does not match the existing child cursor". Now another test:
                ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=1000;

                SELECT
                C1,
                C2
                FROM
                T1
                WHERE
                C1 BETWEEN :N1 AND :N2;

                Statistics
                ----------------------------------------------------------
                1 recursive calls
                0 db block gets
                102765 consistent gets
                36619 physical reads
                0 redo size
                22387973 bytes sent via SQL*Net to client
                733583 bytes received via SQL*Net from client
                66661 SQL*Net roundtrips to/from client
                0 sorts (memory)
                0 sorts (disk)
                999890 rows processed

                This is the output from my utility program which shows another child cursor:
                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                OPTIMIZER_MISMATCH - The optimizer environment does not match the existing child cursor.
                
                SQL_ID  cguuvrry2hx1z, child number 2
                -------------------------------------
                SELECT   C1,   C2 FROM   T1 WHERE   C1 BETWEEN :N1 AND :N2
                 
                Plan hash value: 3332582666
                 
                ------------------------------------------------------------------------------------
                | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
                ------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT   |      |       |       |  1964 (100)|          |        |
                |*  1 |  FILTER            |      |       |       |            |          |        |
                |*  2 |   TABLE ACCESS FULL| T1   |   999K|   249M|  1964   (5)| 00:00:10 |    CHK |
                ------------------------------------------------------------------------------------
                 
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                 
                   1 - filter(:N1<=:N2)
                   2 - filter(("C1"<=:N2 AND "C1">=:N1))
                 
                
                Bind Descriptions:
                  Pos  MLen  Data Type   Bind Name
                    1    32  VARCHAR2    N1
                    2    32  VARCHAR2    N2
                Notice the reason for child #2 being created "The optimizer environment does not match the existing child cursor".

                I then disconnected from the database instance and reconnected to reset the OPTIMIZER_INDEX_COST_ADJ parameter to the system default. One more test, gather statistics forcing current plans to be invalidated (there is usually a delay when statistics are gathered before plans accessing those objects are invalidated):
                EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE, NO_INVALIDATE=>FALSE)
                
                SET AUTOTRACE TRACEONLY STATISTICS
                
                VARIABLE N1 VARCHAR2(20)
                VARIABLE N2 VARCHAR2(20)
                
                EXEC :N1 := '1'
                EXEC :N2 := '9999'
                
                SELECT
                  C1,
                  C2
                FROM
                  T1
                WHERE
                  C1 BETWEEN :N1 AND :N2;
                
                Statistics
                ----------------------------------------------------------
                          1  recursive calls
                          0  db block gets
                     102765  consistent gets
                      35424  physical reads
                          0  redo size
                   22387973  bytes sent via SQL*Net to client
                     733583  bytes received via SQL*Net from client
                      66661  SQL*Net roundtrips to/from client
                          0  sorts (memory)
                          0  sorts (disk)
                     999890  rows processed
                My utility program now shows that there is a single plan, which was affected by bind peeking.
                SQL_ID  cguuvrry2hx1z, child number 0
                -------------------------------------
                SELECT   C1,   C2 FROM   T1 WHERE   C1 BETWEEN :N1 AND :N2
                 
                Plan hash value: 3332582666
                 
                ------------------------------------------------------------------------------------
                | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
                ------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT   |      |       |       |  1964 (100)|          |        |
                |*  1 |  FILTER            |      |       |       |            |          |        |
                |*  2 |   TABLE ACCESS FULL| T1   |   999K|   249M|  1964   (5)| 00:00:10 |    CHK |
                ------------------------------------------------------------------------------------
                 
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                   1 - filter(:N1<=:N2)
                   2 - filter(("C1"<=:N2 AND "C1">=:N1))
                 
                
                Bind Descriptions:
                  Pos  MLen  Data Type   Bind Name
                    1    32  VARCHAR2    N1
                    2    32  VARCHAR2    N2
                Follow-up:
                I had originally planned to mention the Oracle 11g introduced adaptive cursor sharing, which is another source of different plans for the same SQL statement. So, in summary some of the sources of the plan changing:
                * Parsing a SQL statement with the bind variable values not defined
                * Parsing a SQL statement with the bind variables defined as the wrong type (or specified as a different type)
                * Statistics collection forcing the invalidation of cursors, which forces a new hard parse on the next execution (on 10g and above, statistics collection does not cause an immediate invalidation of cursors accessing objects for which statistics were collected)
                * Changes in the statistics, or even lack of change in the statistics with supplied values in the SQL statement appearing to be increasingly above or below the maximum and minimum values for the columns.
                * Bind peeking, where different bind variable values were specified on each hard parse
                * Different optimizer environment for the session executing the SQL statement
                * Adaptive cursor sharing in 11g

                Charles Hooper
                IT Manager/Oracle DBA
                K&M Machine-Fabricating, Inc.

                Edited by: Charles Hooper on Aug 17, 2009 11:35 AM
                Added Follow-up section