oracle 11g latch之v$latch系列三

wisdomone1發表於2015-11-03

背景

   本文為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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章