2
Master Replication Concepts and Architecture

HQ.MYCOMPANY.COM = 
'(DESCRIPTION=
   (ADDRESS=(PROTOCOL=TCP)(HOST=server1)
(PORT=1521))
   (CONNECT_DATA=(SID=hqdb)(SERVER=DEDICATED)))'

Connection qualifiers allow several database links pointing to the same remote database to establish connections using different paths. For example, a database named ny can have two public database links named < code>ny.world that connect to the remote database using different paths.

• ny.world@ethernet, a link that connects to ny using an ethernet link
• ny.world@modem, another link that connects to ny using a modem link

For the purposes of replication, connection qualifiers can also enable you to more closely cont rol the propagation characteristics for multiple master groups. Consider, if each master site contains three separate master groups a nd you are not using connection qualifiers, then the scheduling characteristics for the propagation of the deferred transaction queue is the same for all master groups. This may be costly if one master group propagates deferred transactions once an hour while the ot her two master groups propagate deferred transactions once a day.

Associating a connection qu alifier with a master group gives you the ability to define different scheduling characteristics for the propagation of the deferred transaction queue on a master group level versus on a database level as previously described.

When you create a new master group, you can indicate that you want to use a connection qualifier for all sch eduled links that correspond to the group. However, when you use connection qualifiers for a master group, Oracle propagates informat ion only after you have created database links with connection qualifiers at every master site. After a master group is created, you cannot remove, add, or change the connection qualifier for the group.

Replication Objects

• Tables
• Indexes
• Packages and Package Bodies
• Procedures and Functions
• User-Defined Typ es and Type Bodies
• Triggers
• Views, Object Views, and Synonyms
• Indextypes
• User-Defined Operators

Tables

In most cases, replicated tables are the foundation of your replication environment. After a table is selected for replication and has had replication support generated, it is monitored by internal triggers to detect any DML applied to it.

When yo u replicate a table, you have the option of replicating the table structure and table data to the remote data sites or just the table structure. Additionally, if a table of the same name and structure already exists at the target replication site, then you have the option of using the existing object in your replication environment.

Indexes

Any index that is used to enforce a constraint in a ta ble is automatically created at the remote destination sites when a table is selected for replication and created at the remote site. Any index that is used for performance reasons, however, must be explicitly selected for replication to be created at the other mast er sites participating in the replication environment. When an index is replicated to other sites, it operates as if the index was cr eated locally. You do not need to generate replication support for indexes.

Oracle supports t he replication of domain indexes. You can replicate the definition of storage tables for domain indexes, but you cannot replicate the storage tables themselves because they typically contain ROWID information.

Packages and Package Bodies

Selecting packages and package bodies for replication and generating the needed replication support gives you the ability to do proce dural replication. Procedural replication can offer performance advantages for large, batch-oriented operations on large numbers of r ows that can be run serially within a replication environment.

All parameters for a procedure with replication support must be IN parameters. OUT and IN/OUT modes are not supported. The f ollowing datatypes are supported for these parameters:

• VAR CHAR2
• NVARCHAR2
• NUMBER
• DATE
• RAW
• ROWID
• CHAR
• NCHAR
• Binary LOB (BLOB)
• Character LOB (CLOB)
• National character LOB (NCLOB)
• User-defined datatypes

A replicated procedure must be declared in a package. Standalone procedures cannot have replication support.

Procedures and Functions

Procedures and functions not declared a s part of a package cannot have replication support. Though you cannot create a procedural replication environment with standalone pr ocedures and functions, you can still use replication to distribute these standalone procedures and functions to the sites in your re plication environment. When the standalone procedure or function is created at the remote site using replication, the created object does not have replication support and operates as though the object was created locally.

To replicate schema objects with user-defined types, the user-defined types must exist on al l replication sites and be exactly the same at all replication sites.

Triggers

To make sure that any application or database logic is present at each master site, you can select t riggers for replication. An important example of replicating a trigger is replicating a trigger that automatically inserts a timestam p into a table when any DML is applied to the table.

To avoid refiring of the trigger, it is important to insert an API call into the trigger to detect if the trigger is being fired through a local or remote call. This is to a void the situation where the trigger updates a row that causes the trigger to fire again.

