sql 语句的哈希值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16011976/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
hash value for sql statement
提问by Ravi
When we execute any sql statement in Oracle, a hash value is being assigned to that sql statement and stored into the library cache. So, that later, if another user request the same query, then Oracle find the hash value and execute the same execution plan. But, I have one doubt about the hash value. I mean, how hash value gets generated ?, I mean, whether Oracle server uses some algorithms or they just convert the sql string into some numeric value.
当我们在 Oracle 中执行任何 sql 语句时，都会为该 sql 语句分配一个哈希值并存储到库缓存中。因此，稍后，如果另一个用户请求相同的查询，那么 Oracle 会找到哈希值并执行相同的执行计划。但是，我对哈希值有一个疑问。我的意思是，哈希值是如何生成的？，我的意思是，Oracle 服务器是否使用某些算法，或者它们只是将 sql 字符串转换为某个数值。
Since, I was reading Pro Oracle SQLbook, on which it is written that,
因为，我正在阅读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;
will return different hash value, because when sql statement executed, then Oracle first converts the string to a hash value. But, when i tried this, then it return same hash value.
会返回不同的hash值，因为在sql语句执行的时候，Oracle先把字符串转换成hash值。但是，当我尝试这样做时，它返回相同的哈希值。
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)
回答by Justin Cave
In the text of your question, you appear to be describing the sql_idand/or the hash_value. This is the hash of the text of the SQL statement and is what Oracle uses to determine whether a particular SQL statement already exists in the shared pool. What you are showing in your example, however, is the plan_hash_valuewhich is the hash of the plan that is generated for the SQL statement. There is, potentially, a many-to-many relationship between the two. A single SQL statement (sql_id/ hash_value) can have multiple different plans (plan_hash_value) and multiple different SQL statements can share the same plan.
在您的问题文本中，您似乎在描述sql_id和/或hash_value. 这是 SQL 语句文本的散列值，Oracle 使用它来确定共享池中是否已存在特定 SQL 语句。但是，您在示例中显示的plan_hash_value是为 SQL 语句生成的计划的哈希值。两者之间可能存在多对多关系。单个 SQL 语句 ( sql_id/ hash_value) 可以有多个不同的计划 ( plan_hash_value)，多个不同的 SQL 语句可以共享同一个计划。
So, for example, if I write two different SQL statements that are querying a particular row from the EMPtable, I'll get the same plan_hash_value.
因此，例如，如果我编写两个不同的 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')
If I look in v$sql, however, I'll see that two different sql_idand hash_valuevalues were generated
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 recognizes that these two statements are different queries with different sql_idand hash_valuehashes. But they both happen to generate the same plan so they end up with the same plan_hash_value.
Oracle 认识到这两个语句是具有不同sql_id和hash_value散列的不同查询。但他们都碰巧产生了相同的计划，所以他们最终得到了相同的plan_hash_value.
回答by C.B.
I would say that you just proved that the book is wrong in this case. And theoretically it seems better to have the hash indentify the conceptual SQL statement instead of a randomly-capitalized string... And i hope the comments get ignored too when generating the hash. ;-)
我会说你刚刚证明了这本书在这种情况下是错误的。从理论上讲，让散列标识概念性 SQL 语句而不是随机大写的字符串似乎更好……我希望在生成散列时也可以忽略注释。;-)
回答by Selvaraj Prakash
set lines 300 col BEGIN_INTERVAL_TIME for a30 select a.snap_id, a.begin_interval_time, b.plan_hash_value from dba_hist_snapshot a, dba_hist_sqlstat b where a.snap_id=b.snap_id and b.sql_id='&sql_id' order by 1;
设置行 300 col BEGIN_INTERVAL_TIME for a30 select a.snap_id, a.begin_interval_time, b.plan_hash_value from dba_hist_snapshot a, dba_hist_sqlstat b where a.snap_id=b.snap_id and b.sql_id='&sql_id' order
