基於oradebug poke分析不同redo相關latch獲取的先後次序
結論
1,測試環境10.2.0.52,可見寫redo時，先要獲取redo copy latch,然後才是redo writing latch
3，如果是REDO相關的3個LATCH，先是獲取redo copy latch,接著才是redo allocation latch 最後是redo writing latch,
4, 上述3個redo latch到底是在server process---->log buffer--->redo log file，哪個階段獲取，將在下文進行分析
5，掌握了重要的分析思路，即：
其實透過上述的可以把所有的REDO相關的LATCH全部用ORADEBUG POKE HANG住，然後基於v$latch以及v$latch_children的immediate_misses進行對比分析，進而依次釋放首個獲取的LATCH，接著重複前面的動作，即可以把所有相關的REDO LATCH獲取的先後次序
a,先查詢所有要測試LATCH的immediate_gets相關資訊
SQL> select latch#,name,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where latch#=148;
LATCH# NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- -------------- ----------------
148 redo writing 35809 0 0 0 0
SQL> select latch#,name,child#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where latch#=149 and child#=1;
LATCH# NAME CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------------
149 redo copy 1 5 0 0 10690 10
B,ORADEBUG POKE，HANG 測試相關REDO LATCH
C,再次查詢a步的指令碼，進行對比，看哪些LATCH的immediate_misses變化，首先獲取的latch就是哪個
D,釋放由C步獲取的首個LATCH
E，再次重複上述A到D的過程，即可以把獲取相關LATCH的先後次序分析出來
擴充套件問題
1，上述的3個redo latch, 到底具體的含義是什麼，如何反證它們的業務含義，這是最有價值的地方
2，發現自己對於oracle核心後臺程式ckpt,lgwr,dbwr相關互動機制理解還是不到位，還要學習官方手冊
強化測試與總結
測試
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
SQL> select latch#,name from v$latch where lower(name) like '%redo%';
LATCH# NAME
---------- --------------------------------------------------
116 ping redo on-disk SCN
148 redo writing
149 redo copy
372 KFR redo allocation latch
115 redo on-disk SCN
150 redo allocation
6 rows selected.
SQL> select latch#,name,child#,addr from v$latch_children where latch#=149;
LATCH# NAME CHILD# ADDR
---------- ------------------------------ ---------- ----------------
149 redo copy 1 0000000069094BC0
149 redo copy 2 0000000069094C88
SQL> select latch#,name,child#,addr from v$latch_children where latch#=150;
LATCH# NAME CHILD# ADDR
---------- ------------------------------ ---------- ----------------
150 redo allocation 1 00000000693C70F0
150 redo allocation 2 00000000693C7190
150 redo allocation 3 00000000693C7230
150 redo allocation 4 00000000693C72D0
150 redo allocation 5 00000000693C7370
150 redo allocation 6 00000000693C7410
150 redo allocation 7 00000000693C74B0
150 redo allocation 8 00000000693C7550
150 redo allocation 9 00000000693C75F0
150 redo allocation 10 00000000693C7690
150 redo allocation 11 00000000693C7730
11 rows selected.
SQL> select latch#,name,child#,addr from v$latch_children where latch#=148;
no rows selected
SQL> select latch#,name,addr from v$latch where latch#=148;
LATCH# NAME ADDR
---------- ------------------------------ ----------------
148 redo writing 0000000060017DB0
SQL> show user
USER is "SCOTT"
SQL> create table t_redo(a int);
Table created.
SQL> insert into t_redo values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t_redo;
A
----------
1
--hang redo writing latch
SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x0000000060017DB0 4 1
BEFORE: [060017DB0, 060017DB4) = 00000000
AFTER: [060017DB0, 060017DB4) = 00000001
SQL>
--hang redo copy child latch 1, 0000000069094BC0
SQL> oradebug poke 0x0000000069094BC0 4 1
BEFORE: [069094BC0, 069094BC4) = 00000000
AFTER: [069094BC0, 069094BC4) = 00000001
---可見SQL查詢不用持redo相關的latch,當然，我這裡只是一種分析思路，請大家不要侷限於此
SQL> select * from t_redo;
A
----------
1
SQL> select latch#,name,child#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where latch#=149 and child#=1;
LATCH# NAME CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------------
149 redo copy 1 5 0 0 10690 10
SQL> select latch#,name,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where latch#=148;
LATCH# NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- -------------- ----------------
148 redo writing 35809 0 0 0 0
SQL> insert into t_redo values(1);
1 row created.
SQL> select latch#,name,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where latch#=148;
LATCH# NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- -------------- ----------------
148 redo writing 35809 0 0 0 0
SQL> select latch#,name,child#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where latch#=149 and child#=1;
LATCH# NAME CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------------
149 redo copy 1 5 0 0 10690 10
---提交上述的DML HANG住
SQL> commit;
---可見寫redo時，先要獲取redo copy latch,然後才是redo writing latch
SQL> select latch#,name,child#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where latch#=149 and child#=1;
LATCH# NAME CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------------
149 redo copy 1 5 0 0 10690 11
SQL> select latch#,name,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where latch#=148;
LATCH# NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- -------------- ----------------
148 redo writing 35809 0 0 0 0
SQL>
---釋放redo copy latch latch 1,上述的commit仍hang住
SQL> oradebug poke 0x0000000069094BC0 4 0
BEFORE: [069094BC0, 069094BC4) = 000000FF
AFTER: [069094BC0, 069094BC4) = 00000000
--hang redo allocation latch 1
SQL> oradebug poke 0x00000000693C70F0 4 1
BEFORE: [0693C70F0, 0693C70F4) = 00000000
AFTER: [0693C70F0, 0693C70F4) = 00000001
---釋放redo writing latch
SQL> oradebug poke 0x0000000060017DB0 4 0
BEFORE: [060017DB0, 060017DB4) = 000000FF
AFTER: [060017DB0, 060017DB4) = 00000000
--commit還是hang住，可見先是獲取redo copy latch,然後是redo writing latch,最後才是redo allocation latch
SQL> commit;
SQL> select latch#,name,child#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where latch#=150 and child#=1;
LATCH# NAME CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------------
150 redo allocation 1 11867 4 3 10823 1
----釋放 redo allocation latch 1,上述COMMIT提成完成
SQL> oradebug poke 0x00000000693C70F0 4 0
BEFORE: [0693C70F0, 0693C70F4) = 000000FF
AFTER: [0693C70F0, 0693C70F4) = 00000000
SQL> commit;
Commit complete.
---其實透過上述的可以把所有的REDO相關的LATCH全部用ORADEBUG POKE HANG住，然後基於v$latch以及v$latch_children進行對比分析，進而依次釋放首個獲取的LATCH，接著重複前面的動作，即可以把所有相關的REDO LATCH獲取的先後次序
分析出來
---有個問題，上述的哪個LATCH是用於LGWR FLUSH LOG BUFFER到REDO LOG FILE的呢？
SQL> alter system checkpoint;
System altered.
--hang redo writing latch
SQL> oradebug poke 0x0000000060017DB0 4 1
BEFORE: [060017DB0, 060017DB4) = 00000000
AFTER: [060017DB0, 060017DB4) = 00000001
---checkpoint hang，可見是redo writing latch是用於LGWR FLUSH LOG BUFFER到REDO LOG FILE的
SQL> alter system checkpoint;
SQL> oradebug poke 0x0000000060017DB0 4 0
BEFORE: [060017DB0, 060017DB4) = 000000FF
AFTER: [060017DB0, 060017DB4) = 00000000
SQL> alter system checkpoint;
System altered.
為了全面，再看下redo copy及redo allocation latch
---可見redo copy不是用於LGWR FLUSH LOG BUFFER到REDO LOG FILE的，反過來說它用於伺服器程式到log buffer之間
SQL> oradebug poke 0x0000000069094BC0 4 1
BEFORE: [069094BC0, 069094BC4) = 00000000
AFTER: [069094BC0, 069094BC4) = 00000001
SQL> alter system checkpoint;
System altered.
SQL> oradebug poke 0x0000000069094BC0 4 0
BEFORE: [069094BC0, 069094BC4) = 000000FF
AFTER: [069094BC0, 069094BC4) = 00000000
---可見redo allocation latch用於LGWR FLUSH LOG BUFFER到REDO LOG FILE
SQL> oradebug poke 0x00000000693C70F0 4 1
BEFORE: [0693C70F0, 0693C70F4) = 00000000
AFTER: [0693C70F0, 0693C70F4) = 00000001
--檢查點HANG
SQL> alter system checkpoint;
SQL> oradebug poke 0x00000000693C70F0 4 0
BEFORE: [0693C70F0, 0693C70F4) = 000000FF
AFTER: [0693C70F0, 0693C70F4) = 00000000
SQL> alter system checkpoint;
System altered.
--上述發現redo allocation latch及redo writing latch自己的理解還是有些問題，到底它們的先後次序是什麼呢，重複用上述思路分析下
---POKE前
SQL> select latch#,name,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where latch#=148;
LATCH# NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- -------------- ----------------
148 redo writing 36771 8 8 0 0
SQL> select latch#,name,child#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where latch#=150 and child#=1;
LATCH# NAME CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------------
150 redo allocation 1 12198 9 11 11105 5
---POKE
---REDO ALLOCATION LATCH
SQL> oradebug poke 0x00000000693C70F0 4 1
BEFORE: [0693C70F0, 0693C70F4) = 00000000
AFTER: [0693C70F0, 0693C70F4) = 00000001
--REDO WRITING LATCH
SQL> oradebug poke 0x0000000060017DB0 4 1
BEFORE: [060017DB0, 060017DB4) = 00000000
AFTER: [060017DB0, 060017DB4) = 00000001
SQL> insert into t_redo values(3);
1 row created.
--hang commit
SQL> commit;
--poke後
----可見先是獲取redo allocation ,然後是redo writing（注：我們主要看immediate_misses列的變化，另外：發現gets也有變化，這個列的含義還要進一步測試與理解）
SQL> select latch#,name,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where latch#=148;
LATCH# NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- -------------- ----------------
148 redo writing 36876 8 8 0 0
SQL>
SQL>
SQL> select latch#,name,child#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where latch#=150 and child#=1;
LATCH# NAME CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------------
150 redo allocation 1 12221 9 11 11105 7
---釋放REDO ALLOCATION LATCH
SQL> oradebug poke 0x00000000693C70F0 4 0
BEFORE: [0693C70F0, 0693C70F4) = 000000FF
AFTER: [0693C70F0, 0693C70F4) = 00000000
---可見釋放REDO ALLOCATION LATCH，其v$latch_children的列gets有增加，且misses有增加，immediate_gets也有增加（這裡變化之間的含義是什麼，還要好好思考）
SQL> select latch#,name,child#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where latch#=150 and child#=1;
LATCH# NAME CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------------
150 redo allocation 1 12224 11 13 11114 7
---而後者redo writing latch沒有變化，也只是說只要自己的LATCH由獲取不到變化為可以獲取的資訊，它的資訊才會變化
SQL> select latch#,name,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where latch#=148;
LATCH# NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- -------------- ----------------
148 redo writing 36876 8 8 0 0
---釋放REDO writing latch,同上我的分析，它對應的列也發生了變化
SQL> oradebug poke 0x0000000060017DB0 4 0
BEFORE: [060017DB0, 060017DB4) = 000000FF
AFTER: [060017DB0, 060017DB4) = 00000000
SQL>
SQL>
SQL> select latch#,name,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where latch#=148;
LATCH# NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ------------------------------ ---------- ---------- ---------- -------------- ----------------
148 redo writing 36888 11 11 0 0
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-1869295/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 由oradebug poke process allocation latch引發dfs lock handle等待事件進一步分析事件
- oradebug poke模擬shared pool latch與硬解析原理小析
- 關於獲取事件相應的結果事件
- 分析：關於 「關注後使用者資訊獲取介面」調整的通知
- latch 相關等待事件事件
- 獲取網路卡的相關資訊
- Oracle中的redo copy latchOracle
- 關於POST傳值太大後端獲取不到後端
- 獲取app版本號相關資訊APP
- buffer cache與相關的latch等待事件事件
- 使用Python獲取ECS相關資訊Python
- C++獲取硬體相關資訊C++
- 用JS獲取函式相關的程式碼JS函式
- latch相關一些資料
- PHP獲取客戶端、PHP獲取伺服器相關資訊PHP客戶端伺服器
- 關於如何獲取資料的方法
- 關於filter獲取session的問題！！！FilterSession
- 使用oradebug來獲取跟蹤檔案的位置
- 關於latch的一點點理解
- 【Spring Boot】使用JDBC 獲取相關的資料Spring BootJDBC
- C# 獲取系統相關時間C#
- JavaScript獲取元素相對於document的偏移量JavaScript
- 由oradebug poke推進scn理解scn base及scn wrap系列一
- oracle library cache相關的等待事件及latchOracle事件
- 基於學生人口統計分析美國遊戲人才獲取渠道遊戲
- 關於海量資料的獲取問題
- 關於瀏覽器快取問題（圖片更換後，頁面仍優先讀取快取）瀏覽器快取
- 基於Gin框架的web後端開發（三）: 獲取queryString引數框架Web後端
- 獲取v$latch資料來源實驗
- iOS獲取當前裝置的資訊-網路相關iOS
- 關於no-wait latch 的疑問AI
- c++任意變數型別獲取相關C++變數型別
- PHP獲取上級（來路）URL相關資訊PHP
- oracle資料庫獲取指定表的列的相關資訊Oracle資料庫
- 關於模型關聯 獲取不到關聯資訊 求教模型
- 重做日誌（redo log）相關總結
- 關於Oracle的redo和undo的理解Oracle
- 基於Gin框架的web後端開發（四）: 獲取FORM表單引數框架Web後端ORM