Not ice line 5 in the following code example:

1) CREATE OR REPLACE TRIGGER hr.insert_time
2)    BEFORE
3)       INSERT OR UPDATE ON hr.employees FOR EACH ROW
4)    BEGIN
5)       IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
6)          :NEW.TIMESTAMP := SYSDATE;
7)       END IF;
8)    END;
9) /

If the DBMS_REPUTIL.FROM_REMOTE function determines that the insert or update was locally initiated, then the defined action (that is, assign timestamp) occurs. If this function determines that the insert or update is from a remote s ite, then the timestamp value is not assigned.

Views, Object Views, and Synonyms

When you replicate a view, an object view or a synonym, you are simply using replication to distribute these objects to the oth er master sites that are involved in the replication environment. After the object is replicated to the other sites, it operates as i f the object was created locally. No internal trigger or package monitors the object to capture any changes. Because it is a replicat ed object, though, you can still drop or modify it using either the Replication Management tool or the replication management API.

Indextypes

SELECT SYS_GUID() OID FROM DUAL;

4595EF13AB785E73E03400400B40F58B

Internal Triggers

Oracle uses internal triggers to capture and store information about updates to replicated data. Internal triggers build remote pr ocedure calls (RPCs) to reproduce data changes made to the local site at remote replication sites. These deferred RPCs are stored in the deferred transaction queue and are propagated to the other master sites participating in the replication environment. The interna l triggers supporting data replication are essentially components within the Oracle server executable. Therefore, Oracle can capture and store updates to replicated data very quickly with minimal use of system resources.

Oracle forwards data replication information by propagating (that is, sending and executing) the RPCs that are generated by the internal triggers described previously. These RPCs are stored in the deferred transaction queue. In addition to containing the execution command for the internal procedure at the destination site, each RPC also contains the data to be replicate d to the target site. Oracle uses distributed transaction protocols to protect global database integrity automatically and ensure dat a survivability.

Internal Procedure

When a deferred RPC created by an internal trig ger is propagated to the other master sites participating in a replication environment, an internal procedure at the destination site is used to apply the deferred RPC at the remote site. These internal procedures are activated automatically when you generate replic ation support for a table. These internal procedures are executed based on the RPCs that are received from the deferred transaction q ueue of the originating site.

Queues

The following queues manage the transactions t hat are generated by Advanced Replication:

Deferred Transaction Queue

This queue st ores the transactions (for example, DML) that are bound for another destination in the master group. Oracle stores RPCs produced by t he internal triggers in the deferred transaction queue of a site for later propagation. Oracle also records information about initiat ing transactions so that all RPCs from a transaction can be propagated and applied remotely as a transaction. Oracle's replication fa cility implements the deferred transaction queue using Oracle's advanced queuing mechanism.

Error Queue

The error queue stores information about deferred tr ansactions that could not be applied successfully at the local site. The error queue does not display information about errors at oth er master sites in the replication environment. When the error condition has been resolved, you can either reexecute the transaction or delete the transaction from the error queue.

Job Queue

Oracle manages the propag ation process using Oracle's job queue mechanism and deferred tran sactions. Each server has a local job queue. A server's job queue is a database table storing information about local jobs s uch as the PL/SQL call to execute for a job, when to run a job, and so on. Typical jobs in a replication environment include jobs to push deferred transactions to remote master sites, jobs to purge applied transactions from the deferred transaction queue, and jobs t o refresh materialized view refresh groups.

Administrative Mechanisms

Several mecha nisms are required to handle the administrative tasks that are often performed to support a replication environment. These mechanisms allow you to turn on and off a replication environment, as well as monitor the administrative tasks that are generated when you buil d or modify a replication environment.

Replication Modes of Operation

There are thr ee modes of operation for a replication environment.

Normal

A replication environme nt in the normal mode allows replication to occur. The replication environment is "running" in this mode. Any transaction against a r eplicated object is allowed and is appropriately propagated.

Quiescing

Quiescing is the mode that transfers a replication environment from the normal mode to the quiesced mode. While a replication environment is quie scing, the user is no longer able to execute a transaction against a replicated object, but any existing deferred transactions are pr opagated. Queries against a quiescing table are allowed. When all deferred transactions have been successfully propagated to their re spective destinations, the replication environment proceeds to the quiesced mode.

