{"id":2621,"date":"2018-02-02T15:15:13","date_gmt":"2018-02-02T06:15:13","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2621"},"modified":"2018-02-02T17:26:51","modified_gmt":"2018-02-02T08:26:51","slug":"oracle-lgwr-%ec%84%b1%eb%8a%a5%ec%9d%84-%ed%99%95%ec%9d%b8-%ed%95%98%eb%8a%94-%ec%8a%a4%ed%81%ac%eb%a6%bd%ed%8a%b8","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2621","title":{"rendered":"Oracle LGWR \uc131\ub2a5\uc744 \ud655\uc778 \ud558\ub294 \uc2a4\ud06c\ub9bd\ud2b8"},"content":{"rendered":"

Oracle LGWR \uc740 log file sync \uad00\ub828\ud574\uc11c \uc9d1\uc911 \uad00\ub9ac\ud574\uc57c \ud558\ub294 \ud504\ub85c\uc138\uc2a4 \uc785\ub2c8\ub2e4.<\/p>\n

\uc624\ub77c\ud074 DB\ub294 \ub85c\uadf8\ub97c \uc548 \ucc0d\uc73c\uba74 \ub370\uc774\ud0c0 \ubcc0\uacbd \ub418\uc9c0 \uc54a\ub294 \uc194\ub8e8\uc158 \uc785\ub2c8\ub2e4.<\/p>\n

\ub530\ub77c\uc11c LGWR \uc131\ub2a5\uc774 \uc548\ub098\uc628\ub2e4\uba74 \uc774\ub97c \ubaa8\ub2c8\ud130\ub9c1 \ud558\uace0 \uad00\ub9ac \ud574\uc57c \ud569\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

<\uacb0\uacfc \ud654\uba74><\/p>\n

\"image\"<\/a><\/p>\n

 <\/p>\n

<\ucd94\ucd9c \uc2a4\ud06c\ub9bd\ud2b8 1 : lgwr_stat.sql ><\/strong><\/p>\n

set pause off time on linesize 200 pages 999 timing off trimout on trimspool on long 99999\r\n\r\ncol e11 for 999999999990.00 new_value ve11\r\ncol e12 for 999999999990.00 new_value ve12\r\ncol e13 for 999999999990.00 new_value ve13\r\ncol e14 for 999999999990.00 new_value ve14\r\ncol e21 for 999999999990.00 new_value ve21\r\ncol e22 for 999999999990.00 new_value ve22\r\ncol e23 for 999999999990.00 new_value ve23\r\ncol e24 for 999999999990.00 new_value ve24\r\ncol rs1 for 999999999990.00 new_value vrs1\r\ncol lr1 for 999999999990.00 new_value vlr1\r\ncol pr1 for 999999999990.00 new_value vpr1\r\ncol pw1 for 999999999990.00 new_value vpw1\r\ncol rs2 for 999999999990.00 new_value vrs2\r\ncol lr2 for 999999999990.00 new_value vlr2\r\ncol pr2 for 999999999990.00 new_value vpr2\r\ncol pw2 for 999999999990.00 new_value vpw2\r\n\r\ncol snaptime\u00a0\u00a0\u00a0 for a20 head \"Timestamp(oracle)\"\r\ncol wt\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for 9999990.00 head \"LGWR-Wt(ms)\"\r\ncol sync\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for 9999990.00 head \"Sync-Wt(ms)\"\r\ncol lfpw_n\u00a0\u00a0\u00a0\u00a0\u00a0 for 9999990.00 head \"LGWR-W#\"\r\ncol lfpw_wt\u00a0\u00a0\u00a0\u00a0 for 9999990.00 head \"LGWR-WtSum\"\r\ncol lfs_n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for 9999990.00 head \"Sync-W#\"\r\ncol lfs_wt\u00a0\u00a0\u00a0\u00a0\u00a0 for 9999990.00 head \"Sync-WtSum\"\r\ncol rs\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for 9999990.00 head \"Redo(K\/s)\"\r\ncol lr\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for 9999990.00 head \"DB.Read(M\/s)\"\r\ncol pr\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for 9999990.00 head \"Phy.Read(M\/s)\"\r\ncol pw\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for 9999990.00 head \"Phy.Write(M\/s)\"\r\n\r\nset term off verify off\r\nselect\r\nlfpw_wt e11\r\n,lfs_wt\u00a0 e12\r\n,lfpw_n\u00a0 e13\r\n,lfs_n\u00a0\u00a0 e14\r\n,rs\u00a0\u00a0\u00a0\u00a0\u00a0 rs1\r\n,lr\u00a0\u00a0\u00a0\u00a0\u00a0 lr1\r\n,pr\u00a0\u00a0\u00a0\u00a0\u00a0 pr1\r\n,pw\u00a0\u00a0\u00a0\u00a0\u00a0 pw1\r\nfrom\r\n(\r\nselect\r\nmax(case when event='log file parallel write'\u00a0 then TIME_WAITED_MICRO\/1000 else 0 end) lfpw_wt\r\n,max(case when event='log file sync'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 then TIME_WAITED_MICRO\/1000 else 0 end) lfs_wt\r\n,max(case when event='log file parallel write'\u00a0 then TOTAL_WAITS else 0 end) lfpw_n\r\n,max(case when event='log file sync'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 then TOTAL_WAITS else 0 end) lfs_n\r\nfrom v$system_event\r\nwhere event in ('log file parallel write','log file sync')\r\n) event,\r\n(\r\nselect\r\nmax(case when name='redo size'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 then value\/1024 else 0 end) rs\r\n,max(case when name='session logical reads'\u00a0\u00a0\u00a0\u00a0 then value*8192\/1048576 else 0 end) lr\r\n,max(case when name='physical read bytes'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 then value\/1048576 else 0 end) pr\r\n,max(case when name='physical write bytes'\u00a0\u00a0\u00a0\u00a0\u00a0 then value\/1048576 else 0 end) pw\r\nfrom v$sysstat\r\nwhere name in ('redo size','session logical reads','physical read bytes','physical write bytes')\r\n) stat\r\n\/\r\n\r\n!sleep 10\r\n\r\nselect\r\nlfpw_wt e21\r\n,lfs_wt\u00a0 e22\r\n,lfpw_n\u00a0 e23\r\n,lfs_n\u00a0\u00a0 e24\r\n,rs\u00a0\u00a0\u00a0\u00a0\u00a0 rs2\r\n,lr\u00a0\u00a0\u00a0\u00a0\u00a0 lr2\r\n,pr\u00a0\u00a0\u00a0\u00a0\u00a0 pr2\r\n,pw\u00a0\u00a0\u00a0\u00a0\u00a0 pw2\r\nfrom\r\n(\r\nselect\r\nmax(case when event='log file parallel write'\u00a0 then TIME_WAITED_MICRO\/1000 else 0 end) lfpw_wt\r\n,max(case when event='log file sync'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 then TIME_WAITED_MICRO\/1000 else 0 end) lfs_wt\r\n,max(case when event='log file parallel write'\u00a0 then TOTAL_WAITS else 0 end) lfpw_n\r\n,max(case when event='log file sync'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 then TOTAL_WAITS else 0 end) lfs_n\r\nfrom v$system_event\r\nwhere event in ('log file parallel write','log file sync')\r\n) event,\r\n(\r\nselect\r\nmax(case when name='redo size'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 then value\/1024 else 0 end) rs\r\n,max(case when name='session logical reads'\u00a0\u00a0\u00a0\u00a0 then value*8192\/1048576 else 0 end) lr\r\n,max(case when name='physical read bytes'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 then value\/1048576 else 0 end) pr\r\n,max(case when name='physical write bytes'\u00a0\u00a0\u00a0\u00a0\u00a0 then value\/1048576 else 0 end) pw\r\nfrom v$sysstat\r\nwhere name in ('redo size','session logical reads','physical read bytes','physical write bytes')\r\n) stat\r\n\/\r\n\r\nset term on\r\nselect\r\nto_char(sysdate,'yyyy-mm-dd_hh24:mi:ss') snaptime --\"Timestamp\"\r\n,case when &ve23-&ve13 > 0\u00a0\u00a0 then (&ve21-&ve11)\/(&ve23-&ve13) else 0 end wt\r\n,case when &ve24-&ve14 > 0\u00a0\u00a0 then (&ve22-&ve12)\/(&ve24-&ve14) else 0 end sync\r\n, (&vrs2-&vrs1)\/10 rs --\"Redo(K\/s)\"\r\n, (&vlr2-&vlr1)\/10 lr --\"DB.Read(M\/s)\"\r\n, (&vpr2-&vpr1)\/10 pr --\"Phy.Read(M\/s)\"\r\n, (&vpw2-&vpw1)\/10 pw --\"Phy.Write(M\/s)\"\r\n, (&ve21-&ve11) lfpw_wt --\"LGWRWT(ms)\"\r\n, (&ve22-&ve12) lfs_wt --\"SyncWT(ms)\"\r\n, (&ve23-&ve13) lfpw_n --\"LGWRwait#\"\r\n, (&ve24-&ve14) lfs_n --\"Syncwait#\"\r\nfrom dual;\r\n\r\nexit\r\n\r\n<\/pre>\n
 <\/p>\n
<\uc218\ud589 \uc2a4\ud06c\ub9bd\ud2b8 2 : run.sh ><\/strong><\/p>\n
 <\/p>\n
#!\/usr\/bin\/ksh\r\n\r\nSCRIPT=$1\r\nTIMES=$2\r\nSLEEPTIME=$3\r\n\r\nLOGFILE=\"`echo $ORACLE_SID`_`basename $1 .sql`_`date '+%y%m%d'`.log\"\r\n### date '+%Y\/%m\/%d %H:%M:%S'\r\n\r\ninteger INITTIMES=0\r\n\r\nif (($#<3))\r\nthen\r\necho \"Usage: $0 <SCRIPT> <TIMES> <SLEEPTIME>\"\r\nexit 1\r\nfi\r\nwhile [[ $INITTIMES -lt $TIMES ]]\r\ndo\r\nsqlplus -s \"\/as sysdba\" <<!!\r\n@$SCRIPT\r\nexit;\r\n!!\r\nsleep $SLEEPTIME\r\nINITTIMES=$INITTIMES+1\r\ndone >> $LOGFILE<\/pre>\n
 <\/p>\n
< \uc218\ud589 \ubc29\ubc95 ><\/span><\/strong><\/p>\n
[oracle11]yspark-linux:\/home\/oracle11> .\/run.sh lgwr_stat.sql 17700 5<\/span><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle LGWR \uc740 log file sync \uad00\ub828\ud574\uc11c \uc9d1\uc911 \uad00\ub9ac\ud574\uc57c \ud558\ub294 \ud504\ub85c\uc138\uc2a4 \uc785\ub2c8\ub2e4. \uc624\ub77c\ud074 DB\ub294 \ub85c\uadf8\ub97c \uc548 \ucc0d\uc73c\uba74 \ub370\uc774\ud0c0 \ubcc0\uacbd \ub418\uc9c0 \uc54a\ub294 \uc194\ub8e8\uc158 \uc785\ub2c8\ub2e4. \ub530\ub77c\uc11c LGWR \uc131\ub2a5\uc774 \uc548\ub098\uc628\ub2e4\uba74 \uc774\ub97c \ubaa8\ub2c8\ud130\ub9c1 \ud558\uace0 \uad00\ub9ac \ud574\uc57c \ud569\ub2c8\ub2e4.   <\uacb0\uacfc \ud654\uba74>   <\ucd94\ucd9c \uc2a4\ud06c\ub9bd\ud2b8 1 : lgwr_stat.sql > set pause off time on linesize 200 pages 999 timing off trimout […]<\/p>\n","protected":false},"author":1,"featured_media":2338,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[9],"tags":[853,854,855],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/01\/oracle-cloud-logo.png?fit=300%2C194","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2621"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2621"}],"version-history":[{"count":5,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2621\/revisions"}],"predecessor-version":[{"id":2641,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2621\/revisions\/2641"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2338"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2621"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2621"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2621"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}