[20240930]关于共享池-表对象在库缓存探究2.txt

[20240930]关于共享池-表对象在库缓存探究2.txt

--//以前探究过sql语句在共享池存在父子游标，父游标存在堆0，子游标堆0，堆6，通过各种指针链接起来，
--//父游标的堆0上保存了所有子游标的列表和各个子游标的句柄指针，子游标的堆6中保存了解析过的执行计划等解析信息。
--//前几天测试表对象在库缓存探究，测试中遇到许多问题，今天重新测试看看，使用scott.dept表。

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.

SCOTT@book01p> @desc dept
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      DEPTNO                          NOT NULL NUMBER(2)
    2      DNAME                                    VARCHAR2(14)
    3      LOC                                      VARCHAR2(13)

SYS@book> @ nmsp table/ -1
@ nmsp table -1
@ nmsp '' 74  or @ nmsp '' 0x4a|x4a
KGLSTDSC                                                           KGLSTIDN KGLSTIDN_HEX
---------------------------------------------------------------- ---------- ------------
TABLE/PROCEDURE                                                           1 1
    
$ sql_idz.sh 'DEPT.SCOTT.BOOK01P\x1\0\0\0' 3
sql_text = DEPT.SCOTT.BOOK01P\x1\0\0\0
full_hash_value(16) = 05DB243908B3C797B99628590EDB820C or 05db243908b3c797b99628590edb820c
xxxxx_matching_signature(10) = -5073823567219817972 or  13372920506489733644
hash_value(10) = 249266700 or hash_value(16) = 0EDB820C or 0edb820c
sql_id(32) = bm5j8b47dr0hc
sql_id(32) = bm5j8b47dr0hc
sql_id(32) = bm5j8b47dr0hc

--//表的名字空间1，这样参与计算的字符串是 table_name.owner.con_name\x1\0\0\0 .以后计算结果 hash_value(10) = 249266700.
--//注：表为非sql语句不存在sql_id。

2.测试：
--//随便执行一条关于表dept的sql语句，表对象就会加载到库缓存中。或者执行desc dept也可以，避免刷出，可以执行多次。

SYS@book> @ sharepool/shp4 0 249266700
SYS@book> @ pr
==============================
HANDLE_TYPE                   : parent handle address
KGLHDADR                      : 0000000062452578
KGLHDPAR                      : 0000000062452578
C40                           : DEPT.SCOTT
KGLHDLMD                      : 0
KGLHDPMD                      : 0
KGLHDIVC                      : 0
KGLOBHD0                      : 0000000063586360
KGLOBHD6                      : 00
KGLOBHS0                      : 4064
KGLOBHS6                      : 0
KGLOBT16                      : 0
N0_6_16                       : 4064
N20                           : 4064
KGLNAHSH                      : 249266700
KGLOBT03                      :
KGLOBT09                      : 0
PL/SQL procedure successfully completed.

SYS@book> select KGLOBHD0 ,KGLOBHD1 ,KGLOBHD2 ,KGLOBHD3 ,KGLOBHD4 ,KGLOBHD5 ,KGLOBHD6 ,KGLOBHD7 from x$kglob where KGLNAHSH=249266700;
KGLOBHD0         KGLOBHD1         KGLOBHD2         KGLOBHD3         KGLOBHD4         KGLOBHD5         KGLOBHD6         KGLOBHD7
---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
0000000063586360 00               00               00               00               00               00               00
--//仅仅存在堆0。

SYS@book> @ fchaz 0000000062452578
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 0000000062452548          1          1 KGLHD                   816 recr             80 00               0000000062452548 0000000062452878
--//表对象dept父句柄占用816字节。

SYS@book> @ fchaz 0000000063586360
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 00000000635862F0          1          1 KGLDA                   512 freeabl           0 00               00000000635862F0 00000000635864F0
--//堆0描述符用512字节。
--//我一直有1个奇怪的想法，oracle为什么句柄，堆描述符的KSMCHCOM后面不追加^edb820c，这样不是很清晰吗？

