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

--//增加更多的显示信息。
$ 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
column kgl_bucket_hex format a14

SELECT '&1' sql_id
      ,hash_value
      ,TO_CHAR (hash_value, 'fmxxxxxxxx')                  hash_hex
      ,MOD (hash_value, 131072)                            kgl_bucket
      ,'0x'||TO_CHAR (MOD (hash_value, 131072), 'fmxxxxx') kgl_bucket_hex
  FROM (SELECT DBMS_UTILITY.SQLID_TO_SQLHASH ('&1') hash_value FROM DUAL);

--//简单测试如下：
SCOTT@book01p> select * from dept where deptno=50;
no rows selected

SCOTT@book01p> @ hashz

 HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
----------- ------------- ------------ ---------- ---------- ------------------- -----------
 1927948053 bswp9zttfn9sp            0      10005  72ea2715  2025-12-21 10:43:08    16777219

SCOTT@book01p> @ s2h bswp9zttfn9sp

SQL_ID         HASH_VALUE HASH_HEX   KGL_BUCKET KGL_BUCKET_HEX
------------- ----------- ---------- ---------- --------------
bswp9zttfn9sp  1927948053 72ea2715        10005 0x2715