Quiesced

A quiesced replication environment can be considered disabled for normal replication use and is used primarily for administra tive purposes (such as adding and removing replicated objects). Replication is "stopped" in this mode. A quiesced state prevents user s from executing any transactions against a replicated object in the quiesced master group unless they turn off replication, which ca n result in divergent data after replication is resumed. Transactions include DML against a replicated table or the execution of a wr apper for a replicated procedure. If master tables are quiesced, then materialized views based on those master tables cannot propagat e their changes to the target master tables, but local changes to the materialized view can continue.

A replication environment is quiesced on a master group level. All master sites participating in the master group are affect ed. When a master group reaches a quiesced state, you can be certain that any transactions in the deferred transaction queue have bee n successfully propagated to the other master sites or put into the error queue. Users can still query tables that belong to a quiesc ed master group.

Quiescing one master group does not affect other master groups. A master gro up in normal mode can continue to process updates while other master groups are quiesced.

Replication Mode Control

Though there are three modes of replication operation, there are only two mechanisms to control these modes (recall that the quiescing mode is a transition from a normal to quiesced mode).

The suspend mechanism can only be ex ecuted when the replication environment is in normal mode. Execute suspend when you need to modify the replication environment.

Resume

The resume mechanism transfers a master group from the quiesced replication mode to the normal mode. If you have been performing administrative work on your replication environment (for example, adding replicated o bjects), then you should verify that the administrative request queue (DBA_REPCATLOG) is empty before executing the resu me mechanism.

Administrative Requests

To configure and manage a replication environ ment, each participating server uses Oracle's replication management API. A server's replication management API is a set of PL/SQL pa ckages encapsulating procedures and functions administrators can use to configure Oracle's replication features. The Replication Mana gement tool also uses the procedures and functions of each site's replication management API to perform work.

An administrative request is a call to a procedure or function in Oracle's rep lication management API. For example, when you use the Replication Management tool to create a new master group, the tool completes t he task by making a call to the DBMS_REPCAT.CREATE_MASTER_REPGROUP procedure. Some administrative requests generate addi tional replication management API calls to complete the request.

The Administrative Request Mechanisms

When you use the Replication Management tool or make a call to a procedure in the DBMS_REPCAT packag e to administer a replication system, Oracle uses its internal mechanisms to broadcast the request synchronously. If a synchronous br oadcast fails for any reason, then Oracle returns an error message and rolls back the encompassing transaction.

When ever you use the Replication Management tool to create an administrative request for a replication group, Oracle automatically insert s a job into the local job queue, if one does not already exist for the group. This job periodically executes the DBMS_REPCAT.D O_DEFERRED_REPCAT_ADMIN procedure. Whenever you synchronously broadcast a request, Oracle attempts to start this job immediate ly in order to apply the replicated changes at each master site.

Assuming that Oracle does no t encounter any errors, DO_DEFERRED_REPCAT_ ADMIN is run whenever a background process is available to execute the job. Oracle automatically determines how often the background process wakes up. You may experience a delay if you do not have enough backg round processes available to execute the outstanding jobs.

For each call of DO_DEFERRED_REPCAT_ADMIN at a master site, the site checks the DBA_REPCATLOG view to see if there a re any requests that need to be performed. When one or more administrative requests are present, Oracle applies the request and updat es any local views as appropriate. This event can occur asynchronously at each master site.

< code>DO_DEFERRED_REPCAT_ADMIN executes the local administrative requests in the proper order. When DO_DEFERRED_REPCAT_AD MIN is executed at a master that is not the master definition site, it does as much as possible. Some asynchronous activities, such as populating a replicated table, require communication with the master definition site. If this communication is not possible, then DO_DEFERRED_REPCAT_ADMIN stops executing administrative requests to avoid executing requests out of order. Some co mmunication with the master definition site, such as the final step of updating or deleting an administrative request at the master d efinition site, can be deferred and will not prevent DO_DEFERRED_REPCAT_ADMIN from executing additional requests.

The success or failure of an administrative request at each master site is noted in the DBA_R EPCATLOG view at each site. For each master group, the Replication Management tool displays the corresponding status of each a dministrative request. Ultimately, each master site propagates the status of its administrative requests to the master definition sit e. If a request completes successfully at a master site, then Oracle removes the callback for the site from the DBA_REPCATLOG view at the master definition site.

If a request completes successfully at all sites, t hen all entries in the DBA_REPCATLOG view at all sites, including the master definition site, are removed. If a request at a non master definition site fails, then Oracle removes the request at the master site and updates the corresponding AWAIT_C ALLBACK request at the master definition site with ERROR status and the reason for the failure.

By synchronously broadcasting the change, Oracle ensures that all sites are aware of the change, and thus are capable of remaining synchronized. By allowing the change to be applied at the site at a future point in time, Oracle provides you wi th the flexibility to choose the most appropriate time to apply changes at a site.

If an obje ct requires replication support, then you must regenerate replication support after altering the object. Oracle then activates the in ternal triggers and regenerates the packages to support replication of the altered object at all master sites.

Note: Although the DDL must be successfully applied at the master definition site in order for these procedures to comp lete without error, this does not guarantee that the DDL is successfully applied at each master site. The Replication Management tool displays the status of all administrative requests. Additionally, the DBA_REPCATLOG view contains interim status and an y asynchronous error messages generated by the request.

Any mate rialized view sites that are affected by a DDL change are updated the next time you perform a refresh of the materialized view site. While all master sites can communicate with one another, materialized view sites can communicate only with their associated master si te.

If you must alter the shape of a materialized view as the result of a change to its maste r, then you must drop and re-create the materialized view.

Administrative Request Queue

Often referred to as the administrative request queue, the DBA_REPCATLOG view stores administrative requests that m anage and modify your replication environment. Some DBMS_REPCAT procedures that are executed are listed in the administr ative request queue. For example, if you wanted to add an additional replicated table to an existing master group, then you would see a request naming the DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure.

You can vie w the administrative request queue by querying the DBA_REPCATLOG view or view the Administrative Requests dialog box in the Replication Management tool.

Each request has a status that displays the state of the req uest. Here are the possible states:

• READY: The READY state indicates that the request is ready to be executed. If you monitor the administrative request q ueue and a request remains in the READY state for a long time, then a request in front of the ready request may be waiti ng for a callback. Typically, administrative requests in the READY state are waiting for a job to execute them. You can execute them manually by using the DO_DEFERRED_REPCAT_ADMIN procedure in the DBMS_REPCAT package.
• AWAIT_CALLBACK: The AWAIT_CALLBACK state indic ates that the request is waiting for a request to be executed at another site and is waiting for confirmation of the request executio n. After the request receives the callback, the request is either removed or has its status changed. The request is removed from the queue if it was applied successfully, or its status is changed to ERROR if it failed. This state is only possible at the master definition site.
• ERROR: If a request canno t be successfully executed, then it is placed in the ERROR state. The error number appears in the ERRNUM co lumn and the error message appears in the MESSAGE column of the administrative request queue (it is in the Error column when using the Replication Management tool).

  Note: If a request is in the ERROR state, then resolve the error c ondition as described by the error number and resubmit the request.

• DO_CALLBACK: If a request at a master site is in the DO_CALLBACK state, then it means that the master site must inform the master definition site about the su ccess or failure of the request. This state is only possible at a master site that is not the master definition site.

The administrative request queue of each master site lists only the administrative requests to be perf ormed at that master site. The master definition site for a master group, however, lists administrative requests to be performed at e ach of the master sites. The administrative request queue at the master definition site lets the DBA monitor administrative requests of all the master sites in the replication environment.

Note: When the restricted session is enabled by the SQL statement ALTER SYSTEM with the ENABLE RESTRICTED SESSION clause, administrative requests are not executed. When the restr icted session is disabled, they are executed.

Organizational Mechanisms

Oracle uses several organizational mechanisms to organize the previously described master site and administrative m echanisms to create discrete replication groups. Most notable of these organizational mechanisms is the master group. An additional o rganization mechanism helps to group columns that are used to resolve conflicts for a replicated table.

Master Group

In a replication environment, Oracle manages replication objects using replication groups. A replication group is a collection of replication objects that are always updated in a transactionally consistent manner.

