[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;