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