Oracle LGWR 은 log file sync 관련해서 집중 관리해야 하는 프로세스 입니다.
오라클 DB는 로그를 안 찍으면 데이타 변경 되지 않는 솔루션 입니다.
따라서 LGWR 성능이 안나온다면 이를 모니터링 하고 관리 해야 합니다.
<결과 화면>
<추출 스크립트 1 : lgwr_stat.sql >
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 |
set pause off time on linesize 200 pages 999 timing off trimout on trimspool on long 99999 col e11 for 999999999990.00 new_value ve11 col e12 for 999999999990.00 new_value ve12 col e13 for 999999999990.00 new_value ve13 col e14 for 999999999990.00 new_value ve14 col e21 for 999999999990.00 new_value ve21 col e22 for 999999999990.00 new_value ve22 col e23 for 999999999990.00 new_value ve23 col e24 for 999999999990.00 new_value ve24 col rs1 for 999999999990.00 new_value vrs1 col lr1 for 999999999990.00 new_value vlr1 col pr1 for 999999999990.00 new_value vpr1 col pw1 for 999999999990.00 new_value vpw1 col rs2 for 999999999990.00 new_value vrs2 col lr2 for 999999999990.00 new_value vlr2 col pr2 for 999999999990.00 new_value vpr2 col pw2 for 999999999990.00 new_value vpw2 col snaptime for a20 head "Timestamp(oracle)" col wt for 9999990.00 head "LGWR-Wt(ms)" col sync for 9999990.00 head "Sync-Wt(ms)" col lfpw_n for 9999990.00 head "LGWR-W#" col lfpw_wt for 9999990.00 head "LGWR-WtSum" col lfs_n for 9999990.00 head "Sync-W#" col lfs_wt for 9999990.00 head "Sync-WtSum" col rs for 9999990.00 head "Redo(K/s)" col lr for 9999990.00 head "DB.Read(M/s)" col pr for 9999990.00 head "Phy.Read(M/s)" col pw for 9999990.00 head "Phy.Write(M/s)" set term off verify off select lfpw_wt e11 ,lfs_wt e12 ,lfpw_n e13 ,lfs_n e14 ,rs rs1 ,lr lr1 ,pr pr1 ,pw pw1 from ( select max(case when event='log file parallel write' then TIME_WAITED_MICRO/1000 else 0 end) lfpw_wt ,max(case when event='log file sync' then TIME_WAITED_MICRO/1000 else 0 end) lfs_wt ,max(case when event='log file parallel write' then TOTAL_WAITS else 0 end) lfpw_n ,max(case when event='log file sync' then TOTAL_WAITS else 0 end) lfs_n from v$system_event where event in ('log file parallel write','log file sync') ) event, ( select max(case when name='redo size' then value/1024 else 0 end) rs ,max(case when name='session logical reads' then value*8192/1048576 else 0 end) lr ,max(case when name='physical read bytes' then value/1048576 else 0 end) pr ,max(case when name='physical write bytes' then value/1048576 else 0 end) pw from v$sysstat where name in ('redo size','session logical reads','physical read bytes','physical write bytes') ) stat / !sleep 10 select lfpw_wt e21 ,lfs_wt e22 ,lfpw_n e23 ,lfs_n e24 ,rs rs2 ,lr lr2 ,pr pr2 ,pw pw2 from ( select max(case when event='log file parallel write' then TIME_WAITED_MICRO/1000 else 0 end) lfpw_wt ,max(case when event='log file sync' then TIME_WAITED_MICRO/1000 else 0 end) lfs_wt ,max(case when event='log file parallel write' then TOTAL_WAITS else 0 end) lfpw_n ,max(case when event='log file sync' then TOTAL_WAITS else 0 end) lfs_n from v$system_event where event in ('log file parallel write','log file sync') ) event, ( select max(case when name='redo size' then value/1024 else 0 end) rs ,max(case when name='session logical reads' then value*8192/1048576 else 0 end) lr ,max(case when name='physical read bytes' then value/1048576 else 0 end) pr ,max(case when name='physical write bytes' then value/1048576 else 0 end) pw from v$sysstat where name in ('redo size','session logical reads','physical read bytes','physical write bytes') ) stat / set term on select to_char(sysdate,'yyyy-mm-dd_hh24:mi:ss') snaptime --"Timestamp" ,case when &ve23-&ve13 > 0 then (&ve21-&ve11)/(&ve23-&ve13) else 0 end wt ,case when &ve24-&ve14 > 0 then (&ve22-&ve12)/(&ve24-&ve14) else 0 end sync , (&vrs2-&vrs1)/10 rs --"Redo(K/s)" , (&vlr2-&vlr1)/10 lr --"DB.Read(M/s)" , (&vpr2-&vpr1)/10 pr --"Phy.Read(M/s)" , (&vpw2-&vpw1)/10 pw --"Phy.Write(M/s)" , (&ve21-&ve11) lfpw_wt --"LGWRWT(ms)" , (&ve22-&ve12) lfs_wt --"SyncWT(ms)" , (&ve23-&ve13) lfpw_n --"LGWRwait#" , (&ve24-&ve14) lfs_n --"Syncwait#" from dual; exit |
<수행 스크립트 2 : run.sh >
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
#!/usr/bin/ksh SCRIPT=$1 TIMES=$2 SLEEPTIME=$3 LOGFILE="`echo $ORACLE_SID`_`basename $1 .sql`_`date '+%y%m%d'`.log" ### date '+%Y/%m/%d %H:%M:%S' integer INITTIMES=0 if (($#<3)) then echo "Usage: $0 <SCRIPT> <TIMES> <SLEEPTIME>" exit 1 fi while [[ $INITTIMES -lt $TIMES ]] do sqlplus -s "/as sysdba" <<!! @$SCRIPT exit; !! sleep $SLEEPTIME INITTIMES=$INITTIMES+1 done >> $LOGFILE |
< 수행 방법 >
[oracle11]yspark-linux:/home/oracle11> ./run.sh lgwr_stat.sql 17700 5