基于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
正文到此结束
热门推荐
相关文章
Loading...
![[HBLOG]公众号](http://www.liuhaihua.cn/img/qrcode_gzh.jpg)
