Streams troubleshooting

Home Up

 


Streams Set parameters
 

Install parameters for 9iR2

Listing existing parameters related to streams with 'pars'
Parameters set in the INIT.ORA
Parameters set using DBMS_CAPTURE_ADM package
Parameters set using DBMS_APPLY_ADM package:

Listing existing parameters related to streams with 'pars'

This shortcuts checks is similiar to the one of dataguard 'parg'. It display the current parameters related to streams. The present  listing was taken from a installed
streams that worked fine for an average 200msg/second all day long. Smenu provide the shortcuts 'pars' to display all parameters related to streams configuration :

[NSCSHACC]:/export/home/oracle> pars

Date              -  Friday    06th January   2006  17:05:19
Username          -  SYS
Report            -  Streams pararmeters

.     parg = data guard         pars = Streams
                                                                                                      Is Sys
Parameter                        Value                                                                    Modifiable
-------------------------------- ------------------------------------------------------------------------ ----------
aq_tm_processes                  4                                                                        IMMEDIATE
archive_lag_target               0                                                                        IMMEDIATE
compatible                       9.2.0                                                                    FALSE
db_name                          NSCSHACC                                                                 FALSE
global_names                     TRUE                                                                     IMMEDIATE
job_queue_processes              10                                                                       IMMEDIATE
log_archive_dest_1               LOCATION=/archive/NSCST                                                  IMMEDIATE
log_archive_format               arch_%t_%s.arc                                                           FALSE
log_archive_start                TRUE                                                                     FALSE
log_parallelism                  1                                                                        FALSE
logmnr_max_persistent_sessions   1                                                                        FALSE
open_links                       4                                                                        FALSE
parallel_max_servers             16                                                                       FALSE
processes                        300                                                                      FALSE
sessions                         335                                                                      FALSE
sga_max_size                     906516520                                                                FALSE
shared_pool_size                 536870912                                                                IMMEDIATE


Parameters set in the INIT.ORA

bullet
aq_tm_processes=4
     This parameters is needed to start the queues monitor processes. It is also responsible for the number of
     apply execution server that purges the queue for applied events. Setting it to 4 for 1 queues doubles the speed
     of apply due to the faster cleanout of applied events.    
bullet
SHARED_POOL_SIZE
     Amount of  memory available for logminer processing is take out of shared_pool_size. The default amount of
     shared_pool memory allocated to logminer is 10Mb. Increase this value especially in environment
     where large LOBs are process. Stream will use by default, 10% of the shared_pool_size. When the 10% are used
     by the buffers queues, then spillover start to occurs Use _first_spare_parameter to increase
    the % of shared_pool allocate to streams.
bullet
Logmnr_max_persistent_session=1
     You need at least one of this. You neeed to have at least one logminer per server capture process.
     If you set multiple capture processes, you will have multiple logminer session, so you will need
     to increase this parameters to match the number of capture process.
     Do not mix 'capture process' with sever capture server. These last and are defined with
     DBMS_CAPTURE_ADM.SET_PARAMETER(parallelism=> nn) and can be up to 3 * cpu
bullet
_first_spare_parameter=50
     This parameter allocate percentage of the SGA for the streams processes. 
     The default is 10% of your SGA
bullet
_job_queue_interval=1
     Setting this to one overwrite the default setting of 5 secondes at which the queues are scanned by job.
     There is no reason to wait 5 seconds to realize new event arrived.
bullet
PARALLEL_MAX_SERVERS=(> apply or capture processes)
     Each capture process and apply process may use multiple parallel execution servers. The apply
     process by default needs two parallel servers.  So this parameter needs to set to at least 2 even 
     for a single non-parallel apply process. Specify a value for this parameter to ensure that there are enough 
     parallel execution servers. In our installation we went for 12 apply server, so we increased
     the number of parallel_max_server above this figure of 12.
bullet
_kghdsidx_count=1 	
     This parameter prevents the shared_pool from being divided among CPUs
bullet
LOG_PARALLELISM=1
     This parameter must be set to 1 at each database that captures events.

Parameters set using DBMS_CAPTURE_ADM package:

   Using the DBMS_CAPTURADM.SET_PARAMETER procedure there a 3 a parameters that are of common usage to affect installation
bulletPARALLELISM=3
     There may be only one logminer session for the whole ruleset and only one enqueuer process that will push the
     objects. you can safely define as much as 3 execution capture process
     per CPU
bullet_CHECKPOINT_FREQUENCY=1
     Increase the frequency of logminer checkpoints especially in a database with significant LOB or DDL
     activity. A logminer checkpoint is requested by default every 10Mb of redo mined.
bullet _SGA_SIZE
     Amount of memory available from the shared pool for logminer processing. The default amount of shared_pool memory
     allocated to logminer is 10Mb. Increase this value especially in environments where large LOBs are processed.
Example : Set the logminer sessionto 12 mega:

[POLDEV]:> cap -sga  12582912 -cn CAPTURE_MISPPROD -v

Statement gererated and executed :

execute dbms_capture_adm.set_parameter(capture_name=> 'MY_CAP_PROCEESS' , parameter=> '_SGA_SIZE', value => 12582912);

Parameters set using DBMS_APPLY_ADM package

     This create more apply server processes to insert/update/delete the dequeued rows. You can have only one 
         Reader process to dequeue each queue but you can safely define as much as 3 execution apply process per CPU.
         On heavy loaded this have many influcence.

[POLDEV]:> app -par 3 -an MY_APPLY_PROCESS -v

Statement generated and executed

execute dbms_apply_adm.set_parameter(apply_name=> 'MY_APPLY_PROCESS' , parameter=> 'parallelism', value => 3);

bulletPARALLELISM=3