SQL PLAN_HASH_VALUE Change When Literals Change?

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

A user asked the following question:

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?

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 in the WHERE clause:

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

The spool file included the following:

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')))

So, the answer is that the SQL PLAN_HASH_VALUE does not change when literals in the WHERE clause change, if the actual execution plan is the same.