No question at this time
DBA Top 10
1 M. Cadot 10400
2 A. Kavsek 9400
3 B. Vroman 4800
4 P. Wisse 4300
5 J. Schnackenberg 2900
6 J. PĂ©ran 2000
7 . Lauri 1000
8 F. Pachot 500
9 Z. Hudec 400
9 G. Lambregts 400
9 N. Havard 400
9 T. Boles 400
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48524
Total active users1409
Act. users last 24h3
Act. users last hour0
Registered user hits last week77
Registered user hits last month415
Go up

Oracle to stop spilled transaction
Next thread: restore fails
Prev thread: convert date format

Message Score Author Date
Hello Team, In our streams envirnoment, some of l...... Sivakumar KS Jun 10, 2020, 16:43

Follow up by mail Click here

Subject: Oracle to stop spilled transaction
Author: Sivakumar KS, India
Date: Jun 10, 2020, 16:43, 58 days ago
Os info: Linux == x86_64 x86_64 x86_64 GNU/Linux
Oracle info: oracle 12c == Enterprise Edition Release - 64bit Production
Message: Hello Team,
In our streams envirnoment, some of long process / transactions creates more archive generation in source DB, Hence streams also begin mining the archive log fast and try to apply on the target side, But same time it creating spilled transaction on target DB,we encount such situation only if source DB had few big volumne transaction.

How to avoid creating / encount spilled transaction. since once it is spilled then appply process get hungs / processing very slow. is there any way to propagate transaction slowly as per streams capacity.

Note: I do not want to ignore those spilled transaction.which should apply in target side.
also we do not have control on these transaction since source DB is hosted insurance product, so we do not have control on it.
could you help me to advice for the above.

FYI we have the following stream settings in our envirnoment

execute dbms_capture_adm.set_parameter('CAP4CCUSER','_CHECKPOINT_FREQUENCY','1000');
execute dbms_capture_adm.set_parameter('CAP4CCUSER','_SGA_SIZE','1500');

exec dbms_apply_adm.Set_parameter('APY4CCUSER','parallelism','4');
exec dbms_apply_adm.Set_parameter('APY4CCUSER','_dynamic_stmts','Y');
execute DBMS_APPLY_ADM.SET_PARAMETER('APY4CCUSER','_hash_table_size','2000000');
execute DBMS_APPLY_ADM.Set_parameter('APY4CCUSER','_txn_buffer_size','120');

also streams_pools_Size as 25 GB. ( is there impact if we set more stream_pool_size then required ? ?)

Also apart fromt the above is there any other setting can be considered for part of spill / performance. since if we encount spill / big volume of transaction then parallelisam also not working, coz source goes for paused for flow control.

IS there any way to create more than one DB_link which allows to serve / send data to target etc., is just my thought correct me if I am wrong.

Thanks for your support.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here