[20131217]从sql语句计算sql_id.txt
这个方面的内容从网上能找到许多,从sql语句可以检查出sql_id的值以及HASH_VALUE的值.
http://www.johnnydb.com/2012/03/sql_id-vs-hash_value/
www.dbthink.com/?p=321
自己做一些测试以及总结,记录一些简单的方法在11G下:
SYS@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SYS@test> select sysdate from dual;
SYSDATE
-------------------
2013-12-18 10:03:56
SYS@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7h35uxf5uhmm1, child number 0
-------------------------------------
select sysdate from dual
Plan hash value: 1388734953
-------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
-------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | FAST DUAL | | 1 | 2 (0)|
-------------------------------------------------------
-- sql_id='7h35uxf5uhmm1'.
SYS@test> select sql_id,hash_value from v$sql where sql_id='7h35uxf5uhmm1';
SQL_ID HASH_VALUE
-------------------- ----------
7h35uxf5uhmm1 2343063137
SYS@test> select dbms_sqltune_util0.sqltext_to_sqlid('select sysdate from dual'||chr(0)) sql_id from dual;
SQL_ID
--------------------
7h35uxf5uhmm1
SYS@test> select dbms_utility.sqlid_to_sqlhash('7h35uxf5uhmm1') from dual;
DBMS_UTILITY.SQLID_TO_SQLHASH('7H35UXF5UHMM1')
----------------------------------------------
2343063137
--sql语句后面要补充1个chr(0).正好对上.
在12c上:
SYS@ztest> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SYS@ztest> select dbms_sql_translator.sql_id ('select sysdate from dual') sql_id, dbms_sql_translator.sql_hash ('select sysdate from dual') hash_value from dual;
SQL_ID HASH_VALUE
-------------------- ----------
7h35uxf5uhmm1 2343063137
SYS@ztest> define s='select sysdate from dual';
SYS@ztest> select dbms_sql_translator.sql_id ('&s') sql_id, dbms_sql_translator.sql_hash ('&s') hash_value from dual;
old 1: select dbms_sql_translator.sql_id ('&s') sql_id, dbms_sql_translator.sql_hash ('&s') hash_value from dual
new 1: select dbms_sql_translator.sql_id ('select sysdate from dual') sql_id, dbms_sql_translator.sql_hash ('select sysdate from dual') hash_value from dual
SQL_ID HASH_VALUE
-------------------- ----------
7h35uxf5uhmm1 2343063137
