Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Slow Streams Performance.

RE: Slow Streams Performance.

From: Thotangare, Ajay \(GTI\) <>
Date: Mon, 6 Nov 2006 14:18:04 -0500
Message-ID: <>

I tried to set the aq_atm_processess=4 but there was no change in performance.

	-----Original Message-----
	From: Polarski, Bernard []

	Sent: Friday, November 03, 2006 1:25 AM
	To: Thotangare, Ajay (GTI);
	Subject: RE: Slow Streams Performance.
	Encountering flow control just means that the dequeue is not up
to the enqueue.
	Given the fact that you have free readers while encountering the
flow control, I would rather think that this is the cleanout of applied transaction that is lagging behind.

        The only parameter that affect the number of reader devoted to the cleanout is 'aq_atm_processess'. Most of the time it is left to 1, you should try a value of 4.                    

        B. Polarski

	From: Thotangare, Ajay (GTI) [] 
	Sent: Thursday, 02 November, 2006 8:50 PM
	To: Schultz, Charles;
	Subject: RE: Slow Streams Performance.
	In my case database is not looking for old log sequence number.
Its looking the right and current log file. Propagation seems likely to be bottleneck due to which Capture process halts and time is wasted.

        SQL> select
first_scn,start_scn,captured_scn,applied_scn,required_checkpoint_scn from dba_capture;          



		-----Original Message-----
		From: Schultz, Charles [] 
		Sent: Thursday, November 02, 2006 12:21 PM
		To: Thotangare, Ajay (GTI)
		Subject: RE: Slow Streams Performance.
		Where are first_scn and start_scn in terms of
last_required_scn? We had a somewhat similar performance problem when the database was keeping track of a really old log sequence, and things sped up a bit when we moved the first_scn up.

[] On Behalf Of Thotangare, Ajay (GTI)

		Sent: Thursday, November 02, 2006 11:04 AM
		Subject: Slow Streams Performance.

		Hi All, 

		Slow Streams Performance. I want to reduce the time for
the changes to be reflected in target site sooner.
		Site A : 
		I execute a particular transaction and it takes 3 Mins. 
		(Transaction Inserts 1,000,000 rows in a table. Commit
every 1000 Rows. No Indexes)
		SIte B 
		I execute same transaction and it takes approx. 3 Mins. 
		(Transaction Inserts 1,000,000 rows in a table.Commit
every 1000 Rows. No Indexes)
		Site A : 
		I execute a particular transaction and it takes 3 Mins. 
		(Transaction Inserts 1,000,000 rows in a table.Commit
every 1000 Rows. No Indexes)
		Site B : 
		Capture, Propagation and Apply runs on this site. 

		Steps : 
		(a) Execute transaction on SiteA ( 3 minutes) (Generates
approx. 275MB of redo) 
		(b) Switch archivelog on Site A atleast 2 times (done
		(c) Time to transport archivelog from SiteA to SiteB
(approx. 1mins). Archive log is automatically transported via RFS 
		(d) It takes around 2mins to get status of capture
process as "capturing changes"(V$STREAMS_CAPTURE) 
		(e) It takes 10Mins to reflect the transaction on SiteB
which is a slow performance

                Earlier it use to take 24Mins. Now its reduced to 10mins due to following changes

                (a) Add parallelism to Capture : 12 Parallel process (4CPU BOX)
                (b) Add parallelism to Apply : 8 Parallel process (4CPU BOX).
                    Parallelism for apply was reduced from 12 to 8 which gained performance by 1 mins.

                        Noticed that 4 parallel out of 12 process were always idle so reduced the parallelism to 8 (Gained 1 Minute in performance)

		(c) Changed propagation latency to 0 
		(d) Streams Pool SIze increased to 2G. (This helped to
remove all spilling and incresed performance) 
		(e) _SGA_SIZE increased to 500M. (our logfile is just
		(f) Checkpoint Frequency : 100M 

		When I check the status continuously from
v$buffer_subscriber and v$buffer_publishers, 
		It shows the publisher state as "IN FLOW CONTROL: Lots
of Unbrowsed msgs". Capture Process also shows status as "PAUSED for flow control intermitently".

                How can I completely eliminate the status "IN FLOW CONTROL" , which indicates either low memory or apply or propagation is not as fast as capture.

                We confirmed its not memory problem by reducing commit size. We think problem is with Propagation as apply seems working fine.

		Can anybody help to resolve this problem? 
		Pls let me know if anybody needs more info. 


                If you are not an intended recipient of this e-mail, please notify the sender, delete it and do not read, act upon, print, disclose, copy, retain or redistribute it. Click here <> for important additional terms relating to this e-mail.

Received on Mon Nov 06 2006 - 13:18:04 CST

Original text of this message