Oracle LGWR 은 log file sync 관련해서 집중 관리해야 하는 프로세스 입니다.

오라클 DB는 로그를 안 찍으면 데이타 변경 되지 않는 솔루션 입니다.

따라서 LGWR 성능이 안나온다면 이를 모니터링 하고 관리 해야 합니다.

 

<결과 화면>

image

 

<추출 스크립트 1 : lgwr_stat.sql >

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 >

 

#!/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

By haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

답글 남기기

이메일 주소를 발행하지 않을 것입니다. 필수 항목은 *(으)로 표시합니다