RE: Slow Streams Performance.
- From: "Thotangare, Ajay \(GTI\)" <Ajay_Thotangare@xxxxxx>
- To: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 3 Nov 2006 10:21:56 -0500
Hi,
No harm in trying your suggestion.
As per my knowledge, In 10g QMON Architecture, If AQ_TM_PROCESSES is
explicitly specified then the process(es) started will only maintain
persistent messages. For example if aq_tm_processes=1 then at least one
queue monitor slave process will be dedicated to maintaining persistent
messages. Other process can still be automatically started to maintain
buffered messages. If you explicitly set aq_tm_processes = 10 then there
will be no processes available to maintain buffered messages. I think
Increasing AQ_TM_PROCESSES will help only in case of persistent
messages. It will not help in buffered messages.
Pls correct me if I have wrong understanding
regards,
Ajay
-----Original Message-----
From: Polarski, Bernard [mailto:Bernard.Polarski@xxxxxxxxxxxxxx]
Sent: Friday, November 03, 2006 1:25 AM
To: Thotangare, Ajay (GTI); oracle-l@xxxxxxxxxxxxx
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) [mailto:Ajay_Thotangare@xxxxxx]
Sent: Thursday, 02 November, 2006 8:50 PM
To: Schultz, Charles; oracle-l@xxxxxxxxxxxxx
Subject: RE: Slow Streams Performance.
Hi,
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;
FIRST_SCN START_SCN CAPTURED_SCN APPLIED_SCN REQUIRED_CHECKPOINT_SCN
---------- ---------- ------------ ----------- -----------------------
461616864 461616864 461645048 461635296 461635296
SQL>
-----Original Message-----
From: Schultz, Charles [mailto:sac@xxxxxxxxxxxxx]
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.
_____
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Thotangare, Ajay
(GTI)
Sent: Thursday, November 02, 2006 11:04 AM
To: oracle-l@xxxxxxxxxxxxx
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/
_____
Other related posts:
