Oracle 10gR2 Streams - Issue with excessive Redo

We have noticed that Streams generating more than 5x Redo on Master when 
TXN_LCR_SPILL_THRESHOLD set to Infinite and when we updated 300K rows in one 
txn. From the stats we captured during the test, it seems that the Capture 
process captured all changes and propagated them without any 
bottleneck/performance issue, but, started spilling messages when the apply 
server started applying them at destination. We have mined through the online 
redo logs and found that the excessive redo is coming from Streams spilling. 
Please note that the streams pool was used only up to 68% and Logminer session 
used up to 64% of allocated space. Stats captured clearly shows that there was 
no bottleneck during capture/propagation. Please note that the Streams 
completed replication in 9 minutes.

We have repeated the same test using TXN_LCR_SPILL_THRESHOLD set to 100,000 and 
noticed that there was no spilling and no additional redo generated by Streams. 
But, it took over 20 minutes to replicate changes as the capture process was 
pausing in-between with the event "PAUSED FOR FLOW CONTROL".

We couldn't understand why the spill occurred (caused excessive redo) when the 
parameter txn_lcr_spill_threshold set to infinite, though there was no 
performance/bottleneck issue and it hardly used 70% of allocated streams pool. 
Anyone experienced the same issue? Will greatly appreciate your feedback. 

I've read somewhere in Metalink that the Oracle Capture process spills messages 
if the LCRs in buffered queue life span exceeds 5 minutes. Is this true? If 
yes, is there any way we can increase the LCR life span time limit?

Please note that the following parameters have been set according to Metalink 
docs (335516.1, 298877.1) on source/destination databases:
        >> Source/Capture:PARALLELISM=4; _CHECKPOINT_FREQUENCY=100; 
_CHECKPOINT_FORCE=Y
        >> Destination/Apply: PARALLELISM=4; _TXN_BUFFER_SIZE=2; 
TXN_LCR_SPILL_THRESHOLD=INIFINITE
        >> init.ora (_job_queue_interval=1)
        >> Latency of propagation schedule = 1

Thanks for your help in advance.

Regards,


 
---------------------------------
Food fight? Enjoy some healthy debate
in the Yahoo! Answers Food & Drink Q&A.

Other related posts: