Oracle DG logical Standby Data Synchronization performance optimization

1, adjust the number of applier process

Applier processes are processes that perform application operations. By default, logical standby starts 5 applier processes, and if the log application task is heavy (or primary a larger database modification), it is helpful to increase the efficiency of the application by starting several applier processes appropriately.

To view the number of currently idle applier processes:

Sql> SELECT COUNT (*) as Idle_applier from v$logstdby_process WHERE TYPE = ' applier ' and status_code = 16166;

Idle_applier

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

0

Return the result is 0, are you busy? This is not true, the number of idle applier processes is 0 does not necessarily mean that the application is very busy, it may be because there is no current need to apply the log, or even did not start the application process.

Description: status_code=16166 indicates that the process is idle because the stats_code=16166 corresponding Status Description column stats to Ora-16116:no work available.

Check the application of the transaction:

Sql> SELECT name,value from v$logstdby_stats WHERE NAME like ' transactions% ';

NAME VALUE

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

Trans 358

Trans 358

If the value of the ready-applied is greater than twice times the number of applier processes, it is necessary for the DBA to consider increasing the number of applier processes, if the applied is about the same size as the ready, or if the difference is smaller than the number of applier processes, Indicates that the number of applier processes is too high, and DBAs need to consider reducing the number of processes appropriately.

If you are sure that the current applier process is busy and you want to increase the applier process, you can do the following:

1 Stop the logical standby end of the SQL application:

sql> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Database altered.

2) Execute the following statement and adjust the number of applier processes to 10:

Sql> EXECUTE Dbms_logstdby. Apply_set (' Apply_servers ', 10);

Pl/sql procedure successfully completed.

3 Restart SQL application:

jssldg> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

4 the number of applier processes currently running is identified:

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

sql> SELECT COUNT (0) from v$logstdby_process WHERE type= ' applier ';

COUNT (0)

----------

10

You can also query through the V$logstdby_stats view, for example:

Sql> SELECT * from v$logstdby_stats WHERE name= ' number of appliers ';

NAME VALUE

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

Numbe 10

2, adjust the number of preparer process

The Prepaper process converts block modifications in the received redo data to Lcrs (Logical change Records). There are generally few opportunities to adjust the number of prepaper processes, usually with only one situation: applier processes are idle, transactions ready many, but there is no idle prepaper process, at which point the DBA may need to add some prepaper processes.

Check the number of idle prepaper processes first:

Sql> SELECT COUNT (*) as Idle_preparer from v$logstdby_process

WHERE TYPE = ' preparer ' and status_code = 16166;

Idle_preparer

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

0

Description: If shown as 0, don't be afraid, it may also be because there is no new redo data to be processed at the moment.

If you do need to adjust the number of prepaper processes, follow these steps.

First stop the SQL application:

sql> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Database altered.

The number of adjustment prepaper processes is 4 (the default is only 1 Prepaper processes):

Sql> EXECUTE Dbms_logstdby. Apply_set (' Prepare_servers ', 4);

Pl/sql procedure successfully completed.

Restart the SQL application:

sql> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

View the number of prepaper processes that are currently started, and query the V$logstdby_stats view, for example:

Sql> SELECT * from v$logstdby_stats WHERE name= ' number of preparers ';

NAME VALUE

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

Numbe 4

3, adjust the memory used by LCR

In LCR, a converted block modified record is saved, which is stored in the SGA.

Query the maximum memory available for the current LCR:

Sql> SELECT * from v$logstdby_stats WHERE name= ' maximum SGA for LCR cache ';

NAME VALUE