Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Oracle 10gR2 Streams - Issue with excessive Redo

Oracle 10gR2 Streams - Issue with excessive Redo

From: krishna sarabu <ksarabu_at_yahoo.com>
Date: Tue, 3 Apr 2007 04:56:18 -0700 (PDT)
Message-ID: <292749.30227.qm@web56202.mail.re3.yahoo.com>


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.
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 03 2007 - 06:56:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US