By organizing related database objects within a replication group, it is easier to administer many objects together. Typically, you create and use a replication group to organize the schema objects necessar y to support a particular database application. That is not to say that replication groups and schemas must correspond with one anoth er. Objects in a replication group can originate from several database schemas, and a schema can contain objects that are members of different replication groups. The restriction is that a replication object can be a member of only one group.

In a multimaster replication environment, the replication groups are called master grou ps. Corresponding master groups at different sites must contain the same set of replication objects (see "Replication Objects"). Figure 2-4 illustrates t hat master group hr_mg contains an exact replica of the replicated objects at each master site.

Figure 2-4 Master Group hr_mg Contains Same Replication Objects at All Sites

Text description of the illustration repln073.gif

The master group org anization at the master site plays an integral role in the organization of replication objects at a materialized view site.

See Also: < p class="NB">"Organizational Mechanisms" for more information on th e organizational mechanisms at a materialized view site

Additionally, Figure 2-5 illustrates that each site may contain multiple replication groups, though each gro up must contain exactly the same set of objects at each master site.

Figure 2-5 Master Groups Are Identical at Each Master Site

Text description of the illustration re pln078.gif

Column Groups

Column groups provide the organizational mechanism to group all columns that are involved in a conflict resolution routine. If a conflict occurs in one of the columns of the group, then t he remainder of the group's columns may be used to resolve the conflict. For example, if a column group for a table contains a min_price, list_price, cost_price, and timestamp field and a conflict arises for the list_price field, then the timestamp field may be used to resolve the conflict, assuming that a timestamp con flict resolution routine has been used.

Initially, you might think that you should put all co lumns in the table into a single column group. Although this makes setup and administration easier, it may decrease the performance o f your replicated table and may increase the potential for data conflicts. As you will learn in the "Per formance Mechanisms", if a conflict occurs in one column group of a table, then the minimum comm unication feature does not send data from other column groups in the table. Therefore, placing all columns into a single column group may negate the advantages of the minimum communication feature, unless you use the SEND_OLD_VALUES and COMPARE_OL D_VALUES procedures in the DBMS_REPCAT package.

See Also: Chapter 5 , "Conflict Resolution Concepts and Architecture" for more information about column groups

Propagation Mechanism

Propagation is the essence of replication because it is the mechanism that sends or distributes any actions to all other master sites in the replication environment.

Propagation Types

As the internal trigger captures any DML applied to a replicated table, the DML must be propagated (or sent) to the other master sites in the replication environment. Internal triggers are described in the section "Internal Triggers".

Advanced Replication supports both asynchronous and synchronous replication.

Asynchronous

< p class="BP">Typical replication configurations use asynchronous data replication. Asynchronous data replication occurs when an application updates a local replica of a table, stores replication information in a local queue, and then forwards the replication information to other replication sites at a later time. Consequently, asynchronous data replication is also called store-and-forward data replication.

As Figure 2-6 shows, Oracle uses its internal triggers, deferred transactions, deferred transaction q ueues, and job queues to propagate data-level changes asynchronously among master sites in a replication environment, as well as from an updatable materialized view to its master table.

Figure 2-6 Asynchronous Data Replication Mechanisms

Text description of the illustration repln032.gif

Synchronous

Oracle also supports synchronous data propagation for applications with specia l requirements. Synchronous data propagation occurs when an application updates a local replica of a table, and within the same transaction also updates at least one other replica of the same table. Consequently, synchronous dat a replication is also called real-time data replication. Use synchronous replication only when applications require that replicated sites remain continuously synchronized.

Figure 2-7 Synchronous Data Replication Mechanisms

Text description of the illustration r epln039.gif

As Figure 2-7 shows, Oracle uses the sa me internal triggers to generate remote procedure calls (RPCs) that asynchronously replicate data-level changes to other replication sites to support synchronous, row-level data replication. However, Oracle does not defer the execution of such RPCs. Instead, data re plication RPCs execute within the boundary of the same transaction that modifies the local replica. Consequently, a data-level change must be possible at all synchronously linked sites that manage a replicated table; otherwise, a transaction rollback occurs.

Synchronous Data Pro pagation

