[20210524]分析library cache转储 3.txt

[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
/




上一篇:Android 系统负载简介


下一篇:Linux服务器丢包故障的解决思路及引申的TCP/IP协议栈理论