[20260925]21c数字对象是什么(补充).txt
[20260925]21c数字对象是什么(补充).txt
--//2024年8月事情记得第1次在21c跟踪执行sql语句library cache lock/library cache pin,有点点吃惊.直到现在不理解,现在大部分
--//数字对象都知道来自那里,前段时间写了[20260901]表exp_head$的exp_id如何计算.txt,实际上还有1个数字对象无法确定,今天在整
--//理笔记时才发现,在写那篇文章时,我已经知道答案,只不过当时专注exp_head$的exp_id的计算,给跳过了,今天做1点点补充.
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.
2.测试分析：
SCOTT@book01p> @ spid
==============================
SID : 24
SERIAL# : 18612
PROCESS : 3038
SERVER : DEDICATED
SPID : 3040
PID : 8
P_SERIAL# : 5
KILL_COMMAND : alter system kill session '24,18612' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> @ oname scott.^dept$
SCOTT@book01p> @pr
==============================
O_OWNER : SCOTT
O_OBJECT_NAME : DEPT
O_OBJECT_TYPE : TABLE
SUBOBJECT_NAME :
CREATED : 2026-06-25 09:20:13
LAST_DDL_TIME : 2026-06-25 09:20:13
O_STATUS : VALID
DATA_OBJECT_ID : 75828
OBJECT_ID : 75828
PL/SQL procedure successfully completed.
SCOTT@book01p> @ 10to16 75828
10 to 16 HEX (LOWER) 10 TO 16 HEX (UPPER) REVERSE16_LOWER REVERSE16_UPPER
-------------------- -------------------- -------------------- --------------------
0000000000012834 0000000000012834 0x34280100-00000000 0X34280100-00000000
--//记下spid=3040, 执行select dname from dept where rownum<=1;多次,避免递归sql语句.
--//然后修改sql语句1个字符，主要目的执行的sql语句硬解析。
SCOTT@book01p> Select dname from dept where rownum<=1;
DNAME
------------------------------
ACCOUNTING
# bpftrace kgghash2.bt 3040
Attached 8 probes
Tracing Oracle kgghash2... Target PID: 3040
Hit Ctrl-C to stop.
qosdGetOptDir Begin
[16:17:56] kgghash2 #01 pid=3040 ptr=00007fe463ffdfd8 hash_text=\x01 length=1 seed=0x0000000000012834
[16:17:56] kgghash2 #01 pid=3040 return hash_value=04f24b967a0d16cd 356430430180546253
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
qosdGetOptDir End
--//在qosdGetOptDir执行之间计算的hash值就是遗漏的数字对象。
--//也可以看出它的计算方法，参与运算的字符\x01,估计表示表的namespace=1，长度1，seed = 0x0000000000012834，正是dept的
--//object_id的16进制表示。
qosdExprTravTrackOpn Begin
[16:17:56] kgghash2 #02 pid=3040 ptr=00007fe4636a18b0 hash_text=T75828,2 length=8 seed=0x0000000000000000
[16:17:56] kgghash2 #02 pid=3040 return hash_value=95fedc9553dfa586 10808318689651172742
[16:17:56] kgghash2 #03 pid=3040 ptr=00007ffc20e79958 hash_text=4(\x01\x00 length=4 seed=0x95fedc9553dfa586
[16:17:56] kgghash2 #03 pid=3040 return hash_value=8500f19f6d979982 9583925674085161346
[16:17:56] kgghash2 #04 pid=3040 ptr=00007ffc20e79940 hash_text=\x00\x00\x00\x00\x00\x00\x00\x00 length=8 seed=0x8500f19f6d979982
[16:17:56] kgghash2 #04 pid=3040 return hash_value=3a58369ddfc089ad 4204170303842060717
qosdExprTravTrackOpn End)
--//在qosdExprTravTrackOpn执行之间计算的值对应exp_head$.exp_id, 前面已经做了解析。
--//参考链接: [20260901]表exp_head$的exp_id如何计算.txt
--//跟踪执行sql语句的library cache lock/library cache pin,注意再增加修改1个字符,保证每次硬解析:
SCOTT@book01p> SElect dname from dept where rownum<=1;
DNAME
------------------------------
ACCOUNTING
# bpftrace lkpn21c.bt 3040
Attached 2 probes
kgllkal count 01 -- handle address: 00000000661bd608, mode: 1 spid 3040 kglnaobj address: SElect dname from dept where rownum<=1
kglpnal count 01 -- handle address: 00000000661bd608, mode: 2 spid 3040 kglnaobj address: SElect dname from dept where rownum<=1
kgllkal count 02 -- handle address: 0000000073d5bae0, mode: 2 spid 3040 kglnaobj address: bookSYSCDB$ROOT
kgllkal count 03 -- handle address: 000000006de44850, mode: 2 spid 3040 kglnaobj address: 1847639831SYSCDB$ROOT
kgllkal count 04 -- handle address: 00000000661bc0d8, mode: 2 spid 3040 kglnaobj address: 8e1057d26a121171203d24887a25f180$BUILD$BOOK01P
kgllkal count 05 -- handle address: 00000000661bbea8, mode: 1 spid 3040 kglnaobj address:
kglpnal count 02 -- handle address: 00000000661bbea8, mode: 3 spid 3040 kglnaobj address:
kgllkal count 06 -- handle address: 0000000073d5bae0, mode: 2 spid 3040 kglnaobj address: bookSYSCDB$ROOT
kgllkal count 07 -- handle address: 000000006de44850, mode: 2 spid 3040 kglnaobj address: 1847639831SYSCDB$ROOT
kgllkal count 08 -- handle address: 00000000661baa78, mode: 1 spid 3040 kglnaobj address: 8e1057d26a121171203d24887a25f180Child:0BOOK01P
kglpnal count 03 -- handle address: 00000000661baa78, mode: 3 spid 3040 kglnaobj address: 8e1057d26a121171203d24887a25f180Child:0BOOK01P
kgllkal count 09 -- handle address: 0000000069ef8298, mode: 1 spid 3040 kglnaobj address: SCOTTBOOK01P
kgllkal count 10 -- handle address: 0000000073d5bae0, mode: 2 spid 3040 kglnaobj address: bookSYSCDB$ROOT
kgllkal count 11 -- handle address: 000000006de44850, mode: 2 spid 3040 kglnaobj address: 1847639831SYSCDB$ROOT
kgllkal count 12 -- handle address: 0000000069c8f720, mode: 2 spid 3040 kglnaobj address: DEPTSCOTTBOOK01i
kglpnal count 04 -- handle address: 0000000069c8f720, mode: 2 spid 3040 kglnaobj address: DEPTSCOTTBOOK01i
kgllkal count 13 -- handle address: 0000000069c88bd0, mode: 2 spid 3040 kglnaobj address: 356430430180546253BOOK01P
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
kglpnal count 05 -- handle address: 0000000069c88bd0, mode: 2 spid 3040 kglnaobj address: 356430430180546253BOOK01P
kgllkal count 14 -- handle address: 0000000069c87470, mode: 2 spid 3040 kglnaobj address: 4204170303842060717BOOK01P --//dept.dname
kglpnal count 06 -- handle address: 0000000069c87470, mode: 2 spid 3040 kglnaobj address: 4204170303842060717BOOK01P
--//看下划线的数字部分与前面的计算一样.hash_value=04f24b967a0d16cd 356430430180546253
3.看看是什么数字对象：
SYS@book> @ sharepool/shp4z 0000000069c88bd0 -1
SYS@book> @ pr
==============================
HANDLE_TYPE : parent handle address
KGLHDADR : 0000000069C88BD0
KGLHDPAR : 0000000069C88BD0
C40 : 356430430180546253
KGLHDLMD : 0
KGLHDPMD : 0
KGLHDIVC : 0
KGLOBHD0 : 00
KGLOBHD6 : 00
KGLOBHS0 : 0
KGLOBHS6 : 0
KGLOBT16 : 0
N0_6_16 : 0
N20 : 0
KGLNAHSH : 926311590
KGLOBT03 :
KGLHDBID : 25766
KGLOBT09 : 0
PL/SQL procedure successfully completed.
--//该对象的hash值=926311590。
SYS@book> @ kglob '' 926311590 ''
==============================
INST_ID : 1
OWNER :
NAME : 356430430180546253
DB_LINK :
NAMESPACE : OPTIMIZER DIRECTIVE OWNER
TYPE : CURSOR
NAMESPACE_NUM : 104
NAMESPACE_HEX : 68
SHARABLE_MEM : 0
PERSISTENT_MEM : 0
RUNTIME_MEM : 0
LOADS : 1
EXECUTIONS : 0
LOCKS : 0
PINS : 0
KEPT : NO
CHILD_LATCH : 25766
INVALIDATIONS : 0
HASH_VALUE : 926311590
HASH_HEX : 373664a6
LOCK_MODE : NONE
PIN_MODE : NONE
STATUS : UNKOWN
TIMESTAMP :
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 3
PINNED_TOTAL : 3
PROPERTY :
FULL_HASH_VALUE : 21a7a5646ae58a2d5120aa51373664a6
CON_ID : 3
CON_NAME : BOOK01P
ADDR : 0000000069C88BD0
PAR_ADDR : 0000000069C88BD0
HANDLE_TYPE : parent handle address
KGLOBHD0 : 00
KGLOBHD6 : 00
EDITION :
SQL_ID :
KGL_BUCKET : 25766
OBJECT_STR : 356430430180546253
PL/SQL procedure successfully completed.
--//注：kglob.sql脚本大部分字段信息来源于视图v$db_object_cache。
--//该对象NAMESPACE= OPTIMIZER DIRECTIVE OWNER，TYPE = CURSOR。NAMESPACE的16进制值等于68。
--//使用我写的sql_idz.sh脚本计算full_hash_value，hash_value,完全能对上.
--//\x68表示 NAMESPACE的16进制值.
$ sql_idz.sh "356430430180546253.BOOK01P\x68\0\0\0" 3
sql_text = 356430430180546253.BOOK01P\x68\0\0\0
full_hash_value(16) = 21A7A5646AE58A2D5120AA51373664A6 or 21a7a5646ae58a2d5120aa51373664a6
xxxxx_matching_signature(10) = 5845859582122288294 or 24292603655831839910
hash_value(10) = 926311590 or hash_value(16) = 373664A6 or 373664a6
kgl_backet(10) = 25766 or kgl_bucket(16) = 64A6 or 64a6
sql_id(16) = 5120AA51373664A6 or 5120aa51373664a6
sql_id(32) = 5285aa4vmct56
sql_id(32) = 5285aa4vmct56
sql_id(32) = 5285aa4vmct56
--//直接在gdb调用kgghash2计算看看，简单验证看看：
qosdGetOptDir Begin
[16:17:56] kgghash2 #01 pid=3040 ptr=00007fe463ffdfd8 hash_text=\x01 length=1 seed=0x0000000000012834
[16:17:56] kgghash2 #01 pid=3040 return hash_value=04f24b967a0d16cd 356430430180546253
qosdGetOptDir End
(gdb) p /x (long int) kgghash2((char *)"\x01",1,0x12834)
$1 = 0x4f24b967a0d16cd
--//4f24b967a0d16cd = 356430430180546253
--//完全对上，验证成功。
4.做1个10046跟踪：
--//刷新共享池多次。
SYS@book> alter system flush shared_pool;
System altered.
SCOTT@book01p> @ 10046on 12
Session altered.
SCOTT@book01p> SELECT dname from dept where rownum<=1;
DNAME
------------------------------
ACCOUNTING
SCOTT@book01p> @ 10046off
Session altered.
SCOTT@book01p> @ t
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3598.trc
$ ./extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3598.trc
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2, spare3, signature, spare7, spare8, spare9, nvl(dflcollid, 16382), creappid, creverid, modappid, modverid, crepatchid, modpatchid from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,nvl(t.spare7,0),ts.cachedblk,ts.cachehit,ts.logicalread,ts.im_imcu_count,ts.im_block_count,ts.im_sys_incarnation,ts.im_stat_update_time,ts.scanrate,nvl(t.acdrflags, 0),nvl(t.acdrtsobj#, 0),t.acdrdefaulttime,nvl(t.acdrrowtsintcol#, 0) from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln, minimum_enc, maximum_enc from hist_head$ where obj#=:1 and intcol#=:2
select audit$ from tab$ where obj# = :1
select policy#, action# from aud_object_opt$ where object# = :1 and type = 2
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),nvl(i.evaledition#,1),nvl(i.unusablebefore#,0),nvl(i.unusablebeginning#,0), ist.cachedblk,ist.cachehit,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(intcols) unicols,min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0), nvl(evaledition#,1),nvl(unusablebefore#,0),nvl(unusablebeginning#,0), case when (type# in (1,8,9,96,112)) then nvl(collid, 16382) else 0 end case, nvl(collintcol#,0), nvl(acdrrescol#, 0), nvl(spare7, 0), nvl(spare9, 0), nvl(spare10, 0) from col$ where obj#=:1 order by intcol#
select name,online$,contents$,undofile#,undoblock#,blocksize,dflmaxext,dflinit,dflincr,dflextpct,dflminext, dflminlen, owner#,scnwrp,scnbas, NVL(pitrscnwrp, 0), NVL(pitrscnbas, 0), dflogging, bitmapped, inc#, MOD(flags,(4294967296 * 4294967296)), plugged, NVL(spare1,0), NVL(spare2,0), affstrength, FLOOR(flags/(4294967296 * 4294967296)) from ts$ where ts#=:1
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1
select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3,refact from cdef$ where robj#=:1
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3 from cdef$ where obj#=:1
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
select user#,password,datats#,tempts#,type#,defrole,resource$,ptime,decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,ext_username,spare2,nvl(spare3,16382),spare9,spare10 from user$ where name=:1
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)), max(bitand(nvl(option$,0), 8) /8), max(bitand(nvl(option$,0), 16) /16), max(bitand(nvl(option$,0),64) /64), max(bitand(nvl(option$,0), 128) /128) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee#
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)),max(bitand(nvl(option$,0), 8) /8), max(bitand(nvl(option$,0), 16) /16), max(bitand(nvl(option$,0),64) /64), max(bitand(nvl(option$,0), 128) /128) from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
select /* QOSD */ /*+ index(do) */ dir_cnt from opt_directive_own$ do where dir_own# = :1
select /* QOSD */ /*+ index(eh)*/ objn, sub_id, fixed_cost, text, col_list, flags, ctime from exp_head$ eh where exp_id = :1
--//以上都是递归执行的sql语句。
SELECT dname from dept where rownum<=1
alter session set events '10046 trace name context off'
$ ./extractbindvalue.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3598.trc
.....
select /* QOSD */ /*+ index(do) */ dir_cnt from opt_directive_own$ do where dir_own# = :1
Bind#0 value=356430430180546253
--//可以发现查询的是表sys.opt_directive_own$
SCOTT@book01p> select /* QOSD */ /*+ index(do) */ dir_cnt from sys.opt_directive_own$ do where dir_own# = 356430430180546253;
no rows selected
--//并没有记录返回，我的理解没有该执行计划的指导。
5.如果sql语句存在多表呢？
SCOTT@book01p> SELECT /*+ leading (dept emp) */dname from dept,emp where dept.deptno=31 and dept.deptno=emp.deptno;
no rows selected
# bpftrace kgghash2.bt 3598
Attached 8 probes
Tracing Oracle kgghash2... Target PID: 3598
Hit Ctrl-C to stop.
qosdGetOptDir Begin
[16:53:41] kgghash2 #01 pid=3598 ptr=00007f921f9cc988 hash_text=\x01 length=1 seed=0x0000000000012836
[16:53:41] kgghash2 #01 pid=3598 return hash_value=4512135f8fe4f3e6 4977061839354786790
[16:53:41] kgghash2 #02 pid=3598 ptr=00007f921f9cc8f8 hash_text=\x01 length=1 seed=0x0000000000012834
[16:53:41] kgghash2 #02 pid=3598 return hash_value=04f24b967a0d16cd 356430430180546253
qosdGetOptDir End
SCOTT@book01p> @ oname scott.^dept$|^emp$
OWNER OBJECT_NAME OBJECT_TYPE SUBOBJECT_NAME CREATED LAST_DDL_TIME STATUS DATA_OBJECT_ID OBJECT_ID DATA_OBJECT_ OBJECT_ID_HE
----- ----------- ----------- -------------- --------------------------------- ------------------- --------- -------------- ---------- ------------ ------------
SCOTT DEPT TABLE 2026-06-25 09:20:13 2026-06-25 09:20:13 VALID 75828 75828 0x000012834 0x000012834
SCOTT EMP TABLE 2026-06-25 09:20:13 2026-06-25 09:20:13 VALID 75830 75830 0x000012836 0x000012836
--//可以看出分开计算的，每个对象都计算1次。
SCOTT@book01p> sELECT /*+ leading (dept emp) */dname from dept,emp where dept.deptno=31 and dept.deptno=emp.deptno;
no rows selected
# bpftrace lkpn21c.bt 3598
Attached 2 probes
kgllkal count 01 -- handle address: 00000000704831b8, mode: 1 spid 3598 kglnaobj address: sELECT /*+ leading (dept emp) */dname from dept,emp where dept.deptno=31 and dept.deptno=emp.deptno
kglpnal count 01 -- handle address: 00000000704831b8, mode: 2 spid 3598 kglnaobj address: sELECT /*+ leading (dept emp) */dname from dept,emp where dept.deptno=31 and dept.deptno=emp.deptno
kgllkal count 02 -- handle address: 0000000073d5bae0, mode: 2 spid 3598 kglnaobj address: bookSYSCDB$ROOT
kgllkal count 03 -- handle address: 000000006de44850, mode: 2 spid 3598 kglnaobj address: 1847639831SYSCDB$ROOT
kgllkal count 04 -- handle address: 0000000069674248, mode: 2 spid 3598 kglnaobj address: f390ae09c52f932f5ffcbdb9e8316052$BUILD$BOOK01P
kgllkal count 05 -- handle address: 0000000069674018, mode: 1 spid 3598 kglnaobj address:
kglpnal count 02 -- handle address: 0000000069674018, mode: 3 spid 3598 kglnaobj address:
kgllkal count 06 -- handle address: 0000000073d5bae0, mode: 2 spid 3598 kglnaobj address: bookSYSCDB$ROOT
kgllkal count 07 -- handle address: 000000006de44850, mode: 2 spid 3598 kglnaobj address: 1847639831SYSCDB$ROOT
kgllkal count 08 -- handle address: 00000000665edc00, mode: 1 spid 3598 kglnaobj address: f390ae09c52f932f5ffcbdb9e8316052Child:0BOOK01P
kglpnal count 03 -- handle address: 00000000665edc00, mode: 3 spid 3598 kglnaobj address: f390ae09c52f932f5ffcbdb9e8316052Child:0BOOK01P
kgllkal count 09 -- handle address: 0000000069e965c0, mode: 1 spid 3598 kglnaobj address: SCOTTBOOK01P
kgllkal count 10 -- handle address: 0000000073d5bae0, mode: 2 spid 3598 kglnaobj address: bookSYSCDB$ROOT
kgllkal count 11 -- handle address: 000000006de44850, mode: 2 spid 3598 kglnaobj address: 1847639831SYSCDB$ROOT
kgllkal count 12 -- handle address: 00000000664a2a20, mode: 2 spid 3598 kglnaobj address: EMPSCOTTBOOK01P
kglpnal count 04 -- handle address: 00000000664a2a20, mode: 2 spid 3598 kglnaobj address: EMPSCOTTBOOK01P
kgllkal count 13 -- handle address: 00000000699dbf58, mode: 2 spid 3598 kglnaobj address: DEPTSCOTTBOOK01P?i
kglpnal count 05 -- handle address: 00000000699dbf58, mode: 2 spid 3598 kglnaobj address: DEPTSCOTTBOOK01P?i
kgllkal count 14 -- handle address: 000000006653c790, mode: 2 spid 3598 kglnaobj address: 4977061839354786790BOOK01P
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
kglpnal count 06 -- handle address: 000000006653c790, mode: 2 spid 3598 kglnaobj address: 4977061839354786790BOOK01P
kgllkal count 15 -- handle address: 0000000063fa3898, mode: 2 spid 3598 kglnaobj address: 356430430180546253BOOK01P
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
kglpnal count 07 -- handle address: 0000000063fa3898, mode: 2 spid 3598 kglnaobj address: 356430430180546253BOOK01P
kgllkal count 16 -- handle address: 0000000063fb53c8, mode: 2 spid 3598 kglnaobj address: 4204170303842060717BOOK01P
kglpnal count 08 -- handle address: 0000000063fb53c8, mode: 2 spid 3598 kglnaobj address: 4204170303842060717BOOK01P
kgllkal count 17 -- handle address: 00000000697d9708, mode: 2 spid 3598 kglnaobj address: 16574777722901676145BOOK01P
kglpnal count 09 -- handle address: 00000000697d9708, mode: 2 spid 3598 kglnaobj address: 16574777722901676145BOOK01P
5.附上测试使用相关脚本：
$ cat kgghash2.bt
BEGIN
{
printf("Tracing Oracle kgghash2... 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:qosdGetOptDir
/ $1 == 0 || pid == $1 /
{
printf("\nqosdGetOptDir Begin\n");
}
uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:qosdGetOptDir
/ $1 == 0 || pid == $1 /
{
printf("qosdGetOptDir End\n");
}
uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:qosdExprTravTrackOpn
/ $1 == 0 || pid == $1 /
{
printf("\nqosdExprTravTrackOpn Begin\n");
}
uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:qosdExprTravTrackOpn
/ $1 == 0 || pid == $1 /
{
printf("qosdExprTravTrackOpn End\n");
}
uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kgghash2
/ $1 == 0 || pid == $1 /
{
@kgg++;
@is_kgg[pid] = 1;
printf("[%s] kgghash2 #%02d pid=%d ptr=%016lx hash_text=%s length=%d seed=0x%016lx \n", strftime("%H:%M:%S", nsecs), @kgg, pid,arg0,buf(arg0,arg1), arg1,arg2);
}
uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kgghash2
/ $1 == 0 || pid == $1 /
{
if (@is_kgg[pid] == 1) {
printf("[%s] kgghash2 #%02d pid=%d return hash_value=%016lx %lu\n", strftime("%H:%M:%S", nsecs), @kgg,pid, retval, retval);
delete(@is_kgg[pid]);
}
}
END
{
printf("\nTotal kgghash2: %d\n", @kgg);
delete(@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 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;
$ cat tpt/oname.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.
-- Script by Tanel Poder (http://www.tanelpoder.com)
--
-- Look up object info by object_name
col o_owner heading OWNER for a25
col o_object_name heading OBJECT_NAME for a30
col o_object_type heading OBJECT_TYPE for a18
col o_status heading STATUS for a9
set term off
column v_owner new_value v_owner
column v_table new_value v_table
select
upper(CASE
WHEN INSTR('&1','.') > 0 THEN
SUBSTR('&1',INSTR('&1','.')+1)
ELSE
'&1'
END
) v_table,
nvl(CASE WHEN INSTR('&1','.') > 0 THEN
UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
ELSE
user
END,user) v_owner
from dual;
set term on
select
owner o_owner,
object_name o_object_name,
object_type o_object_type,
subobject_name,
created,
last_ddl_time,
status o_status,
data_object_id,
object_id,
'0x'|| to_char( data_object_id,'fm0xxxxxxxx') data_object_id_hex,
'0x'|| to_char( object_id,'fm0xxxxxxxx') object_id_hex
from
dba_objects
where
owner = '&v_owner'
and regexp_like (object_name , '&v_table','i')
order by
o_object_name,
o_owner,
o_object_type
/
$ cat bin/extractsql.sh
#! /bin/bash
awk '/PARSING IN CURSOR/,/END OF STMT/' $1 | egrep -v '^PARSING|^END OF STMT'
$ cat bin/extractbindvalue.sh
#!/bin/bash
awk '/^ Bind#/{printf "%s",$0};/^ value=/;/^ kxsbbbfp=.* avl=00 .*$/{print " value=NULL"};/PARSING IN CURSOR/,/END OF STMT/' $1 | egrep -v '^PARSING|^END OF STMT'
--//2024年8月事情记得第1次在21c跟踪执行sql语句library cache lock/library cache pin,有点点吃惊.直到现在不理解,现在大部分
--//数字对象都知道来自那里,前段时间写了[20260901]表exp_head$的exp_id如何计算.txt,实际上还有1个数字对象无法确定,今天在整
--//理笔记时才发现,在写那篇文章时,我已经知道答案,只不过当时专注exp_head$的exp_id的计算,给跳过了,今天做1点点补充.
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.
2.测试分析：
SCOTT@book01p> @ spid
==============================
SID : 24
SERIAL# : 18612
PROCESS : 3038
SERVER : DEDICATED
SPID : 3040
PID : 8
P_SERIAL# : 5
KILL_COMMAND : alter system kill session '24,18612' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> @ oname scott.^dept$
SCOTT@book01p> @pr
==============================
O_OWNER : SCOTT
O_OBJECT_NAME : DEPT
O_OBJECT_TYPE : TABLE
SUBOBJECT_NAME :
CREATED : 2026-06-25 09:20:13
LAST_DDL_TIME : 2026-06-25 09:20:13
O_STATUS : VALID
DATA_OBJECT_ID : 75828
OBJECT_ID : 75828
PL/SQL procedure successfully completed.
SCOTT@book01p> @ 10to16 75828
10 to 16 HEX (LOWER) 10 TO 16 HEX (UPPER) REVERSE16_LOWER REVERSE16_UPPER
-------------------- -------------------- -------------------- --------------------
0000000000012834 0000000000012834 0x34280100-00000000 0X34280100-00000000
--//记下spid=3040, 执行select dname from dept where rownum<=1;多次,避免递归sql语句.
--//然后修改sql语句1个字符，主要目的执行的sql语句硬解析。
SCOTT@book01p> Select dname from dept where rownum<=1;
DNAME
------------------------------
ACCOUNTING
# bpftrace kgghash2.bt 3040
Attached 8 probes
Tracing Oracle kgghash2... Target PID: 3040
Hit Ctrl-C to stop.
qosdGetOptDir Begin
[16:17:56] kgghash2 #01 pid=3040 ptr=00007fe463ffdfd8 hash_text=\x01 length=1 seed=0x0000000000012834
[16:17:56] kgghash2 #01 pid=3040 return hash_value=04f24b967a0d16cd 356430430180546253
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
qosdGetOptDir End
--//在qosdGetOptDir执行之间计算的hash值就是遗漏的数字对象。
--//也可以看出它的计算方法，参与运算的字符\x01,估计表示表的namespace=1，长度1，seed = 0x0000000000012834，正是dept的
--//object_id的16进制表示。
qosdExprTravTrackOpn Begin
[16:17:56] kgghash2 #02 pid=3040 ptr=00007fe4636a18b0 hash_text=T75828,2 length=8 seed=0x0000000000000000
[16:17:56] kgghash2 #02 pid=3040 return hash_value=95fedc9553dfa586 10808318689651172742
[16:17:56] kgghash2 #03 pid=3040 ptr=00007ffc20e79958 hash_text=4(\x01\x00 length=4 seed=0x95fedc9553dfa586
[16:17:56] kgghash2 #03 pid=3040 return hash_value=8500f19f6d979982 9583925674085161346
[16:17:56] kgghash2 #04 pid=3040 ptr=00007ffc20e79940 hash_text=\x00\x00\x00\x00\x00\x00\x00\x00 length=8 seed=0x8500f19f6d979982
[16:17:56] kgghash2 #04 pid=3040 return hash_value=3a58369ddfc089ad 4204170303842060717
qosdExprTravTrackOpn End)
--//在qosdExprTravTrackOpn执行之间计算的值对应exp_head$.exp_id, 前面已经做了解析。
--//参考链接: [20260901]表exp_head$的exp_id如何计算.txt
--//跟踪执行sql语句的library cache lock/library cache pin,注意再增加修改1个字符,保证每次硬解析:
SCOTT@book01p> SElect dname from dept where rownum<=1;
DNAME
------------------------------
ACCOUNTING
# bpftrace lkpn21c.bt 3040
Attached 2 probes
kgllkal count 01 -- handle address: 00000000661bd608, mode: 1 spid 3040 kglnaobj address: SElect dname from dept where rownum<=1
kglpnal count 01 -- handle address: 00000000661bd608, mode: 2 spid 3040 kglnaobj address: SElect dname from dept where rownum<=1
kgllkal count 02 -- handle address: 0000000073d5bae0, mode: 2 spid 3040 kglnaobj address: bookSYSCDB$ROOT
kgllkal count 03 -- handle address: 000000006de44850, mode: 2 spid 3040 kglnaobj address: 1847639831SYSCDB$ROOT
kgllkal count 04 -- handle address: 00000000661bc0d8, mode: 2 spid 3040 kglnaobj address: 8e1057d26a121171203d24887a25f180$BUILD$BOOK01P
kgllkal count 05 -- handle address: 00000000661bbea8, mode: 1 spid 3040 kglnaobj address:
kglpnal count 02 -- handle address: 00000000661bbea8, mode: 3 spid 3040 kglnaobj address:
kgllkal count 06 -- handle address: 0000000073d5bae0, mode: 2 spid 3040 kglnaobj address: bookSYSCDB$ROOT
kgllkal count 07 -- handle address: 000000006de44850, mode: 2 spid 3040 kglnaobj address: 1847639831SYSCDB$ROOT
kgllkal count 08 -- handle address: 00000000661baa78, mode: 1 spid 3040 kglnaobj address: 8e1057d26a121171203d24887a25f180Child:0BOOK01P
kglpnal count 03 -- handle address: 00000000661baa78, mode: 3 spid 3040 kglnaobj address: 8e1057d26a121171203d24887a25f180Child:0BOOK01P
kgllkal count 09 -- handle address: 0000000069ef8298, mode: 1 spid 3040 kglnaobj address: SCOTTBOOK01P
kgllkal count 10 -- handle address: 0000000073d5bae0, mode: 2 spid 3040 kglnaobj address: bookSYSCDB$ROOT
kgllkal count 11 -- handle address: 000000006de44850, mode: 2 spid 3040 kglnaobj address: 1847639831SYSCDB$ROOT
kgllkal count 12 -- handle address: 0000000069c8f720, mode: 2 spid 3040 kglnaobj address: DEPTSCOTTBOOK01i
kglpnal count 04 -- handle address: 0000000069c8f720, mode: 2 spid 3040 kglnaobj address: DEPTSCOTTBOOK01i
kgllkal count 13 -- handle address: 0000000069c88bd0, mode: 2 spid 3040 kglnaobj address: 356430430180546253BOOK01P
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
kglpnal count 05 -- handle address: 0000000069c88bd0, mode: 2 spid 3040 kglnaobj address: 356430430180546253BOOK01P
kgllkal count 14 -- handle address: 0000000069c87470, mode: 2 spid 3040 kglnaobj address: 4204170303842060717BOOK01P --//dept.dname
kglpnal count 06 -- handle address: 0000000069c87470, mode: 2 spid 3040 kglnaobj address: 4204170303842060717BOOK01P
--//看下划线的数字部分与前面的计算一样.hash_value=04f24b967a0d16cd 356430430180546253
3.看看是什么数字对象：
SYS@book> @ sharepool/shp4z 0000000069c88bd0 -1
SYS@book> @ pr
==============================
HANDLE_TYPE : parent handle address
KGLHDADR : 0000000069C88BD0
KGLHDPAR : 0000000069C88BD0
C40 : 356430430180546253
KGLHDLMD : 0
KGLHDPMD : 0
KGLHDIVC : 0
KGLOBHD0 : 00
KGLOBHD6 : 00
KGLOBHS0 : 0
KGLOBHS6 : 0
KGLOBT16 : 0
N0_6_16 : 0
N20 : 0
KGLNAHSH : 926311590
KGLOBT03 :
KGLHDBID : 25766
KGLOBT09 : 0
PL/SQL procedure successfully completed.
--//该对象的hash值=926311590。
SYS@book> @ kglob '' 926311590 ''
==============================
INST_ID : 1
OWNER :
NAME : 356430430180546253
DB_LINK :
NAMESPACE : OPTIMIZER DIRECTIVE OWNER
TYPE : CURSOR
NAMESPACE_NUM : 104
NAMESPACE_HEX : 68
SHARABLE_MEM : 0
PERSISTENT_MEM : 0
RUNTIME_MEM : 0
LOADS : 1
EXECUTIONS : 0
LOCKS : 0
PINS : 0
KEPT : NO
CHILD_LATCH : 25766
INVALIDATIONS : 0
HASH_VALUE : 926311590
HASH_HEX : 373664a6
LOCK_MODE : NONE
PIN_MODE : NONE
STATUS : UNKOWN
TIMESTAMP :
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 3
PINNED_TOTAL : 3
PROPERTY :
FULL_HASH_VALUE : 21a7a5646ae58a2d5120aa51373664a6
CON_ID : 3
CON_NAME : BOOK01P
ADDR : 0000000069C88BD0
PAR_ADDR : 0000000069C88BD0
HANDLE_TYPE : parent handle address
KGLOBHD0 : 00
KGLOBHD6 : 00
EDITION :
SQL_ID :
KGL_BUCKET : 25766
OBJECT_STR : 356430430180546253
PL/SQL procedure successfully completed.
--//注：kglob.sql脚本大部分字段信息来源于视图v$db_object_cache。
--//该对象NAMESPACE= OPTIMIZER DIRECTIVE OWNER，TYPE = CURSOR。NAMESPACE的16进制值等于68。
--//使用我写的sql_idz.sh脚本计算full_hash_value，hash_value,完全能对上.
--//\x68表示 NAMESPACE的16进制值.
$ sql_idz.sh "356430430180546253.BOOK01P\x68\0\0\0" 3
sql_text = 356430430180546253.BOOK01P\x68\0\0\0
full_hash_value(16) = 21A7A5646AE58A2D5120AA51373664A6 or 21a7a5646ae58a2d5120aa51373664a6
xxxxx_matching_signature(10) = 5845859582122288294 or 24292603655831839910
hash_value(10) = 926311590 or hash_value(16) = 373664A6 or 373664a6
kgl_backet(10) = 25766 or kgl_bucket(16) = 64A6 or 64a6
sql_id(16) = 5120AA51373664A6 or 5120aa51373664a6
sql_id(32) = 5285aa4vmct56
sql_id(32) = 5285aa4vmct56
sql_id(32) = 5285aa4vmct56
--//直接在gdb调用kgghash2计算看看，简单验证看看：
qosdGetOptDir Begin
[16:17:56] kgghash2 #01 pid=3040 ptr=00007fe463ffdfd8 hash_text=\x01 length=1 seed=0x0000000000012834
[16:17:56] kgghash2 #01 pid=3040 return hash_value=04f24b967a0d16cd 356430430180546253
qosdGetOptDir End
(gdb) p /x (long int) kgghash2((char *)"\x01",1,0x12834)
$1 = 0x4f24b967a0d16cd
--//4f24b967a0d16cd = 356430430180546253
--//完全对上，验证成功。
4.做1个10046跟踪：
--//刷新共享池多次。
SYS@book> alter system flush shared_pool;
System altered.
SCOTT@book01p> @ 10046on 12
Session altered.
SCOTT@book01p> SELECT dname from dept where rownum<=1;
DNAME
------------------------------
ACCOUNTING
SCOTT@book01p> @ 10046off
Session altered.
SCOTT@book01p> @ t
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3598.trc
$ ./extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3598.trc
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2, spare3, signature, spare7, spare8, spare9, nvl(dflcollid, 16382), creappid, creverid, modappid, modverid, crepatchid, modpatchid from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,nvl(t.spare7,0),ts.cachedblk,ts.cachehit,ts.logicalread,ts.im_imcu_count,ts.im_block_count,ts.im_sys_incarnation,ts.im_stat_update_time,ts.scanrate,nvl(t.acdrflags, 0),nvl(t.acdrtsobj#, 0),t.acdrdefaulttime,nvl(t.acdrrowtsintcol#, 0) from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln, minimum_enc, maximum_enc from hist_head$ where obj#=:1 and intcol#=:2
select audit$ from tab$ where obj# = :1
select policy#, action# from aud_object_opt$ where object# = :1 and type = 2
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),nvl(i.evaledition#,1),nvl(i.unusablebefore#,0),nvl(i.unusablebeginning#,0), ist.cachedblk,ist.cachehit,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(intcols) unicols,min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0), nvl(evaledition#,1),nvl(unusablebefore#,0),nvl(unusablebeginning#,0), case when (type# in (1,8,9,96,112)) then nvl(collid, 16382) else 0 end case, nvl(collintcol#,0), nvl(acdrrescol#, 0), nvl(spare7, 0), nvl(spare9, 0), nvl(spare10, 0) from col$ where obj#=:1 order by intcol#
select name,online$,contents$,undofile#,undoblock#,blocksize,dflmaxext,dflinit,dflincr,dflextpct,dflminext, dflminlen, owner#,scnwrp,scnbas, NVL(pitrscnwrp, 0), NVL(pitrscnbas, 0), dflogging, bitmapped, inc#, MOD(flags,(4294967296 * 4294967296)), plugged, NVL(spare1,0), NVL(spare2,0), affstrength, FLOOR(flags/(4294967296 * 4294967296)) from ts$ where ts#=:1
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1
select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3,refact from cdef$ where robj#=:1
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3 from cdef$ where obj#=:1
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
select user#,password,datats#,tempts#,type#,defrole,resource$,ptime,decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,ext_username,spare2,nvl(spare3,16382),spare9,spare10 from user$ where name=:1
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)), max(bitand(nvl(option$,0), 8) /8), max(bitand(nvl(option$,0), 16) /16), max(bitand(nvl(option$,0),64) /64), max(bitand(nvl(option$,0), 128) /128) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee#
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)),max(bitand(nvl(option$,0), 8) /8), max(bitand(nvl(option$,0), 16) /16), max(bitand(nvl(option$,0),64) /64), max(bitand(nvl(option$,0), 128) /128) from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
select /* QOSD */ /*+ index(do) */ dir_cnt from opt_directive_own$ do where dir_own# = :1
select /* QOSD */ /*+ index(eh)*/ objn, sub_id, fixed_cost, text, col_list, flags, ctime from exp_head$ eh where exp_id = :1
--//以上都是递归执行的sql语句。
SELECT dname from dept where rownum<=1
alter session set events '10046 trace name context off'
$ ./extractbindvalue.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3598.trc
.....
select /* QOSD */ /*+ index(do) */ dir_cnt from opt_directive_own$ do where dir_own# = :1
Bind#0 value=356430430180546253
--//可以发现查询的是表sys.opt_directive_own$
SCOTT@book01p> select /* QOSD */ /*+ index(do) */ dir_cnt from sys.opt_directive_own$ do where dir_own# = 356430430180546253;
no rows selected
--//并没有记录返回，我的理解没有该执行计划的指导。
5.如果sql语句存在多表呢？
SCOTT@book01p> SELECT /*+ leading (dept emp) */dname from dept,emp where dept.deptno=31 and dept.deptno=emp.deptno;
no rows selected
# bpftrace kgghash2.bt 3598
Attached 8 probes
Tracing Oracle kgghash2... Target PID: 3598
Hit Ctrl-C to stop.
qosdGetOptDir Begin
[16:53:41] kgghash2 #01 pid=3598 ptr=00007f921f9cc988 hash_text=\x01 length=1 seed=0x0000000000012836
[16:53:41] kgghash2 #01 pid=3598 return hash_value=4512135f8fe4f3e6 4977061839354786790
[16:53:41] kgghash2 #02 pid=3598 ptr=00007f921f9cc8f8 hash_text=\x01 length=1 seed=0x0000000000012834
[16:53:41] kgghash2 #02 pid=3598 return hash_value=04f24b967a0d16cd 356430430180546253
qosdGetOptDir End
SCOTT@book01p> @ oname scott.^dept$|^emp$
OWNER OBJECT_NAME OBJECT_TYPE SUBOBJECT_NAME CREATED LAST_DDL_TIME STATUS DATA_OBJECT_ID OBJECT_ID DATA_OBJECT_ OBJECT_ID_HE
----- ----------- ----------- -------------- --------------------------------- ------------------- --------- -------------- ---------- ------------ ------------
SCOTT DEPT TABLE 2026-06-25 09:20:13 2026-06-25 09:20:13 VALID 75828 75828 0x000012834 0x000012834
SCOTT EMP TABLE 2026-06-25 09:20:13 2026-06-25 09:20:13 VALID 75830 75830 0x000012836 0x000012836
--//可以看出分开计算的，每个对象都计算1次。
SCOTT@book01p> sELECT /*+ leading (dept emp) */dname from dept,emp where dept.deptno=31 and dept.deptno=emp.deptno;
no rows selected
# bpftrace lkpn21c.bt 3598
Attached 2 probes
kgllkal count 01 -- handle address: 00000000704831b8, mode: 1 spid 3598 kglnaobj address: sELECT /*+ leading (dept emp) */dname from dept,emp where dept.deptno=31 and dept.deptno=emp.deptno
kglpnal count 01 -- handle address: 00000000704831b8, mode: 2 spid 3598 kglnaobj address: sELECT /*+ leading (dept emp) */dname from dept,emp where dept.deptno=31 and dept.deptno=emp.deptno
kgllkal count 02 -- handle address: 0000000073d5bae0, mode: 2 spid 3598 kglnaobj address: bookSYSCDB$ROOT
kgllkal count 03 -- handle address: 000000006de44850, mode: 2 spid 3598 kglnaobj address: 1847639831SYSCDB$ROOT
kgllkal count 04 -- handle address: 0000000069674248, mode: 2 spid 3598 kglnaobj address: f390ae09c52f932f5ffcbdb9e8316052$BUILD$BOOK01P
kgllkal count 05 -- handle address: 0000000069674018, mode: 1 spid 3598 kglnaobj address:
kglpnal count 02 -- handle address: 0000000069674018, mode: 3 spid 3598 kglnaobj address:
kgllkal count 06 -- handle address: 0000000073d5bae0, mode: 2 spid 3598 kglnaobj address: bookSYSCDB$ROOT
kgllkal count 07 -- handle address: 000000006de44850, mode: 2 spid 3598 kglnaobj address: 1847639831SYSCDB$ROOT
kgllkal count 08 -- handle address: 00000000665edc00, mode: 1 spid 3598 kglnaobj address: f390ae09c52f932f5ffcbdb9e8316052Child:0BOOK01P
kglpnal count 03 -- handle address: 00000000665edc00, mode: 3 spid 3598 kglnaobj address: f390ae09c52f932f5ffcbdb9e8316052Child:0BOOK01P
kgllkal count 09 -- handle address: 0000000069e965c0, mode: 1 spid 3598 kglnaobj address: SCOTTBOOK01P
kgllkal count 10 -- handle address: 0000000073d5bae0, mode: 2 spid 3598 kglnaobj address: bookSYSCDB$ROOT
kgllkal count 11 -- handle address: 000000006de44850, mode: 2 spid 3598 kglnaobj address: 1847639831SYSCDB$ROOT
kgllkal count 12 -- handle address: 00000000664a2a20, mode: 2 spid 3598 kglnaobj address: EMPSCOTTBOOK01P
kglpnal count 04 -- handle address: 00000000664a2a20, mode: 2 spid 3598 kglnaobj address: EMPSCOTTBOOK01P
kgllkal count 13 -- handle address: 00000000699dbf58, mode: 2 spid 3598 kglnaobj address: DEPTSCOTTBOOK01P?i
kglpnal count 05 -- handle address: 00000000699dbf58, mode: 2 spid 3598 kglnaobj address: DEPTSCOTTBOOK01P?i
kgllkal count 14 -- handle address: 000000006653c790, mode: 2 spid 3598 kglnaobj address: 4977061839354786790BOOK01P
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
kglpnal count 06 -- handle address: 000000006653c790, mode: 2 spid 3598 kglnaobj address: 4977061839354786790BOOK01P
kgllkal count 15 -- handle address: 0000000063fa3898, mode: 2 spid 3598 kglnaobj address: 356430430180546253BOOK01P
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
kglpnal count 07 -- handle address: 0000000063fa3898, mode: 2 spid 3598 kglnaobj address: 356430430180546253BOOK01P
kgllkal count 16 -- handle address: 0000000063fb53c8, mode: 2 spid 3598 kglnaobj address: 4204170303842060717BOOK01P
kglpnal count 08 -- handle address: 0000000063fb53c8, mode: 2 spid 3598 kglnaobj address: 4204170303842060717BOOK01P
kgllkal count 17 -- handle address: 00000000697d9708, mode: 2 spid 3598 kglnaobj address: 16574777722901676145BOOK01P
kglpnal count 09 -- handle address: 00000000697d9708, mode: 2 spid 3598 kglnaobj address: 16574777722901676145BOOK01P
5.附上测试使用相关脚本：
$ cat kgghash2.bt
BEGIN
{
printf("Tracing Oracle kgghash2... 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:qosdGetOptDir
/ $1 == 0 || pid == $1 /
{
printf("\nqosdGetOptDir Begin\n");
}
uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:qosdGetOptDir
/ $1 == 0 || pid == $1 /
{
printf("qosdGetOptDir End\n");
}
uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:qosdExprTravTrackOpn
/ $1 == 0 || pid == $1 /
{
printf("\nqosdExprTravTrackOpn Begin\n");
}
uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:qosdExprTravTrackOpn
/ $1 == 0 || pid == $1 /
{
printf("qosdExprTravTrackOpn End\n");
}
uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kgghash2
/ $1 == 0 || pid == $1 /
{
@kgg++;
@is_kgg[pid] = 1;
printf("[%s] kgghash2 #%02d pid=%d ptr=%016lx hash_text=%s length=%d seed=0x%016lx \n", strftime("%H:%M:%S", nsecs), @kgg, pid,arg0,buf(arg0,arg1), arg1,arg2);
}
uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kgghash2
/ $1 == 0 || pid == $1 /
{
if (@is_kgg[pid] == 1) {
printf("[%s] kgghash2 #%02d pid=%d return hash_value=%016lx %lu\n", strftime("%H:%M:%S", nsecs), @kgg,pid, retval, retval);
delete(@is_kgg[pid]);
}
}
END
{
printf("\nTotal kgghash2: %d\n", @kgg);
delete(@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 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;
$ cat tpt/oname.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.
-- Script by Tanel Poder (http://www.tanelpoder.com)
--
-- Look up object info by object_name
col o_owner heading OWNER for a25
col o_object_name heading OBJECT_NAME for a30
col o_object_type heading OBJECT_TYPE for a18
col o_status heading STATUS for a9
set term off
column v_owner new_value v_owner
column v_table new_value v_table
select
upper(CASE
WHEN INSTR('&1','.') > 0 THEN
SUBSTR('&1',INSTR('&1','.')+1)
ELSE
'&1'
END
) v_table,
nvl(CASE WHEN INSTR('&1','.') > 0 THEN
UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
ELSE
user
END,user) v_owner
from dual;
set term on
select
owner o_owner,
object_name o_object_name,
object_type o_object_type,
subobject_name,
created,
last_ddl_time,
status o_status,
data_object_id,
object_id,
'0x'|| to_char( data_object_id,'fm0xxxxxxxx') data_object_id_hex,
'0x'|| to_char( object_id,'fm0xxxxxxxx') object_id_hex
from
dba_objects
where
owner = '&v_owner'
and regexp_like (object_name , '&v_table','i')
order by
o_object_name,
o_owner,
o_object_type
/
$ cat bin/extractsql.sh
#! /bin/bash
awk '/PARSING IN CURSOR/,/END OF STMT/' $1 | egrep -v '^PARSING|^END OF STMT'
$ cat bin/extractbindvalue.sh
#!/bin/bash
awk '/^ Bind#/{printf "%s",$0};/^ value=/;/^ kxsbbbfp=.* avl=00 .*$/{print " value=NULL"};/PARSING IN CURSOR/,/END OF STMT/' $1 | egrep -v '^PARSING|^END OF STMT'
浙公网安备 33010602011771号