[20210524]分析library cache轉儲 3.txt

lfree發表於2021-05-24

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




來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2773571/,如需轉載,請註明出處,否則將追究法律責任。

相關文章