SQL语句的哈希值

Question

当我们在Oracle中执行任何sql语句时，一个哈希值被分配给该sql语句并存储在库缓存中。这样，以后，如果另一个用户请求相同的查询，那么Oracle找到哈希值并执行相同的执行计划。但是，我对哈希值有一个疑问。我的意思是，哈希值是如何生成的?，我的意思是，Oracle服务器是否使用一些算法，或者他们只是将sql字符串转换成一些数值。

因为，我正在阅读Pro Oracle SQL书，上面写着，

select * from employees where department_id = 60;
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;
select /* a_comment */ * from employees where department_id = 60;

将返回不同的哈希值，因为当sql语句执行时，Oracle首先将字符串转换为哈希值。但是，当我尝试这个时，它返回相同的哈希值。

SQL> select * from boats where bid=10;
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2799518614
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    16 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BOATS |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | B_PK  |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("BID"=10)
SQL> SELECT * FROM BOATS WHERE BID=10;
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2799518614
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    16 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BOATS |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | B_PK  |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("BID"=10)

Answer 1

在你的问题文本中，你似乎是在描述sql_id和/或hash_value。这是SQL语句文本的哈希值，Oracle使用它来确定某个SQL语句是否已经存在于共享池中。然而，您在示例中显示的是plan_hash_value，它是为SQL语句生成的计划的哈希值。这两者之间可能存在多对多关系。一个SQL语句(sql_id/hash_value)可以有多个不同的计划(plan_hash_value)，多个不同的SQL语句可以共享同一个计划。

因此，例如，如果我编写两个不同的SQL语句来查询EMP表中的特定行，我将得到相同的plan_hash_value。

SQL> set autotrace traceonly;
SQL> select * from emp where ename = 'BOB';
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    39 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    39 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ENAME"='BOB')

SQL> ed
Wrote file afiedt.buf
  1* select * FROM emp WHERE ename = 'BOB'
SQL> /
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    39 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    39 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ENAME"='BOB')

如果我查看v$sql，但是，我将看到生成了两个不同的sql_id和hash_value值

SQL> set autotrace off;
SQL> ed
Wrote file afiedt.buf
  1  select sql_id, sql_text, hash_value, plan_hash_value
  2    from v$sql
  3   where sql_text like 'select%BOB%'
  4*    and length(sql_text) < 50
SQL> /
SQL_ID        SQL_TEXT                                 HASH_VALUE PLAN_HASH_VALUE
------------- ---------------------------------------- ---------- ---------------
161v96c0v9c0n select * FROM emp WHERE ename = 'BOB'      28618772      3956160932
cvs1krtgzfr78 select * from emp where ename = 'BOB'    1610046696      3956160932

Oracle知道这两个语句是不同的查询，具有不同的sql_id和hash_value哈希值。但它们恰好生成相同的计划，因此它们最终得到相同的plan_hash_value。

Answer 2

我想说你刚刚证明了这本书在这件事上是错的。从理论上讲，它似乎更好的哈希识别概念的SQL语句，而不是一个随机大写的字符串…我希望在生成哈希时也忽略这些评论。: -)

Answer 3

set lines = 300col BEGIN_INTERVAL_TIME for a30从dba_histrongnapshot a, dba_histrongqlstat b中选择a.snap_id, a.begin_interval_time, b.plan_hash_valuesql_id='&sql_id' order by 1;