Determining Dataguard Standby Apply Rate

It can be very useful to see how your standby database is performing, whether your infrastructure has the capacity to keep your standby in synch and how fast it can actually apply the data. In particular if you have decided to save on hardware and licensing costs at your business continuity site, then you may have a lower performing server for your standby than you have at your primary location. If this is the case you really want to see what kind of apply rate you are managing, and how this compares to the amount of redo your primary is generating.

Paul Moen from Pythian wrote about a script he uses to keep a historical record of the apply rate. However if you want something more immediate you can maybe use a view called V$STANDBY_APPLY_SNAPSHOT. There does not appear to be much information out there on this view, and it was only recently that I stumbled across it. This is not documented in the Oracle documentation  in either the 10g or 11g docs. There is also nothing about this view on metalink as a search turned up no hits. The view gets a brief mention in a talk by Dataguard product manager, Larry Carpenter.

Here is a description of the view:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

PL/SQL Release 10.2.0.3.0 - Production

CORE    10.2.0.3.0      Production

TNS for Linux: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 - Production

SQL> desc v$standby_apply_snapshot

 Name                              Null?    Type

 --------------------------------- -------- -----------------------------------------------

 SNAPSHOT_TIME                               DATE

 SESSION_ID                                  NUMBER

 THREAD#                                     NUMBER

 RESET_TIMESTAMP                             NUMBER

 SEQUENCE#                                   NUMBER

 BLOCK#                                      NUMBER

 APPLIED_SCN                                 NUMBER

 APPLIED_TIME                                DATE

 NEWEST_RESET_TIMESTAMP                      NUMBER

 NEWEST_ARCHIVED_SEQ#                        NUMBER

 NEWEST_TIME                                 DATE

 NEWEST_USED                                 NUMBER

 NEWEST_SRL_SEQ#                             NUMBER

 BLOCKSIZE                                   NUMBER

 APPLY_RATE                                  NUMBER

This is what I see on my system:

sys@STANDBY> select snapshot_time, thread#, sequence#, applied_scn, applied_time, apply_rate
from V$standby_apply_snapshot;

SNAPSHOT_TIME     THREAD# SEQUENCE# APPLIED_SCN APPLIED_TIME  APPLY_RATE
----------------- ------- --------- ----------- ------------- ----------
27-02-08 15:45:08     1      8684    2549146933 27-02-08 15:44:48  47234
27-02-08 15:45:08     2      7145    2549146847 27-02-08 15:44:49  47234

The advantage of this view is that it’s updated pretty much continually, and you can see from the applied_time how far behind you are in applying. So is my apply rate 47MB per second? I do hope so. Though in Larry’s talk he states the apply rate is in KB/sec which is a little bit fantastic for the numbers I am seeing!