[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
/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2773571/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210524]分析library cache轉儲 4.txt
- [20210507]分析library cache轉儲.txt
- [20210602]分析library cache轉儲 5.txt
- [20210508]分析library cache轉儲 2.txt
- [20201203]探究library cache mutex X 3.txtMutex
- [20241108]跟蹤library cache lock library cache pin使用gdb(11g)3.txt
- library cache pin(轉)
- [20210902]library_cache物件級別轉儲.txt物件
- [20220303]oracle如何定位使用library cache mutex 3.txtOracleMutex
- library cache pin和library cache lock(一)
- library cache pin和library cache lock (zt)
- library cache pin和library cache lock(二)
- Library Cache最佳化篇（一）降低library cache lock和library cache pin的方法
- library cache lock和library cache bin實驗_2.0
- Oracle Library cacheOracle
- 一次library cache lock 問題分析
- [20210520]11g shared pool latch與library cache mutex的簡單探究3.txtMutex
- [20240920]跟蹤library cache lock library cache pin使用gdb.txt
- [20240824]跟蹤library cache lock library cache pin使用gdb.txt
- 【等待事件】library cache pin事件
- 【ASK_ORACLE】Library Cache概念篇（二）之Library Cache Pin的定義Oracle
- [20190402]Library Cache mutex.txtMutex
- latch:library cache lock等待事件事件
- [20210507]dump library_cache.txt
- [20241105]跟蹤library cache lock library cache pin使用gdb(11g)2.txt
- [20241108]跟蹤library cache lock library cache pin使用gdb(11g)4.txt
- 徹底搞清楚library cache lock的成因和解決方法(轉)
- 【ASM_ORACLE】Library Cache最佳化篇（二）Library cache load lock的概念和解決辦法ASMOracle
- DBA手記（學習）-library cache pin
- [20210507]dump library_cache 2.txt
- [20240827]分析為什麼出現library cache lock等待事件2.txt事件
- [20240828]分析為什麼出現library cache lock等待事件5.txt事件
- 【TUNE_ORACLE】等待事件之“library cache lock”Oracle事件
- 【TUNE_ORACLE】等待事件之“library cache pins”Oracle事件
- 【ASK_ORACLE】Library cache pin 與 library load lock的關係和區別Oracle
- [20220301]oracle如何定位使用library cache mutex.txtOracleMutex
- [20220302]oracle如何定位使用library cache mutex 2.txtOracleMutex
- [20220304]測試library cache mutex遇到的疑問.txtMutex