Optimizing and improving performance

The following settings and configurations maybe optimized in high volume transactions to achieve higher performance.

Variable

Description

STATUS_BEAT_LCR

This determines how often a log message is written showing number of processed LCRs (in LCR count) (use 0 to disable). As of Dbvisit Replicate 2.3.14 the default is 10,000. For high volume configurations consider increasing to higher number.

OCI_COMMIT_MODE = NOWAIT BATCH

This may improve APPLY processing speed. Only set for non RAC target systems. See Configuration Variable Reference for more information.

_ENABLE_5_19_PARSE = NO

Disables processing of auditing information. This information is used by CDC/Audit function or by querying the DBRSAPPLY_PKG package on the target database (e.g. in triggers).

This information includes source sid, serial#, transaction id, os user, os machine, login name. If this information is not required, then this can be disabled.

Please Internal Variables for more information

MINE

Generally reasons for slow down of MINE are:

Very large transactions that cause MINE to spill to disk because the transaction is too large to keep in memory (see messages "SPILL_TO_DISK" in the log file). Look at large transactions and increase memory of MINE.
Slow disks for the PLOGS. Ie MINE cannot write fast enough to the PLOG files. Consider moving the PLOGS to fast disks separate from the Oracle redo and archive logs.
Many "Create Table as Selects" - CTAS. These are slow operations for MINE because Oracle does not write the whole table to the redo and so MINE has to extract the whole table with a normal select statement to replicate the data. If these are used as temporary tables, it is recommend to filter out CTAS
Exotic datatypes such as LOBS etc. These require extra processing to replicate. If these data types are not needed, it is recommend to exclude these columns from the replication.

Improving performance

Following settings/tips can be applied to improve Replicate performance. Please test them first on a test system before implementing in a production environment.

Process	Type	Description	Setting
MINE	No CDC/AUDIT	Disable maintaining of session information. This information is needed only if CDC/Audit is in use to populate auxiliary fields such as source user login, program, module, action and so on. If _MINE_PRINT_OPEN_TRANSACTIONS is used, this feature populates the sid/serial info.	_ENABLE_5_19_PARSE = NO
	No trace files	Disable keeping of past debug messages. So the trace files will not contain this information. This is only recommended in a tested environment.	_DISABLE_DBGPRINTF = YES
	No redo log checksums	Disable checking of redolog checksums. This is recommended only if FETCHER is not used on a local filesystem, no ASM if the replication has been running fine for some time	_MINE_CHECK_ORACHECKSUM = NO
	No error logging to database	Do not log errors to DBRSCOMMON_ERRORLOG. Note that this decreases the table size and not performance.	_LOG_ERRORS_TO_DATABASE = NO
	Less frequent updates of table names in list progress	This setting only updates table names in list progress when a log switch occurs. Note that new tables are added on the fly even with this setting. The setting can confuse if there is a lot of DDL, table renames etc. Then list progress may show stale information until next log switch happens.	_MINE_UPDATE_TABLIST_ON_SWITCH_ONLY = YES
	No compression of kept PLOGs/redologs	Disables compression of obsolete PLOGs (or redologs - that's with FETCHER only). This means less logs will be available for support if needed.	DELETE_OBSOLETE_PLOGS_GZIP = NO DELETE_OBSOLETE_RLOGS_GZIP = NO
	Move PLOGS to fast disks	Consider moving the PLOGS to fast disks separate from the Oracle redo and archive logs.
APPLY	No trace files	See MINE
	No error logging to database	See MINE
	Commit Nowait	Change default commit settings. Use only if target is not RAC, otherwise data loss can occur.	OCI_COMMIT_MODE = NOWAIT BATCH
	No audit info, no triggers	if DBRSAPPLY_PKG is used at all, APPLY can stop populating it's variables. It's used for: CDC/Audit auxiliary columns like source login, sid, serial, program, module, action, ... To determine whether current session is a replicate session (used in triggers to disable double-firing)	_SET_APPPLY_PKG_AUDIT NO (on login) _SET_APPPLY_PKG_CONFLICT NO (on conflict) _SET_APPPLY_PKG_ALWAYS NO (on each change; NO is the default)
	No compression of kept PLOGs	Disables compression of obsolete PLOGs. This means less PLOGs will be available for support if needed.	DELETE_OBSOLETE_PLOGS_GZIP = NO
	Disabling Deletion of obsolete PLOGs and RLOGS	Disabling deletion of Obsolete PLOGS (or Redologs with FETCHER only) . This means the replication does not have to go through the plog maintenance ,which can speed up the replication. If this parameter is set to NO, The PLOGS and RLOGS maintenance has to be done manually or by using a separate method	set ._DELETE_OBSOLETE_PLOGS_ENABLED = NO set ._DELETE_OBSOLETE_RLOGS_ENABLED = NO

Examples:

This shows an example of how to change the settings to further improve the performance:

Please add these settings to your MINE ddc file on the source server after the line ########## Any manual settings should go below this line ########## as instructed for them to take effect. Note a restart is also needed to take effect.

memory_set _ENABLE_5_19_PARSE NO -- this disables auxiliary data like action, module, osprog, osproc etc. available on apply with CDC/Audit or using the DBRSAPPLY_PKG package
memory_set _DISABLE_DBGPRINTF YES -- this disables most of the contents of trace files
memory_set _MINE_CHECK_ORACHECKSUM NO -- disable checking of redo log checksums
memory_set _LOG_ERRORS_TO_DATABASE NO -- disables logging of errors to DBRSCOMMON_ERRORLOG table
memory_set _MINE_UPDATE_TABLIST_ON_SWITCH_ONLY YES -- updates table names in list progress only on log switch; so if you rename a table, you won't see the change until a log switch

Please add these settings to your APPLY ddc file on the target server after the line ########## Any manual settings should go below this line ########## as instructed for them to take effect. Note a restart is also needed to take effect.

memory_set _DISABLE_DBGPRINTF YES 
memory_set _LOG_ERRORS_TO_DATABASE NO

Further performance improvements

Performance profiling statistics can be collected by Dbvisit Replicate and these can be sent to Dbvisit for further analysis to determine if further optimisations can be made for the specific environment. Please see Dbvisit Replicate Profile Performance Statistics for more information on how to collect profiling statistics.