[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