RE: Logical Standby Issues (cont.)
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <bpolarsk@xxxxxxxxx>, <ramick@xxxxxxxxxxx>
- Date: Thu, 27 Jul 2006 12:58:54 -0500
I have no intention of starting some sort of flame war about this, but
it's quite backwards to say the Logical Standby apply process uses
Streams. The reality is that Streams uses the technology created for
Logical standby to do its work. Yes I'm aware that the Streams
documentation/collateral makes it appear otherwise.
The size of the SGA will affect the apply process, not necessarily
directly but it will impact it. The apply process (or service) is busy
creating SQL and to do this it is access two data dictionaries, one from
the Primary database and another from the Standby database. So the
indirect impact of the SGA size could be more library cache misses and
like events. And as you raise the number of parallel processes, those
will take up some SGA (and PGA) space as well. Can't get something for
nothing!
Ric Van Dyke
Hotsos Enterprises
-----------------------
Hotsos Symposium March 4-8, 2007. Be there.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bernard Polarski
Sent: Thursday, July 27, 2006 3:49 AM
To: ramick@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Logical Standby Issues (cont.)
Logical Standby uses streams, and you refer a problem in apply process.
I put some settings and explanations on streams settings, but only a few
are related to apply process, maybe it is of some help:
http://www.smenu.org/smenu_streams_set_parameters.html
I am not aware that SGA size has a real influence on apply process, it
has an indirect effect on capture process through percentage of SGA
allocated to logminer. I know for sure that aq_tm_processes does makes a
differences in apply process. However this influence is more on the
speed to purge the queue tables and your issue seems to be related to
single SQL.You may also consider the number of 'PARALLEL_MAX_SERVERS',
what is its current value ? Having more apply process will help.
Increasing the parallelism of the apply process will probably help the
more in your case.
But above all, since a bulk update in source DB is transformed in single
statement, you 'd better check the statement explain plan for FS on a
single update. For info, with redo of 100 meg on a sun450 4 cpu 8G ram,
I got an avg of 3 min apply for bulk update on logical STDBY. So your 4
min for 10meg seems to underline another issue than raw perf.
let us know what you find. It is interresting to compile evrything in
the domain of the apply process parameters.
B. Polarski
http://www.smenu.org
-----Original Message-----
From: ramick [mailto:ramick@xxxxxxxxxxx]
Sent: Thursday, 27 July, 2006 4:41 AM
To: strickland.mark@xxxxxxxxx; 'Ric Van Dyke'
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Logical Standby Issues (cont.)
"some better settings for SQL Apply parameters"
Would you please post these and the reasons for them?
TIA
Other related posts:
