[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;
如有侵犯您的版权，请及时联系3500663466#qq.com(#换@)，我们将第一时间删除本站数据。
暂无评论内容