As shown in Figure 2-8, whenever an application makes a DML change to a local replicated table and the replication group is using synchronous row-level repl ication, the change is synchronously propagated to the other master sites in the replication environment using internal triggers. Whe n the application applies a local change, the internal triggers issue calls to generated procedures at the remote master sites in the security context of the replication propagator. Oracle ensures that all distributed transactions either comm it or rollback in the event of a failure.

See Also: Oracle Database Administrator's Guide for more information about distributed transactions

Figure 2-8 Propagating Changes Using Synchronous Row-Level Replication

Text description of the illustration repln030.gif

Restrictions

Because of the locking mechanism used by synchronous replication, deadlocks can occur wh en the same row is updated at two different sites at the same time. When an application performs a synchronous update to a replicated table, Oracle first locks the local row and then uses an AFTER ROW trigger to lock the corresponding remot e row. Oracle releases the locks when the transaction commits at each site.

Note: A replication s ystem that uses real-time propagation of replication data is highly dependent on system and network availability because it can funct ion only when all sites in the system are concurrently available.

Destination of Synchronously Replicated Transaction s

The necessary remote procedure calls to support synchronous replicat ion are included in the internal trigger for each object. When you generate replication support for a replicated object, Oracle activ ates the triggers at all master sites to add the necessary remote procedure calls for the new site. Conversely, when you remove a mas ter site from a master group, Oracle removes the calls from the internal triggers.

Conflict Detection

< /a>

If all sites of a master group communicate synchronously with one another, then applications should never experien ce replication conflicts. However, if even one site is sending changes asynchronously to another site, then applications can experien ce conflicts at any site in the replication environment.

If the change is being propagated sy nchronously, then an error is raised and a rollback is required. If the change is propagated asynchronously, then Oracle automaticall y detects the conflicts and either logs the conflict in the error queue or, if you designate an appropriate resolution method, resolv es the conflict.

See Also: Chapter 5, "Conflict Resolution Concepts and Architecture"

Understanding Mixed-Mode Multimaster Systems

In some sit uations, you might decide to have a mixed-mode environment in which some master sites propagate a master group's changes asynchronous ly and others propagate changes synchronously. The order in which you add new master sites to a group with different data propagation modes can be important.

For example, suppose that you have three master sites: A, B, and C. If you first create site A as the master definition site, and then add site B with a synchronous propagation mode, then site A sends changes to site B synchronously and site B sends changes to site A synchronously. There is no need to concern yourself with the sched uling of links at either site, because neither site is creating deferred transactions.

Now su ppose that you create master site C with an asynchronous propagation mode. The propagation modes are now as illustrated in Figure 2-9.

Figure 2-9 Selecting a Propagation Mode

Text description of the illustration repln040.gif

You must now schedule propagation of the deferred transaction queue from site A to site C, from site B to site C, and from sit e C to sites A and B.

As another example, consider what would happen if you created site A as the master definition site, then added site C with an asynchronous propagation mode, then added site B with a synchronous propagatio n mode. Now the propagation modes would be as shown in Figure 2-10.

< h4 class="FT">Figure 2-10 Ordering Considerations

Text description of the i llustration repln041.gif

Each time that you add a new master site to a mixed-mode multima ster system, consider how the addition affects the data propagation modes to and from existing sites.

Initiating Propagation

When synchronous propagation is used, the propagation of the DML is handled immediately and is automatically initiated. If asynchronous propagation is used, then you can use the following methods to propagate the deferred tr ansactions:

• Scheduled job: In most cases, use a scheduled job to automatically propagate the deferred transactions at a set interval.
• Manual propagation: You can also manually propagate the changes by executing a stor ed procedure or using the Replication Management tool. You may occasionally need to manually propagate your deferred transactions if you do not want to wait for the job queue to automatically propagate the deferred transactions.

Performance Mechanisms

As with any enterprise database solution, performance is always an important issue for the database administrator. Advanced Replication provides several mechanisms to help increase the performance of your replication environ ment.

Paralle l Propagation

With parallel propagation, Oracle asynchronously propagates replicated transactions using multiple, parallel transit streams for higher throughput. Whe n necessary, Oracle orders the execution of dependent transactions to ensure global database integrity.

