[20240824]跟踪library cache lock library cache pin使用gdb.txt

[20240824]跟踪library cache lock library cache pin使用gdb.txt

--//这几天一直想写一个gdb脚本实现这个功能,先开始自己尝试,遇到一些问题,冷静下来看了以前的学习笔记,网上查了相关链接,能找到
--//的资源很少:
--//https://nenadnoveljic.com/blog/tracing-library-cache-locks/
--//https://nenadnoveljic.com/blog/library-cache-lock-debugger/
--//https://nenadnoveljic.com/blog/library-cache-lock-object-name/
--//https://mvelikikh.blogspot.com/2021/02/tracing-library-cache-locks-using.html

--//记忆里当时测试过链接https://nenadnoveljic.com/blog/library-cache-lock-debugger/,在11g失败,不知道为什么.
--//我当时想也许作者使用不是intel cpu,前面https://nenadnoveljic.com/blog/tracing-library-cache-locks/使用dtrace.
--//我个人不熟悉dtrace,实际上gdb也不熟悉,这类工具不做internal的研究基本很少使用。

--//我决定在21c下重复再次尝试看看:

1.环境:
SCOTT@book01p> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

create table t (n1 integer,n2 integer);
create index ix_t on t(n1,n2);
exec dbms_stats.gather_table_stats (null, 'T', cascade => true ) ;

2.测试:
$ cat lcl.gdb
break kgllkal if $rcx==3
commands 1
backtrace
p/x $rdx
finish
end
--//注:原始链接使用的是p/z $rdx,我测试报错,修改为p/x $rdx,所以我猜测他使用环境可能不是intel cpu系列。

--//session 1:
SCOTT@book01p> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID                               PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------------------------------ ------- ---------- --------------------------------------------------
       153      14481 3573                     DEDICATED 3575                                93          3 alter system kill session '153,14481' immediate;

--//session 2:
$ rlgdb -f -p  3575 -x lcl.gdb

--//session 1:
SCOTT@book> alter index ix_t invisible ;

--//session 2:
Breakpoint 1 at 0x15367e90
(gdb) c
Continuing.

Breakpoint 1, 0x0000000015367e90 in kgllkal ()
#0  0x0000000015367e90 in kgllkal ()
#1  0x0000000015363ad0 in kglLock ()
#2  0x000000001535e178 in kglget ()
#3  0x00000000039d07d6 in kkdllk0 ()
#4  0x00000000039ee51e in kkdllppac0 ()
#5  0x000000000bbd0ea3 in aindrv ()
#6  0x0000000014efc7f2 in opiexe ()
#7  0x00000000151ca093 in opiosq0 ()
#8  0x0000000014f6f166 in kpooprx ()
#9  0x0000000014f6ca1b in kpoal8 ()
#10 0x0000000014eefdf8 in opiodr ()
#11 0x0000000015294359 in ttcpip ()
#12 0x00000000030025c2 in opitsk ()
#13 0x0000000003007920 in opiino ()
#14 0x0000000014eefdf8 in opiodr ()
#15 0x0000000002ffe2ab in opidrv ()
#16 0x0000000003e8bd15 in sou2o ()
#17 0x0000000000e91ae0 in opimai_real ()
#18 0x0000000003e9915c in ssthrdmain ()
#19 0x0000000000e91924 in main ()
$1 = 0x6e2bd378
~~~~~~~~~~~~~
0x0000000015363ad0 in kglLock ()

(gdb) c
Continuing.

Breakpoint 1, 0x0000000015367e90 in kgllkal ()
#0  0x0000000015367e90 in kgllkal ()
#1  0x0000000015363ad0 in kglLock ()
#2  0x000000001535e178 in kglget ()
#3  0x00000000039d07d6 in kkdllk0 ()
#4  0x00000000039ee60b in kkdllppac0 ()
#5  0x000000000bbd0ea3 in aindrv ()
#6  0x0000000014efc7f2 in opiexe ()
#7  0x00000000151ca093 in opiosq0 ()
#8  0x0000000014f6f166 in kpooprx ()
#9  0x0000000014f6ca1b in kpoal8 ()
#10 0x0000000014eefdf8 in opiodr ()
#11 0x0000000015294359 in ttcpip ()
#12 0x00000000030025c2 in opitsk ()
#13 0x0000000003007920 in opiino ()
#14 0x0000000014eefdf8 in opiodr ()
#15 0x0000000002ffe2ab in opidrv ()
#16 0x0000000003e8bd15 in sou2o ()
#17 0x0000000000e91ae0 in opimai_real ()
#18 0x0000000003e9915c in ssthrdmain ()
#19 0x0000000000e91924 in main ()
$2 = 0x68c3f220
~~~~~~~~~~~~~
0x0000000015363ad0 in kglLock ()
--//不知道为什么当时失败在11g,有机会重新测试看看.

SYS@book> select kgllkmod,kglnaobj from x$kgllk where kgllkhdl = upper('000000006e2bd378') ;
  KGLLKMOD KGLNAOBJ
---------- ------------------------------
         3 T

SYS@book> select kgllkmod,kglnaobj from x$kgllk where kgllkhdl = upper('0000000068c3f220') ;
  KGLLKMOD KGLNAOBJ
---------- ------------------------------
         3 IX_T

