[20250511]建立完善s2h.sql脚本.txt

[20250511]建立完善s2h.sql脚本.txt

--//写过一个sql_id 计算hash_value的bash shell脚本,实际上oracle使用dbms_utility.SQLID_TO_SQLHASH就可以实现,

$ cat s2h.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
----------------------------------------------------------------------------------------------
--
-- Name:        s2h.sql
-- Purpose:     display Sql_id of Hash_value
--
-- Author:      lfree
-- Usage:
--     @ s2h <sql_id>
--
-----------------------------------------------------------------------------------------------
column sql_id format a13
column hash_value format 9999999999

select '&1' sql_id,dbms_utility.SQLID_TO_SQLHASH('&1') hash_value FROM DUAL;

--//简单验证:
SCOTT@test01p> select count(*) from dept;
  COUNT(*)
----------
         4

SCOTT@test01p> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
 761178024 1jrz3ucqpx9x8            0      42920      3051237957  2d5ea7a8  2024-05-18 21:10:09    16777223

SCOTT@test01p> @ s2h 1jrz3ucqpx9x8
SQL_ID        HASH_VALUE
------------- ----------
1jrz3ucqpx9x8  761178024