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> s elect 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