SQL PLAN_HASH_VALUE Changes for the Same SQL Statement

December 1, 2009

In this OTN thread:
http://forums.oracle.com/forums/thread.jspa?messageID=3697025

A user asked the following question:

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

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 (my Toy Project for Performance Tuning) 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

• 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