3.获取KGLNAOBJ对象:
--//看了链接https://nenadnoveljic.com/blog/library-cache-lock-object-name/,他是通过handle address的偏移获得对象名称.
--//我自己也做了尝试,我发现偏移量存在偏差对比作者的测试,我的测试多了8个偏移,作者的偏移量0x1c0.
--//最后生成gdb的测试脚本,其中的分析过程跳过。
--//另外作者提到lock address从调用kglGetSO的返回获得,感觉写gdb比较困难,我放弃这部分内容。
--//与以前vage<oracle内核技术解密>书上的介绍有点不同.
--//https://nenadnoveljic.com/blog/tracing-library-cache-locks/
--//In order to close this gap I, first, examined the following two Oracle C functions on the release 19.6.0.0.200114:
--//kgllkal and kglGetSO.

--//kgllkal allocates a library cache lock. It receives the following arguments:

--//rdx: handle address
--//rcx: lock mode
--//注:rdx是handle address没有问题,rcx是lock mode,我不知道是否正确,暂且认为这样,我估计作者也是猜测获得.

--//Further, it calls kglGetSO to allocate the library cache state object. kglGetSO returns the lock address.

--//有了以上的素材,最终修改跟踪脚本如下:

$ cat lkpn.gdb
set pagination off
set logging file /tmp/lkpn.log
set logging overwrite on
set logging on
set $lk  = 0
set $pn  = 0

#break kgllkal if $rcx==3
break kgllkal
commands
 silent
 printf "kgllkal count %02d -- handle address: %016x, mode: %d ", ++$lk ,$rdx ,$rcx
 echo kglnaobj address:
 x/s $rdx+0x1c8
 c
 end

#break kglpnal if $rcx==3
break kglpnal
commands
 silent
 printf "kglpnal count %02d -- handle address: %016x, mode: %d ", ++$pn ,$rdx ,$rcx
 echo kglnaobj address:
 x/s $rdx+0x1c8
 c
 end

#break kglGetSO
#commands
# silent
# printf "lock address : %016x\n", $r11
# c
# end

4.利用以上脚本测试看看:
--//测试执行sql语句的情况看看.
--//session 1:
SCOTT@book01p> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID                               PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------------------------------ ------- ---------- --------------------------------------------------
       400      34954 6214                     DEDICATED 6216                                63         13 alter system kill session '400,34954' immediate;

--//Select * from dept where deptno=20; 执行多次,注意第1个字母大写.减少递归,不然下面的输出内容会更多.

--//session 2:
$ rlgdb -f -p  6216 -x lkpn.gdb
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-94.el7
Copyright (C) 2013 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
..
0x00007f6ffaf66480 in __read_nocancel () at ../sysdeps/unix/syscall-template.S:81
/usr/src/debug/glibc-2.17-c758a686/sysdeps/unix/syscall-template.S:81:3374:beg:0x7f6ffaf66480
Breakpoint 1 at 0x15367e90
Breakpoint 2 at 0x1536c020

--//session 1:
SCOTT@book01p> select * from dept where deptno=20;
--//注意第1个字母小写

--//挂起,切换到session 2:
Breakpoint 1 at 0x15367e90
Breakpoint 2 at 0x1536c020
(gdb) c
Continuing.
kgllkal count 01 -- handle address: 000000006cf61798, mode: 1 kglnaobj address:0x6cf61960:  "select * from dept where deptno=20"
kgllkal count 02 -- handle address: 0000000065f7a2e8, mode: 2 kglnaobj address:0x65f7a4b0:  "e8ec445edab00042802d511305ab90fa$BUILD$BOOK01P"
kgllkal count 03 -- handle address: 000000006c880250, mode: 1 kglnaobj address:0x6c880418:  ""
kglpnal count 01 -- handle address: 000000006c880250, mode: 3 kglnaobj address:0x6c880418:  ""
kgllkal count 04 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300:  "bookSYSCDB$ROOT"
kgllkal count 05 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:  "1073777561SYSCDB$ROOT"
kgllkal count 06 -- handle address: 0000000062ce3480, mode: 1 kglnaobj address:0x62ce3648:  "e8ec445edab00042802d511305ab90faChild:0BOOK01P"
kglpnal count 02 -- handle address: 0000000062ce3480, mode: 3 kglnaobj address:0x62ce3648:  "e8ec445edab00042802d511305ab90faChild:0BOOK01P"
kgllkal count 07 -- handle address: 0000000066bf5db0, mode: 1 kglnaobj address:0x66bf5f78:  "SCOTTBOOK01P"
kgllkal count 08 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300:  "bookSYSCDB$ROOT"
kgllkal count 09 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:  "1073777561SYSCDB$ROOT"
kgllkal count 10 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:  "DEPTSCOTTBOOK01Pp~\027k"
kglpnal count 03 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:  "DEPTSCOTTBOOK01Pp~\027k"
kgllkal count 11 -- handle address: 000000006cd389b8, mode: 2 kglnaobj address:0x6cd38b80:  "5358706841214419813BOOK01P"
kglpnal count 04 -- handle address: 000000006cd389b8, mode: 2 kglnaobj address:0x6cd38b80:  "5358706841214419813BOOK01P"
kgllkal count 12 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828:  "1256087081022357994BOOK01P"
kglpnal count 05 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828:  "1256087081022357994BOOK01P"
kgllkal count 13 -- handle address: 0000000063e7ea10, mode: 2 kglnaobj address:0x63e7ebd8:  "13547376130454050250BOOK01P"
kglpnal count 06 -- handle address: 0000000063e7ea10, mode: 2 kglnaobj address:0x63e7ebd8:  "13547376130454050250BOOK01P"
kgllkal count 14 -- handle address: 0000000066a38988, mode: 2 kglnaobj address:0x66a38b50:  "4448762010415191240BOOK01P"
kglpnal count 07 -- handle address: 0000000066a38988, mode: 2 kglnaobj address:0x66a38b50:  "4448762010415191240BOOK01P"
kgllkal count 15 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828:  "1256087081022357994BOOK01P"
kglpnal count 08 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828:  "1256087081022357994BOOK01P"
--//第1次执行硬解析,后面的内容就是kglnaobj对象名称.注意有2个没有内容,这是因为是sql语句的的child handle address,应该从
--//parent handle address上取,为空很正常.
--//比如 bookSYSCDB$ROOT 实际上就是数据库的实例名,不知道为什么实际上oracle计算full_hash_value中间有小数点分割开的.
--//实际上计算使用book.SYS.CDB$ROOT\x<namespace_hex>\0\0\0字符串.
SYS@book> @ nmsp.sql %instance% -1
KGLSTDSC    KGLSTIDN NMSP_HEX
----------- -------- --------
DBINSTANCE        74 4a

