这篇文章主要介绍了数据库中如何验证SQL ID与SQL HASH VALUE转换,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。
实验目的:验证SQL ID与SQL HASH VALUE转换
SQL> set linesize 1000
SQL> select count(1) from tab_01;
COUNT(1)
----------
85128
SQL> select * from table(dbms_xplan.display_cursor( null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6mhbq2s7z9ydn, child number 0
-------------------------------------
select count(1) from tab_01
Plan hash value: 4218700832
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 342 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | TABLE ACCESS FULL| TAB_01 | 85128 | 342 (1)| 00:00:01 |
---------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TAB_01@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TAB_01"@"SEL$1")
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
已选择39行。
SQL> select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'select count(1) from tab_01%';
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID HASH_VALUE CHILD_NUMBER
-------------------------- ---------- ------------
select count(1) from tab_01
6mhbq2s7z9ydn 267712948 0
SQL> select lower(trim('6mhbq2s7z9ydn')) sql_id,trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('6mhbq2s7z9ydn ')), level, 1)) - 1) *
2 power(32, length(trim('6mhbq2s7z9ydn ')) - level)), power(2, 32))) hash_value from dual connect by level <= length(trim('6mhbq2s7z9ydn'));
SQL_ID HASH_VALUE
-------------------------- ----------
6mhbq2s7z9ydn 267712948
感谢你能够认真阅读完这篇文章,希望小编分享的“数据库中如何验证SQL ID与SQL HASH VALUE转换”这篇文章对大家有帮助,同时也希望大家多多支持亿速云,关注亿速云行业资讯频道,更多相关知识等着你来学习!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。