ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 20261113跟踪通过SQL Translation Framewordd的sql语句library cache lock/pin的情况

20261113跟踪通过SQL Translation Framewordd的sql语句library cache lock/pin的情况

原创 Oracle 作者：lfree 时间：2026-11-13 20:28:13 0 删除 编辑

[20261113]跟踪通过SQL Translation Framewordd的sql语句library cache lock，library cache pin的情况2.txt

--//跟踪通过SQL Translation Framework的sql语句library cache lock，library cache pin的情况。
--//设置SQL Translation Framework参考前面的测试。

1.环境：
SYS@book> @ 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.

2.测试：
SCOTT@book01p> alter session set cursor_sharing = force;
Session altered.

SCOTT@book01p> alter session set sql_translation_profile = test_profile;
Session altered.

SCOTT@book01p> select vc from t1 where cr_date = timestamp '2026-10-31 12:16:01';
VC
--------------------------------
test589
/
/
/
/
--//测试前可以在另外的会话执行select vc from t1 where rownum=1多次，避免一些递归sql语句。

# bpftrace lkpn21c.bt 5756
Attached 2 probes

--//补充第1次执行的情况，会发现执行如下语句：
select distinct sqlhash from sqltxl_sql$ where obj#=:1 and bitand(flags,:2) > 0
select txltext, sqlid from sqltxl_sql$ where obj#=:1 and bitand(flags,:2) > 0 and sqlhash=:3 and dbms_lob.compare(sqltext,:4)=0

SYS@book01p> select *  from sqltxl_sql$
  2  @ pr
==============================
OBJ#                          : 78207
SQLTEXT                       : select vc from t1 where cr_date = timestamp :"SYS_B_0"
TXLTEXT                       : select vc from t1 where cr_date = to_date(:"SYS_B_0",'YYYY-MM-DD hh24:mi:ss')
SQLID                         : g035npxsfg1af
SQLHASH                       : 1894221134
FLAGS                         : 1
RTIME                         : 2026-11-09 16:51:59.025865
CINFO                         :
MODULE                        :
ACTION                        :
PUSER#                        :
PSCHEMA#                      :
COMMENT$                      :
ERRCODE#                      :
ERRSRC                        :
TXLMTHD                       :
DICTID                        :
PL/SQL procedure successfully completed.

--//第5次执行的结果。
kgllkal pid=3081 count 204 -- handle address: 000000006b976e50, mode: 1 kglnaobj address: select vc from t1 where cr_date = timestamp :"SYS_B_0"
kgllkal pid=3081 count 205 -- handle address: 0000000069c310a8, mode: 1 kglnaobj address: select vc from t1 where cr_date = to_date(:"SYS_B_0",'YYYY-MM-DD hh24:mi:ss')
kgllkal pid=3081 count 206 -- handle address: 0000000069c2f948, mode: 1 kglnaobj address:

--//可以发现多次执行后还差存在kgllkal的函数调用，实际上其他语句也出现类似。
--//换一句话讲,做这类情况是有代价的.

3.分析：

SCOTT@book01p> select vc from t1 where cr_date = timestamp '2026-10-31 12:16:01';
VC
--------------------------------
test589

SCOTT@book01p> @ hashz
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- ---------- ------------------- -----------
2494224214 82pn8n6aappuq            0      55126  94aad756  2026-11-13 16:20:28    16777220

