Table of Contents

Search

  1. Preface
  2. Introduction
  3. Servers
  4. Console Client
  5. Search Clients
  6. Table Loader
  7. Update Synchronizer
  8. Globalization
  9. Siebel Connector
  10. Web Services
  11. ASM Workbench
  12. System Backup and Restore
  13. Batch Utilities

Performance

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;

0 COMMENTS

We’d like to hear from you!