RE: Standby Database performance
- From: "CRISLER, JON A" <JC1706@xxxxxxx>
- To: kapil vaish <kapilvaish1@xxxxxxxxx>
- Date: Sun, 27 Nov 2011 18:58:34 +0000
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 [mailto:kapilvaish1@xxxxxxxxx]
Sent: Sunday, November 27, 2011 1:52 PM
To: CRISLER, JON A
Subject: Re: Standby Database performance
Hi ,
Can you point me to the Doc you are referring here ? Statspack on standby
database .
Thanks
Kapil
From: "CRISLER, JON A" <JC1706@xxxxxxx>
To: "kapilvaish1@xxxxxxxxx" <kapilvaish1@xxxxxxxxx>; Marcin Przepiorowski
<pioro1@xxxxxxxxx>
Cc: "deshpande.subodh@xxxxxxxxx" <deshpande.subodh@xxxxxxxxx>;
"oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
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: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On
Behalf Of kapil vaish
Sent: Thursday, November 24, 2011 11:46 AM
To: Marcin Przepiorowski
Cc: deshpande.subodh@xxxxxxxxx<mailto:deshpande.subodh@xxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Re: Standby Database performance
Hi,
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 .
Thanks
Kapil
________________________________
From: Marcin Przepiorowski <pioro1@xxxxxxxxx<mailto:pioro1@xxxxxxxxx>>
To: kapilvaish1@xxxxxxxxx<mailto:kapilvaish1@xxxxxxxxx>
Cc: "deshpande.subodh@xxxxxxxxx<mailto:deshpande.subodh@xxxxxxxxx>"
<deshpande.subodh@xxxxxxxxx<mailto:deshpande.subodh@xxxxxxxxx>>;
"oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>"
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
Sent: Thursday, November 24, 2011 5:20 AM
Subject: Re: Standby Database performance
On Wed, Nov 23, 2011 at 6:32 PM, kapil vaish
<kapilvaish1@xxxxxxxxx<mailto:kapilvaish1@xxxxxxxxx>> 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
> .
>
Hi,
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.
regards,
--
Marcin Przepiorowski
http://oracleprof.blogspot.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
