Let's optimize Logical Standby Database Configuration

Oracle Community

Let's optimize Logical Standby Database Configuration

Written By


We have covered with two parts on "Lets optimize Data Guard Configuration" specific to Physical standby, Now we will focus on Logical standby database on how to optimize and managing the configuration and so that how efficiently the SQL apply process can work and the Users can extract the live data for the application usage. This article purely applicable to Logical standby database.

How SQL Apply Works?

As we know the Media recovery process works for Physical standby database, like wise SQL statements will be applied on Logical standby database. We will see how actually the SQL apply works internally.  This process is divided into log mining and the SQL Apply.

  1. Primary database ships the redo to the standby database and from standby database RFS process works to store them either in Standby redo log files or in format of Archived redo logs.
  2. As soon as the redo data is available in Standby redo log files or archived redo logs the reader process reads the redo data and then it bypasses to the preparer process.  Preparer process can be one or more.
  3. The preparer process transforms redo records into Logical Change Records(LCR) and it can be one or more. These LCR's will be stored in the LCR Cache of SGA.
  4. Builder process is responsible to gathering the DDL or DML statements into transactions and it will be processed to applier processes.

  5. When it comes to Apply processes, Analyzer process plays role to gather the dependency between the transactions and at the same time the coordinator process will be involved to ensure it has assigned the transactions to the various applier process by considering the dependencies in mind.
  6. Finally the applier process will execute the DDL/DML statements that make up the transactions.

Optimizing LCR Cache Size

We know that Preparer process transforms redo into LCR(Logical change Records) and then stored in the LCR cache of SGA. We have to ensure instance have sufficient LCR cache for the database, If in case the allocated LCR cache is not sufficient then ultimately SQL apply pages LCR cache into SPILL table of SYSAUX tables which can be extremely expensive. In order to avoid we have to take case of  LCR cache sizing. We can extract the paging value from the view v$logstdby_stats, if the value is "0" that means there is no Paging occurred and which refers that LCR cache is still sufficient. We can come to conclusion whether to increase LCR cache based on the paging value.

SQL> select name,to_number(value) value from v$logstdby_stats where name='bytes paged out';

NAME                                VALUE

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

bytes paged out                         0

In order to know how much LCR cache, the below query can be used. Based on the PCT used the memory can be reduced or increased.

SQL> select name,(least(max_sga,bytes)/max_sga) * 100 pct_utilization

