Operations Guide

10.5 HotFix 2
- 10.5 HotFix 1
- 10.5
- 10.2 HotFix 1
- 10.2
- 10.1 HotFix 1
- 10.1
- 10.0 HotFix 1
- 10.0

Back Next

Performance

Update Synchronization is inherently expensive because MDM-RE denormalizes the USTs in order to provide very fast search performance. The disadvantage is that updates are slower. Conversely, had MDM-RE not denormalized the data, the searches would be much slower and updates would be faster. This is a tradeoff in the design.

The following sections describe methods to optimize update performance.

Overlap Processing

Run one Synchronizer per IDT so that update processing is overlapped for multiple IDTs in a System (

-e

switch).

IDT/IDX Design

The design of each IDT directly impacts the Synchronizer’s performance. It can be improved by minimizing the use of expensive features where possible:

reduce the amount of denormalization (the number of joins and especially the number of one:many joins).

use Flattening where possible.

avoid non-unique PKs

reduce the size of the IDT and IDX records by ensuring that only those columns required for key generation and matching are sourced from USTs.

reduce the number of keys per IDT record (standard or limited key options)

do not use the Auto-Id feature for synchronized tables

Compressed Key Data

Specify a Compress-Key-Data value for each IDX that minimizes the number of database blocks required to store it. This will improve the performance of searching and updating the IDXs. A poorly selected value for Compress-Key-Data can easily double or triple the amount of I/O required.

Use the

histkg

utility to analyze the report file created by the Table Loader. For each IDX, determine the

segLen

value that minimizes

DB-blocks

. This usually occurs when

segs/key

is a multiple of a whole number. The best value occurs when

segs/key

is near 1.0.

Refer to the Compressed Key Data
section in the

DESIGN GUIDE

for details.

Network Issues

Reduce network overhead by running the Synchronizer and the Rulebase Server on the same machine as the database server. If this is not possible, tune your network parameters to optimize throughput.

SQL*Net / Net8

The SDU parameter controls the network packet size. The default value of 2048 is slightly too small to hold a complete Transaction-Table record (2178 bytes). This causes packet fragmentation as the Server must send two packets to the Client (Update Synchronizer) to return each transaction.

Increase the SDU to at least 2200. A value of 3000 is recommended for Ethernet networks, as it is a multiple of Ethernet frame size (1500 bytes).

Change

$ORACLE_HOME/network/admin/listener.ora

(on the server) to include the

SDU

parameter and stop/start the listener using the

lsnrctl

utility. For example:


