关于oracle lgwr 进程的一点思考

66 阅读 0 评论 44 点赞
我是靠谱客的博主英俊河马，最近开发中收集的这篇文章主要介绍关于oracle lgwr 进程的一点思考，觉得挺不错的，现在分享给大家，希望可以做个参考。
概述

今天参加ACOUG活动，eygle大师分享了一个案例，其中讲到进程的strace时，问我某个字符是神马意思？
当时一时想不起了，他简单的进行了一下描述，猛然大悟，但是还不是特别清楚其中的细节，很多东西
确实是不用就会忘记的，晚上回来，准备研究eygle在活动上所提的lgwr 在写redo 时需要获得哪些latch？
具体这些latch的持有和释放的顺序是怎么样的？做了一会儿实验，没有搞明白，突然trace lgwr进程，发现
了另外一点内容，彻底颠覆了我以前的认知。
SQL> !ps -ef|grep lgwr
ora10g   23584     1  0 08:36 ?        00:00:00 ora_lgwr_roger
ora10g   23637 23572  0 08:37 pts/1    00:00:00 /bin/bash -c ps -ef|grep lgwr
 
 
使用strace -ftr -o a.log -p 23584 跟踪lgwr进程，然后另外开启一个窗口进行如下操作：
 
1000 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL>  alter system switch logfile;
 
System altered.
 
SQL> !ps -ef|grep lgwr|grep -v grep
ora10g   23584     1  0 08:36 ?        00:00:00 ora_lgwr_roger
 
然后我们再回头去看看strace 打出来的trace是什么样的。
 
