RE: Standby Database performance

Date: Sun, 27 Nov 2011 18:58:34 +0000
Message-ID: <>

From Oracle Support - Installing and Using Standby Statspack in 11g [ID 454848.1]. Typically noboby bothers with statspack in 11g since AWR has many more metrics and capability, but this is a case where AWR does not work, but statspack does, for standby. You have to create dblinks from primary to standby to support this. I have done this twice now- it's a little bit tricky to set up but works ok. From: kapil vaish [] Sent: Sunday, November 27, 2011 1:52 PM
Subject: Re: Standby Database performance

Hi ,
Can you point me to the Doc you are referring here ? Statspack on standby database .


From: "CRISLER, JON A" <> To: "" <>; Marcin Przepiorowski <> Cc: "" <>; "" <> Sent: Sunday, November 27, 2011 8:08 AM
Subject: RE: Standby Database performance

I find it odd that you say DG did not scale. I would suggest going back and looking at DG again: if implemented properly, it should save a great deal of labor since it will manage archive gaps for you. Have you implemented statspack on the standby side? AWR reports will not be helpful if I recall correctly (i.e. they don't work on standby, at least for 10g), but there is a technote that shows you how to add statspack to a standby db, so it would give you additional metrics to help diagnose the problem.

How hard is your interconnect running ? We have found a lot of benefit in running 10g Ethernet with jumbo frames. Also, have your sysadmin / platform engineer check your HBA's to make sure they are optimially setup for high i/o to your SAN (or network connections if NFS). Look at things like proper multipath setup, proper queue lengths etc. Consider running Orion to benchark your disk i/o, and compare that to the primary side. Run against all LUN's / filesystems as well as some might perform worse than others.

-----Original Message-----
From:<> [<>] On Behalf Of kapil vaish Sent: Thursday, November 24, 2011 11:46 AM To: Marcin Przepiorowski
Cc:<>;<> Subject: Re: Standby Database performance

DG was not able to scale upto this level. We tried combination of parallel threads starting from 8 threads upto 64 . We got best perf with 32 threads. Will review the suggested docs .


From: Marcin Przepiorowski <<>> To:<> Cc: "<>" <<>>; "<>" <<>> Sent: Thursday, November 24, 2011 5:20 AM Subject: Re: Standby Database performance

On Wed, Nov 23, 2011 at 6:32 PM, kapil vaish <<>> wrote:
> Thanks for all the answers, awesome team. Here are some answers to your questions .
> Manual means thru scripts only, this is not Dataguard . There is no issue in shipping time, we hae plenty archived logs available on the standby server to apply. The lag becomes 30-40 hours in 3-4 days and will continue to grow . This DR is used for multiple purposes and we can not afford this much lag . This is 3 node RAC BTW.
> What we are trying to figure out is that if it is limitation of Oracle and it can not get any better or some other tunings can be checked. We are continously working with our storage/hw teams to take care of any contentions .


Why you are not using DataGuard ? in that case you can use real time apply and it can work better than applying archive logs. From other side - did you ever try to check why standby is performing poor ? you can use v$system/session_event and try to figure out where Oracle is loosing time. It can be issue with applying logs but it can be issue with DBWR doing checkpoint as well. I have seen case where MRP was able to apply log in 20 s but checkpoint took 40 s.


Marcin Przepiorowski

Received on Sun Nov 27 2011 - 12:58:34 CST

Original text of this message