当我们在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)
解决方法
在您的问题文本中,您似乎在描述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.
