RE: Logical Standby Issues (cont.)

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bernard Polarski Sent: Thursday, July 27, 2006 3:49 AM
To: ramick_at_dotster.com
Cc: oracle-l_at_freelists.org
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_at_dotster.com] Sent: Thursday, 27 July, 2006 4:41 AM
To: strickland.mark_at_gmail.com; 'Ric Van Dyke' Cc: oracle-l_at_freelists.org
Subject: RE: Logical Standby Issues (cont.)

"some better settings for SQL Apply parameters"

Would you please post these and the reasons for them? TIA

--
http://www.freelists.org/webpage/oracle-l