MRP Speed (Log Apply Rate of a Standby Database)

.

You may want to calculate the apply rate of your standby database if you need performance tuning on your dataguard environment. Also if you are “Using a Physical Standby Database for Read/Write Testing and Reporting” in 10g or Snapshot Standby in 11g, you may need this value to calculate how much time does your standby database needs to re-syncronize in a point of time.

There are some ways of getting a value as log apply rate. For example, the following query shows the active and average apply rate.

set linesize 400
col Values for a65
col Recover_start for a21
select to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss') "Recover_start",to_char(item)||' = '||to_char(sofar)||' '||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi') "Values" from v$recovery_progress where start_time=(select max(start_time) from v$recovery_progress);

Recover_start Values
--------------------- -----------------------------------------------------------------
22.04.2010 09:02:38 Log Files = 83 Files
22.04.2010 09:02:38 Active Apply Rate = 8448 KB/sec
22.04.2010 09:02:38 Average Apply Rate = 3642 KB/sec
22.04.2010 09:02:38 Redo Applied = 85288 Megabytes
22.04.2010 09:02:38 Last Applied Redo = 2147483647 SCN+Time 21.04.2010 11:08
22.04.2010 09:02:38 Active Time = 23931 Seconds
22.04.2010 09:02:38 Apply Time per Log = 270 Seconds
22.04.2010 09:02:38 Checkpoint Time per Log = 16 Seconds
22.04.2010 09:02:38 Elapsed Time = 23974 Seconds



You should now that these values have some weakness in calculation and may not reflect your actual apply rate. “Active Apply Rate” is very momentary and changeable, where “Average Apply Rate” includes the time that MRP process waits for the archive log to arrive, into calculation.

Another and more accurate way may be collecting the archivelogs without applying (i mean stop recovery and keep standby mounted) and then starting recovery, monitoring alertlog and doing some calculations. For example 6 archivelogs were applied in 5 minutes and your log file size is 512mb. So apply rate is 10.2mbps. This value is more reliable but it may be time consuming to perform this process whenever you want to measure the speed.

Now let’s see what Oracle says in “Oracle Database 10g Best Practices: Data Guard Redo Apply and Media Recovery” paper:

Assess Recovery Rate

Use the following queries to get several snapshots while a redo log is being applied to obtain the current recovery rate:

i) Determine Log Block Size (lebsz) since it is different for each operating system. This query only needs to be executed once.

SQL> select lebsz LOG_BLOCK_SIZE from x$kccle where rownum=1;

ii) Derive recovery blocks applied for at least 2 snapshots:

(a) Media Recovery Cases (e.g. recover [standby] database)

select TYPE, ITEM, SOFAR, TO_CHAR(SYSDATE, ‘DD-MON-YYYY HH:MI:SS’) TIME from v$RECOVERY_PROGRESS where ITEM=’Redo Blocks’ and TOTAL=0;

(b) Managed Recovery Cases (e.g. recover managed standby database…)

select PROCESS, SEQUENCE#, THREAD#, BLOCK#, BLOCKS, TO_CHAR(SYSDATE, ‘DD-MON-YYYY HH:MI:SS’) TIME from V$MANAGED_STANDBY where PROCESS=’MRP0’;

iii) To determine the recovery rate (MB/sec) for this archive, use one of these formulas with the information derived above:

(a) Media Recovery Case:

((SOFAR_END – SOFAR_BEG) * LOG_BLOCK_SIZE) /
((TIME_END – TIME_BEG) * 1024 * 1024 )

(b) Managed Recovery Case:

((BLOCK#_END – BLOCK#_BEG) * LOG_BLOCK_SIZE)) /
((TIME_END – TIME_BEG) * 1024 * 1024)

By this way you will have different results in each test you perform. But this doesn’t mean that the method is wrong. The value depends on what kind of operation is your standby database performing (while applying the archivelog) and an average of multiple test results will be accurate enough. I prepared a shell script to perform this test several times and calculate the average. It takes two snapshots of the MRP0 process by waiting 10 seconds. If the snapshots are suitable to calculate the recovery rate (block number on second snapshot is bigger then the one on first snapshot and not equal to zero) it saves the recovery rate value. Then outputs the average of last 50 calculation. Here it is:


#!/usr/bin/sh
############# VARIABLES
#######################
export ORACLE_HOME=/…….
export ORACLE_SID=XXXX
export HOME=/home/oracle
export WORK_PATH=$HOME/DB_SCRIPTS/
export LBS=512

############# DEFINE
###################
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin
export TNS_ADMIN=$ORACLE_HOME/network/admin

############## MRP SNAP BY 10 seconds
###################################
$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' << EOF > $WORK_PATH/snap.log
set linesize 300;
SELECT PROCESS, SEQUENCE#, THREAD#, block#, BLOCKS, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS')
time from v\$MANAGED_STANDBY WHERE PROCESS='MRP0';
EOF
snap1=`cat $WORK_PATH/snap.log |tail -2 |head -1 |awk '{print $4}'`
echo $snap1

sleep 10

$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' << EOF > $WORK_PATH/snap.log
set linesize 300;
SELECT PROCESS, SEQUENCE#, THREAD#, block#, BLOCKS, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS')
time from v\$MANAGED_STANDBY WHERE PROCESS='MRP0';
EOF
snap2=`cat $WORK_PATH/snap.log |tail -2 |head -1 |awk '{print $4}'`
echo $snap2

############## CALCULATE THE MOMENTARY RATE
###########################################
if [[ "$snap1" != "0" && "$snap2" != "0" && "$snap1" -lt "$snap2" ]]
then
echo "ok"
mrpspeed=$(( `expr "$snap2" - "$snap1"` * $LBS ))
echo 'scale=4;'$mrpspeed'/10485760' | bc >> $WORK_PATH/mrpspeedlog
else
echo "not ok"
exit
fi

############# KEEP LAST 50 VALUE
###############################
tail -50 $WORK_PATH/mrpspeedlog > $WORK_PATH/temporary.log
mv $WORK_PATH/temporary.log $WORK_PATH/mrpspeedlog

############# CALCULATE THE AVERAGE
###################################
n=0
sum=0
while read x
do
sum=`echo "scale=4;$sum+$x" |bc`
    if [ "$?" -eq "0" ]; then
    n=`expr $n + 1`
    fi
done < $WORK_PATH/mrpspeedlog
echo "scale=2;$sum/$n" | bc > $WORK_PATH/averagespeed.txt
-->
-->

1 comments:

Tani said...

Performance or tuning your database is very important for getting the optimize output from the database. It is very important when your database is not a standby database.
oracle ebs