v$sql(hash_value,old_hash_value,plan_hash_value)

执行计划的hash值应该是plan_hash_value 而不是hash_value

SQL> explain plan for select * from oe.orders where rownum<=10;

Explained.

SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 335441244

-----------------------------------------------------------------------------
| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |     |  10 | 370 |   2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY    |     |     |     |   |     |
|   2 |   TABLE ACCESS FULL| ORDERS |  10 | 370 |   2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   1 - filter(ROWNUM<=10)

14 rows selected.

SQL> select sql_text from v$sql where hash_value=335441244;

no rows selected

使用plan_hash_value

SQL> select sql_text,hash_value,sql_id from v$sql where plan_hash_value=335441244;

SQL_TEXT
--------------------------------------------------------------------------------
HASH_VALUE SQL_ID
---------- -------------
select * from oe.orders where rownum<=10
 529271034 avvpgu0gss27u

explain plan for select * from oe.orders where rownum<=10
1872689791 fshtymdrtxymz

explain plan for select * from oe.orders where rownum<=10
1872689791 fshtymdrtxymz

SQL_TEXT
--------------------------------------------------------------------------------
HASH_VALUE SQL_ID
---------- -------------
explain plan for select * from oe.orders where rownum<=10
1872689791 fshtymdrtxymz

SQL>