Operations Guide

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

Back Next

Performance

Update Synchronization is inherently expensive because IIR denormalizes the USTs in order to provide very fast search performance. The disadvantage is that updates are slower. Conversely, had IIR 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.

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;

Rename Saved Search

Table of Contents

Operations Guide

Operations Guide

Performance

Performance

Overlap Processing

IDT/IDX Design

Compressed Key Data

Network Issues

SQL*Net / Net8

Optimizer Statistics

Commit Rate

Flat-File Synchronization