$ sql_idz.sh 'book.SYS.CDB$ROOT\x4a\0\0\0' 3
sql_text = book.SYS.CDB$ROOT\x4a\0\0\0
full_hash_value(16) = B885BB910059A4D2FDBFC245A6411BA3 or b885bb910059a4d2fdbfc245a6411ba3
xxxxx_matching_signature(10) = -162197457164231773 or  18284546616545319843
hash_value(10) = 2789284771
sql_id(32) = gvgy28qm426x3
sql_id(32) = gvgy28qm426x3
sql_id(32) = gvgy28qm426x3
--//注:要使用单引号,因为里面有$ROOT,使用双引号会解析为变量.

SYS@book> @ sharepool/shp4 00000000701df138 0
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40      KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
---------------------- ---------------- ---------------- -------- -------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
parent handle address  00000000701DF138 00000000701DF138 SYS.book        0          0          0 00               00                        0          0          0         0          0 2789284771                        0
--//KGLNAHSH=2789284771,说明计算没有问题.

SYS@book> @ kglob 0 2789284771
==============================
INST_ID                       : 1
OWNER                         : SYS
NAME                          : book
DB_LINK                       :
NAMESPACE                     : DBINSTANCE
TYPE                          : CURSOR
NAMESPACE_NUM                 : 74
NAMESPACE_HEX                 : 4a
SHARABLE_MEM                  : 0
LOADS                         : 0
EXECUTIONS                    : 0
LOCKS                         : 0
PINS                          : 0
KEPT                          : NO
CHILD_LATCH                   : 72611
INVALIDATIONS                 : 0
HASH_VALUE                    : 2789284771
LOCK_MODE                     : NONE
PIN_MODE                      : NONE
STATUS                        : UNKOWN
TIMESTAMP                     :
PREVIOUS_TIMESTAMP            :
LOCKED_TOTAL                  : 1313195
PINNED_TOTAL                  : 0
PROPERTY                      :
FULL_HASH_VALUE               : b885bb910059a4d2fdbfc245a6411ba3
CON_ID                        : 1
CON_NAME                      : CDB$ROOT
ADDR                          : 00000000701DF138
EDITION                       :
SQL_ID                        :
OBJECT_STR                    : book.SYS.CDB$ROOT\x4a\0\0\0
PL/SQL procedure successfully completed.
--//FULL_HASH_VALUE : b885bb910059a4d2fdbfc245a6411ba3也能对上.

kgllkal count 16 -- handle address: 000000006cf61798, mode: 1 kglnaobj address:0x6cf61960:      "select * from dept where deptno=20"
kgllkal count 17 -- handle address: 000000006c880250, mode: 1 kglnaobj address:0x6c880418:      ""
kgllkal count 18 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:      "DEPTSCOTTBOOK01Pp~\027k"
kglpnal count 09 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:      "DEPTSCOTTBOOK01Pp~\027k"
--//第2次执行软解析,kglnaobj对象名称后面出现乱码很正常,我没有判断显示长度.

kgllkal count 19 -- handle address: 000000006cf61798, mode: 1 kglnaobj address:0x6cf61960:      "select * from dept where deptno=20"
kgllkal count 20 -- handle address: 000000006c880250, mode: 1 kglnaobj address:0x6c880418:      ""
--//第3次执行缓存光标.

--//第4次执行软软解析.没有任何输出....

5.补充一些解析:
kgllkal count 01 -- handle address: 000000006cf61798, mode: 1 kglnaobj address:0x6cf61960:      "select * from dept where deptno=20"
kgllkal count 02 -- handle address: 0000000065f7a2e8, mode: 2 kglnaobj address:0x65f7a4b0:      "e8ec445edab00042802d511305ab90fa$BUILD$BOOK01P"
--//e8ec445edab00042802d511305ab90fa数字实际上sql语句的full_hash_value,11g取的后16位,21c取的是全部32位.

$ sql_idz.sh 'select * from dept where deptno=20\0' 3
sql_text = select * from dept where deptno=20\0
full_hash_value(16) = E8EC445EDAB00042802D511305AB90FA or e8ec445edab00042802d511305ab90fa
xxxxx_matching_signature(10) = -9210616520761437958 or  9236127552948113658
hash_value(10) = 95129850
sql_id(32) = 80baj2c2ur47u
sql_id(32) = 80baj2c2ur47u
sql_id(32) = 80baj2c2ur47u
--//full_hash_value 完全能对上.