Parallel propagation uses the pool of available parallel processes. This is the same facility Oracle uses for other parall el operations such as parallel query, parallel load, and parallel recovery. Each server process propagates transactions through a sin gle stream. A parallel coordinator process controls these server processes. The coordinator tracks transaction dependencies, allocate s work to the server processes, and tracks their progress.

Parallel processes remain associat ed with a parallel operation on the server throughout the execution of that operation. When the operation is complete, those server p rocesses become available to process other parallel operations. For example, when Oracle performs a parallel push of the deferred tra nsaction queue to its destination, all parallel processes used to push the queue remain dedicated to the push until it is complete.

To configure a pool of parallel processes for a server properly, you must consider several iss ues related to the configuration of a replication system.

• When y ou configure all scheduled links to use serial propagation, the replication system does not use parallel processes. Therefore, you do not need to adjust the size of any server's pool of parallel processes to account for replication. Typically, serial propagation is used only for backward compatibility.
• When you configure one or more scheduled l inks to use parallel propagation, you must consider the number of parallel processes that each link uses to push changes to its desti nation. Furthermore, you should also consider how long each push holds parallel servers from being used by other operations. For exam ple, when you configure a scheduled link for continuous propagation with a large value for delay seconds, Oracle holds on to the para llel processes used to push transactions to its destination. Therefore, you should increase the number of parallel processes for the corresponding database server to ensure that there is a sufficient number of processes for other parallel operations on the server.

To configure a database server's pool of parallel query processes, use the following ini tialization parameters:

• PARALLEL_MAX_SERVERS
< li class="LB1" type="disc">PARALLEL_MIN_SERVERS
See Also:
• "Initialization Parameters"
• Oracle Databas e Reference

Implementing Parallel Propagation

For most users, setting the parallel propagation parameter to a value of 1 provides sufficient performance. A setting of 1 enables the optimized data transfer method discussed in the previous section instead of serial propagation. However, some users may want to further tune the parallel propagation value.

The following procedure is the recommen ded method that should be used to further tune the parallel propagation value:

1. Set the parallel propagation value to 1.
2. T est your database environment and carefully measure the propagation throughput.

   If you ha ve achieved your performance goals with a parallel propagation value of 1, then you have implemented parallel propagation, and you do not need to complete the remaining steps in this procedure.

   Note:: As you increase the value of the parallel propagation param eter, be aware of the trade-offs between increased parallel propagation and the resources required to support the extra parallel slav es.

3. If you want to try to achieve greate r propagation throughput than with a value of 1, then set your parallel propagation value to 2.
4. Test your database environment and carefully measure the propagation throughput.

   In many cases, you will experience propagation throughput degradation with a value of 2. This reduction is due to roun d-trip delays associated with the coordinator assigning dependent transactions to available slaves and waiting for the necessary comm it acknowledgments before assigning additional transactions.

   Repeat Steps 3 and 4 with the parallel propagation value set to 4 and again with 8. If throug hput still does not improve, then it suggests that the transactions in your environment are highly dependent on each other. Reduce th e parallel propagation value to 1 and proceed to Step 5.

   See Also: "Tuning Parallel Propagation" to learn about techniques to reduce transaction dependenci es

   If your performance did improve with a value of 2, 4, or 8, then it suggests that your transactions have a low degree of interdependence. You may even set your parallel propagation parameter to any value greater than 8. Just be sure to thoroughly test your environment and remain aware of the trade-offs between increased paralleli sm and the necessary resources to support those extra parallel slaves.

5. Set parallel propagation to the value that offers the best performance in your environment based on your testing.

Tuning Parallel Propagat ion

To gain the greatest amount of performance benefits from parallel propagation, reduce the amount of dependent transactions that are created. Remember that a transaction cannot start until all of its dependent transactions have been committed.

When trying to reduce the number of dependent tra nsactions:

• Use smaller transactions if possible (that is, commit more often, without destroying autonomy).
• Increase number of freelists for each table that receives inserts.
• Try to avoid hotspots (a row that is frequently mo dified - if the same row is touched, then those transactions are serialized). For example, use an Oracle sequence instead of using a counter in a row and incrementing it "manually."
• Consider using row-level depend ency tracking.

  See Also: "Use of Row-Level Dependency Tracking to Improve Parallelism"