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书上的先容有点差别.
--//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:
--//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
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个字母小写
$ 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
-- 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_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
--//注:内里^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
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
$ 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
-- 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
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。