SYS@book> @ sharepool/shp4 0000000065f7a2e8 0
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
parent handle address  0000000065F7A2E8 0000000065F7A2E8 $BUILD$.e8ec445edab00042802d511305ab90fa          0          0          0 00               00                        0          0          0         0          0 1635416785                        0

SYS@book> @ kglob 0 1635416785
==============================
INST_ID                       : 1
OWNER                         : $BUILD$
NAME                          : e8ec445edab00042802d511305ab90fa
DB_LINK                       :
NAMESPACE                     : SQL AREA BUILD
TYPE                          : CURSOR
NAMESPACE_NUM                 : 82
NAMESPACE_HEX                 : 52
SHARABLE_MEM                  : 0
LOADS                         : 0
EXECUTIONS                    : 0
LOCKS                         : 0
PINS                          : 0
KEPT                          : NO
CHILD_LATCH                   : 31441
INVALIDATIONS                 : 0
HASH_VALUE                    : 1635416785
LOCK_MODE                     : NONE
PIN_MODE                      : NONE
STATUS                        : UNKOWN
TIMESTAMP                     :
PREVIOUS_TIMESTAMP            :
LOCKED_TOTAL                  : 1
PINNED_TOTAL                  : 0
PROPERTY                      :
FULL_HASH_VALUE               : 7e8adf6b1c21e493a6bdcf5a617a7ad1
CON_ID                        : 3
CON_NAME                      : BOOK01P
ADDR                          : 0000000065F7A2E8
EDITION                       :
SQL_ID                        :
OBJECT_STR                    : e8ec445edab00042802d511305ab90fa.$BUILD$.BOOK01P\x52\0\0\0
PL/SQL procedure successfully completed.

kgllkal count 06 -- handle address: 0000000062ce3480, mode: 1 kglnaobj address:0x62ce3648:      "e8ec445edab00042802d511305ab90faChild:0BOOK01P"
kglpnal count 02 -- handle address: 0000000062ce3480, mode: 3 kglnaobj address:0x62ce3648:      "e8ec445edab00042802d511305ab90faChild:0BOOK01P"
--//这里有点特殊,也是我开始遇到的问题,才想看看该对象的内容.
--//它是sql语句的FULL_HASH_VALUE.Child:0.BOOK01P,里面的C大写,明显与子光标有关,里面的0是指child_number.
SYS@book> @ sharepool/shp4 0000000062ce3480 0
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
parent handle address  0000000062CE3480 0000000062CE3480 e8ec445edab00042802d511305ab90fa                  0          0          0 000000006E2982C8 00                     4064          0          0      4064       4064 3280362473                        0

SYS@book> @ kglob 0 3280362473
==============================
INST_ID                       : 1
OWNER                         :
NAME                          : e8ec445edab00042802d511305ab90fa
DB_LINK                       :
NAMESPACE                     : SQL AREA STATS
TYPE                          : CURSOR STATS
NAMESPACE_NUM                 : 75
NAMESPACE_HEX                 : 4b
SHARABLE_MEM                  : 4064
LOADS                         : 1
EXECUTIONS                    : 0
LOCKS                         : 0
PINS                          : 0
KEPT                          : YES
CHILD_LATCH                   : 23529
INVALIDATIONS                 : 0
HASH_VALUE                    : 3280362473
LOCK_MODE                     : NONE
PIN_MODE                      : NONE
STATUS                        : VALID
TIMESTAMP                     : 2024-08-24/10:55:41
PREVIOUS_TIMESTAMP            :
LOCKED_TOTAL                  : 2
PINNED_TOTAL                  : 2
PROPERTY                      :
FULL_HASH_VALUE               : f70b8d76d1df4847c17a0582c3865be9
CON_ID                        : 3
CON_NAME                      : BOOK01P
ADDR                          : 0000000062CE3480
EDITION                       :
SQL_ID                        :
OBJECT_STR                    : e8ec445edab00042802d511305ab90fa
PL/SQL procedure successfully completed.
--//如果仅仅看name,无法猜测FULL_HASH_VALUE的如何计算的.

$ sql_idz.sh 'e8ec445edab00042802d511305ab90fa.Child:0.BOOK01P\x4b\0\0\0' 3
sql_text = e8ec445edab00042802d511305ab90fa.Child:0.BOOK01P\x4b\0\0\0
full_hash_value(16) = F70B8D76D1DF4847C17A0582C3865BE9 or f70b8d76d1df4847c17a0582c3865be9
xxxxx_matching_signature(10) = -4505282418046510103 or  13941461655663041513
hash_value(10) = 3280362473
sql_id(32) = c2yh5hb1scqz9
sql_id(32) = c2yh5hb1scqz9
sql_id(32) = c2yh5hb1scqz9
--//full_hash_value(16) = f70b8d76d1df4847c17a0582c3865be9 能对上.

kgllkal count 09 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"

SYS@book> @ sharepool/shp4 000000006b97c9f8 0
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
parent handle address  000000006B97C9F8 000000006B97C9F8 SYS.1073777561                                    0          0          0 00               00                        0          0          0         0          0  471669191                        0