SID_LIST_LISTENER =
(SID_LIST =
	(SID_DESC =
			(SDU=3000)
			(GLOBAL_DBNAME= ssa16.)
			(ORACLE_HOME= /home/oracle/u01/app/oracle/product/8.0.5)
			(SID_NAME = dba)

You must also change the client side configuration file to specify a matching SDU, as the SDU is negotiated down to the smallest value when the client connects to the server. Change

$ORACLE_HOME/network/admin/tnsnames.ora

to add the

SDU

parm. For example:


ssa16 =
(DESCRIPTION =
		(SDU=3000)
		(ADDRESS = (PROTOCOL= TCP)(Host= ssa16)(Port= 1521))
		(CONNECT_DATA = (SID = dba))
)

However on fast (low traffic) networks, this will only provide a minor performance boost.

A major improvement comes from specifying the TCP parameter NoDelay (assuming that TCP/IP is the protocol being used). This tells TCP to flush buffers without waiting for them to fill. Modify (or create)

$ORACLE_HOME/network/admin/protocol.ora

and add a line to it that specifies,

tcp.nodelay=yes

Optimizer Statistics

Ensure that DBMS optimizer statistics are up-to-date. This is especially important if a batch job has added many new transactions to the Synchronizer’s Transaction Table. Use the

SQL ANALYZE

command to update the Optimizer’s statistics, or use the Synchronizer’s

-o

switch to regularly update statistics automatically.

analyze table ids_upd_sync_txn estimate statistics

The USTs and IDT/IDXs should also be analyzed regularly. The Table loader will automatically analyze the IDTs and IDXs after they have been loaded.

Database Table Maintenance

You might want to periodically run the

ANALYZE TABLE/INDEX

queries to improve the database performance.

You can run the

ANALYZE

UPDATE STATISTICS

, and

RUNSTATS

queries as required.

Based on your database, you can run the following queries after connecting to your database:

IBM Db2.

RUNSTATS ON TABLE <Object Name>

Oracle.

ANALYZE <Object Type> <Object Name> ESTIMATE STATISTICS

MS SQL.

ANALYZE LOCAL TABLE <Table Name>

UPDATE STATISTICS <Table Name>

Commit Rate

An appropriate commit rate needs to be selected by tuning.

In general, a high commit rate will provide better transaction throughput. However, too high a rate may cause the database to run out of rollback space in a multi-user update environment, and updated records won’t be visible to searches for long periods. A database failure that interrupts Synchronization processing will mean more work will be repeated when the Synchronizer is restarted.

A very low commit rate will cause frequent database I/O that slows down the Synchronization process.

The commit rate must tend toward a low value when multiple Synchronizers are running simultaneously against the same database. High commit rates will create contention for the table that allocates the unique record numbers for each IDT, causing lots of database I/O to maintain "read consistency".

Flat-File Synchronization

The two-phase commit table (

IDS_2PC

) is used to record the file-names of the files that have been applied. File-names are not removed from this table, so that if an input-file is accidentally reused, the situation will be recognized and the transactions will be ignored.

The consequence of this is that the table will grow at the rate of one row per input-file processed. The table is not normally indexed in order to optimize update performance when very few rows are present (as is the case for user-source synchronization). As the number of rows grows, performance will slowly degrade. To avoid this problem, create an index on the table:

CREATE INDEX IDS_2PC_I ON IDS_2PC (ID);
ANALYZE TABLE IDS_2PC ESTIMATE STATISTICS;

KEY DATA Definition

Configure the

KEY-DATA

parameter to optimize the search and Update Synchronizer performances. When you use the

FULL-KEY-DATA

parameter, the data in the IDT table gets compressed into multiple IDX records. The search performance improves while reducing the performance of the Update Synchronizer. The Update Synchronizer consumes more time to update multiple IDX records.

If you use the

KEY-DATA

parameter, the number of IDX records reduce considerably because of the reduction in the amount of data stored in the IDX record. Thus, the performance of the Update Synchronizer improves but reduces the search performance.

To enhance search performance without compromising the performance of the Update Synchronizer, configure the search definitions of the

KEY-DATA

parameter and

Matching-Fields

option. In the

KEY-DATA

parameter, include only the fields that you define in the

Matching-Fields

option.

When you perform a search, the system verifies whether the fields defined in the

Matching-Fields

option and fields included in the

KEY-DATA

parameter match. If the fields match, the system uses only the fields included in the IDX records to match and search. Thus, the search performance improves without compromising the performance of the Update Synchronizer.

You can specify up to five fields in the

KEY-DATA

parameter.

In the following example, the

KEY-DATA

parameter includes only the fields defined in the

Matching-Fields

option:

idx-definition

*=============

NAME=                           IDX_LIC_NBR

…

OPTIONS=             --Full-Key-Data,

                               No-Null-Field,

                               No-Null-Key

KEY-DATA=            LIC_NBR,LIC_STATE_CD

... 

search-definition

*================

NAME=                          Search_LIC_Number

IDX=                           IDX_LIC_NBR

…

                               Matching-Fields("LIC_NBR:Attribute1,LIC_STATE_CD:Code")

Environment Variable

You can use the

SSA_SEARCH_RETURN_KEY_DATA_ONLY

environment variable to specify the system to read only the fields present in the IDX records. To confine the system to read only the fields within the IDX records, set the environment variable

SSA_SEARCH_RETURN_KEY_DATA_ONLY=1

before you start the Search server.

Update Synchronizer

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal