logical standby 同步缓慢

 

If there are a number of appliers that consistently have a status code of 16124, then there is a possibility of SQL Apply being delayed by an excessive number of Eager Transactions.

 

An Eager Transaction is a transaction that updates many rows. From Data Guard 10g Release 1, an Eager Transaction is defined as updating more than 201 rows. The purpose of an Eager Transaction is to alleviate an apparent "hang" by trickling records into the standby database as they become available rather than waiting for the entire transaction to be built before applying it to the standby database.

This works well for most cases. However the performance of SQL Apply will deteriorate if an application creates multiple concurrent eager transactions on the primary database (multiple sessions connected to the primary database, all modifying more than 201 rows each).

 

If the slowed performance of SQL Apply impacts the standby database service levels, it is possible to reduce the interference by increasing the definition of an Eager Transaction.

 

Use the following query to determine if this is the cause of a hung or slowly progressing SQL Apply:

col event format a50 trunc
select ls.status_code, s.event, count(1) No_Of_Appliers
  from v$logstdby ls
      , v$streams_apply_server sas
      , v$session s
  where ls.type = 'APPLIER'
   and ls.status_code in ( 16124, 16123 )
   and ls.logstdby_id = sas.server_id
   and s.sid = sas.sid
 group by ls.status_code, s.event;

 

In the example above, the large number of appliers waiting on the "rdbms ipc message" event suggests that a number of eager transactions are being applied, and that performance would benefit from increasing the number of rows that define an eager transaction.

To determine an appropriate value for Eager Size, analyze the application (if possible) that is creating the slow down to occur, determine the number of rows that make up the transaction, and set Eager Size equal to this value.

To increase the Eager Transaction parameter, SQL Apply must be stopped briefly.

 

SQL> alter database stop logical standby apply;
SQL> execute dbms_logstdby.apply_set('_EAGER_SIZE',<no_of_rows>);
SQL> alter database start logical standby apply [immediate];

 

The value to which the _ EAGER_SIZE should be increased should not exceed 1000. Since a higher value will result in fewer transactions being defined as Eager, this may result in the LCR Cache (controlled by the MAX_SGA parameter) becoming full.

 

After changing the _EAGER_SIZE parameter, double-check to ensure paging is not occurring per b above. If paging is occurring, then the MAX_SGA parameter should be increased (if possible) or else the _EAGER_SIZE parameter should be reduced to a level where paging does not occur.