Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to verify the conversion between SQL ID and SQL HASH VALUE in the database, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

Purpose: to verify the conversion between SQL ID and 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 | 342100 | | |

| | 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 lines have been selected.

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 (sum ((instr (' 0123456789abcdfghjkmpqrstuvwydnpqr (trim ('6mhbq2s7z9ydn'), level, 1))-1) *

2 power (32, length (trim ('6mhbq2s7z9ydn'))-level), power (2,32)) hash_value from dual connect by level

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.