SCOTT@book01p> select * from gv$open_cursor where sql_id='82pn8n6aappuq';
INST_ID SADDR                   SID USER_NAME        ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                                     LAST_SQL_ACTIVE_TIM CURSOR_TYPE            CHILD_ADDRESS    CON_ID
------- ---------------- ---------- ---------------- ---------------- ---------- ------------- ------------------------------------------------------------ ------------------- ---------------------- ---------------- ------
      1 000000007E7387A8         28 SCOTT            0000000069C310A8 2494224214 82pn8n6aappuq select vc from t1 where cr_date = to_date(:"SYS_B_0",'YYYY-M 2026-11-13 16:20:28 SESSION CURSOR CACHED  0000000069C2F948      3
      1 000000007E7387A8         28 SCOTT            0000000069C310A8 2494224214 82pn8n6aappuq select vc from t1 where cr_date = to_date(:"SYS_B_0",'YYYY-M 2026-11-13 16:19:07 SESSION CURSOR CACHED  0000000069C2F948      3
      1 000000007E7387A8         28 SCOTT            0000000069C310A8 2494224214 82pn8n6aappuq select vc from t1 where cr_date = to_date(:"SYS_B_0",'YYYY-M 2026-11-13 16:19:03 SESSION CURSOR CACHED  0000000069C2F948      3

--//出现1个奇怪的现象出现gv$open_cursor视图，出现3行除了LAST_SQL_ACTIVE_TIM外，其他信息完全一样。

SYS@book> @ sharepool/shp4 82pn8n6aappuq -1
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16       N20   KGLNAHSH KGLOBT03        KGLHDBID   KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- --------- ---------- ------------- ---------- ----------
child handle address   0000000069C2F948 0000000069C310A8 select vc from t1 where cr_date = to_dat          1          0          0 0000000069C2F788 0000000069C306D0       8128      20224       3366     31718     31718 2494224214 82pn8n6aappuq      55126          0
parent handle address  0000000069C310A8 0000000069C310A8 select vc from t1 where cr_date = to_dat          1          0          0 0000000069C30EE8 00                     4064          0          0      4064      4064 2494224214 82pn8n6aappuq      55126      65535

--//注意看上面handle address对应第2，3行是父子游标。

--//再来看看第1行的handle address: 000000006b976e50
SYS@book> @ sharepool/shp4z 000000006b976e50 -1
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16       N20   KGLNAHSH KGLOBT03        KGLHDBID   KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- --------- ---------- ------------- ---------- ----------
parent handle address  000000006B976E50 000000006B976E50 select vc from t1 where cr_date = timest          0          0          0 00               00                        0          0          0         0         0 2457767451 g035npxsfg1af      36379          0
--//存在1行非常独特对象。sql_id=g035npxsfg1af.

SYS@book> @ kglob g035npxsfg1af -1 '' ''
==============================
INST_ID                       : 1
OWNER                         :
NAME                          : select vc from t1 where cr_date = timestamp :"SYS_B_0"
DB_LINK                       :
NAMESPACE                     : SQL TRANSLATION CACHE
TYPE                          : SQL TRANSLATION CACHE
NAMESPACE_NUM                 : 128
NAMESPACE_HEX                 : 80
SHARABLE_MEM                  : 0
PERSISTENT_MEM                : 0
RUNTIME_MEM                   : 0
LOADS                         : 0
EXECUTIONS                    : 5
LOCKS                         : 0
PINS                          : 0
KEPT                          : NO
CHILD_LATCH                   : 36379
INVALIDATIONS                 : 0
HASH_VALUE                    : 2457767451
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HASH_HEX                      : 927e8e1b
LOCK_MODE                     : NONE
PIN_MODE                      : NONE
STATUS                        : UNKOWN
TIMESTAMP                     : 2026-11-13/16:18:55
PREVIOUS_TIMESTAMP            :
LOCKED_TOTAL                  : 5
PINNED_TOTAL                  : 0
PROPERTY                      :
FULL_HASH_VALUE               : 61c0bee710252fb1c1d2ac05927e8e1b
CON_ID                        : 3
CON_NAME                      : BOOK01P
ADDR                          : 000000006B976E50
PAR_ADDR                      : 000000006B976E50
HANDLE_TYPE                   : parent handle address
KGLOBHD0                      : 00
KGLOBHD6                      : 00
EDITION                       :
SQL_ID                        : g035npxsfg1af
KGL_BUCKET                    : 36379
OBJECT_STR                    : select vc from t1 where cr_date = timestamp :"SYS_B_0"
PL/SQL procedure successfully completed.
--//NAMESPACE : SQL TRANSLATION CACHE

4.手工计算sql_id看看:

$ sql_idz.sh 'select vc from t1 where cr_date = timestamp :"SYS_B_0"\x00' 3
sql_text = select vc from t1 where cr_date = timestamp :"SYS_B_0"\x00
full_hash_value(16) = 8487722C8A7DBEE0F00CB4AF70E7854E or 8487722c8a7dbee0f00cb4af70e7854e
xxxxx_matching_signature(10) = -1149345139279821490 or  17297398934429730126
hash_value(10) = 1894221134 or hash_value(16) = 70E7854E or 70e7854e
kgl_backet(10) = 99662 or kgl_bucket(16) =  1854E or 1854e
sql_id(16) = F00CB4AF70E7854E or f00cb4af70e7854e
sql_id(32) = g035npxsfg1af
sql_id(32) = g035npxsfg1af
sql_id(32) = g035npxsfg1af
--//sql_id能对上,但是hash_value(10) = 1894221134与查询结果对不上HASH_VALUE = 2457767451.

$ sql_idz.sh "select vc from t1 where cr_date = to_date(:\"SYS_B_0\",'YYYY-MM-DD hh24:mi:ss')\x00" 3
sql_text = select vc from t1 where cr_date = to_date(:"SYS_B_0",'YYYY-MM-DD hh24:mi:ss')\x00
full_hash_value(16) = 110BC4B89D42620B815688A194AAD756 or 110bc4b89d42620b815688a194aad756
xxxxx_matching_signature(10) = -9126957367254395050 or  9319786706455156566
hash_value(10) = 2494224214 or hash_value(16) = 94AAD756 or 94aad756
kgl_backet(10) = 55126 or kgl_bucket(16) =  D756 or d756
sql_id(16) = 815688A194AAD756 or 815688a194aad756
sql_id(32) = 82pn8n6aappuq
sql_id(32) = 82pn8n6aappuq
sql_id(32) = 82pn8n6aappuq
--//真实执行的sql语句能对上.

--//说明NAMESPACE=SQL TRANSLATION CACHE计算方式不对.

--//使用自己写md5sum.bt脚本测试看看。
# bpftrace md5sum.bt 3081
Attached 10 probes
Tracing Oracle kggmd5Update... Target PID: 3081
Hit Ctrl-C to stop.


kgscComputeHash Begin
[16:37:39] kggmd5Update #01 pid=3081 ptr=00007ffc59984b28 hash_text=select vc from t1 where cr_date = timestamp '2026-10-31 12:16:01'\x00 length=66
[16:37:39] kgscComputeHash #03 pid=3081 return hash_valuer=8549fece 2236219086
kgscComputeHash END

kglComputeHash0 Begin
[16:37:39] kggmd5Update #04 pid=3081 ptr=00007f7f81a9fd10 hash_text=select vc from t1 where cr_date = timestamp :"SYS_B_0"\x00 length=55
[16:37:39] kggmd5Update #05 pid=3081 ptr=00000000154f4ffc hash_text=. length=1
[16:37:39] kggmd5Update #06 pid=3081 ptr=00007ffc599826c0 hash_text=78207 length=5
[16:37:39] kggmd5Update #07 pid=3081 ptr=00000000154f4ffc hash_text=. length=1
[16:37:39] kggmd5Update #08 pid=3081 ptr=000000007e738eb4 hash_text=BOOK01P length=7
[16:37:39] kggmd5Update #09 pid=3081 ptr=00007ffc599821e0 hash_text=\x80\x00\x00\x00 length=4
[16:37:39] kglComputeHash0 #11 pid=3081 return hash_valuer=0x927e8e1b 2457767451
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
kglComputeHash0 End

kgscComputeHash Begin
[16:37:39] kggmd5Update #12 pid=3081 ptr=00007f7f81a9fd58 hash_text=select vc from t1 where cr_date = to_date(:"SYS_B_0",'YYYY-MM-DD hh24:mi:ss')\x00 length=78
[16:37:39] kgscComputeHash #14 pid=3081 return hash_valuer=94aad756 2494224214
kgscComputeHash END

--//可以看出参与计算的字符串拼接如下：
select vc from t1 where cr_date = timestamp :"SYS_B_0"\x00.78207.BOOK01P\x80\x00\x00\x00

$ sql_idz.sh 'select vc from t1 where cr_date = timestamp :"SYS_B_0"\x00.78207.BOOK01P\x80\x00\x00\x00' 3
sql_text = select vc from t1 where cr_date = timestamp :"SYS_B_0"\x00.78207.BOOK01P\x80\x00\x00\x00
full_hash_value(16) = 61C0BEE710252FB1C1D2AC05927E8E1B or 61c0bee710252fb1c1d2ac05927e8e1b
xxxxx_matching_signature(10) = -4480329539347640805 or  13966414534361910811
hash_value(10) = 2457767451 or hash_value(16) = 927E8E1B or 927e8e1b
kgl_backet(10) = 36379 or kgl_bucket(16) =  8E1B or 8e1b
sql_id(16) = C1D2AC05927E8E1B or c1d2ac05927e8e1b
sql_id(32) = c3npc0q97x3hv
sql_id(32) = c3npc0q97x3hv
sql_id(32) = c3npc0q97x3hv

--//hash_value(10) = 2457767451 ,完全对上。

--//  \x80\x00\x00\x00 对应的namespace= 128 = 0x80，看看数字78207表示什么？

SCOTT@book01p> @ oid 78207
SCOTT@book01p> @ pr
==============================
O_OWNER                       : SCOTT
O_OBJECT_NAME                 : TEST_PROFILE
O_OBJECT_TYPE                 : SQL TRANSLATION PROFILE
SUBOBJECT_NAME                :
CREATED                       : 2026-11-09 16:51:59
LAST_DDL_TIME                 : 2026-11-09 16:51:59
O_STATUS                      : VALID
DATA_OBJECT_ID                :
OBJECT_ID                     : 78207
PL/SQL procedure successfully completed.

--//对应的类型SQL TRANSLATION PROFILE。

5.附上测试使用脚本：
# cat md5sum.bt
BEGIN
{
    printf("Tracing Oracle kggmd5Update... Target PID: %d\n", $1);
    printf("Hit Ctrl-C to stop.\n\n");
}

uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kggmd5Finish
/ $1 == 0 || pid == $1 /
{
    @is_fin[pid] = 1;
    //printf("\n%s Begin\n",func);
}

uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kggmd5Finish
/ $1 == 0 || pid == $1 /
{
    delete(@is_fin[pid]);
    //printf("%s End\n",func);
}

uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kgscComputeHash
/ $1 == 0 || pid == $1 /
{
    printf("\n%s Begin\n",func);
}

uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kgscComputeHash
/ $1 == 0 || pid == $1 /
{
    printf("\033[1;32m[%s] %s #%02d pid=%d return hash_valuer=%08lx %lu \033[0m\n", strftime("%H:%M:%S", nsecs), func, @kgg, pid,retval,retval);
    printf("%s END\n",func);
}

uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kglComputeHash0
/ $1 == 0 || pid == $1 /
{
    printf("\n%s Begin\n",func);
}

uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kglComputeHash0
/ $1 == 0 || pid == $1 /
{
    printf("\033[1;31m[%s] %s #%02d pid=%d return hash_valuer=0x%08lx %lu \033[0m\n", strftime("%H:%M:%S", nsecs),func,@kgg,pid,retval,retval);
    //printf("[%s] %s #%02d pid=%d return hash_valuer=%08lx %lu\n", strftime("%H:%M:%S", nsecs), func, @kgg, pid,retval,retval);
    printf("%s End\n",func);
}

uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kggmd5Update
/ $1 == 0 || pid == $1 /
{
    @kgg++;
    @is_kgg[pid] = 1;
    if (@is_fin[pid] != 1 ){
        printf("[%s] %s #%02d pid=%d ptr=%016lx hash_text=%s length=%d\n", strftime("%H:%M:%S", nsecs), func,@kgg, pid,arg1,buf(arg1,arg2), arg2);
    }
}

uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kksComputeSqlid
/ $1 == 0 || pid == $1 /
{
    printf("\033[1;36m[%s] %s #%02d pid=%d return xxxxx_matching_signature=0x%016lx %lu\033[0m\n", strftime("%H:%M:%S", nsecs),func, @kgg,pid, retval, retval);
}

END
{
    printf("\nTotal kggmd5Update: %d\n", @kgg);
    delete(@kgg);
    clear(@is_kgg);
}

# cat lkpn21c.bt
#!/usr/bin/env bpftrace
uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kgllkal
/ $1 == 0 || pid == $1 /
{
    @lk++;
    printf("kgllkal count %02d -- handle address: %016lx, mode: %d ", @lk, arg2, arg3);
    printf("spid %d ",pid);
    printf("kglnaobj address: %s\n", str(arg2 + 0x1c8, 256));
}

uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kglpnal
/ $1 == 0 || pid == $1 /
{
    @pn++;
    printf("kglpnal count %02d -- handle address: %016lx, mode: %d ", @pn, arg2, arg3);
    printf("spid %d ",pid);
    printf("kglnaobj address: %s\n", str(arg2 + 0x1c8, 256));
}

$ cat sharepool/shp4z.sql
column N0_6_16 format 99999999
column N20 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,
       kglhdbid,
       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;

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3188945/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20261109]关于sql语句对象的idn.txt
下一篇： 没有了~
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4019
  • 访问量
    7997586

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593