SYS@book> @ kglob 0 471669191
==============================
INST_ID                       : 1
OWNER                         : SYS
NAME                          : 1073777561
DB_LINK                       :
NAMESPACE                     : DBINSTANCE
TYPE                          : CURSOR
NAMESPACE_NUM                 : 74
NAMESPACE_HEX                 : 4a
SHARABLE_MEM                  : 0
LOADS                         : 0
EXECUTIONS                    : 0
LOCKS                         : 0
PINS                          : 0
KEPT                          : NO
CHILD_LATCH                   : 72135
INVALIDATIONS                 : 0
HASH_VALUE                    : 471669191
LOCK_MODE                     : NONE
PIN_MODE                      : NONE
STATUS                        : UNKOWN
TIMESTAMP                     :
PREVIOUS_TIMESTAMP            :
LOCKED_TOTAL                  : 1126873
PINNED_TOTAL                  : 0
PROPERTY                      :
FULL_HASH_VALUE               : 25d8c2e2a1f8d5e74176b9b61c1d19c7
CON_ID                        : 1
CON_NAME                      : CDB$ROOT
ADDR                          : 000000006B97C9F8
EDITION                       :
SQL_ID                        :
OBJECT_STR                    : 1073777561.SYS.CDB$ROOT\x4a\0\0\0
PL/SQL procedure successfully completed.
--//这里使用数字表示PDB NAME.ORACLE很奇怪,本来应该使用BOOK01P.

SYS@book> select PDB_ID,PDB_NAME,DBID,CON_UID from dba_pdbs;

    PDB_ID PDB_NAME                             DBID    CON_UID
---------- ------------------------------ ---------- ----------
         3 BOOK01P                        1073777561 1073777561
         2 PDB$SEED                       2763294012 2763294012
--//1073777561可以对上.

kgllkal count 11 -- handle address: 000000006cd389b8, mode: 2 kglnaobj address:0x6cd38b80:      "5358706841214419813BOOK01P"
kglpnal count 04 -- handle address: 000000006cd389b8, mode: 2 kglnaobj address:0x6cd38b80:      "5358706841214419813BOOK01P"
kgllkal count 12 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828:      "1256087081022357994BOOK01P"
kglpnal count 05 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828:      "1256087081022357994BOOK01P"
kgllkal count 13 -- handle address: 0000000063e7ea10, mode: 2 kglnaobj address:0x63e7ebd8:      "13547376130454050250BOOK01P"
kglpnal count 06 -- handle address: 0000000063e7ea10, mode: 2 kglnaobj address:0x63e7ebd8:      "13547376130454050250BOOK01P"
kgllkal count 14 -- handle address: 0000000066a38988, mode: 2 kglnaobj address:0x66a38b50:      "4448762010415191240BOOK01P"
kglpnal count 07 -- handle address: 0000000066a38988, mode: 2 kglnaobj address:0x66a38b50:      "4448762010415191240BOOK01P"
kgllkal count 15 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828:      "1256087081022357994BOOK01P"
kglpnal count 08 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828:      "1256087081022357994BOOK01P"
--//再次出现一大堆数字.

SCOTT@book01p> @o2 dept
owner                     object_name                    object_type          SEG_PART_NAME        status           OID      D_OID CREATED             LAST_DDL_TIME
------------------------- ------------------------------ -------------------- -------------------- --------- ---------- ---------- ------------------- -------------------
SCOTT                     DEPT                           TABLE                                     VALID          76191      76191 2024-08-16 09:33:38 2024-08-16 09:33:38

SCOTT@book01p> set numw 20
SCOTT@book01p> select * from sys.exp_head$ where objn=76191;
              EXP_ID                 OBJN               SUB_ID           FIXED_COST TEXT                           COL_                FLAGS CTIME
-------------------- -------------------- -------------------- -------------------- ------------------------------ ---- -------------------- -------------------
 1256087081022357994                76191                    0 5.51081878534547E-08 "DEPTNO"                       1                       8 2024-08-16 09:35:07
13547376130454050250                76191                    0 5.51081878534547E-08 "DNAME"                        2                       8 2024-08-16 09:35:07
 4448762010415191240                76191                    0 5.51081878534547E-08 "LOC"                          3                       8 2024-08-16 09:35:07
--//只有5358706841214419813BOOK01P没有。
--//1256087081022357994BOOK01P 出现4次,估计与where deptno=20有关.
--//感觉这样如果第一次执行假设表字段很多的情况下,执行select * 岂不是要建立大量的chunk.
--//看一些介绍与_column_tracking_level隐含参数有关,设置17可以减少这类的chunk.21c等于53.
SYS@book> @ hide _column_tracking_level
NAME                                     DESCRIPTION           DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE           ISSES ISSYS_MOD
---------------------------------------- --------------------- ---------------------- ---------------------- ---------------------- ----- ---------
_column_tracking_level                   column usage tracking TRUE                   53                     53                     TRUE  IMMEDIATE

SYS@book> @ 10tox 53 2
BASE10  BASE2
------- -------
53      110101
--//FULL_HASH_VALUE就不计算了.

--//看看:5358706841214419813BOOK01P
SYS@book> @ sharepool/shp4 000000006cd389b8 0
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
parent handle address  000000006CD389B8 000000006CD389B8 5358706841214419813                               0          0          0 00               00                        0          0          0         0          0 3095809875                        0

SYS@book> @ kglob 0 3095809875
==============================
INST_ID                       : 1
OWNER                         :
NAME                          : 5358706841214419813
DB_LINK                       :
NAMESPACE                     : OPTIMIZER DIRECTIVE OWNER
TYPE                          : CURSOR
NAMESPACE_NUM                 : 104
NAMESPACE_HEX                 : 68
SHARABLE_MEM                  : 0
LOADS                         : 1
EXECUTIONS                    : 0
LOCKS                         : 0
PINS                          : 0
KEPT                          : NO
CHILD_LATCH                   : 20307
INVALIDATIONS                 : 0
HASH_VALUE                    : 3095809875
LOCK_MODE                     : NONE
PIN_MODE                      : NONE
STATUS                        : UNKOWN
TIMESTAMP                     :
PREVIOUS_TIMESTAMP            :
LOCKED_TOTAL                  : 2
PINNED_TOTAL                  : 2
PROPERTY                      :
FULL_HASH_VALUE               : d1faee06f59008e2ce16faeeb8864f53
CON_ID                        : 3
CON_NAME                      : BOOK01P
ADDR                          : 000000006CD389B8
EDITION                       :
SQL_ID                        :
OBJECT_STR                    : 5358706841214419813
PL/SQL procedure successfully completed.
--//不熟悉这部分内容,放弃.

