[20210524]分析library cache转储 3.txt
--//前几天探究11g shared pool latch与library cache mutex时,分析11g library cache转储,里面的mutex地址我当时得出的结论是每
--//个占用40字节.实际上犯了一点点错误,仅仅说明下一个mutext地址在偏移40字节的位置.实际上muext仅仅占用24个字节.
--//这个问题的产生在于我当时使用oradebug peek查看的遇到的情况:
SYS@book> oradebug peek 0x80528f40 40
[080528F40, 080528F68) = 00000001 00000000 0000092B 00042180 000190FA 00000006 80528F58 00000000 80528F58 00000000
--//我后面16字节始终不变,而且如果0x080528F40+0x18(十进制24)=0x80528F58,正好等于对应地址.
--//我当时推断0000092B => 表示get,00042180 表示与sleep相关,000190FA = 102650表示bucket,00000006 不知道.
--//我想既然不变,尝试peek地址 0x80528f40-0x10(十进制16)=0x80528f30的情况,当我看到对比dump library cache,马上明白其中的奥秘.
--//当时快下班了,也没心情继续探究,找一个完整的时间仔细探究看看.
1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.分析：
--//退出全部session。执行：
--//session 2:
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug peek 0x80528f30 40
[080528F30, 080528F58) = 80528F30 00000000 80528F30 00000000 00000000 00000000 00000077 00000000 000190FA 00000000
--//0x80528F30 正好等于 peek的开始地址，也就是当bucket里面没有对象时，该地址正好等于开始地址。
--//session 1:
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//执行多次,避免该光标不在共享池中.
SCOTT@book> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
95129850 80baj2c2ur47u 0 5ab90fa
--//95129850%131072 = 102650
--//session 2:
SYS@book> oradebug peek 0x80528f30 40
[080528F30, 080528F58) = 7C1B0958 00000000 7C1B0958 00000000 00000000 00000000 0000007B 00000000 000190FA 00000000
--//注意看现在记录的是0x7C1B0958，表示什么呢？
SYS@book> @ sharepool/shp4 80baj2c2ur47u 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007C57C898 000000007C1B0958 select * from dept where deptno=20 1 0 0 000000007E1C1220 000000007EA4AD40 4536 12144 3067 19747 19747 95129850 80baj2c2ur47u 0
parent handle address 000000007C1B0958 000000007C1B0958 select * from dept where deptno=20 1 0 0 000000007E1A22C0 00 4720 0 0 4720 4720 95129850 80baj2c2ur47u 65535
--//正好记录的就是是父游标的handle地址。0x000000007C1B0958。
SYS@book> @fcha 000000007C1B0958
Find in which heap (UGA, PGA or Shared Pool) the memory address 000000007C1B0958 resides...
WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention
in systems under load and with large shared pool. This may even completely hang
your instance until the query has finished! You probably do not want to run this in production!
Press ENTER to continue, CTRL+C to cancel...
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 000000007C1B0928 1 1 KGLHD 560 recr 80 00
--//父游标句柄地址记录的是000000007C1B0958，与开头偏移0x30(48字节)。
--// 0x000000007C1B0958-0x30=0x000000007C1B0928
SYS@book> oradebug peek 0x000000007C1B0928 560 1
[07C1B0928, 07C1B0B58) = 00000231 80B38F00 7C1B06F8 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 40080050 80528F30 00000000 ...
~~~~~~~~
--//注意看下划线内容，可以发现正好记录的是bucket 的地址。这样就形成了一个链表。
3.分析转储:
SYS@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_40996_0001.trc
SYS@book> oradebug dump library_cache 10;
Statement processed.
--//检索Bucket: #=102650.
Bucket: #=102650 Mutex=0x80528f40(0, 127, 0, 6)
LibraryHandle: Address=0x7c1b0958 Hash=5ab90fa LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
--//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=>父游标句柄。
ObjectName: Name=select * from dept where deptno=20
FullHashValue=e8ec445edab00042802d511305ab90fa Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=95129850 OwnerIdn=83
Statistics: InvalidationCount=0 ExecutionCount=5 LoadCount=2 ActiveLocks=1 TotalLockCount=3 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=2 HandleInUse=2 HandleReferenceCount=0
Concurrency: DependencyMutex=0x7c1b0a08(0, 1, 0, 0) Mutex=0x7c1b0a98(44, 35, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x7c1b09e8[0x7c1b09e8,0x7c1b09e8]
Pin=0x7c1b09c8[0x7c1b09c8,0x7c1b09c8]
LoadLock=0x7c1b0a40[0x7c1b0a40,0x7c1b0a40]
Timestamp: Current=05-24-2021 08:28:01
HandleReference: Address=0x7c1b0b28 Handle=(nil) Flags=[00]
ReferenceList:
Reference: Address=0x7cae8b10 Handle=0x7caea870 Flags=ROD[21]
LibraryObject: Address=0x7ea4a680 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=KGLH0^5ab90fa pins=0 Change=NONE
Heap=0x7e1a22c0 Pointer=0x7ea4a720 Extent=0x7ea4a600 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=2.437500 Size=3.976562 LoadTime=13159699060
ChildTable: size='16'
Child: id='0' Table=0x7ea4b530 Reference=0x7ea4af70 Handle=0x7c57c898
NamespaceDump:
Parent Cursor: sql_id=80baj2c2ur47u parent=0x7ea4a720 maxchild=1 plk=y ppn=n
--//现在就很清晰了，muext结构体仅仅占用24字节，bucket+muext的结构大致如下：
--//bucket 占用16字节 +muext 占用24字节。
--//bucket 应该保存地址，我猜测一个链表的开始地址，一个是结束地址。至于那个在前那个在后我现在还不能确定。
--//如果多个对象在一个bucket里面通过通过父游标或者对应对象里面里面记录下一个对象的句柄来实现链接的，这样就能实现检索功能。
--//可以通过找一些hash_value一样的sql语句来验证自己的判断。
--//退出session 1：
--//session 2:
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> oradebug peek 0x80528f30 40
[080528F30, 080528F58) = 80528F30 00000000 80528F30 00000000 00000000 00000000 00000083 00000000 000190FA 00000000
--//回到没有对象的情况。
4.附上执行脚本：
$ cat sharepool/shp4.sql
column N0_6_16 format 99999999
SELECT DECODE (kglhdadr,
kglhdpar, 'parent handle address',
'child handle address')
text,
kglhdadr,
kglhdpar,
substr(kglnaobj,1,40) c40,
KGLHDLMD,
KGLHDPMD,
kglhdivc,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16 N0_6_16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
kglnahsh,
kglobt03 ,
kglobt09
FROM x$kglob
WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;
$ cat tpt/fcha.sql
--------------------------------------------------------------------------------
--
-- File name: fcha.sql (Find CHunk Address) v0.2
-- Purpose: Find in which heap (UGA, PGA or Shared Pool) a memory address resides
--
-- Author: Tanel Poder
-- Copyright: (c) http://blog.tanelpoder.com | @tanelpoder
--
-- Usage: @fcha <addr_hex>
-- @fcha F6A14448
--
-- Other: This would only report an UGA/PGA chunk address if it belongs
-- to *your* process/session (x$ksmup and x$ksmpp do not see other
-- session/process memory)
--
--------------------------------------------------------------------------------
prompt Find in which heap (UGA, PGA or Shared Pool) the memory address &1 resides...
prompt
prompt WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention
prompt in systems under load and with large shared pool. This may even completely hang
prompt your instance until the query has finished! You probably do not want to run this in production!
prompt
pause Press ENTER to continue, CTRL+C to cancel...
select
'SGA' LOC,
KSMCHPTR,
KSMCHIDX,
KSMCHDUR,
KSMCHCOM,
KSMCHSIZ,
KSMCHCLS,
KSMCHTYP,
KSMCHPAR
from
x$ksmsp
where
to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')
between
to_number(ksmchptr,'XXXXXXXXXXXXXXXX')
and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1
union all
select
'UGA',
KSMCHPTR,
null,
null,
KSMCHCOM,
KSMCHSIZ,
KSMCHCLS,
KSMCHTYP,
KSMCHPAR
from
x$ksmup
where
to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')
between
to_number(ksmchptr,'XXXXXXXXXXXXXXXX')
and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1
union all
select
'PGA',
KSMCHPTR,
null,
null,
KSMCHCOM,
KSMCHSIZ,
KSMCHCLS,
KSMCHTYP,
KSMCHPAR
from
x$ksmpp
where
to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')
between
to_number(ksmchptr,'XXXXXXXXXXXXXXXX')
and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1
/