背景

本文為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.

oracle 11g latch之v$latch系列三

背景

結論

測試

相關文章