from ( select *

from v$sgastat

where name = 'Logminer LCR c'


, (select value*(1024*1024) max_sga

from dba_logstdby_parameters

where name = 'MAX_SGA'


NAME                           PCT_UTILIZATION

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

Logminer LCR c                      20.3366216

Now we can see how much PCT of LCR used out of the MAX_SGA, if it is utilization is less then we consider to reduce and note that if the utilization is 100% then it means LCR cache used out of MAX_SGA parameter value.

To increase, modify the value of MAX_SGA then the DBMS_LOGSTDBY procedure can be used.

SQL> exec dbms_logstdby.apply_set('MAX_SGA',200);

PL/SQL procedure successfully completed.

SQL> select name,value from v$logstdby_stats where name='maximum SGA for LCR cache (MB)';

NAME                                     VALUE

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

maximum SGA for LCR cache (MB)           200


We can verify the updated value for MAX_SGA from the view v$logstdby_stats.

Optimizing SQL Apply

As explained how the SQL apply works from the standby redo logs phase to until it applies to database, We have control to adjust the number of processes allocated to SQL apply. During the Redo mining phase more than one preparer process can work and during apply phase one or more applier process can be worked to apply statements as transactions in Logical standby.  Now we will see how the number of processes will be calculated and what parameters we have to consider before estimate and also we will see how to adjust the parameters using the PL/SQL procedure.

Even though we mention here that we are going to perform adjusting applier and preparer process, internally we are going to deal and manage with the logical standby parameters. They are

APPLY_SERVERS: Describes the number of processes to apply changes over Logical standby database.

MAX_SERVERS: Describes the number of processes uses to read and apply the redo

PREPARER_SERVERS: Describes number of the preparer processes to prepare he changes

There are lot of changes comes into picture from 11gR1 for Logical standby database, In detail the number of SQL apply processes count were used to taken from the parallel processes running on the system of the parameter PARALLEL_MAX_SERVERS. If in case the number of parallel servers are already occupied by the queries then the appropriate processes were not allocated to Logical standby database, So by consideration of this conflict from 11gR1 Oracle allows us to allocate explicitly with the MAX_SERVERS parameter.  

Now we will work on how many number of processes we can allocate for APPLY_SERVERS and PREPARER processes out of the MAX_SERVERS.  Out of the MAX_SERVERS we have to allocate each process for READER, BUILDER and ANALYZER roles and remaining of the MAX_SERVERS can be distributed to the APPLY and PREPARER processes. As we said again we can alter the changes of these parameters we can perform using DBMS_LOGSTDBY procedure.


Before allocating the number of processes to APPLIER and PREPARER processes, we have to review the value of MAX_SERVERS based on the Hardware configuration and support by the server and also the transaction frequency over Logical standby database. There is no accurate figure for MAX_SERVERS to determine for the initial level, The default setting of this value is 9 where it is not sufficient for the huge transactions. So we can consider this value as "MAX_SERVERS = 8 * Core", from that value we can start assigning the number of processes to the APPLIER or PREPARER processes. If the server consists of high configuration with 32 cores then consider to maximum value of 100 for MAX_SERVERS. 

The CPU core's information can be gathered from the Linux level by using command or the same information can be retrieved from the AWR reports as well.

[oracle@ora-X2 ~]$ cat /proc/cpuinfo | grep 'core id'

In order to change/modify the value of MAX_SERVERS, we have to use DBMS_LOGSTDBY procedure like below.

Optimizing Applier processes

After performing and changes in MAX_SERVERS parameter, now we can adjust the number of APPLY and PREPARER processes. In order to estimate the required APPPLIER processes we can consider reviewing the current usage at Logical standby database.

1) Gathering details of the APPLIER process current number of processes used and the idle.  From the below output only one APPLIER process is working and remaining out of 5 are in idle status.

SQL> select username,sid,serial#,status,program,module from v$session where program like '%AS0%';

USERNAME          SID    SERIAL# STATUS   PROGRAM                                  MODULE

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

SYS                56          9 ACTIVE   oracle@ora-X2.localdomain (AS00)         Streams                    <-- Analyzer Process

SYS                61          7 ACTIVE   oracle@ora-X2.localdomain (AS04)         Streams

SYS                62          9 ACTIVE   oracle@ora-X2.localdomain (AS05)         Streams

SYS                66          7 ACTIVE   oracle@ora-X2.localdomain (AS01)         Streams

SYS                67          7 ACTIVE   oracle@ora-X2.localdomain (AS03)         Streams

SYS                74         11 ACTIVE   oracle@ora-X2.localdomain (AS02)         Streams

6 rows selected.


SQL> select count(*) as IDLE from v$logstdby_process where type='APPLIER' and status_code=16116;




SQL> select type,status from v$logstdby where type='APPLIER';

TYPE           STATUS

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

APPLIER        ORA-16125: large transaction 10 18 1048 is waiting for more data

APPLIER        ORA-16116: no work available

APPLIER        ORA-16116: no work available

APPLIER        ORA-16116: no work available

APPLIER        ORA-16116: no work available


If we have enough applier processes then it doesn't make sense to increase the APPLIER processes. But ensure the findings are from the peak hours rather than when the database is idle.

If the all APPLIER processes are busy then we need to gather information of whether there is enough work is available for the APPLIER processes, for this we have to gather the number of transactions that are ready to be applied by the APPLIER process and the transactions that have already applied. If suppose we found the difference between transactions mined and the transactions applied is higher than we can multiply the number of processes.

For example the CPU cores are 8 the, we have to exclude 3 processes for the READER, BUILDER and ANALYZER. After all remaining those number of processes can be used for APPLIER+PREPARER processes.

SQL> exec dbms_logstdby.apply_set('APPLY_SERVERS',3);

PL/SQL procedure successfully completed.

SQL> select name,value from v$logstdby_stats where name='number of appliers';

NAME                           VALUE

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

number of appliers             3


As soon as we have reduced the APPLIER processes from 5 to 3, then two of the APPLIER processes will be stopped.

Wed Sep 16 21:38:05 2015


Wed Sep 16 21:38:05 2015

LOGSTDBY Apply process AS05 server id=5 pid=47 OS id=13683 stopped

Wed Sep 16 21:38:05 2015

LOGSTDBY Apply process AS04 server id=4 pid=46 OS id=13681 stopped

Optimizing Preparer processes

As we said in the SQL Apply process architecture, we can have one or more processes for APPLIER and the PREPARER process because it has the mechanism of transform redo records into Logical change records(LCR). So it should be quick enough in case of huge transactions.

Likewise we determined how busy are APPLIER process, we have to find out the status of the PRAPARER process whether all of the processes are busy or idle. From the below output if we see only one preparer server is configured and hence it's not an easy task to estimate how many more PREPARER process is required. The formula to increase the PRPARER is same i.e. the number of transactions are ready to be applied is less than the number of APPLIER processes.

SQL> select name,value from v$logstdby_stats where name='number of preparers';

NAME                           VALUE

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

number of preparers            1

SQL> select type,status from v$logstdby where type='PREPARER';

TYPE           STATUS

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

PREPARER       ORA-16117: processing

So for the existing transactions of course it is clear that one process will not sufficient and hence increase the value based on the formula above we discussed. The value of PREPARE_SERVERS can be changed using DBMS_LOGSTDBY procedure.

SQL> exec dbms_logstdby.apply_set('PREPARE_SERVERS',3);

PL/SQL procedure successfully completed.


Basically the number of PREPARER or APPLIER process we need consider by the usage of the processes, in order to increase we have to consider some optimal value based on our observations.

SQL> select type,status from v$logstdby where type='PREPARER';

TYPE           STATUS

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

PREPARER       ORA-16117: processing

PREPARER       ORA-16117: processing

PREPARER       ORA-16117: processing


Wed Sep 16 22:01:26 2015


Wed Sep 16 22:01:26 2015

LOGMINER: session#=1 (Logical_Standby$), preparer MS03 pid=47 OS id=13779 sid=55 started

Wed Sep 16 22:01:27 2015

LOGMINER: session#=1 (Logical_Standby$), preparer MS04 pid=50 OS id=13781 sid=70 started

Wed Sep 16 22:02:44 2015

Please note that PREPARE_SERVERS or APPLIER_SERVERS values we have to configure after the MAX_SERVERS parameter.


We've seen how actually the SQL Apply process works and how important is to configure the Logical change records cache in order to avoid excessive usage of SYSAUX and finally how to familiarize by playing with the SQL apply process parameters such as MAX_SERVERS, APPLIER_SERVERS and the PREPARE_SERVERS.



4327 1 /
Follow / 23 Sep 2015 at 2:08pm

Nice Article with good explanation steps !!!