SYS@book> @ ksmsp 0000000063586360 0=1
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1    HEAP_DESC
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- -------------------------
SGA 00000000635862F0          1          1 KGLDA                   512 freeabl           0 00               00000000635862F0 00000000635864F0
SGA 0000000062451018          1          1 KGLH0^edb820c          4096 recr           4095 0000000063586360 0000000062451018 0000000062452018  KSMCHPAR=0000000063586360
--//堆0占用4096字节。

SYS@book> @opeek 0000000062451018 4096 1
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3917_0001.trc
Statement processed.
[062451018, 062452018) = 00001001 80B38F00 62450E18 00000000 63593870 00000000 6855E5C0 00000000 67555388 00000000 01000003 00060FFF 63586360 00000000 ...

--//打开转储文件可以发现如下内容：
062451530 00000000 00000000 00000FD0 534C474B  [............KGLS]
062451540 6264655E 63303238 27000000 00387FFF  [^edb820c...'..8.]
062451550 7FFF0030 00000FE0 00010003 00000401  [0...............]
062451560 00000000 00000000 61EE46B8 00000000  [.........F.a....]
062451570 61EE4708 00000000 00000061 00B38F00  [.G.a....a.......]
062451580 156BF6DC 00000000 62451648 00000000  [..k.....H.Eb....]
062451590 6818E9A0 00000000 00000000 00040041  [...h........A...]
0624515A0 00000000 00000000 00000FC0 000001C0  [................]
0624515B0 62451108 00000000 6818DB70 00000000  [..Eb....p..h....]
0624515C0 00000009 00000000 0013907F 00000000  [................]
0624515D0 7C7F37D8 00000000 00000061 00B38F00  [.7.|....a.......]
0624515E0 156BF6DC 00000000 624516E0 00000000  [..k.......Eb....]
0624515F0 67B64E48 00000000 00000000 00040041  [HN.g........A...]
062451600 00000000 00000000 00000FC0 00000A08  [................]
062451610 62451108 00000000 67B63F38 00000000  [..Eb....8?.g....]
062451620 0000000A 00000000 0013907F 00000000  [................]
062451630 7C7F37D8 00000000 00000099 00B38F00  [.7.|............]
062451640 17ADE2C8 00000000 601FA3C8 00000000  [...........`....]
062451650 00000000 00000000 62451588 00000000  [..........Eb....]
062451660 6818DB70 00000000 6818DB80 00000000  [p..h.......h....]
062451670 00000000 00000000 00000000 00000000  [................]
062451680 00010200 0000FFFF 00000000 00000000  [................]
062451690 00000FD0 534C474B 6264655E 63303238  [....KGLS^edb820c]
0624516A0 27000000 00387FFF 7FFF0030 00000FE0  [...'..8.0.......]
0624516B0 00010003 00000401 00000000 00000000  [................]
0624516C0 6818DBC0 00000000 6818DC10 00000000  [...h.......h....]
0624516D0 00000099 00B38F00 17ADE2C8 00000000  [................]
0624516E0 601FA3C8 00000000 00000000 00000000  [...`............]
0624516F0 624515E8 00000000 67B63F38 00000000  [..Eb....8?.g....]
062451700 67B63F48 00000000 00000000 00000000  [H?.g............]
062451710 00000000 00000000 00010200 0000FFFF  [................]
062451720 00000000 00000000 00000FD0 534C474B  [............KGLS]
062451730 6264655E 63303238 27000000 00387FFF  [^edb820c...'..8.]
062451740 7FFF0030 00000FE0 00010003 00000401  [0...............]
--//出现KGLS^edb820c 内容3次，而后面 edb820c = 249266700，正好与表的hash值对上。

SYS@book> @ ksmsp 0000000063586360 "regexp_like(KSMCHCOM,'edb820c') order by KSMCHPAR"
LOC KSMCHPTR         KSMCHIDX KSMCHDUR KSMCHCOM      KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1    HEAP_DESC
--- ---------------- -------- -------- ------------- -------- -------- -------- ---------------- ---------------- ----------------- -------------------------
SGA 00000000635862F0        1        1 KGLDA              512 freeabl         0 00               00000000635862F0 00000000635864F0                           --//堆0描述符
SGA 0000000061EE4638        1        4 KGLS^edb820c      4096 recr         4095 00000000624514F0 0000000061EE4638 0000000061EE5638
SGA 000000006818DB40        1        4 KGLS^edb820c      4096 recr         4095 0000000062451648 000000006818DB40 000000006818EB40
SGA 0000000067B63F08        1        4 KGLS^edb820c      4096 recr         4095 00000000624516E0 0000000067B63F08 0000000067B64F08
SGA 0000000062451018        1        1 KGLH0^edb820c     4096 recr         4095 0000000063586360 0000000062451018 0000000062452018  KSMCHPAR=0000000063586360--//堆0
--//注：我开始也以为是hash冲突，不过转储堆0的内容也存在类似信息，说明不大可能。

SYS@book> @ fchaz 00000000624514F0
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 0000000062451018          1          1 KGLH0^edb820c          4096 recr           4095 0000000063586360 0000000062451018 0000000062452018

SYS@book> @ fchaz 0000000062451648
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 0000000062451018          1          1 KGLH0^edb820c          4096 recr           4095 0000000063586360 0000000062451018 0000000062452018

SYS@book> @ fchaz 00000000624516E0
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 0000000062451018          1          1 KGLH0^edb820c          4096 recr           4095 0000000063586360 0000000062451018 0000000062452018
--//这3个地址都在堆0中，说明指向的3个chunk也是属于表对象的相关信息。

3.转储看看里面内容：
SYS@book> @ opeek 0000000061EE4638 4096 1
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3917_0002.trc
Statement processed.
[061EE4638, 061EE5638) = 00001001 80B38F00 61EE3638 00000000 6818DB50 00000000 60204EF8 00000000 661A9A90 00000000 00000003 00020FFF 624514F0 00000000 ...

SYS@book> @ opeek 000000006818DB40 4096 1
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3917_0003.trc
Statement processed.
[06818DB40, 06818EB40) = 00001001 80B38F00 6818CB40 00000000 67B63F18 00000000 61EE4648 00000000 675554F0 00000000 00000003 00020FFF 62451648 00000000 ...

SYS@book> @ opeek 0000000067B63F08 4096 1
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3917_0004.trc
Statement processed.
[067B63F08, 067B64F08) = 00001001 80B38F00 67B62F08 00000000 6348D9D0 00000000 6818DB50 00000000 675554C8 00000000 00000003 00020FFF 624516E0 00000000 ...

$ cdf /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3917_0004.trc
cd /u01/app/oracle/diag/rdbms/book/book/trace

$  egrep "DNAME|LOC|DEPTNO|NULL" book_ora_3917_000[234].trc
book_ora_3917_0002.trc:061EE4A60 00000000 00000000 4F4C0003 00000043  [..........LOC...]
book_ora_3917_0002.trc:061EE4C20 4E440005 00454D41 00000000 00000000  [..DNAME.........]
book_ora_3917_0002.trc:061EE4DB0 00000000 00000000 45440006 4F4E5450  [..........DEPTNO]
book_ora_3917_0003.trc:06818DF70 4F4C0003 00000043 00000000 00000000  [..LOC...........]
book_ora_3917_0003.trc:06818E120 00000000 00000000 4E440005 00454D41  [..........DNAME.]
book_ora_3917_0003.trc:06818E2C0 45440006 4F4E5450 00000000 00000000  [..DEPTNO........]
--//可以发现有2个chunk存在表字段信息。
--//注：关于转储的测试多次，大部分情况仅仅1个转储有表字段信息，其他2个转储文件根本看不出是什么内容。

4.加入一些约束看看：
SCOTT@book01p> alter table dept modify(dname  not null ,loc not null);
Table altered.

SYS@book> @ sharepool/shp4 0 249266700
SYS@book> @ pr
==============================
HANDLE_TYPE                   : parent handle address
KGLHDADR                      : 0000000062452578
KGLHDPAR                      : 0000000062452578
C40                           : DEPT.SCOTT
KGLHDLMD                      : 0
KGLHDPMD                      : 0
KGLHDIVC                      : 0
KGLOBHD0                      : 0000000063586360
KGLOBHD6                      : 00
KGLOBHS0                      : 4064
KGLOBHS6                      : 0
KGLOBT16                      : 0
N0_6_16                       : 4064
N20                           : 4064
KGLNAHSH                      : 249266700
KGLOBT03                      :
KGLOBT09                      : 0
PL/SQL procedure successfully completed.
--//句柄以及堆0都在。

SYS@book> @ ksmsp 0000000063586360 "regexp_like(KSMCHCOM,'edb820c') order by KSMCHPAR"
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1    HEAP_DESC
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- -------------------------
SGA 00000000635862F0          1          1 KGLDA                   512 freeabl           0 00               00000000635862F0 00000000635864F0
SGA 0000000067B63F08          1          4 KGLS^edb820c           4096 recr           4095 00000000624514F0 0000000067B63F08 0000000067B64F08
SGA 0000000062451018          1          1 KGLH0^edb820c          4096 recr           4095 0000000063586360 0000000062451018 0000000062452018  KSMCHPAR=0000000063586360
--//加入一些约束后一些chunk会刷新出共享池。KSMCHCOM=KGLS^edb820c仅仅存在1个chunk。

SCOTT@book01p> @desc dept
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      DEPTNO                          NOT NULL NUMBER(2)
    2      DNAME                           NOT NULL VARCHAR2(14)
    3      LOC                             NOT NULL VARCHAR2(13)

SYS@book> @ ksmsp 0000000063586360 "regexp_like(KSMCHCOM,'edb820c') order by KSMCHPAR"
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1    HEAP_DESC
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- -------------------------
SGA 00000000635862F0          1          1 KGLDA                   512 freeabl           0 00               00000000635862F0 00000000635864F0
SGA 0000000067B63F08          1          4 KGLS^edb820c           4096 recr           4095 00000000624514F0 0000000067B63F08 0000000067B64F08
SGA 0000000062AB3990          1          4 KGLS^edb820c           4096 recr           4095 0000000062451648 0000000062AB3990 0000000062AB4990
SGA 0000000061EE4638          1          4 KGLS^edb820c           4096 freeabl           0 00000000624516E0 0000000061EE4638 0000000061EE5638
SGA 000000006818DB40          1          4 KGLS^edb820c           4096 freeabl           0 00000000624516E0 000000006818DB40 000000006818EB40
SGA 0000000062AAF990          1          4 KGLS^edb820c           4096 recr           4095 00000000624516E0 0000000062AAF990 0000000062AB0990
SGA 0000000062451018          1          1 KGLH0^edb820c          4096 recr           4095 0000000063586360 0000000062451018 0000000062452018  KSMCHPAR=0000000063586360
7 rows selected.
--//前面相同的查询仅仅看到5条记录，而现在看到7条记录。
--//KSMCHPAR=00000000624516E0的记录有3条，可以猜测这个KSMCHPAR=00000000624516E0指向的chunk保存的是约束定义。
--//我感觉有点吃惊的是仅仅增加2个字段的非null约束，就增加2个chunk，难道1个chunk仅仅保存1个约束条件吗？

SYS@book> @ opeek 0000000067B63F08 4096 1
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4412_0001.trc
Statement processed.
[067B63F08, 067B64F08) = 00001001 80B38F00 67B62F08 00000000 62AB39A0 00000000 60204980 00000000 675554F0 00000000 00000003 00020FFF 624514F0 00000000 ...

SYS@book> @ opeek 0000000062AB3990 4096 1
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4412_0002.trc
Statement processed.
[062AB3990, 062AB4990) = 00001001 80B38F00 62AB2990 00000000 62AAF9A0 00000000 67B63F18 00000000 696FC040 00000000 00000003 00020FFF 62451648 00000000 ...

SYS@book> @ opeek 0000000061EE4638 4096 1
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4412_0003.trc
Statement processed.
[061EE4638, 061EE5638) = 00001001 00B38F00 61EE3638 00000000 60200628 00000000 00000003 00000000 624516E0 00000000 6818DB60 00000000 00000701 C0B38F00 ...

SYS@book> @ opeek 000000006818DB40 4096 1
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4412_0004.trc
Statement processed.
[06818DB40, 06818EB40) = 00001001 00B38F00 6818CB40 00000000 60200628 00000000 00000003 00000000 624516E0 00000000 62AAF9C0 00000000 00000701 C0B38F00 ...

SYS@book> @ opeek 0000000062AAF990 4096 1
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4412_0005.trc
Statement processed.
[062AAF990, 062AB0990) = 00001001 80B38F00 62AAE990 00000000 61536F80 00000000 62AB39A0 00000000 696FC018 00000000 00000003 00020FFF 624516E0 00000000 ...

 $ egrep "DNAME|LOC|DEPTNO|NULL" book_ora_4412_000?.trc
book_ora_4412_0001.trc:067B64330 00000000 00000000 4F4C0003 00000043  [..........LOC...]
book_ora_4412_0001.trc:067B644F0 4E440005 00454D41 00000000 00000000  [..DNAME.........]
book_ora_4412_0001.trc:067B64680 00000000 00000000 45440006 4F4E5450  [..........DEPTNO]

book_ora_4412_0002.trc:062AB40A0 15B50E98 00007F57 414E4422 2022454D  [....W..."DNAME" ]
book_ora_4412_0002.trc:062AB40B0 4E205349 4E20544F 004C4C55 00000000  [IS NOT NULL.....]

book_ora_4412_0003.trc:061EE4A60 00000000 00000000 4F4C0003 00000043  [..........LOC...]
book_ora_4412_0003.trc:061EE4C20 4E440005 00454D41 00000000 00000000  [..DNAME.........]

book_ora_4412_0004.trc:06818DF70 4F4C0003 00000043 00000000 00000000  [..LOC...........]
book_ora_4412_0004.trc:06818E120 00000000 00000000 4E440005 00454D41  [..........DNAME.]

--//KSMCHPAR=00000000624514F0 确实是表定义应该没有问题。
--//KSMCHPAR=00000000624516E0 指向3个chunk，保存应该是约束定义(我猜测)。但是并没有看到is not null的信息。
--//KSMCHPAR=0000000062451648 执行的chunk保存是什么确实猜测不出来。

4，简单总结：
--//以上仅仅是我的猜测，表对象父句柄，堆0，堆0描述符。
--//堆0里面保存KSMCHCOM=KGLS^<hash_hex>的堆描述符，存在3个，其中1个指向表定义，另外1个指向是约束。
--//没有想到约束消耗空间还很大，几乎1个约束1个chunk，大约4k。
--//注：这个很容易测试，你可以建立多个字段的空表，然后对比加入约束与不加入约束的情况比较就很清楚了。
--//至于里面的信息转储我真心看不出来。
--//还有1个我猜测不出来，看到的大部分信息是乱码。

5.附上测试使用的脚本：
$ type cdf
cdf is a function
cdf ()
{
    echo cd $(dirname $1);
    cd $(dirname $1)
}

$ cat opeek.sql
@ ti
oradebug setmypid
oradebug peek 0x&1 &2 &3
prompt
--//ti.sql来自tpt包。

$ cat ksmsp.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

--------------------------------------------------------------------------------
--
-- File name:   fcha.sql (Find CHunk Address) v0.2
-- Purpose:     Find in which heap (UGA, PGA or Shared Pool) a memory address resides
--
-- Author:      Tanel Poder
-- Copyright:   (c) http://blog.tanelpoder.com | @tanelpoder
--
-- Usage:       @fchaz <addr_hex> --
--              @fchaz F6A14448  --
--
-- Other:       This would only report an UGA/PGA chunk address if it belongs
--              to *your* process/session (x$ksmup and x$ksmpp do not see other
--              session/process memory)
--
--------------------------------------------------------------------------------

--prompt Find in which heap (UGA, PGA or Shared Pool) the memory address &1 resides...
--prompt
--prompt WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention
--prompt in systems under load and with large shared pool. This may even completely hang
--prompt your instance until the query has finished! You probably do not want to run this in production!
--prompt
--pause  Press ENTER to continue, CTRL+C to cancel...

col fcura_addrlen new_value _fcura_addrlen format 999
set termout off
select vsize(addr)*2 fcura_addrlen from x$dual;

col 2 new_value 2
select null "2" from dual where 1=0;
select decode('&2',null,'0=1',0,'0=1','&2') "2" from dual;
set termout on

SELECT 'SGA' LOC
      ,KSMCHPTR
      ,KSMCHIDX
      ,KSMCHDUR
      ,KSMCHCOM
      ,KSMCHSIZ
      ,KSMCHCLS
      ,KSMCHTYP
      ,KSMCHPAR
      ,KSMCHPTR KSMCHPTR_BEGIN
      ,TO_CHAR
       (
          TO_NUMBER (KSMCHPTR, 'XXXXXXXXXXXXXXXX') + KSMCHSIZ
         ,'FM0XXXXXXXXXXXXXXX'
       )
          "KSMCHPTR_END+1"
      , DECODE ( ksmchpar , HEXTORAW (LPAD (UPPER ('&1'), &_fcura_addrlen, '0')),'KSMCHPAR=&1') heap_desc
  FROM x$ksmsp
 WHERE    ksmchptr = HEXTORAW (LPAD (UPPER ('&1'), &_fcura_addrlen, '0'))
       OR ksmchpar = HEXTORAW (LPAD (UPPER ('&1'), &_fcura_addrlen, '0'))
       OR TO_NUMBER
          (
             SUBSTR ('&1', INSTR (LOWER ('&1'), 'x') + 1)
            ,LPAD ('X', &_fcura_addrlen, 'X')
          ) BETWEEN TO_NUMBER (ksmchptr, LPAD ('X', &_fcura_addrlen, 'X'))
                AND   TO_NUMBER (ksmchptr, LPAD ('X', &_fcura_addrlen, 'X'))
                    + ksmchsiz
                    - 1
       OR &&2;



$ cat sharepool/shp4.sql
column N0_6_16 format 99999999
column handle_type format a22

set term off
define vc=&&2
col 2 new_value 2
column text format a20
column hex_status format a10
SELECT text
      ,n10 "2"
      ,REPLACE (TO_CHAR (n10, LPAD ('x', 16, 'x')), ' ') c16
      ,hex_status
  FROM (SELECT '&&vc' text
--             ,DECODE ( hex_status ,'hex', TO_NUMBER ( SUBSTR ('&&vc', INSTR (LOWER ('&&vc'), 'x',-1) + 1) ,'xxxxxxxxxxxxxxxx') ,'dec', TO_NUMBER ('&&vc') ,0) n10
              ,DECODE ( hex_status ,'hex', TO_NUMBER ( replace (lower('&&vc'), 'x') ,'xxxxxxxxxxxxxxxx') ,'dec', TO_NUMBER ('&&vc') ,0) n10
              ,hex_status
          FROM (SELECT CASE
                          WHEN REGEXP_LIKE (LOWER ('&&vc'), '^[0-9]+$') THEN 'dec'
                          WHEN REGEXP_LIKE (LOWER ('&&vc') ,'^0?[xX]?[0-9a-f]+[xX]?$') THEN 'hex'
                          ELSE 'other'
                       END AS Hex_Status
                  FROM DUAL));

set term on

SELECT /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */
       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, kglnaobj||'.'||kglnaown), '(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 kglobt03 = lower('&1') or KGLNAHSH= &2;