oracle9204(9i)_dg(data guard)__Tuning Logical Standby Databases

Take the following actions to increase system performance:

• On the primary database, if a table does not have a primary key or a unique index, then create a primary key RELY constraint. On the logical standby database, create an index on the columns that make up the primary key. The following query generates a list of tables with no index information that can be used by a logical standby database to apply to uniquely identify rows. By creating an index on the following tables, performance can be improved significantly.

  SQL> SELECT OWNER, TABLE_NAME FROM DBA_TABLES 
    2> WHERE OWNER NOT IN('SYS','SYSTEM','OUTLN','DBSNMP') 
    3> MINUS
    3> SELECT DISTINCT TABLE_OWNER, TABLE_NAME FROM DBA_INDEXES 
    4> WHERE INDEX_TYPE NOT LIKE ('FUNCTION-BASED%')
    5> MINUS
    6> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED;
  
  

  The following example shows the creation of an index for the table EMP. This should be done for all the tables returned by the previous query:

  SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON;
  SQL> CREATE INDEX EMPI ON EMP (EMPNO);
  SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF;
  
  

  See Also: Section 4.1.2 and for more information about RELY constraints

• Gather statistics for the cost-based optimizer (CBO) periodically on the logical standby database for objects, where the statistics become stale over time because of changing data volumes or changes in column values. New statistics should be gathered after the data or structure of a schema object is modified in ways that make the previous statistics inaccurate. For example, after inserting or deleting a significant number of rows into a table, collect new statistics on the number of rows.

  Statistics should be gathered on the standby database because DML/DDL operations on the primary are executed as a function of the workload. While the standby database is logically equivalent to the primary, SQL apply operations might execute the workload in a different way. This is why using the DBMS_STATS package on the logical standby database and the V$SYSSTAT view can be useful in determining which tables are consuming the most resources and table scan operations.

  See Also: Oracle9i Supplied PL/SQL Packages and Types Reference

• Adjust the transaction consistency.

  Use the TRANSACTION_CONSISTENCY parameter of the DBMS_LOGSTDBY.APPLY_SET procedure to control how transactions are applied to the logical standby database. The default setting is FULL, which applies transactions to the logical standby database in the same order in which they were committed on the primary database.

  Specify one of the following values:

  • FULL

    Transactions are applied to the logical standby database in the exact order in which they were committed on the primary database. This option results in the lowest performance. This is the default parameter setting.

  • READ_ONLY

    Transactions are applied out of order from how they were committed on the primary database. The READ_ONLY option provides better performance than the FULL value, and SQL SELECT statements return read-consistent results. This is particularly beneficial when you are using the logical standby database to generate reports.

  • NONE

    Transactions are applied out of order from how they were committed on the primary database, and no attempt is made to provide read-consistent results. This results in the best performance of the three values. If applications that are reading the logical standby database make no assumptions about transaction order, this option works well.

    Note: The READ_ONLY and NONE options should only be used when ALTER DATABASE GUARD ALL is set.

• Adjust the maximum number of parallel execution processes.

  Use the PARALLEL_MAX_SERVERS initialization parameter to adjust the maximum number of parallel execution processes and parallel recovery processes for an instance. The default value for this parameter is derived from the values of the CPU_COUNT, PARALLEL_AUTOMATIC_TUNING, and PARALLEL_ADAPTIVE_MULTI_USER initialization parameters. This parameter must not be set to a value less than 5 on a logical standby database.

  You can use the MAX_SERVERS parameter of the DBMS_LOGSTDBY.APPLY_SET procedure to limit the number of parallel servers used by log apply services. The default value of this parameter is set equal to the value of the PARALLEL_MAX_SERVERS initialization parameter. If you set this parameter explicitly, do not set it to a value less than 5 or greater than the value of the PARALLEL_MAX_SERVERS initialization parameter.

  Increasing the number of parallel execution processes and parallel recovery processes for an instance can speed up execution and recovery operations, but this improvement must be balanced against the consumption of additional system resources by the processes.

• Control memory usage on the logical standby database.

  You can use the MAX_SGA parameter of the DBMS_LOGSTDBY.APPLY_SET procedure to set the maximum amount of shared pool space used by log apply services for redo cache. By default, log apply services will use up to one quarter of the shared pool. Generally speaking, increasing the size of the shared pool or the amount of shared pool space used by log apply services will improve the performance of a logical standby database.