$ sql_idz.sh '5358706841214419813.BOOK01P\x68\0\0\0' 3
sql_text = 5358706841214419813.BOOK01P\x68\0\0\0
full_hash_value(16) = D1FAEE06F59008E2CE16FAEEB8864F53 or d1faee06f59008e2ce16faeeb8864f53
xxxxx_matching_signature(10) = -3596411349203792045 or  14850332724505759571
hash_value(10) = 3095809875
sql_id(32) = cw5ruxuw8cmum
sql_id(32) = cw5ruxuw8cmum
sql_id(32) = cw5ruxuw8cmum

6.简单总结:

--//如果看vage的书<oracle内核技术解密>,这样简单的sql语句,第1次硬解析执行共10次调用,其中kgllkal 7次,kglpnal 3次.
--//而21c调用明显增加,第1次硬解析执行共15+8=23次调用,其中kgllkal 15次,kglpnal 8次.
--//如果按照后面的测试还多3次.
--//再次提醒OLTP系统使用绑定变量的重要性,应该再加两个字"合理"地使用.

--//看看sql执行锁模式的情况.1表示NULL 2表示共享 3表示排他.第1次执行出现mode=3的情况2次.
kglpnal count 01 -- handle address: 000000006c880250, mode: 3 kglnaobj address:0x6c880418:  ""
kglpnal count 02 -- handle address: 0000000062ce3480, mode: 3 kglnaobj address:0x62ce3648:  "e8ec445edab00042802d511305ab90faChild:0BOOK01P"
--//仅仅出现2次mode: 3,而且是kglpnal,这样表不存在大量密集执行,应该看到library cache pin.不应该是library cache lock.
--//也许作者和我取的值$rcx不对,该问题暂时放一放,

--//最后,也许我的探究存在许多缺陷,许多都是受限自己的能力乱猜,不知道是否正确.

--//在结束测试时,我有换1条sql语句尝试:
kgllkal count 93 -- handle address: 0000000065b0d0d0, mode: 1 kglnaobj address:0x65b0d298:      "Select * from dept where deptno=10"
kglpnal count 35 -- handle address: 0000000065b0d0d0, mode: 2 kglnaobj address:0x65b0d298:      "Select * from dept where deptno=10"
kgllkal count 94 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300:      "bookSYSCDB$ROOT"
kgllkal count 95 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
--//不知道为什么我前面的尝试少了前面这3行.也许刷新共享池时这些对象时没有清楚干净,再测试前我执行了1次,再刷新共享池的.
kgllkal count 96 -- handle address: 000000006e261978, mode: 2 kglnaobj address:0x6e261b40:      "85d6f5c0bce7df033db8e86ed0624d44$BUILD$BOOK01P"
kgllkal count 97 -- handle address: 000000006f9af9a8, mode: 1 kglnaobj address:0x6f9afb70:      ""
kglpnal count 36 -- handle address: 000000006f9af9a8, mode: 3 kglnaobj address:0x6f9afb70:      ""
kgllkal count 98 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300:      "bookSYSCDB$ROOT"
kgllkal count 99 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 100 -- handle address: 0000000063d4de10, mode: 1 kglnaobj address:0x63d4dfd8:     "85d6f5c0bce7df033db8e86ed0624d44Child:0BOOK01P"
kglpnal count 37 -- handle address: 0000000063d4de10, mode: 3 kglnaobj address:0x63d4dfd8:      "85d6f5c0bce7df033db8e86ed0624d44Child:0BOOK01P"
kgllkal count 101 -- handle address: 0000000066bf5db0, mode: 1 kglnaobj address:0x66bf5f78:     "SCOTTBOOK01P"
kgllkal count 102 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300:     "bookSYSCDB$ROOT"
kgllkal count 103 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:     "1073777561SYSCDB$ROOT"
kgllkal count 104 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:     "DEPTSCOTTBOOK01Pp~\027k"
kglpnal count 38 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:      "DEPTSCOTTBOOK01Pp~\027k"
kgllkal count 105 -- handle address: 0000000067504158, mode: 2 kglnaobj address:0x67504320:     "5358706841214419813BOOK01P"
kglpnal count 39 -- handle address: 0000000067504158, mode: 2 kglnaobj address:0x67504320:      "5358706841214419813BOOK01P"
kgllkal count 106 -- handle address: 0000000069ef2ff8, mode: 2 kglnaobj address:0x69ef31c0:     "1256087081022357994BOOK01P"
kglpnal count 40 -- handle address: 0000000069ef2ff8, mode: 2 kglnaobj address:0x69ef31c0:      "1256087081022357994BOOK01P"
kgllkal count 107 -- handle address: 0000000068e857f0, mode: 2 kglnaobj address:0x68e859b8:     "13547376130454050250BOOK01P"
kglpnal count 41 -- handle address: 0000000068e857f0, mode: 2 kglnaobj address:0x68e859b8:      "13547376130454050250BOOK01P"
kgllkal count 108 -- handle address: 000000006f394f70, mode: 2 kglnaobj address:0x6f395138:     "4448762010415191240BOOK01P"
kglpnal count 42 -- handle address: 000000006f394f70, mode: 2 kglnaobj address:0x6f395138:      "4448762010415191240BOOK01P"
kgllkal count 109 -- handle address: 0000000069ef2ff8, mode: 2 kglnaobj address:0x69ef31c0:     "1256087081022357994BOOK01P"
kglpnal count 43 -- handle address: 0000000069ef2ff8, mode: 2 kglnaobj address:0x69ef31c0:      "1256087081022357994BOOK01P"
--//这次多了3个调用.

