otak-otak | favorite evening food

May 9, 2011

streams tuning on high batch jobs

Filed under: streams — ora62 @ 7:19 am
this apply parameter in streams environment is intended for the machine that has many cpus. 
the parallelis and commit_serialization will help when there are many insert, 
the streams will extremely fast enough to handle huge transaction.
but if your transaction is mandatory by update or delete, commit_serialization will depends on the scn order. 
so there are no impact to your business (but it should need careful test)

BEGIN
 DBMS_APPLY_ADM.SET_PARAMETER (
   apply_name => 'A_NONAME',
   parameter => 'TXN_LCR_SPILL_THRESHOLD',
   value => '15000000');

DBMS_APPLY_ADM.SET_PARAMETER (
   apply_name => 'A_NONAME',
   parameter => '_TXN_BUFFER_SIZE',
   value => '64');
 DBMS_APPLY_ADM.SET_PARAMETER (
   apply_name => 'A_NONAME',
   parameter => '_hash_table_size',
   value => '10000000');
dbms_apply_adm.set_parameter('A_NONAME','_SGA_SIZE','8192');
dbms_apply_adm.set_parameter('A_NONAME', '_RESTRICT_ALL_REF_CONS','N');
dbms_apply_adm.set_parameter('A_NONAME', 'COMMIT_SERIALIZATION','NONE');
dbms_apply_adm.set_parameter('A_NONAME', 'PARALLELISM','64');

END;
/

exec dbms_apply_adm.stop_apply('A_NONAME');

exec dbms_apply_adm.start_apply('A_NONAME');
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: