Streams Recommendations

Recommended patches

Capture site

• The capture process writes its own checkpoint information to its data dictionary tables in order to keep track of the SCN values already scanned by the capture process. By default, the capture process writes checkpoints very frequently and stores their data for a long time. On a very write-intensive system, the performance of the capture process can become severely degraded if the following parameters are not adjusted properly.
• Checkpoint retention:
  • Periodically, the mining process checkpoints itself for quicker restart.
  • The capture parameter checkpoint_retention_time controls the amount of checkpoint data retained. Default: 60 days.
  • Reducing the checkpont_retention_time will move automatically move the first_scn forward. This would purge the the Streams metadata tables previous to this scn(FIRST_SCN) .
  • Since the first_scn is moved forward the restart SCN is automatically moved forward .Thus the Capture Process will scan through less archive logs.
  • Hence Capture Restart time is reduced.
  • General recommendation: set the checkpoint_retention_time to 7 days or less.
  • exec dbms_capture_adm.alter_capture(' %capture_name% ', checkpoint_retention_time => 7);
• Checkpoint frequency:
  • Modify the frequency of logminer checkpoints especially in a database with significant LOB or DDL activity.
  • Increase the value of the capture parameter _checkpoint_frequency for active databases with significant redo generated per hour. Default: 10M
  • exec dbms_capture_adm.set_parameter(' %capture_name% ', '_checkpoint_frequency', ' %value% ');
  • ATLAS: 2048M, LHCB: 350M, LFC:50M
• Restart the process to apply the changes.

Propagation site

• Latency: maximum wait, in seconds, in the propagation window for a message to be propagated after it is enqueued.
• latency=1
• exec dbms_aqadm.alter_propagation_schedule(' %source_queue% ', ' %destination_db_link% ', destination_queue=>' %destination_queue% ', latency=>1);

Apply site

• Performance can be improved by setting the following parameters:
  • _dynamic_stmts = 'Y'
  • _hash_table_size = '10000000'
  • _txn_buffer_size = The parameter should initially be set to 10. If individual transactions have a large number of row changes (100,000 or more) and apply parallelism is greater than 1, consider reducing _TXN_BUFFER_SIZE to a lower value.
• exec dbms_apply_adm.set_parameter(' %apply_name% ', ' %parameter% ', ' %value% ');

Capture and Apply site - flow control threshold

• By default flow control kicks when there are 5000 unbrowsed messages in a queue. This default behavior can be changed in 10.2 by applying the patch 5093060 which allows you to manipulate when buffer publisher flow control is activated. The fix is event based in 10.2.0.3:
  • 10867: this event controls flow control threshold values for any buffered message publisher. The level of the event decides the threshold value.
    • Example: alter system set event=10867 trace name context forever, level 30000 scope=spfile;
  • 10868: this event controls flow control threshold values for only capture publisher. The level of the event decides the threshold value.
    • Example: alter system set event=10868 trace name context forever, level 20000 scope=spfile;
• The fix is present in 10.2.0.4 onwards and can be activated by setting the following hidden parameters:
  • alter system set "_capture_publisher_flow_control_threshold"=20000
  • alter system set "_buffered_publisher_flow_control_threshold"=30000
• See metalink note 551516.1

Periodic maintenance

• Dump fresh copy of Dictionary redo:

   DBMS_CAPTURE_ADM.BUILD(); 

• Prepare instantiation for the database objects captured by Streams:

   DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION( =%schema%= ); 

• How to check the oldest archive log that will be needed by the capture process:
  • minimum_archivelog.sql: minimum_archivelog.sql

• How to display detailed information about Apply Errors:
  • (as SYSDBA):

     GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin; 

  • (as strmadmin): print_LCR.sql: print_LCR.sql
  • To run this procedure after you have created it, enter the following:

     
     SET SERVEROUTPUT ON SIZE 1000000
     EXEC print_errors 
     

• Logminer table: the access to this table can be very slow due to the high space fragmentation
  • How to reorganize SYSTEM.LOGMNR_RESTART_CKPT$?
    • stop the capture process
    • connect as system user

       alter table system.logmr_restart_ckpt$ enable row movement;
       alter table system.logmr_restart_ckpt$ shrink space;
       alter table system.logmr_restart_ckpt$ disable row movement;
       

    • re-start capture process
  • shrink_logminer_tables.txt: Script and job to shrink the logminer tables periodically

• Queue tables: it is recommended to perform on regular basis a dynamic shrink of the AQ objects
  • shrink_queue_tables.txt: Script and job to shrink the queue tables periodically