kgllkal count 110 -- handle address: 0000000065b0d0d0, mode: 1 kglnaobj address:0x65b0d298:     "Select * from dept where deptno=10"
kgllkal count 111 -- handle address: 000000006f9af9a8, mode: 1 kglnaobj address:0x6f9afb70:     ""
kgllkal count 112 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:     "DEPTSCOTTBOOK01Pp~\027k"
kglpnal count 44 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:      "DEPTSCOTTBOOK01Pp~\027k"

kgllkal count 113 -- handle address: 0000000065b0d0d0, mode: 1 kglnaobj address:0x65b0d298:     "Select * from dept where deptno=10"
kgllkal count 114 -- handle address: 000000006f9af9a8, mode: 1 kglnaobj address:0x6f9afb70:     ""
--//感觉出现这样的机会更大一些.

7.附上执行脚本的源代码:

$ cat kglob.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name:   kglob.sql
-- Purpose:     query v$db_object_cache view
--
-- Author:      lfree
--
-- Usage:
--     @ kglob <sql_id> <hash_value>
-- for example
--     @ kglob 0 123456678
--     @ kglob 7h35uxf5uhmm1 0
--
--------------------------------------------------------------------------------
set term off head off
define noprint='noprint'

col tpt_version_old  &noprint new_value _tpt_version_old
col tpt_version_new  &noprint new_value _tpt_version_new
col tpt_noprint      &noprint new_value _tpt_noprint

WITH version AS (SELECT TO_NUMBER (SUBSTR (version, 1, 2)) v FROM v$instance)
SELECT CASE WHEN v <= 10 THEN '' ELSE '--' END tpt_version_old
      ,CASE WHEN v > 10  THEN '' ELSE '--' END tpt_version_new
            FROM version;

select  /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */
 inst_id                                                                                                                           INST_ID
,KGLNAOWN                                                                                                                          OWNER
,kglnaobj                                                                                                                          NAME
,kglnadlk                                                                                                                          DB_LINK
,kglhdnsd                                                                                                                          NAMESPACE
,kglobtyd                                                                                                                          TYPE
,kglhdnsp                                                                                                                          NAMESPACE_NUM
,to_char(kglhdnsp,'FMxx')                                                                                                          NAMESPACE_HEX
,kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6                                                                    SHARABLE_MEM
,kglhdldc                                                                                                                          LOADS
,kglhdexc                                                                                                                          EXECUTIONS
,kglhdlkc                                                                                                                          LOCKS
,kglobpc0                                                                                                                          PINS
,decode(kglhdkmk,0,'NO','YES')                                                                                                     KEPT
,kglhdclt                                                                                                                          CHILD_LATCH
,kglhdivc                                                                                                                          INVALIDATIONS
,kglnahsh                                                                                                                          HASH_VALUE
,decode(kglhdlmd,0, 'NONE',1, 'NULL', 2, 'SHARED',3, 'EXCLUSIVE','UNKOWN')                                                         LOCK_MODE
,decode(kglhdpmd,0, 'NONE',1, 'NULL', 2, 'SHARED',3, 'EXCLUSIVE','UNKOWN')                                                         PIN_MODE
,decode(kglobsta,1, 'VALID',2,'VALID_AUTH_ERROR',3,'VALID_COMPILE_ERROR',4,'VALID_UNAUTH',5,'INVALID_UNAUTH',6,'INVALID','UNKOWN') STATUS
,substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19)                                                                            TIMESTAMP
,substr(to_char(kglnaptm,'YYYY-MM-DD/HH24:MI:SS'),1,19)                                                                            PREVIOUS_TIMESTAMP
,kgloblct                                                                                                                          LOCKED_TOTAL
,kglobpct                                                                                                                          PINNED_TOTAL
,kglobprop                                                                                                                         PROPERTY
,kglnahsv                                                                                                                          FULL_HASH_VALUE
&&_tpt_version_new ,con_id                                                                                                          CON_ID
&&_tpt_version_new ,KGLNACON                                                                                                        CON_NAME
,kglhdadr                                                                                                                          ADDR
,kglnaedn                                                                                                                          EDITION
,KGLOBT03                                                                                                                          SQL_ID
&&_tpt_version_old,substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaobj||'.'||kglnaown||'.'||'\x'||to_char(kglhdnsp,'FMxx')||'\0\0\0'), '(name not found)'),chr(13),'') ,1,60)  end      OBJECT_STR
&&_tpt_version_new,substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaobj||'.'||kglnaown||'.'||kglnacon||'\x'||to_char(kglhdnsp,'FMxx')||'\0\0\0'), '(name not found)'),chr(13),'') ,1,60) OBJECT_STR
from x$kglob
where kglnaobj is not null and (KGLOBT03 = lower('&1') or  KGLNAHSH= &2);
set term on head on
@ pr
--//注:pr来自tpt包.

