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_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.