oracle 11g latch之v$latch系列三
背景
本文為oracle 11g latch系列的第三篇文章,繼續深入學習latch,想要熟悉其原理,還是先了解下相關檢視的含義,爾後進一步深入其中,便於解決問題。
本系列前2文連結如下:
oracle 11g latch之系列一
http://blog.itpub.net/9240380/viewspace-1820418/
oracle 11g latch之v$latch系列二
http://blog.itpub.net/9240380/viewspace-1820457/
結論
1,oradebug poke手工模擬shared pool latch雖然成功,但v$latchholder卻一直沒值,還是沒有理解清晰latch的機制和v$latchholder的關係2,沒有子LATCH的LATCH的情況只在V$LATCH及V$LATCH_PARENT儲存資料
而有子LATCH的LATCH在v$latch及v$latch_parent和v$latch_childrent皆儲存資料
3,模擬佔有shared pool latch為
用oradebug模擬shared poo latch
SQL> select 'oradebug poke 0x'||addr||' 4 0x00000001;' from v$latch_children where latch#=293;
'ORADEBUGPOKE0X'||ADDR||'40X00000001;'
--------------------------------------------------------------------------------------------
oradebug poke 0x0000000060103C88 4 0x00000001;
oradebug poke 0x0000000060103BE8 4 0x00000001;
oradebug poke 0x0000000060103B48 4 0x00000001;
oradebug poke 0x0000000060103AA8 4 0x00000001;
oradebug poke 0x0000000060103A08 4 0x00000001;
oradebug poke 0x0000000060103968 4 0x00000001;
oradebug poke 0x00000000601038C8 4 0x00000001;
4,模擬釋放shared pool latch為
oradebug poke 0x0000000060103C88 4 0x00000000;
oradebug poke 0x0000000060103BE8 4 0x00000000;
oradebug poke 0x0000000060103B48 4 0x00000000;
oradebug poke 0x0000000060103AA8 4 0x00000000;
oradebug poke 0x0000000060103A08 4 0x00000000;
oradebug poke 0x0000000060103968 4 0x00000000;
oradebug poke 0x00000000601038C8 4 0x00000000;
可見即1為佔有,0為釋放
測試
1,資料庫版本
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
2,獲取有子latch的測試latch
SQL> select addr,latch#,level#,name,hash,gets,misses,immediate_gets,immediate_misses,spin_gets from v$latch where gets >0 and immediate_gets>0 and rownum<=10;
ADDR LATCH# LEVEL# NAME HASH GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS
---------------- ---------- ---------- ------------------------------ ---------- ---------- ---------- -------------- ---------------- ----------
0000000060009720 2 8 post/wait queue 823771719 685636 1383 858180 2479 0
0000000060009F38 9 7 process allocation 2600548697 22679 2 7624 0 0
000000006000A9E0 16 8 longop free list parent 853437045 96 0 88 0 0
000000006000CF38 28 4 enqueue hash chains 1456202064 11610594 4823 325 0 3
000000006000D3A0 33 6 SGA IO buffer pool latch 2719726273 1 0 1 0 0
00000000600101C8 70 0 active service list 4226341592 1931885 58 10716 1 9
00000000600178B0 137 7 Memory Management Latch 1808980316 26 0 6943 0 0
00000000600188A8 145 2 cache buffers lru chain 3559635447 177525 149 170932 140 0
0000000060019E38 149 5 checkpoint queue latch 4259362863 298484 1 81957 3 0
000000006001A620 150 1 cache buffers chains 3563305585 299012270 13426 182643 77 7195
10 rows selected.
可見上述的10個LATCH只有6個LATCH有有子LATCH,還有4個LATCH沒有子LATCH
SQL> select latch#,count(child#) from v$latch_children where latch# in (select latch# from v$latch where gets >0 and immediate_gets>0 and rownum<=10) group by latch# order by 2;
LATCH# COUNT(CHILD#)
---------- -------------
28 1
16 1
2 7
145 16
149 16
150 8192
6 rows selected.
v$latch和v$latch_parent儲存資料是相同的,源自官方手冊
SQL> select addr,latch#,level#,name,hash,gets,misses,immediate_gets,immediate_misses,spin_gets from v$latch where latch#=2;
ADDR LATCH# LEVEL# NAME HASH GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS
---------------- ---------- ---------- ------------------------------ ---------- ---------- ---------- -------------- ---------------- ----------
0000000060009720 2 8 post/wait queue 823771719 702401 1412 879227 2545 0
SQL> select addr,latch#,level#,name,hash,gets,misses,immediate_gets,immediate_misses,spin_gets from v$latch_parent where latch#=2;
ADDR LATCH# LEVEL# NAME HASH GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS
---------------- ---------- ---------- ------------------------------ ---------- ---------- ---------- -------------- ---------------- ----------
0000000060009720 2 8 post/wait queue 823771719 158 0 0 0 0
獲取上述LATCH的子LATCH,可見有7個子LATCH,且每個子LATCH的地址和其父LATCH的地址各為不同
SQL> select addr,latch#,child#,level#,name,hash,gets,misses,immediate_gets,immediate_misses,spin_gets from v$latch_children where latch#=2 order by 3;
ADDR LATCH# CHILD# LEVEL# NAME HASH GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS
---------------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- -------------- ---------------- ----------
00000000DF0E8E18 2 1 8 post/wait queue 823771719 0 0 0 0 0
00000000DF0E8ED8 2 2 8 post/wait queue 823771719 0 0 0 0 0
00000000DF0E8F98 2 3 8 post/wait queue 823771719 0 0 0 0 0
00000000DF0E9058 2 4 8 post/wait queue 823771719 0 0 0 0 0
00000000DF0E9118 2 5 8 post/wait queue 823771719 0 0 0 0 0
00000000DF0E91D8 2 6 8 post/wait queue 823771719 318043 822 437417 1505 0
00000000DF0E9298 2 7 8 post/wait queue 823771719 382458 585 439529 1031 0
7 rows selected.
再看下餘下LATCH的沒有子LATCH的4個LATCH的情況
可見沒有子LATCH的LATCH,只在V$LATCH及V$LATCH_PARENT儲存資料
SQL> select addr,latch#,level#,name,hash,gets,misses,immediate_gets,immediate_misses,spin_gets from v$latch where latch#=9;
ADDR LATCH# LEVEL# NAME HASH GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS
---------------- ---------- ---------- ------------------------------ ---------- ---------- ---------- -------------- ---------------- ----------
0000000060009F38 9 7 process allocation 2600548697 23328 2 7789 0 0
SQL> select addr,latch#,level#,name,hash,gets,misses,immediate_gets,immediate_misses,spin_gets from v$latch_parent where latch#=9;
ADDR LATCH# LEVEL# NAME HASH GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS
---------------- ---------- ---------- ------------------------------ ---------- ---------- ---------- -------------- ---------------- ----------
0000000060009F38 9 7 process allocation 2600548697 23328 2 7789 0 0
SQL> select addr,latch#,child#,level#,name,hash,gets,misses,immediate_gets,immediate_misses,spin_gets from v$latch_children where latch#=9 order by 3;
no rows selected
再學習下v$latchholder
經用ORADEBUG POKE模擬shared pool latch,雖然成功,但始終v$latchholder沒有資料,也就是說我對於v$latchholder理解不準確
會話1
可見當前沒有程式或會話持有LATCH
pid為持latch的程式號,sid為持latch會話,laddr為latch地址,gets為請求LATCH的次數(以樂意等待或不樂意等待),這理還是沒有掌握到如何模擬出v$latchholder的深一層使用
SQL> select pid,sid,laddr,name,gets from v$latchholder;
no rows selected
SQL> select latch#,child#,name from v$latch_children where latch#=293 order by 2;
LATCH# CHILD# NAME
---------- ---------- --------------------
293 1 shared pool
293 2 shared pool
293 3 shared pool
293 4 shared pool
293 5 shared pool
293 6 shared pool
293 7 shared pool
7 rows selected.
SQL> select pid,spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));
PID SPID
---------- ------------------------------------------------
31 7675
SQL> select sid from v$mystat where rownum=1;
SID
----------
40
可見當前SHARE POOL LATCH的MISES為1931
SQL> select addr,latch#,level#,name,hash,gets,misses,immediate_gets,immediate_misses,spin_gets from v$latch where latch#=293;
ADDR LATCH# LEVEL# NAME HASH GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS
---------------- ---------- ---------- ------------------------------ ---------- ---------- ---------- -------------- ---------------- ----------
0000000060033B08 293 7 shared pool 2276811941 607836 1931 0 0 0
SQL> select addr,latch#,level#,name,hash,gets,misses,immediate_gets,immediate_misses,spin_gets from v$latch where latch#=293;
ADDR LATCH# LEVEL# NAME HASH GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS
---------------- ---------- ---------- ------------------------------ ---------- ---------- ---------- -------------- ---------------- ----------
0000000060033B08 293 7 shared pool 2276811941 618910 1931 0 0 0
會話2
SQL> select pid,spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));
PID SPID
---------- ------------------------------------------------
30 7581
SQL> select sid from v$mystat where rownum=1;
SID
----------
38
會話1
用oradebug模擬shared poo latch
SQL> select 'oradebug poke 0x'||addr||' 4 0x00000001;' from v$latch_children where latch#=293;
'ORADEBUGPOKE0X'||ADDR||'40X00000001;'
--------------------------------------------------------------------------------------------
oradebug poke 0x0000000060103C88 4 0x00000001;
oradebug poke 0x0000000060103BE8 4 0x00000001;
oradebug poke 0x0000000060103B48 4 0x00000001;
oradebug poke 0x0000000060103AA8 4 0x00000001;
oradebug poke 0x0000000060103A08 4 0x00000001;
oradebug poke 0x0000000060103968 4 0x00000001;
oradebug poke 0x00000000601038C8 4 0x00000001;
7 rows selected.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x0000000060103C88 4 0x00000001;
oradebug poke 0x0000000060103BE8 4 0x00000001;
oradebug poke 0x0000000060103B48 4 0x00000001;
oradebug poke 0x0000000060103AA8 4 0x00000001;
oradebug poke 0x0000000060103A08 4 0x00000001;
oradebug poke 0x0000000060103968 4 0x00000001;
BEFORE: [060103C88, 060103C8C) = 00000000
AFTER: [060103C88, 060103C8C) = 00000001
SQL> BEFORE: [060103BE8, 060103BEC) = 00000000
AFTER: [060103BE8, 060103BEC) = 00000001
SQL> BEFORE: [060103B48, 060103B4C) = 00000000
AFTER: [060103B48, 060103B4C) = 00000001
SQL> BEFORE: [060103AA8, 060103AAC) = 00000000
AFTER: [060103AA8, 060103AAC) = 00000001
SQL> BEFORE: [060103A08, 060103A0C) = 00000000
AFTER: [060103A08, 060103A0C) = 00000001
SQL> BEFORE: [060103968, 06010396C) = 00000000
AFTER: [060103968, 06010396C) = 00000001
SQL> oradebug poke 0x00000000601038C8 4 0x00000001;
BEFORE: [0601038C8, 0601038CC) = 00000000
AFTER: [0601038C8, 0601038CC) = 00000001
會話2
SQL> create table t_latch(a int);
hang住了
會話1
SQL> select pid,sid,laddr,name,gets from v$latchholder;
no rows selected
但好像沒有資料顯示
會話1
釋放shared pool latch
SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x0000000060103C88 4 0x00000000;
BEFORE: [060103C88, 060103C8C) = 00000001
AFTER: [060103C88, 060103C8C) = 00000000
SQL> oradebug poke 0x0000000060103BE8 4 0x00000000;
BEFORE: [060103BE8, 060103BEC) = 00000001
AFTER: [060103BE8, 060103BEC) = 00000000
oradebug poke 0x0000000060103B48 4 0x00000000;
BEFORE: [060103B48, 060103B4C) = 00000001
AFTER: [060103B48, 060103B4C) = 00000000
SQL> oradebug poke 0x0000000060103AA8 4 0x00000000;
BEFORE: [060103AA8, 060103AAC) = 00000001
AFTER: [060103AA8, 060103AAC) = 00000000
SQL> oradebug poke 0x0000000060103A08 4 0x00000000;
BEFORE: [060103A08, 060103A0C) = 00000001
AFTER: [060103A08, 060103A0C) = 00000000
SQL> oradebug poke 0x0000000060103968 4 0x00000000;
BEFORE: [060103968, 06010396C) = 00000001
AFTER: [060103968, 06010396C) = 00000000
SQL> oradebug poke 0x00000000601038C8 4 0x00000000;
BEFORE: [0601038C8, 0601038CC) = 00000001
AFTER: [0601038C8, 0601038CC) = 00000000
會話2
ddl執行成功
SQL> create table t_latch2(a int);
Table created.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-1821684/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORACLE LOCK,LATCH,PINOracle
- Oracle Latch 說明Oracle
- Systematic Latch Contention Troubleshooting in OracleOracle
- 【BUFFER】Oracle buffer cache之 latch 學習記錄Oracle
- Latch free等待事件三(轉)事件
- [20190416]11g下那些latch是Exclusive的.txt
- [20190415]11g下那些latch是共享的.txt
- mutex,latch,lock,enqueue hash chains latch基礎概念MutexENQAI
- oracle一次卡頓案例(六)-latch freeOracle
- oracle常見異常等待——latch處理思路Oracle
- hiccup和Latch off
- [20190319]shared pool latch與library cache latch的簡單探究.txt
- [20210512]shared pool latch與library cache latch的簡單探究.txt
- latch等待事件彙總事件
- Latch free等待事件(轉)事件
- [20210520]11g shared pool latch與library cache mutex的簡單探究.txtMutex
- Latch free等待事件四(轉)事件
- [20190416]process allocation latch.txt
- Latch的spin及sleep(zt)
- latch:library cache lock等待事件事件
- 【FPGA基礎】Latch基礎FPGA
- Latch free等待事件二(轉)事件
- [20210521]11g shared pool latch與library cache mutex的簡單探究4.txtMutex
- [20210520]11g shared pool latch與library cache mutex的簡單探究3.txtMutex
- MySQL latch爭用深入分析MySql
- [20190419]shared latch spin count.txt
- [20190418]exclusive latch spin count.txt
- DB BUFFER LRU 列表的latch等待
- [20200211]檢視v$db_object_cache的CHILD_LATCH欄位.txtObject
- [20190416]exclusive latch測試指令碼.txt指令碼
- [20190419]shared latch spin count 2.txt
- [20210218]shared latch spin count 6.txt
- [20210218]shared latch spin count 5.txt
- [20190409]latch get 引數where and why.txt
- [20190415]關於shared latch(共享栓鎖).txt
- [20200223]關於latch and mutext的優化.txtMutex優化
- 碰到一個latch free相關的BUG
- [異常等待事件latch undo global data]分析事件
- [20210708]使用那個shared pool latch.txt