Rough and ready performance tuning of logical standby database instance


Today, we did some rough and ready performance tuning of one of our logical standby database instance.

The problem was that the logical standby database instance was between 110 and 130 minutes behind the primary database instance all morning.

We checked for all of the obvious problems for standby database instances:

  • APPLY LAG was set to zero (0)
  • No errors in alert log
  • No hung transactions
  • DDL “storms” — product going wild with DDL statements

The problem turned out to be that the following default settings were too low for the current apply workload. We fixed the problem by setting to the new higher values:

Parameter Default Value New Setting Maximum Value (11.2)
MAX_SERVERS 9 40 2048
MAX_SGA 30 MB 1,200 MB 4095 MB

We got these new settings by trial and error.

These new settings are done through the DBMS_LOGSTDBY.APPLY_SET as shown below:

BEGIN
  dbms_logstdby.apply_set('MAX_SERVERS',   '40');
  dbms_logstdby.apply_set('MAX_SGA',     '1200');
END;
/

The progress of redo apply is monitored through the following query on the V$LOGSTDBY_PROGRESS view:

ALTER SESSION SET nls_date_format='YYYY/MM/DD HH24:MI:SS';
SELECT
    applied_time,
    latest_time
  FROM
    v$logstdby_progress;

It took about half an hour to catch up fully.

Advertisements

One thought on “Rough and ready performance tuning of logical standby database instance

  1. Implementation of Logical Standby Database a Failure – Yet Another OCM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s