In this Document
Purpose
Scope and Application
Master Note for Streams Performance Recommendations
General Operational Considerations
Implications of Tables with no Primary Key Column(s)
GeneralRecommendations
Logminer Related Configuration
Capture Related Recommendations
PropagationRecommendations
Apply Recommendations
Additional Apply Performance Tips
References
Customers should be on the ultimate patchset and it is endorsed to request applicable rags as minute in :
Document 437838.1 Streams Specific Patches . These rags detailedaddress many of the ordinarily encountered opening and fortitude problems related with Streams.
A total list of 10.2 recommendations is minute in :
Document 418755.1 10gR2 Streams Recommended Configuration (it is expected that assorted related records will be fused at a few point).
If there are opening issues, more item may be found to help accumulate applicable data together with keep apart from the result in in records :
Document 746247.1 Troubleshooting Streams Capture when position is Paused For Flow Control
Document 730036.1 Overview for Troubleshooting Streams Performance Issues.
The subsequent to principal areas of operation may be affected by the setup sum that follow.
Streams uses buffered queues with the aimthat all LCRs are processedin memory.
Spill of data from mental recall to hoop can start opening and can happen in 3 organic areas;thesespill actions are termed :
Logminer brief (source capture)
Queue brief (capture and apply)
Apply Spill (apply).
All Oracle versionshave Queue spill. Queue brief is typically associated with LCRs not being processed in a timely conform from the buffered reserve ; rsther than than permitting aged LCRs to sojourn in mental recall for an lengthened time of time , they are spilled out to diskto the list :aq$_;_p.
Queue brief may moreover be associated with mental recall (Streams pool)space; if there is not sufficient mental recall toaccommodateLCRs, they are once again spilled to disk. Spill of this inlet may be associated with a accumulation of causes thus it is value deliberation the parameter surroundings below.
Oracle versions >= 10gR2 in addition introduceApply Spill. Apply brief isprimarily related with the estimate of considerable exchange nonetheless it doestake accountoflong running transactions. It is value nothing that Apply brief can run on reserve spill.
The Capture side leaves the administration of these two types of contract to the Apply side. These types of contract are created to a partitioned list at the Apply side database; the partitioned list is far simpler to control than the reserve brief table. The request brief turning point may be configured.
Logminer brief writes cached logminer data out to logminer brief tables. It may be vital to reconfigure the amount of space for cached logminer information. Typically the default apportionment is sufficient for many applications. Where there is Queue brief , there may be the must be deallocate unusedspaceor shrink the space use of the related aq$_;_p periodically.
Streams (Capture) has to checkpoint as it goes along to be able to make sure that it can restart from a point in the past that does not require too many redo logs to be reprocessed. Care should be taken to comprehend when checkpoints will take place as well asthe length of time checkpoint data is retained. Checkpoint data is stored in a singular unpartitioned table. Thereforeconsideration should be since tothe related parameters next mainly in the eventuality of there being multi-part Capture processes. Checkpoint magnitude and cleane interlude may be configured. It is emasculate to checkpoint too frequently,similarly it is imprudent to grip on to checkpointinformationtoo long- both can start performance.
The Capture routine in 10gR1 and on top of right away uses an inbuilt upsurge control mechanism.Flow control aims to ensurethat the source of LCRs (the Capture) does not inundate the aegis reserve of the Apply routine before the Apply routine has had a luck to request these. In established cases it may be applicable to rectify the upsurge control related parameters.
This is related with Apply processing. Streams issuited toenvironments where all tables have first keys. TheApplyprocessshould run as rapidly as probable when conducting refurbish and undo operations and a first key allows the quarrel anxious to be directly referenced for these operation types. It is value checking the division :' Substitute Key Columns' that outlines what is compulsory in the environment where a list does not have a first key - more sum are found in the Oracle Streams Replication Administrator's Guide.
Whilst the record mentions thatit is probable to have nulls in the replacement key, in practice permitting a nothing in an indexcan enable multi-part nothing values in the indexed mainstay and consequentially directaccess to key data may not be possible.
Remember this point as the target is to have the replacement key columns effectively handle similar to a first key (unique and not nothing data values).
Streams Pool Allocation
11g: set STREAMS_POOL_SIZE to be larger or next to to 100 MB;
10gR2: set SGA_TARGET > 0 and STREAMS_POOL_SIZE=0 to enable autotuningof the Streams pool;
10gR1: use theSTREAMS_POOL_SIZE init.ora parameter to configure the Streams mental recall allocation;
9.2: enlarge the SHARED_POOL size to minimize spill-over from aegis reserve to hoop
Initialization filehidden parameters
10gR2
_job_queue_interval=1
_spin_count=5000
10gR1
_job_queue_interval=1
_spin_count=5000
9.2
_first_spare_parameter=50
_kghdsidx_count=1
_job_queue_interval=1
_spin_count=5000
Queue Monitor/ AQ_TM_PROCESSES
In 10gR1 onwards,remove the AQ_TM_PROCESSES parameter from your database initialization parameter file. This will enable the reserve administration credentials wake up tobe managed automatically.
Queue to Queue Propagation
When upgrading to Oracle Database Release 10.2 and above, from releases 9.2 or 10.1, tumble any propagations and reconstruct them naming the queue_to_queue parameter as TRUE. This is mainly applicable where a RAC end is in place as this allows propagation to flop over to an existing example that right away owns the Streams buffered queue.
Multiple Separate Queues for Capture and Apply
From all Streams versionsup toand including11g , wherebi-directional riposte is in place, configure well-defined Capture and Applyqueues to minimize the start of Queue brief to disk. Do not have both these processes pity the same reserve unless Capture and Apply reside in same database and there is no propagation receiving place.Both processes will see LCRs for both activities; if the Capturequeue has spilled (Queue spill) this can start Apply operation and slow it down.
Avoid Complex Rules
Make sure that manners as this enable Fast evaluation. Fast rule analysis may be used on elementary manners with object_owner, object_name, source_database_name in elementary equivalence statements.
In 9.2, it was vital to prevent 'LIKE' , 'NOT' and != operators as much as probable as these operators disallowed rule analysis optimizations. It is improved (i.e., faster to evaluate) to have multi-part elementary manners than 1 intricate rule.
In 10g and above,the samecan be completed by using a disastrous rule set to discard tables or objects from Streams processing.
Examples of elementary rules:
Implement a "heart beat" list
It is utilitarian to do this is to reasons summarized :
9.2: Implement Flow Control .
These handbook upsurge control scripts should be used on Oracle 9.2 Streams source databases (i.e., databases running 9.2 streams takeover processes). Refer to Document 259609.1
Generally, this parameter should not be modified. The onlyoccasions whereit is current to change the value of _SGA_SIZE is to Capture/logminer session areunder environment :
ORA-1341 is observed; or
Where there is log miner spill. Log miner brief is outlay as an instructive bell in the streams healthcheck inform beneath division minute : 'performance checks' .
Themajority of Capture / logminer mental recall problems (ORA-01280) would be settled by stepping up _SGA_SIZE to 80 or even 150 Mb, that could be set as follows :
Purging Streams Checkpoints
10.1: Alter the first_scn at regular intervals (weekly or daily) to remove unneeded metadata for Streams capture;
10.2: Alter the takeover parameter CHECKPOINT_RETENTION_TIME from the default influence of 60 days to a pragmatic value for your database.
11g : setthe parameter : CHECKPOINT_RETENTION_TIMEto an apt value for your environment. Again7 days would show up to be a in accord with time of time.
Propagation Job Interval
To lower the latency between jobs, set the dark parameter _job_queue_interval = 1. This should be done as an init.ora parameter or an spfile parameter. Because it is a dark parameter, the database must be restarted in order is to value to take effect. The default for _job_queue_interval is 5 seconds.
Remove not essential Propagation Rules
If you are configuring a propagation that takes ALL changes from the source reserve to the end reserve (ie, no selectivity requirements), you can remove the rule set from the propagation definition. This will discard the prerequisite to do ANY rule analysis and will result in aloft propagation throughput.
Propagation Latency
Propagation latency is the limit wait, in seconds, in the propagation window for a summary to be propagated after it is enqueued. The default propagation latency value is 60. Reduce the latency of the propagation schedule to 1 by arising the subsequent to :
Queue to Queue Propagationor Queue-to-Database couple
When upgrading to Oracle Database Release 10.2 and above, from releases 9.2 or 10.1, tumble any propagations and reconstruct them naming the queue_to_queue parameter as TRUE.
In 11g use queue-to-queue propagations when possible. A queue-to-queue propagation always has its own disdainful propagation work to generate messages from the source reserve to the end queue. Because any propagation work has its own propagation schedule, the propagation schedule of any queue-to-queue propagation may be managed separately.
TCP related Parameter Settings
Increase the SDU in a Wide Area Network for Better Network Performance In addition, the SEND_BUF_SIZE and RECV_BUF_SIZE parameters in the listener.ora and tnsnames.ora files enlarge the opening of propagation on your system. These parameters enlarge the size of the aegis used to send or take the propagated messages. These parameters should usually be increased after clever analysis of their on the whole effect on network performance. For more sum , impute to Document 780733.1 .
Queue Spill related Space Management
Versions >= 10gR2 .
To upgrade the opening of streamsaftersignificant Queue brief activity, performthe following:
Versions ; for more details.
Handling Very Large Transactions
Set Hidden request parameter _TXN_BUFFER_SIZE ONLY for Large contract
For 10g and where correspondence > 1, to prevent spillover from occurring when using parallelism, lower the dark request parameter _TXN_BUFFER_SIZE to 10. If the exchange are really considerable (ie, have many quarrel changes inside of a singular contract [100000's] and correspondence > 1, ponder shortening the contract aegis size even lower (for example, _TXN_BUFFER_SIZE=2)
In 11g :The parameter _TXN_BUFFER_SIZE is autotuned so this parameter should not be set. If upgrading from 10g to 11g, _TXN_BUFFER_SIZE should be unset :
Recommended Parameters
For 10.2 and on top of set the subsequent to request parameters:
Apply Parallelism
Increase the correspondence of the request routine at the end to tie in the concurrency of the source database wake up :
If you set correspondence > 1 on the request process, be sure to configure the apt constraints at the end site (as well as supplemental logging at the source site) to do dependency computations.
For releases lower than 10.1.0.5, be sure to mention UNCONDITIONAL supplemental logging at the source database for ANY indexed columns at the end database that experience in Streams.
Test with correspondence values formed on existing cpu. Typical settings are 4, 8, 16, or 24. 16 and 24 are routinely used for high contract loading with high end machines. Values between 1 and 4 are typically used on low end machines. Mid-range network typically configure 4 or 8 request servers.
_DYNAMIC_STMTS :
For 10gR2 and on top of with a effort consisting of many UPDATE exchange that cgange reduction than half of the columns for any since row, ponder using the dark request parameter _DYNAMIC_STMTS. This parameter surroundings may lower redo era together with upgrade request performance. To set this parameter, situation :
If more than half of the columns are
_HASH_TABLE_SIZE :
The subsequent to is applicable to allreleases. In an churned (Insert/UPdate/Delete) or complicated refurbish effort that results in a considerable number of dependency waits (WAIT_DEPs) on replicated tables with many constraints , ponder stepping up the size of the dependency crush list with the dark parameter _HASH_TABLE_SIZE. Set the value of this parameter to a considerable number, for example, 1 million (1000000) or 10 million (10000000). i.e :
Are All Changes to be Applied
If you are configuring an request routine to take ALL changes that are put in to the reserve (ie, no selectivity or subsetting requirements), you can remove the rule set from the request definition. This will discard the prerequisite to do ANY rule evaluations and will result in aloft request throughput.
Constraints + Supplemental Logging
Increase the correspondence of the request routine at the end to tie in the concurrency of the source database. If you set correspondence > 1 on the request process, be sure to configure the apt constraints at the end site (as well as supplemental logging at the source site) to do dependency computations.
If adding extra columns at the target database, mention DEFAULT values for those extra columns, if possible, so that a DML_HANDLER is not compulsory for INSERTs in to the table. For example, if the list at the target site includes an extra mainstay LAST_UPDATE (ie, the LAST_UPDATE mainstay does not exist at the source database -only at the target database) that is the date/timestamp of the many new update, then mention a DEFAULT value of SYSDATE is to LAST_UPDATE column. This will discard the must be govern a dml_handler for any insert in to the table. Of course, UPDATEs will require that the dml_handler be invoked.
For total detailsrefer to the Oracle Streams Replication Administrator's Guide.
Conflict Detection :
If the target database is used in a read-only mode (i.e., no DMLs are performed on the replicated tables), ponder disabling strife showing for non-key columns on replicated tables with the DBMS_APPLY_ADM.COMPARE_OLD_VALUES procedure.
For complicated DELETE workloads, use the DBMS_STREAMS_ADM.DELETE_COLUMN procession is to APPLY list rule to remove the non-key columns from the DELETE LCRs.
Apply Parallelism and INITRANS
To minimize ITLcontention issues,modify the INITRANS value for any replicated list and indexso that INITRANS is next to to the request correspondence value or higher.
Streams and Large Transaction
Foreign Key Constraints
Partition Tables
Where there are no referential honesty constraints or other judicious dependencies. Configure the Apply routine as follows :
_HASH_TABLE_SIZE: In an insert usually workload, discard the dependency computation between exchange by surroundings the request dark parameter _HASH_TABLE_SIZE to 0;
COMMIT_SERIALIZATION: In an insert usually workload, spin commit serialization off by surroundings COMMIT_SERIALIZATION to NONE;
PARALLELISM: Increase the correspondence request parameter to enable more request servers;
To minimize ITL pressure issues, be sure to cgange the INITRANS value for any replicated list so that INITRANS is next to to the request correspondence value or higher. PCTFREE for replicated tables should be at least 10. (ALTER TABLE ... INITRANS 16 PCTFREE 10)
Consider partitioning the first key index is to replicated tables to upgrade request processing.
No comments:
Post a Comment