$ cat sharepool/shp4.sql
column N0_6_16 format 99999999
column fcura_addrlen new_value _fcura_addrlen format 999
column handle_type format a22

set termout off
select vsize(addr)*2 fcura_addrlen from x$dual;
set termout on

SELECT DECODE (kglhdadr,
               kglhdpar, 'parent handle address',
               'child handle address')
       handle_type,
       kglhdadr,
       kglhdpar,
       --//substr(kglnaobj,1,40) c40,
           substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)'),chr(13),'') ,1,40)  c40,
           KGLHDLMD,
           KGLHDPMD,
           kglhdivc,
       kglobhd0,
       kglobhd6,
       kglobhs0,kglobhs6,kglobt16,
       kglobhs0+kglobhs6+kglobt16 N0_6_16,
           kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
           kglnahsh,
           kglobt03 ,
           kglobt09
  FROM x$kglob
 WHERE
    KGLHDPAR = lpad(upper('&1'), &_fcura_addrlen, '0')
or  KGLHDADR = lpad(upper('&1'), &_fcura_addrlen, '0')
or  KGLOBHD0 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or  KGLOBHD1 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or  KGLOBHD2 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or  KGLOBHD3 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or  KGLOBHD4 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or  KGLOBHD5 = lpad(upper('&1'), &_fcura_addrlen, '0')
or  KGLOBHD6 = lpad(upper('&1'), &_fcura_addrlen, '0')
or  KGLOBT03 = lower('&1')
or  KGLNAHSH= &2;

--//注:里面^M在vim下ctrl+v,ctrl+M输入.
$ cat -v $(which sql_idz.sh )
#! /bin/bash
# calcucate sql_text of full_hash_value(16),hash_value(10),sql_id(32).
# argv1 sql statement or sql of text file
# argv2 flag: 0= sql statement 1=sql of text file for sqlplus  2=sql of text file for other 3=original
# argv3 default = '\0' add tailstr

odebug=${ODEBUG:-0}
oflag=${2:-0}
tailstr=${3:-'\0'}

if [ $oflag -eq 0 ]
then
    sql_text=${1}${tailstr}
fi

# sqlplus format sql_text
if [ $oflag -eq 1 ]
then
sql_text="$( cat $1 | sed -e "s/^M$//" -e "s/\s*$//" -e '$s/;$//')""${tailstr}"
# sql_text="$( cat $1 | unix2dos | sed '$s/;\s*$//')"'\0'
# sql_text="$( cat $1 | sed -e "s/^M$//" -e sed '$s/;\s*$//')"'\0'
# sql_text="$( cat $1 | sed '$s/;\s*$//')"'\0'
fi

# other format sql_text
if [ $oflag -eq 2 ]
then
sql_text="$( cat $1 | sed '$s/;\s*$//')""${tailstr}"
# sql_text="$( cat $1 | unix2dos | sed '$s/;\s*$//')"'\0'
# sql_text="$( cat $1 | sed -e "s/^M$//" -e '$s/;\s*$//')"'\0'
# sql_text="$( cat $1 | sed '$s/;\s*//')"'\0'
fi

# exact_matching_signature, force_matching_signature
if [ $oflag -eq 3 ]
then
    sql_text=${1}
fi

v1=$(echo -e -n "$sql_text" | md5sum | sed 's/  -//' | xxd -r -p | od -t x4 |  sed   -n  -e 's/^0\+ //' -e 's/ //gp' | tr 'a-z' 'A-Z')
v2=${v1:(-16):16}
v3=${v2:(-8):8}
# v2=$(echo "obase=16;ibase=16; $v1 % 10000000000000000" | bc| tr -d '\\\r\n')
# v3=$(echo "obase=10;ibase=16; $v1 % 100000000" | bc| tr -d '\\\r\n')

if [ $odebug -eq 1 ] ; then
        echo v1=$v1 v2=$v2 v3=$v3
fi

echo "sql_text = $sql_text"
echo "full_hash_value(16) = $v1 or ${v1,,}"

if [ $oflag -eq 3 ] ; then
        echo "xxxxx_matching_signature(10) = $(( 16#$v2 )) or " $(echo $(( 16#$v2 )) + 2^64|bc )
fi

echo "hash_value(10) = $(( 16#$v3 )) "

BASE32=($(echo {0..9} {a..z} | tr -d 'eilo'))
res=''
for i in $(echo "obase=32;ibase=16; $v2" | bc| tr -d '\\\r\n')
do
        res=${res}${BASE32[$(( 10#$i ))]}
done
echo "sql_id(32) = $(printf "%13s" $res | tr ' ' '0')"
echo "sql_id(32) = $(printf "%013s" $res)"

res1=$(eval $(echo "obase=32;ibase=16; $v2" | bc| tr -d '\\\r\n' | awk 'BEGIN{RS=" +"; printf "echo " }/./{printf "${BASE32[$(( 10#%02d))]}", $1}' ))
echo "sql_id(32) = $(printf "%013s" $res1)"

$ cat nmsp.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name:   nmsp.sql
-- Purpose:     query namespace from x$kglst
--
-- Author:      lfree
--
-- Usage:
--     @ nmsp <namespace> <namespace_num>
-- for example
--     @ nmsp %table% -1
--     @ name '' 74
--
--------------------------------------------------------------------------------
column nmsp_hex format a8
select kglstdsc,kglstidn,to_char(kglstidn,'FMxx') nmsp_hex from x$kglst where KGLSTTYP='NAMESPACE' and (kglstdsc like upper('&1') or kglstidn = &2) order by 2