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

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

RE: Slow Streams Performance.

From: Thotangare, Ajay \(GTI\) <Ajay_Thotangare_at_ml.com>
Date: Thu, 2 Nov 2006 14:52:21 -0500
Message-ID: <E91392BCB7BF9F409939B5E730164B00080241B6@MLNYA210MB.amrs.win.ml.com>


Hi,  

Are you facing any performance problem in streams ?  

regards,
Ajay

-----Original Message-----
From: Bhandarkar, Gary [mailto:gary_bhandarkar_at_merck.com] Sent: Thursday, November 02, 2006 1:22 PM To: Thotangare, Ajay (GTI)
Subject: FW: Slow Streams Performance.

Ajay,
We are using streams heavily too in production. Can we discuss things together?
Regards
Gary B
732-940-8854[home]
732-594-5549


From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thotangare, Ajay
(GTI)

Sent: Thursday, November 02, 2006 12:04 PM To: oracle-l_at_freelists.org
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.

WITHOUT USING STREAMS



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)

CONFIGURED DOWNSTREAMS



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 Immediately)
(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 275MB)
(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.

reagrds,
Ajay


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 <http://www.ml.com/email_terms/> for important additional terms relating to this e-mail. http://www.ml.com/email_terms/





Notice: This e-mail message, together with any attachments, contains information of Merck & Co., Inc. (One Merck Drive, Whitehouse Station, New Jersey, USA 08889), and/or its affiliates (which may be known outside the United States as Merck Frosst, Merck Sharp & Dohme or MSD and in Japan, as Banyu - direct contact information for affiliates is available at http://www.merck.com/contact/contacts.html) that may be confidential, proprietary copyrighted and/or legally privileged. It is intended solely for the use of the individual or entity named on this message. If you are not the intended recipient, and have received this message in error, please notify us immediately by reply e-mail and then delete it from your system.

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 02 2006 - 13:52:21 CST

Original text of this message

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