Making Oracle Streams Fly
Not wishing to clog my Blog with 8 pages of text, please view the article by following the link below:
http://www.oracle11ggotchas.com/articles/Making%20Oracle%20Streams%20Fly.pdf
Not wishing to clog my Blog with 8 pages of text, please view the article by following the link below:
http://www.oracle11ggotchas.com/articles/Making%20Oracle%20Streams%20Fly.pdf
March 31, 2010 at 5:57 pm
Setting the TXN_LCR_SPILL_THRESHOLD to very large will result in using the 10gR1 queue spill behaviour. This is really NOT a recommended setting as you may appear to you showing a great performance by putting all LCRS in memory queue the link above. You forget about the queue spilling problems. The apply process will begins to spill messages from memory to hard disk for a particular transaction when the amount of time that any message in the transaction has been in memory exceeds the number of seconds ( 5 minutes in 10gR2 and in 11gr2 you can change by setting txn_age_spill_threshold parameter). Even you do have 4G of streams pool, the streams will start spill the queue to disk. Queue spillover is slower than apply spillover. In queue spillover, the LCRs are spilled to a queue table ( AQ$__P individually. After a transaction has been applied, the LCRs are dequeued: they are removed one by one from this queue table. For a large transaction, this can take a significant amount of time. You are better of leaving TXN_LCR_SPILL_THRESHOLD to default to avoid queue spilling. The advantage of apply spill is that Streams can handle any size of transaction as long as there is enough disk space to hold the transaction,
You can try now running many large transaction of 200K for example for 35 mins with commit, and open another session and insert large transaction of 100K , wait 25 minutes and commit and see the outcome of queue spilling
April 14, 2010 at 11:49 am
In Oracle 10g and above you will not see the Streams capture process spilling many messages. This is largely due to automatic flow control being enabled. If a large number of messages build up in the capture process’ buffered queue, then flow control will cause the capture process to temporarily stop capturing any new messages, until some messages are removed from the queue. When this happens, the capture process state is “Paused for Flow Control”.
May 10, 2010 at 11:40 am
I saw about 5 millions spill messages in queue on customer side , and it took them many many hours to clean the queue, Flow control is automatic in 10g and has a number of conditions that can trigger the capture process to pause for flow control.
1 memory pressure
2.Unseen messages.
If the apply is slow and the condition of flow control are not meet , the queue spilling will start spilling to AQ$_QUEUE_NAME_P.
regards,
Bous