23584      0.000056 gettimeofday({1345909238, 375806}, NULL) = 0
23584      0.000053 times(NULL)         = 432511470
23584      0.000071 gettimeofday({1345909238, 375933}, NULL) = 0
23584      0.000060 gettimeofday({1345909238, 375990}, NULL) = 0
23584      0.000059 semtimedop(622592, 0x1, 0, {4294901770, 0}) = -1 EAGAIN (Resource temporarily unavailable)
23584      3.001243 gettimeofday({1345909241, 377292}, NULL) = 0
.....省略部分信息
23584      0.000166 times(NULL)         = 432511770
23584      0.000032 gettimeofday({1345909241, 377821}, NULL) = 0
23584      0.000043 times(NULL)         = 432511770
23584      0.000031 pwrite64(20, "1"1622620200303265h4"..., 921088, 7168) = 921088
23584      0.008318 times(NULL)         = 432511771
23584      0.000040 gettimeofday({1345909241, 386276}, NULL) = 0
23584      0.000072 times(NULL)         = 432511771
23584      0.000040 gettimeofday({1345909241, 386367}, NULL) = 0
23584      0.000036 getrusage(RUSAGE_SELF, {ru_utime={0, 13997}, ru_stime={0, 66989}, ...}) = 0
23584      0.000048 getrusage(RUSAGE_SELF, {ru_utime={0, 13997}, ru_stime={0, 66989}, ...}) = 0
23584      0.000046 gettimeofday({1345909241, 386496}, NULL) = 0
.....省略部分信息
23584      0.000126 gettimeofday({1345909242, 708436}, NULL) = 0
23584      0.000071 times(NULL)         = 432511903
23584      0.000074 times(NULL)         = 432511903
23584      0.000054 gettimeofday({1345909242, 708690}, NULL) = 0
23584      0.000134 times(NULL)         = 432511903
23584      0.000110 pwrite64(20, "1"25722620200j3512145235"..., 512, 928256) = 512
23584      0.000944 times(NULL)         = 432511904
23584      0.000062 gettimeofday({1345909242, 709896}, NULL) = 0
23584      0.000089 times(NULL)         = 432511904
23584      0.000121 gettimeofday({1345909242, 710096}, NULL) = 0
23584      0.000171 semctl(622592, 23, IPC_64|SETVAL, 0xbfffd4fc) = 0
23584      0.001596 gettimeofday({1345909242, 711864}, NULL) = 0
23584      0.000064 gettimeofday({1345909242, 711935}, NULL) = 0
23584      0.000068 gettimeofday({1345909242, 711992}, NULL) = 0
23584      0.000060 times(NULL)         = 432511904
23584      0.000068 gettimeofday({1345909242, 712122}, NULL) = 0
23584      0.000120 gettimeofday({1345909242, 712247}, NULL) = 0
23584      0.000072 semtimedop(622592, 0x1, 670000, {4294901770, 0}) = -1 EAGAIN (Resource temporarily unavailable)
23584      1.671260 gettimeofday({1345909244, 383580}, NULL) = 0
23584      0.000072 gettimeofday({1345909244, 383647}, NULL) = 0
......省略部分信息
23584      0.000462 gettimeofday({1345909247, 141720}, NULL) = 0
23584      0.000106 pread64(15, "25302114225377"..., 16384, 16384) = 16384
23584      0.000135 gettimeofday({1345909247, 142041}, NULL) = 0
23584      0.000193 gettimeofday({1345909247, 142133}, NULL) = 0
23584      0.000139 pread64(16, "25302114225377"..., 16384, 16384) = 16384
23584      0.000105 gettimeofday({1345909247, 142405}, NULL) = 0
23584      0.000122 gettimeofday({1345909247, 142503}, NULL) = 0
23584      0.000071 pread64(17, "25302114225377"..., 16384, 16384) = 16384
23584      0.000093 gettimeofday({1345909247, 143127}, NULL) = 0
23584      0.000576 gettimeofday({1345909247, 143255}, NULL) = 0
23584      0.000087 pread64(15, "25302202273137737714P31200"..., 16384, 262144) = 16384
23584      0.000237 gettimeofday({1345909247, 143563}, NULL) = 0
23584      0.000156 gettimeofday({1345909247, 143722}, NULL) = 0
23584      0.000464 pread64(15, "25302222273137737714vL"..., 16384, 294912) = 16384
23584      0.000408 gettimeofday({1345909247, 144677}, NULL) = 0
23584      0.000472 gettimeofday({1345909247, 145071}, NULL) = 0
23584      0.000095 pread64(15, "25302242263137737714243R17"..., 16384, 327680) = 16384
23584      0.000112 gettimeofday({1345909247, 145282}, NULL) = 0
23584      0.000090 gettimeofday({1345909247, 145358}, NULL) = 0
23584      0.000082 pread64(15, "25302252273137737714B<220"..., 16384, 344064) = 16384
23584      0.000181 gettimeofday({1345909247, 145623}, NULL) = 0
23584      0.000083 gettimeofday({1345909247, 145705}, NULL) = 0
23584      0.000230 semctl(622592, 9, IPC_64|SETVAL, 0xbfffe10c) = 0
23584      0.000809 gettimeofday({1345909247, 146745}, NULL) = 0
23584      0.000069 times(NULL)         = 432512347
23584      0.000055 pwrite64(15, "25302262303137737714362230"..., 16384, 360448) = 16384
23584      0.000852 times(NULL)         = 432512347
23584      0.000052 times(NULL)         = 432512347
23584      0.000053 pwrite64(16, "25302262303137737714362230"..., 16384, 360448) = 16384
23584      0.000689 times(NULL)         = 432512348
23584      0.000057 times(NULL)         = 432512348
23584      0.000052 pwrite64(17, "25302262303137737714362230"..., 16384, 360448) = 16384
23584      0.000612 times(NULL)         = 432512348
23584      0.000067 gettimeofday({1345909247, 149302}, NULL) = 0
23584      0.000077 gettimeofday({1345909247, 149402}, NULL) = 0
23584      0.000089 times(NULL)         = 432512348
23584      0.000088 pwrite64(15, "2530221230313773771410L"..., 16384, 278528) = 16384
23584      0.000565 times(NULL)         = 432512348
23584      0.000050 times(NULL)         = 432512348
23584      0.000052 pwrite64(16, "2530221230313773771410L"..., 16384, 278528) = 16384
23584      0.000562 times(NULL)         = 432512348
23584      0.000051 times(NULL)         = 432512348
23584      0.000051 pwrite64(17, "2530221230313773771410L"..., 16384, 278528) = 16384
23584      0.000616 times(NULL)         = 432512348
23584      0.000067 gettimeofday({1345909247, 151570}, NULL) = 0
23584      0.000073 gettimeofday({1345909247, 151643}, NULL) = 0
23584      0.000063 times(NULL)         = 432512348
23584      0.000052 pwrite64(15, "2530217230313773771431733"..., 16384, 245760) = 16384
23584      0.000622 times(NULL)         = 432512348
23584      0.000067 times(NULL)         = 432512348
23584      0.000091 pwrite64(16, "2530217230313773771431733"..., 16384, 245760) = 16384
23584      0.000641 times(NULL)         = 432512348
23584      0.000051 times(NULL)         = 432512348
23584      0.000062 pwrite64(17, "2530217230313773771431733"..., 16384, 245760) = 16384
23584      0.000649 times(NULL)         = 432512348
23584      0.000127 gettimeofday({1345909247, 154107}, NULL) = 0
23584      0.000119 gettimeofday({1345909247, 154198}, NULL) = 0
23584      0.000085 gettimeofday({1345909247, 154271}, NULL) = 0
23584      0.000063 times(NULL)         = 432512348
23584      0.000054 pwrite64(15, "25302114300377"..., 16384, 16384) = 16384
23584      0.001031 times(NULL)         = 432512348
23584      0.000083 times(NULL)         = 432512348
23584      0.000056 pwrite64(16, "25302114300377"..., 16384, 16384) = 16384
23584      0.000639 times(NULL)         = 432512348
23584      0.000060 times(NULL)         = 432512348
23584      0.000051 pwrite64(17, "25302114300377"..., 16384, 16384) = 16384
23584      0.000624 times(NULL)         = 432512348
23584      0.000088 gettimeofday({1345909247, 157021}, NULL) = 0
23584      0.000065 gettimeofday({1345909247, 157085}, NULL) = 0
23584      0.000065 pread64(15, "25302114300377"..., 16384, 16384) = 16384
23584      0.000090 gettimeofday({1345909247, 157250}, NULL) = 0
23584      0.000077 gettimeofday({1345909247, 157317}, NULL) = 0
23584      0.000124 times(NULL)         = 432512348
23584      0.000092 times(NULL)         = 432512348
23584      0.000061 gettimeofday({1345909247, 157594}, NULL) = 0
23584      0.000119 semctl(622592, 23, IPC_64|SETVAL, 0xbfffcfb4) = 0
23584      0.000143 gettimeofday({1345909247, 157857}, NULL) = 0
23584      0.000065 gettimeofday({1345909247, 158008}, NULL) = 0
23584      0.000170 pread64(20, "1"12262002462705 n"..., 512, 512) = 512
23584      0.000067 gettimeofday({1345909247, 158151}, NULL) = 0
23584      0.000042 gettimeofday({1345909247, 158200}, NULL) = 0
23584      0.000046 pread64(15, "25302362233137737714236177"..., 16384, 491520) = 16384
23584      0.000054 gettimeofday({1345909247, 158293}, NULL) = 0
23584      0.000047 gettimeofday({1345909247, 158340}, NULL) = 0
23584      0.000039 pwrite64(18, "1"12272002442725 n"..., 512, 512) = 512
23584      0.000504 gettimeofday({1345909247, 158885}, NULL) = 0
23584      0.000078 gettimeofday({1345909247, 158994}, NULL) = 0
23584      0.000084 pread64(20, "1"12262002462705 n"..., 512, 512) = 512
23584      0.000050 gettimeofday({1345909247, 159095}, NULL) = 0
23584      0.000041 gettimeofday({1345909247, 159137}, NULL) = 0
23584      0.000039 pwrite64(20, "1"1226200u2105 n)M"..., 512, 512) = 512
23584      0.000874 gettimeofday({1345909247, 160050}, NULL) = 0
23584      0.000052 gettimeofday({1345909247, 160103}, NULL) = 0
23584      0.000041 times(NULL)         = 432512349
23584      0.000032 pwrite64(15, "25302252313137737714217234"..., 16384, 344064) = 16384
23584      0.000816 times(NULL)         = 432512349
23584      0.000031 times(NULL)         = 432512349
23584      0.000032 pwrite64(16, "25302252313137737714217234"..., 16384, 344064) = 16384
23584      0.000589 times(NULL)         = 432512349
23584      0.000030 times(NULL)         = 432512349
23584      0.000031 pwrite64(17, "25302252313137737714217234"..., 16384, 344064) = 16384
23584      0.000571 times(NULL)         = 432512349
23584      0.000036 gettimeofday({1345909247, 162310}, NULL) = 0
23584      0.000044 gettimeofday({1345909247, 162354}, NULL) = 0
23584      0.000054 pread64(15, "25302(12203137737714200Ln"..., 16384, 4849664) = 16384
23584      0.000077 gettimeofday({1345909247, 162488}, NULL) = 0
23584      0.000047 gettimeofday({1345909247, 162532}, NULL) = 0
23584      0.000043 gettimeofday({1345909247, 162575}, NULL) = 0
23584      0.000038 pread64(15, "253022752263137737714$177Y"..., 16384, 3096576) = 16384
23584      0.000052 gettimeofday({1345909247, 162665}, NULL) = 0
23584      0.000042 gettimeofday({1345909247, 162707}, NULL) = 0
23584      0.000038 pread64(15, "25302252313137737714217234"..., 16384, 344064) = 16384
23584      0.000051 gettimeofday({1345909247, 162796}, NULL) = 0
23584      0.000106 gettimeofday({1345909247, 162904}, NULL) = 0
23584      0.000039 times(NULL)         = 432512349
23584      0.000051 pwrite64(15, "253022762313137737714}177Y"..., 16384, 3112960) = 16384
23584      0.000520 times(NULL)         = 432512349
23584      0.000030 times(NULL)         = 432512349
23584      0.000032 pwrite64(16, "253022762313137737714}177Y"..., 16384, 3112960) = 16384
23584      0.000598 times(NULL)         = 432512349
23584      0.000037 times(NULL)         = 432512349
23584      0.000031 pwrite64(17, "253022762313137737714}177Y"..., 16384, 3112960) = 16384
23584      0.000507 times(NULL)         = 432512349
23584      0.000035 gettimeofday({1345909247, 164782}, NULL) = 0
23584      0.000042 gettimeofday({1345909247, 164825}, NULL) = 0
23584      0.000039 pread64(15, "25302242263137737714243R17"..., 16384, 327680) = 16384
23584      0.000054 gettimeofday({1345909247, 164917}, NULL) = 0
23584      0.000078 gettimeofday({1345909247, 165000}, NULL) = 0
23584      0.000043 times(NULL)         = 432512349
23584      0.000031 pwrite64(15, "25302232313137737714246R17"..., 16384, 311296) = 16384
23584      0.000568 times(NULL)         = 432512349
23584      0.000043 times(NULL)         = 432512349
23584      0.000032 pwrite64(16, "25302232313137737714246R17"..., 16384, 311296) = 16384
23584      0.000458 times(NULL)         = 432512349
23584      0.000039 times(NULL)         = 432512349
23584      0.000031 pwrite64(17, "25302232313137737714246R17"..., 16384, 311296) = 16384
23584      0.000543 times(NULL)         = 432512349
23584      0.000034 gettimeofday({1345909247, 166819}, NULL) = 0
23584      0.000045 gettimeofday({1345909247, 166863}, NULL) = 0
23584      0.000038 times(NULL)         = 432512349
23584      0.000031 pwrite64(15, "253022223131377377143332"..., 16384, 294912) = 16384
23584      0.000697 times(NULL)         = 432512349
23584      0.000030 times(NULL)         = 432512349
23584      0.000031 pwrite64(16, "253022223131377377143332"..., 16384, 294912) = 16384
23584      0.000509 times(NULL)         = 432512349
23584      0.000037 times(NULL)         = 432512349
23584      0.000032 pwrite64(17, "253022223131377377143332"..., 16384, 294912) = 16384
23584      0.000565 times(NULL)         = 432512350
23584      0.000035 gettimeofday({1345909247, 168867}, NULL) = 0
23584      0.000060 gettimeofday({1345909247, 168933}, NULL) = 0
23584      0.000289 times(NULL)         = 432512350
23584      0.000037 pwrite64(15, "25302202313137737714P8200$"..., 16384, 262144) = 16384
23584      0.000525 times(NULL)         = 432512350
23584      0.000030 times(NULL)         = 432512350
23584      0.000032 pwrite64(16, "25302202313137737714P8200$"..., 16384, 262144) = 16384
23584      0.000576 times(NULL)         = 432512350
23584      0.000046 times(NULL)         = 432512350
23584      0.000092 pwrite64(17, "25302202313137737714P8200$"..., 16384, 262144) = 16384
23584      0.000630 times(NULL)         = 432512350
23584      0.000043 gettimeofday({1345909247, 171228}, NULL) = 0
23584      0.000045 gettimeofday({1345909247, 171272}, NULL) = 0
23584      0.000044 gettimeofday({1345909247, 171316}, NULL) = 0
23584      0.000037 times(NULL)         = 432512350
23584      0.000054 pwrite64(15, "25302114300377"..., 16384, 16384) = 16384
23584      0.000804 times(NULL)         = 432512350
23584      0.000038 times(NULL)         = 432512350
23584      0.000032 pwrite64(16, "25302114300377"..., 16384, 16384) = 16384
23584      0.000589 times(NULL)         = 432512350
23584      0.000030 times(NULL)         = 432512350
23584      0.000031 pwrite64(17, "25302114300377"..., 16384, 16384) = 16384
23584      0.000606 times(NULL)         = 432512350
23584      0.000034 gettimeofday({1345909247, 173571}, NULL) = 0
23584      0.000038 gettimeofday({1345909247, 173609}, NULL) = 0
23584      0.000039 pread64(15, "25302114300377"..., 16384, 16384) = 16384
23584      0.000054 gettimeofday({1345909247, 173702}, NULL) = 0
23584      0.000040 gettimeofday({1345909247, 173742}, NULL) = 0
23584      0.000040 times(NULL)         = 432512350
23584      0.000751 times(NULL)         = 432512350
23584      0.000043 gettimeofday({1345909247, 174576}, NULL) = 0
23584      0.000041 semctl(622592, 23, IPC_64|SETVAL, 0xbfffd09c) = 0
23584      0.001190 gettimeofday({1345909247, 175992}, NULL) = 0
23584      0.000242 pread64(15, "25302362233137737714236177"..., 16384, 491520) = 16384
23584      0.000061 gettimeofday({1345909247, 176111}, NULL) = 0
23584      0.000057 gettimeofday({1345909247, 176175}, NULL) = 0
23584      0.000111 getrusage(RUSAGE_SELF, {ru_utime={0, 15997}, ru_stime={0, 72988}, ...}) = 0
23584      0.000051 getrusage(RUSAGE_SELF, {ru_utime={0, 15997}, ru_stime={0, 72988}, ...}) = 0
23584      0.000045 gettimeofday({1345909247, 176374}, NULL) = 0
23584      0.000078 gettimeofday({1345909247, 176545}, NULL) = 0
23584      0.000155 semctl(622592, 9, IPC_64|SETVAL, 0xbfffe0f4) = 0
23584      0.000165 open("/proc/23604/stat", O_RDONLY) = 32
23584      0.000058 read(32, "23604 (oracle) S 1 23604 23604 0"..., 999) = 190
23584      0.000085 close(32)           = 0
23584      0.000115 gettimeofday({1345909247, 177038}, NULL) = 0
23584      0.000049 semctl(622592, 20, IPC_64|SETVAL, 0xbfffe0a4) = 0
23584      0.000624 semctl(622592, 20, IPC_64|SETVAL, 0xbfffe14c) = 0
23584      0.000042 close(8)            = 0
23584      0.000035 open("/home/ora10g/admin/roger/bdump/alert_roger.log", O_WRONLY|O_APPEND|O_CREAT|O_LARGEFILE, 0660) = 8
23584      0.000062 time(NULL)          = 1345909247
23584      0.000040 stat64("/etc/localtime", {st_mode=S_IFREG|0644, st_size=1017, ...}) = 0
23584      0.000146 stat64("/etc/localtime", {st_mode=S_IFREG|0644, st_size=1017, ...}) = 0
23584      0.000071 stat64("/etc/localtime", {st_mode=S_IFREG|0644, st_size=1017, ...}) = 0
23584      0.000064 writev(8, [{"Sat Aug 25 08:40:47 PDT 2012n", 29}, {"Thread 1 advanced to log sequenc"..., 51}, {"n", 1}], 3) = 81
23584      0.000074 times(NULL)         = 432512350
23584      0.000051 times(NULL)         = 432512350
23584      0.000031 close(8)            = 0
23584      0.000031 open("/home/ora10g/admin/roger/bdump/alert_roger.log", O_WRONLY|O_APPEND|O_CREAT|O_LARGEFILE, 0660) = 8
23584      0.000063 writev(8, [{"  Current log# 1 seq# 151 mem# 0"..., 71}, {"n", 1}], 2) = 72
23584      0.000072 gettimeofday({1345909247, 178488}, NULL) = 0
.....省略部分信息
23584      0.000057 gettimeofday({1345909247, 179580}, NULL) = 0
23584      0.000197 semtimedop(622592, 0x1, 200000, {4294901770, 0}) = -1 EAGAIN (Resource temporarily unavailable)
23584      0.201062 gettimeofday({1345909247, 380703}, NULL) = 0
.....省略部分信息
23584      0.000068 gettimeofday({1345909247, 382030}, NULL) = 0
23584      0.000047 semtimedop(622592, 0x1, 0, {4294901770, 0}) = -1 EAGAIN (Resource temporarily unavailable)
23584      3.000146 gettimeofday({1345909250, 382231}, NULL) = 0
23584      0.000068 gettimeofday({1345909250, 382295}, NULL) = 0
23584      0.000058 gettimeofday({1345909250, 382353}, NULL) = 0
23584      0.000062 gettimeofday({1345909250, 382415}, NULL) = 0
23584      0.000055 gettimeofday({1345909250, 382548}, NULL) = 0
23584      0.000170 gettimeofday({1345909250, 382644}, NULL) = 0
23584      0.000063 times(NULL)         = 432512671
23584      0.000225 gettimeofday({1345909250, 382930}, NULL) = 0
23584      0.000058 gettimeofday({1345909250, 382984}, NULL) = 0
23584      0.000055 times(NULL)         = 432512671
23584      0.000065 times(NULL)         = 432512671
23584      0.000107 gettimeofday({1345909250, 383219}, NULL) = 0
23584      0.000073 times(NULL)         = 432512671
23584      0.000050 pwrite64(18, "1"222720200200oh4223"..., 512, 1024) = 512
23584      0.000886 times(NULL)         = 432512671
23584      0.000078 gettimeofday({1345909250, 384300}, NULL) = 0
.....省略部分信息
23584      0.000092 gettimeofday({1345909250, 385232}, NULL) = 0
23584      0.000061 semtimedop(622592, 0x1, 0, {4294901770, 0}) = -1 EAGAIN (Resource temporarily unavailable)
23584      3.000481 gettimeofday({1345909253, 385780}, NULL) = 0
.....省略部分信息
23584      0.001228 times(NULL)         = 432512971
23584      0.000520 gettimeofday({1345909253, 388094}, NULL) = 0
23584      0.000184 getrusage(RUSAGE_SELF, {ru_utime={0, 15997}, ru_stime={0, 74988}, ...}) = 0
23584      0.000081 getrusage(RUSAGE_SELF, {ru_utime={0, 15997}, ru_stime={0, 74988}, ...}) = 0
23584      0.000183 gettimeofday({1345909253, 388540}, NULL) = 0
.....省略部分信息
23584      0.000141 gettimeofday({1345909253, 389471}, NULL) = 0
23584      0.000079 semtimedop(622592, 0x1, 0, {4294901770, 0} <unfinished ...>
[root@killdb ~]#
我们来简单分析下上面的trace内容，首先pread64 这样的，是一个参数，可以看到时一些读的操作。
全文请见原文博客链接地址关于lgwr 进程的一点思考